Question 89389 in PostgreSQL Question posted in
The official documentation can be found here.

Remove an object from a JSON array – Postgresql

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

3

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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:

    Login or Signup to reply.
  3. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search