Re: jsonb_set for nested new item?

On 9/23/16, Deven Phillips <deven(dot)phillips(at)gmail(dot)com> wrote:
> On Fri, Sep 23, 2016 at 10:14 AM, Deven Phillips <deven(dot)phillips(at)gmail(dot)com>
> wrote:
>
>> 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"
>> }
>> }
>>
>> But that does not appear to work..
>>
>> Any suggestions would be appreciated.
>>
>
> Actually, it looks like I have to create all of the parent objects first
> before it would work... Is that correct?
>
> Deven

Yes, you are correct. The documentation[1] says:
> Returns target ... with new_value added if create_missing is true ...
> and the item designated by path does not exist.

There is nothing about a "path", only about a "new_value".
I think it is because of impossibility to understand what intermediate
objects are needed to be created (objects or arrays).

There is no easy way to create variadic intermediate objects, but in
your particular case (only one subobject) it can be like:

SELECT
jsonb_set(
CASE
WHEN DATA ? 'boo'
THEN DATA
ELSE jsonb_set(DATA, array['boo'], '{}')
END,
'{boo,baz}'::text[],
'"newvalue"'
)
FROM (VALUES('{"foo": "bar"}'::jsonb)) AS t(data)

[1] https://www.postgresql.org/docs/devel/static/functions-json.html
--
Best regards,
Vitaly Burovoy