Re: jsonb_set for nested new item?

Am 23.09.16 um 16:14 schrieb Deven Phillips:

> Is there a way to set a nested element for which the parent paths do not
> yet exist?
>
> For example, if I have a JSONB value called 'data':
>
> {
> "foo": "bar"
> }
>
> and run
>
> jsonb_set(data, {'boo', 'baz'}, 'newvalue')
>
> I would expect the output to be:
>
> {
> "foo": "bar",
> "boo": {
> "baz": "newvalue"
> }
> }
I don't know why jsonb_set() does not simply allow
SELECT jsonb_set('{"foo": "bar"}'::jsonb, '{boo,baz}', '"newvalue"');
even not in PostgreSQL 9.6.

The trick for now is to use JSONB operators to get the (maybe existing)
old 1st-level value and insert/overwrite the 2nd-level key.

-- 1st-level key 'boo' does not exist
WITH jsonb_table AS (SELECT '{"foo": "bar"}'::jsonb AS jsonb_column)
SELECT jsonb_column, jsonb_set(jsonb_column, '{boo}',
coalesce(jsonb_column->'boo', '{}) || '{"baz": "newvalue"}') FROM
jsonb_table;

jsonb_column | jsonb_set
----------------+--------------------------------------------
{"foo": "bar"} | {"boo": {"baz": "newvalue"}, "foo": "bar"}

-- 2nd-level key 'baz' does not exist (but other keys)
WITH jsonb_table AS (SELECT '{"foo": "bar", "boo": {"otherkey":
"othervalue"}}'::jsonb AS jsonb_column)
SELECT jsonb_column, jsonb_set(jsonb_column, '{boo}',
coalesce(jsonb_column->'boo', '{}') || '{"baz": "newvalue"}') FROM
jsonb_table;

jsonb_column
| jsonb_set
---------------------------------------------------+----------------------------------------------------------------------
{"boo": {"otherkey": "othervalue"}, "foo": "bar"} | {"boo": {"baz":
"newvalue", "otherkey": "othervalue"}, "foo": "bar"}

-- 2nd-level key 'baz' exists (and other keys)
WITH jsonb_table AS (SELECT '{"foo": "bar", "boo": {"baz": "oldvalue",
"otherkey": "othervalue"}}'::jsonb AS jsonb_column)
SELECT jsonb_column, jsonb_set(jsonb_column, '{boo}',
coalesce(jsonb_column->'boo', '{}') || '{"baz": "newvalue"}') FROM
jsonb_table;

jsonb_column
| jsonb_set
----------------------------------------------------------------------+----------------------------------------------------------------------
{"boo": {"baz": "oldvalue", "otherkey": "othervalue"}, "foo": "bar"} |
{"boo": {"baz": "newvalue", "otherkey": "othervalue"}, "foo": "bar"}

Please note that the actual jsonb_set() call is always the same, only
jsonb_column changes to show all possible cases.
The 2 JSONB literals empty/new in the jsonb_set() call need no casting,
just the jsonb_column for the -> operator and jsonb_set() to work
(already done in the WITH clause).

The WITH clause is just there for this example, otherwise you would have
to duplicate the same value. Just use your existing JSONB column instead.

JSONB sorts the keys in alphanumerical order, so don't get confused by
the insert positions.