Postgres jsonb_set multiple nested fields

Question

I have DB table with a jsonb column that has an entity, with nested child entities. Let's say we have:

SELECT jsonb_set('{"top": {"nested": {"leaf" : 1}}}', '{top,nested,leaf}', '2');

Which work's fine by updating top.nested.leaf to 2.

But what if we wanted to do multiple fields, such as:

SELECT jsonb_set('{"top": {"nested": {"leaf" : 1}, "other_nested": {"paper": 0}}}', '[{top,nested,leaf}, {top,other_nested,paper}]', '[2, 2]');

The above does not 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?

Answer 1

https://www.postgresql.org/docs/current/static/functions-json.html

jsonb_set(target jsonb, path text[], new_value jsonb[, create_missing boolean])

neither path, nor new value can't have several values. you have to run it twice for wanted result, eg:

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'
);