How to update multiple json fields at root level with Postgres?

Question

I am trying to update the fields age and city of one json feed using:

select jsonb_set(d,'{0,age,city}',d || '{"age":30,"city":"los angeles"}') 
from (
  values ('{"name":"john", "age":26,"city":"new york city"}'::jsonb)
) t(d);

but what I get back is:

{"age": 26, "city": "new york city", "name": "john"}

instead of the expected:

{"age": 30, "city": "los angeles", "name": "john"}

that means none of the wanted fields have been updated.

I have already looked at:

postgres jsonb_set multiple keys update

and went through the relative documentation but I cannot get it right. Any help?

klin · Accepted Answer · 2018-11-23 19:50:06Z

5

From the documentation:

All the items of the path parameter of jsonb_set as well as jsonb_insert except the last item must be present in the target.

The path given in the query does not meet the above condition. Actually, jsonb_set() does not work for objects at the root level, and the only way is to use the || operator:

select d || '{"age":30,"city":"los angeles"}'
from (
    values ('{"name":"john", "age":26,"city":"new york city"}'::jsonb)
) t(d);

                      ?column?                      
----------------------------------------------------
 {"age": 30, "city": "los angeles", "name": "john"}
(1 row)

Maybe it would be logical that you could use an empty path

select jsonb_set(d, '{}', d || '{"age":30,"city":"los angeles"}')

Unfortunately, jsonb developers did not provide such a possibility.

answered Nov 23, 2018 at 19:50

klin

123k15 gold badges240 silver badges262 bronze badges

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

Collectives™ on Stack Overflow

How to update multiple json fields at root level with Postgres?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related