Remove an object from a JSON array

From table rows in my PostgreSQL 12.8 database, I am trying to remove an object from an array, but instead it is deleting whole array of objects from the table.

settings column holds below array of objects:

    [
        {
            "id": 100,
            "name": "testOne",
            "settings": "settingOne"
        },
        {
            "id": 101,
            "name": testTwo,
            "settings": "settingTwo"
        },
]

I have three rows in the users table with the column settings of type jsonb that holds an array of objects.

I want to delete the object with id = 101 for all users. I tried the below query:

update users
set settings = 
    jsonb_set(settings , '{settings}', (settings->'id') - (select distinct position-1 from users, jsonb_array_elements(settings) 
    with ordinality arr(elem, position) WHERE elem->>'id' = '101')::int)

By executing the above query it is deleting everything from the settings. How can I modify the above query in order to achieve the below result?

   [
            {
                "id": 100,
                "name": "testOne"
                "settings": "settingOne"
            }
    ]

This should do it:

UPDATE users u
SET    settings = (SELECT jsonb_agg(a.elem)
                   FROM   jsonb_array_elements(u.settings) AS a(elem)
                   WHERE  (a.elem ->> 'id' = '101') IS NOT TRUE)
WHERE  u.settings @> '[{"id":101}]';

fiddle

The added outer WHERE clause makes sure only rows are updated that actually change. You may have additional filters ...

This removes all objects from the array that have "id": 101. (There might be more than one.)

To only remove the first match:

UPDATE users u
SET    settings = u.settings
                - (SELECT a.ord::int-1
                   FROM   jsonb_array_elements(u.settings) WITH ORDINALITY a(elem, ord)
                   WHERE  a.elem ->> 'id' = '101'
                   LIMIT  1)
WHERE  u.settings @> '[{"id":101}]';

fiddle

Notably, do not repeat the table name in the correlated subquery, that would be expensive bloat - and require addtitional filters to link back to the updated table.

-1 is there because Postgres ordinality numbers start with 1 while JSON array elements start with 0.

About WITH ORDINALITY:

• https://stackoverflow.com/questions/8760419/postgresql-unnest-with-element-number/8767450#8767450

Use operator - only, no need to use jsonb_set.

This is an optimized solution since it call where only once. if no data found it will return the settings length this way we will be sure nothing will be removed.

update users
set settings = 
    settings  - (
    select coalesce(
       (select position::int-1 as index
        from jsonb_array_elements(settings) 
        with ordinality arr(elem, position) 
        WHERE elem->>'id' = '100'
        LIMIT 1
       ), 
       jsonb_array_length(settings))
   )

The operator - Delete the array element with specified index (Negative integers count from the end).

Demo here