PostgreSQL - How to update nested JSONB elements

Question

I found some information, but some of it was not clearly explained or the examples were incomprehensible. This example was successfully executed on PostgreSQL 12. The requirement is to partially change a JSON structure with PostgreSQL. JSONB is used as data type.

The data structure has the following appearance

{
   "id":1,
   "person":{
      "name":"fred"
   }
}

The data record is to be changed. The property $.person.name has to be changed and $.person.gender will be added.

select '{"id":1, "person" : {  "name" : "fred" } }'::jsonb || '{"person": { "name" : "lisa", "gender" : "F" }}'::jsonb

The result contains the corresponding modifications.

{
   "id":1,
   "person":{
      "name":"lisa",
      "gender":"F"
   }
}

The update statement for the JSONB column data looks like this

update t_person set data = data || '{"person": { "name" : "lisa", "gender" : "F" }}'::jsonb

Look at Brendan Graetz blog https://blog.bguiz.com/2017/json-merge-postgresql/ for a resursive json merge. Thanks. — Ben Asmussen, Mar 21 '20 at 13:53

score 0 · Answer 1 · answered Mar 21 '20 at 10:42

You could just reset the whole person attribute with jsonb_set():

select jsonb_set(
    '{"id":1, "person" : { "name" : "fred" } }'::jsonb,
    '{person}',
    '{ "name":"lisa", "gender":"F" }'::jsonb
)

Demo on DB Fiddle:

| jsonb_set                                            |
| :--------------------------------------------------- |
| {"id": 1, "person": {"name": "lisa", "gender": "F"}} |

PostgreSQL - How to update nested JSONB elements

1 Answers1