CODEWITHSUNDEEP
sqlpostgresqljsonbpostgresql-9.5
Vlad Ankudinov
Vlad Ankudinov

Reputation: 2046

Recursive update jsonb object in PostgreSQL v9.5+

I use jsonb_set to partially update my jsonb object in postgres.

This what the doc says about this function.

 jsonb_set(
   target jsonb,           # The jsonb value you're amending.
   path text[],            # The path to the value you wish to add to or change, represented as a text array.
   new_value jsonb,        # The new object, key : value pair or array value(s) to add to or change.
   create_missing boolean  # An optional field that, if true (default), creates the value if the key doesn't already exist.
                           #   If false, the path must exist for the update to happen, or the value won't be updated.
 )

I thought that create_missing (which is true by default) will cause to unexisting path appear in my jsonb object but it seem like this works only on one last step (e.g. not recursive).

the query

UPDATE myScheme.myTable SET data = jsonb_set(data, $1, $2, true) where id = $3;

will fail if $1 = {foo,bar,baz} and my current data = {foo: {}}

The question is: How to update my jsonb object with recursive creating unexisting subobjects in PostgreSQL v9.5+?

Upvotes: 4

Views: 773

Answers (1)

Dzmitry Savinkou
Dzmitry Savinkou

Reputation: 5190

You can use something like that, where instead of t put the name of your table:

UPDATE t SET data = jsonb_set(data,'{foo,bar}','{"baz":{"key1":{},"key2":[2],"key3":3,"key4":"val4"}}'::JSONB);

Result will be:

SELECT jsonb_pretty(data) FROM t;

          jsonb_pretty          
--------------------------------
 {                             +
     "foo": {                  +
         "bar": {              +
             "baz": {          +
                 "key1": {     +
                 },            +
                 "key2": [     +
                     2         +
                 ],            +
                 "key3": 3,    +
                 "key4": "val4"+
             }                 +
         }                     +
     }                         +
 }
(1 row)

With this approach (when all structure is defined inside new_value parameter) you are free to create any kinds of nested elements (arrays, nested documents, string or integer values); On the other side, to do it in path parameter will be very tricky.

Upvotes: 1

Related Questions