Delete value from nested json - postgres

Question

I have the json block modeled below. I want to selectively delete individual blocks from my_items based on the id which is AAA and BBB in my sample. ie if I tried to delete the AAA block under my_items I would want tojust delete the {"id" : "AAA"} but if wanted to delete the BBB block it would delete the larger {"name" : "TestRZ", "id" : "BBB", "description" : ""} block.

I know I can use the #- to remove whole blocks like SELECT '{sample_json}'::jsonb #- '{my_items}' would purge out the whole my_items block. But I dont know how to use this to conditionally delete children under a parent block of json. I have also used code similar to this example to append data inside a nested structure by reading in the node of the nested structure cat-ing new data to it and rewriting it. UPDATE data SET value= jsonb_set(value, '{my_items}', value->'items' || (:'json_to_adds'), true) where id='testnofeed'.

But I dont know how to apply either of these methods to: 1)Delete data in nested structure using #- or 2)Do the same using `jsonb_set. Anyone have any guidance for how to do this using either of these(or another method).

{
  "urlName" : "testurl",
  "countryside" : "",
  "description" : "",
  "my_items" : [
     {
       "id" : "AAA"
     },
    {
      "name" : "TestRZ",
      "id" : "BBB",
      "description" : ""
    },
 ],
   "name" : "TheName"
 }

Data is stored in value jsonb. when I update I will be able to pass in a unique kind so that it only updates this json in one row in db.

  -- Table Definition
  CREATE TABLE "public"."data" (
 "id" varchar(100) NOT NULL,
 "kind" varchar(100) NOT NULL,
 "revision" int4 NOT NULL,
 "value" jsonb
  );

Mike Organek · Accepted Answer · 2020-07-29 16:07:30Z

2

This works in PostgreSQL 12 and later with jsonpath support. If you do not have jsonpath, then please leave a comment.

with data as (
  select '{
  "urlName" : "testurl",
  "countryside" : "",
  "description" : "",
  "my_items" : [
     {
       "id" : "AAA"
     },
    {
      "name" : "TestRZ",
      "id" : "BBB",
      "description" : ""
    }
 ],
   "name" : "TheName"
 }'::jsonb as stuff
)
select jsonb_set(stuff, '{my_items}',
                   jsonb_path_query_array(stuff->'my_items', '$ ? (@."id" <> "AAA")'))
  from data;

                                                                     jsonb_set                                                                     
---------------------------------------------------------------------------------------------------------------------------------------------------
 {"name": "TheName", "urlName": "testurl", "my_items": [{"id": "BBB", "name": "TestRZ", "description": ""}], "countryside": "", "description": ""}
(1 row)

To update the table directly, the statement would be:

update data
   set value = jsonb_set(value, '{my_items}',
                   jsonb_path_query_array(value->'my_items', 
                                          '$ ? (@."id" <> "AAA")'));

This works for versions before PostgreSQL 12:

with data as (
  select 1 as id, '{
  "urlName" : "testurl",
  "countryside" : "",
  "description" : "",
  "my_items" : [
     {
       "id" : "AAA"
     },
    {
      "name" : "TestRZ",
      "id" : "BBB",
      "description" : ""
    }
 ],
   "name" : "TheName"
 }'::jsonb as stuff
), expand as (
  select d.id, d.stuff, e.item, e.rn
    from data d
   cross join lateral jsonb_array_elements(stuff->'my_items') with ordinality as e(item, rn)
)
select id, jsonb_set(stuff, '{my_items}', jsonb_agg(item order by rn)) as new_stuff
  from expand
 where item->>'id' != 'AAA'
 group by id, stuff;

 id |                                                                     new_stuff                                                                     
----+---------------------------------------------------------------------------------------------------------------------------------------------------
  1 | {"name": "TheName", "urlName": "testurl", "my_items": [{"id": "BBB", "name": "TestRZ", "description": ""}], "countryside": "", "description": ""}
(1 row)

The direct update for this is a little more involved:

with expand as (
  select d.id, d.value, e.item, e.rn
    from data d
   cross join lateral jsonb_array_elements(value->'my_items')
         with ordinality as e(item, rn)
), agg as (
  select id, jsonb_set(value, '{my_items}', jsonb_agg(item order by rn)) as new_value
    from expand
   where item->>'id' != 'AAA'
   group by id, value
)
update data
   set value = agg.new_value
  from agg
 where agg.id = data.id;

edited Jul 29, 2020 at 16:07

answered Jul 29, 2020 at 15:08

Mike Organek

12.6k3 gold badges15 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

personalt Over a year ago

Will be back in front of computer in 10 minutes to try it out but do you know how I would turn this in to an update for {my_items}. Woulld I modify this to update the whole data blob of just the {my_items} part? Thanks

Mike Organek Over a year ago

@personalt You have to update the entire jsonb. If you put your table definition in your question and indicate whether you have jsonpath, I can put an example in my answer.

personalt Over a year ago

@nike-organek - Thanks. I updated the question with the table definition. For jsonbpath do you mean the online evaluator or goessner.net/articles/JsonPath or something else? I can install whatever is needed but my plan is run update database via psql via command line script if that at all matters

Mike Organek Over a year ago

@personalt I was specifically talking about jsonb_path_query_array(). That is a recent addition to PostgreSQL. I added an example to the PostgreSQL 12 example. For an earlier version, you will need to do an UPDATE FROM.

Mike Organek Over a year ago

@personalt So long as it's 12 or later, you can use the shorter form of the update in my answer.

Collectives™ on Stack Overflow

Delete value from nested json - postgres

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related