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?

Answer 1

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.