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?

I have also tried the following: CREATE TABLE test(data jsonb); INSERT INTO test(data) VALUES('{"top": {"nested": {"leaf" : 1, "paper": 10}, "other_nested": {"paper": 0, "leaf": 0}}}'::jsonb); UPDATE test SET data = data || '{"top": {"nested": {"leaf": 2}, "other_nested": {"paper": 2}}}'; SELECT data FROM test; by using the concatenation, which also did not work. — KyleMulder
– KyleMulder, Commented Aug 3, 2017 at 10:39

Vao Tsun · Accepted Answer · 2017-08-03 11:57:11Z

10

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

answered Aug 3, 2017 at 11:57

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

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

3 Comments

KyleMulder Over a year ago

Is there a way to do this with concatenation as per my comment on my opening question?

Vao Tsun Over a year ago

if you want it in one statement, better try jsonb_set(jsonb_set())

KyleMulder Over a year ago

Need this in an implementation where the jsonb_set or the || concatenation get's buit up by passing a bunch of field's with their new object/value in.

epox · Accepted Answer · 2023-03-22 21:06:19Z

3

JSON_SET another JSON_SET's (sub)result

'joson_set' result type is JsonB with the altered value. So you should be able to nest one call into another:

SELECT jsonb_set(
         json_set('{"top": {"nested": {"leaf" : 1}, "other_nested": {"paper": 0}}}',
                  '{top,nested,leaf}',   -- <-- path1
                  '2'                    -- <-- new value1
                  ),                     -- <-- returns JsonB
          '{top,other_nested,paper}',  -- <-- path2
          '2');                        -- <-- new value2

answered Mar 22, 2023 at 21:06

epox

11.4k2 gold badges67 silver badges43 bronze badges

Comments

Nik · Accepted Answer · 2023-08-18 17:40:07Z

Found a bit more elegant and scalable solution and wanted to share.

Given: the following complex jsonb object inside tbl.data:

{
  top: {
    nested1: { leaf: 1 },
    nested2: { flower1: 0, flower2: 0 },
    nested4: { apple: false }
  },
  bottom: { roots: 'lots' }
}

Problem: need to change values of .top.nested1.leaf = 2 and .top.nested2.flower1 = 2 and even add something like .top.nested3.branch = 1 in one transaction.

Solution: This can be achieved the following way:

WITH tbl as (SELECT '{ "top": { "nested1": { "leaf": 1 }, "nested2": { "flower1": 0, "flower2": 0 }, "nested4": { "apple": false } }, "bottom":{ "roots": "lots" } }'::jsonb AS data)
SELECT jsonb_set(
    data,
    '{top}',
    data->'top' || jsonb_build_object(
        'nested1',
        jsonb_set(data#>'{top,nested1}','{leaf}', to_jsonb(2)),
        'nested2',
        jsonb_set(data#>'{top,nested2}','{flower1}', to_jsonb(2)),
        'nested3',
        jsonb_set('{}','{branch}', to_jsonb(1))
    )
) FROM tbl;

Result: only relevant fields changed.

{
  top: {
    nested1: { leaf: 2 },
    nested2: { flower1: 2, flower2: 0 },
    nested3: { branch: 1 },
    nested4: { apple: false }
  },
  bottom: { roots: 'lots' }
}

Explanation: the keys here are two powerful yet still convenient operations: jsonb_build_object() and jsonb concatenation ||.

top jsonb_set() surgically replaces value of the top-most object field that is going to change with new jsonb value
this new jsonb value is constructed from the original value data->'top' by concatenating (||) it with new object that contains only fields that change with fully reconstructed values. This results in shallow replacement of the fields with same name and addition of fields that did not exist.
since values of the new object are objects themselves, we use jsonb_set() to fully extract those objects and change only related fields inside at once.

P.S.> It's probably worth moving this question to dba.stackexchange.com if possible.

Collectives™ on Stack Overflow

Postgres jsonb_set multiple nested fields

3 Answers 3

3 Comments

JSON_SET another JSON_SET's (sub)result

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

JSON_SET another JSON_SET's (sub)result

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related