Remove an object from a JSON array

Question

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"
            }
    ]

Erwin Brandstetter · Accepted Answer · 2023-03-15T12:26:42.417

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:

PostgreSQL unnest() with element number

Thank you for very well explanation. I have one question regarding the second query. What do you mean by "to only remove the first match" you mean in case of duplicated id's in the json? — JN_newbie, Mar 15 '23 at 12:19
@JN_newbie Yes, observe the effect in the two fifdles I added. — Erwin Brandstetter, Mar 15 '23 at 12:20
Thank you Erwin I have just performed the tests and understand the concept behind as what you have mentioned in your answer. Thank you for the detail answer — JN_newbie, Mar 15 '23 at 12:26

SelVazi · Answer 2 · 2023-03-15T15:15:27.303

1

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

edited Mar 15 '23 at 15:15

answered Mar 15 '23 at 11:13

SelVazi

10,028
2
13
29

@JN_newbie: Much is wrong with this answer. 1.) It updates all rows, including those that don't change. Very expensive, without need. 2.) The subquery is uncorrelated, which makes it incorrect, on top of *very* expensive. 3.) The explanation does not apply. Negative integers have nothing to do with this query. – Erwin Brandstetter Mar 15 '23 at 11:42
@ErwinBrandstetter I do agree with with the statement negative integers have nothing to do with this query. I just realised after performing some tests it will update all the rows as Outer where clause is not provided. – JN_newbie Mar 15 '23 at 12:03
1

@JN_newbie Even more importantly, repeating `users` in the subquery effectiely makes it uncorrelated and completely breaks the query. – Erwin Brandstetter Mar 15 '23 at 12:15
Thanks @ErwinBrandstetter my query wasn't correct when there is no element found the whole json get removed, the third point I think you both misunderstand, the operator `-` accept negative and positive indexes, this is just an info how this operator can be used – SelVazi Mar 15 '23 at 15:07
@SelVazi: This fixes most problems. But it's much better to update only rows that actally change. Then you also don't need coalesce. – Erwin Brandstetter Mar 15 '23 at 19:53

score -1 · Answer 3 · answered Mar 15 '23 at 11:28

You can achieve below mentioned query once try it

 Declare @json Varchar(max)= '[{"id": 100,"name": "testOne","settings": "settingOne"},{"id": 101,"name": "testTwo","settings": "settingTwo"}]'
set @json=(Select * from openJson(@json,'$')with(id int,name varchar(50),settings varchar(50))
 where id!=101 for json path)
 select @json

Remove an object from a JSON array

3 Answers3