Search Postgresql Archives

Re: jsonb_set for nested new item?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 9/23/16, Deven Phillips <deven.phillips@xxxxxxxxx> wrote:
> On Fri, Sep 23, 2016 at 10:14 AM, Deven Phillips <deven.phillips@xxxxxxxxx>
> 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


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux