Re: jsonb_set for nested new item?

> On Sep 23, 2016 5:12 PM, "Vitaly Burovoy" <vitaly.burovoy@gmail.com> wrote:
> On 9/23/16, Deven Phillips <deven.phillips@gmail.com> wrote:
>> On Fri, Sep 23, 2016 at 10:14 AM, Deven Phillips
>> <deven.phillips@gmail.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

On 9/23/16, Deven Phillips <deven.phillips@gmail.com> wrote:
> Thanks for the confirmation. Unfortunately, I will need to handle more
> complex situations. I will look into creating a recursive subroutine to
> handle things.

In such a case the best way is to create a function:
CREATE OR REPLACE FUNCTION jsonb_set_recursive(data jsonb, path
text[], new_value jsonb)
RETURNS jsonb
LANGUAGE plpgsql AS
$$
DECLARE
    chk_path text[];
    cur_path text[];
    cur_idx text;
    cur_value jsonb;
    def_obj jsonb default '{}'::jsonb;
BEGIN
    chk_path := path[:array_length(path, 1) - 1];
    IF (data #> chk_path IS NULL) THEN  -- fast check
        FOREACH cur_idx IN ARRAY chk_path
        LOOP
            cur_path := cur_path || cur_idx;
            cur_value = data #> cur_path;

            IF (cur_value IS NULL) THEN
                data = jsonb_set(data, cur_path, def_obj);
            ELSIF (jsonb_typeof(cur_value) NOT IN ('object', 'array')) THEN
                RAISE EXCEPTION 'path element by % is neither object
nor array', cur_path;
            END IF;
        END LOOP;
    ELSIF (jsonb_typeof(data #> chk_path) NOT IN ('object', 'array')) THEN
        RAISE EXCEPTION 'path element by % is neither object nor
array', chk_path;
    END IF;
    RETURN jsonb_set(data, path, new_value);
END
$$
STABLE;

and use it:
postgres=# \x
Expanded display is on.
postgres=# SELECT
postgres-#     jsonb_set_recursive(data,'{xoo}'::text[],'"newvalue"'),
postgres-#     jsonb_set_recursive(data,'{boo,baz}'::text[],'"newvalue"'),
postgres-#     jsonb_set_recursive(data,'{boo,baG,z,n,2,a}'::text[],'"newvalue"')
postgres-# FROM (VALUES('{"boo": {"baz": "oldvalue"}, "foo":
"bar"}'::jsonb)) AS t(data);
-[ RECORD 1 ]-------+------------------------------------------------------------------------------------------
jsonb_set_recursive | {"boo": {"baz": "oldvalue"}, "foo": "bar",
"xoo": "newvalue"}
jsonb_set_recursive | {"boo": {"baz": "newvalue"}, "foo": "bar"}
jsonb_set_recursive | {"boo": {"baG": {"z": {"n": {"2": {"a":
"newvalue"}}}}, "baz": "oldvalue"}, "foo": "bar"}


but if a jsonb object has a non-array and non-object value by a path,
exception is raised (you can change it by modifying the function
above):

postgres=# SELECT
postgres-#     jsonb_set_recursive(data,'{boo,baz,z,n,2,a}'::text[],'"newvalue"')
postgres-# FROM (VALUES('{"boo": {"baz": "oldvalue"}, "foo":
"bar"}'::jsonb)) AS t(data);
ERROR:  path element by {boo,baz} is neither object nor array
CONTEXT:  PL/pgSQL function jsonb_set_recursive(jsonb,text[],jsonb)
line 19 at RAISE

--
Best regards,
Vitaly Burovoy