Postgres jsonb_set multiple nested fields
I have a DB table with a jsonb column that has an entity, with nested children. Let's say we have:
SELECT jsonb_set('{"top": {"nested": {"leaf" : 1}}}', '{top,nested,leaf}', '2');
Which works great by updating top.nested.leaf
to 2.
But what if we want to make multiple fields, for example:
SELECT jsonb_set('{"top": {"nested": {"leaf" : 1}, "other_nested": {"paper": 0}}}', '[{top,nested,leaf}, {top,other_nested,paper}]', '[2, 2]');
The above doesn't work and says:
ERROR: malformed array literal: "[{top,nested,leaf}, {top,other_nested,paper}]"
LINE 1: ...": {"leaf" : 1}, "other_nested": {"paper": 0}}}', '[{top,nes...
^
DETAIL: "[" must introduce explicitly-specified array dimensions.
Any ideas?
source to share
https://www.postgresql.org/docs/current/static/functions-json.html
jsonb_set(target jsonb, path text[], new_value jsonb[, create_missing boolean])
neither the path nor the new value can have multiple values. you need to run it twice for the desired result, e.g .:
SELECT jsonb_set(
'{"top": {"nested": {"leaf" : 1}, "other_nested": {"paper": 0}}}'
, '{top,nested,leaf}'
, '2'
);
SELECT jsonb_set(
'{"top": {"nested": {"leaf" : 1}, "other_nested": {"paper": 0}}}'
, '{top,other_nested,paper}'
, '2'
);
source to share