Re: jsonb_set for nested new item?

From: René Leonhardt <rene(dot)leonhardt(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Cc: Deven Phillips <deven(dot)phillips(at)gmail(dot)com>
Subject: Re: jsonb_set for nested new item?
Date: 2016-09-24 11:44:00
Message-ID: 110486fb-6dd3-93c6-edc7-e65ddade241a@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Please note that only the first case is not supported in jsonb_set(),
which was a very important addition to PostgreSQL 9.5.
If this case would be added, a simple call with the same path
'{boo,baz}' and '"newvalue"' would always be sufficient.

Goal: first path level (boo) does not exist, create subelement, but if
there is a second path level (baz) requested, create a subdict instead
of just the given value (newvalue).

-- Creating a new 2nd-level dict with a missing 1st-level key/dict just
in the path does not work
SELECT jsonb_set('{"foo": "bar"}'::jsonb, '{boo,baz}', '"newvalue"');

jsonb_set
----------------
{"foo": "bar"}

-- Only with the complete subdict as value the new 1st-level key 'boo'
is added(so the caller has to know if the key is missing)
SELECT jsonb_set('{"foo": "bar"}'::jsonb, '{boo}', '{"baz": "newvalue"}');

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

-- All other cases work fine (when the 1st-level key 'boo' already exists)
SELECT jsonb_set('{"foo": "bar", "boo": {"otherkey":
"othervalue"}}'::jsonb, '{boo,baz}', '"newvalue"');

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

SELECT jsonb_set('{"foo": "bar", "boo": {"baz": "oldvalue", "otherkey":
"othervalue"}}'::jsonb, '{boo,baz}', '"newvalue"');

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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Vitaly Burovoy 2016-09-24 13:31:16 Re: jsonb_set for nested new item?
Previous Message René Leonhardt 2016-09-24 11:01:00 Re: jsonb_set for nested new item?