Обсуждение: jsonb_set for nested new item?

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
--
Best regards,
Vitaly Burovoy

Thanks for the confirmation. Unfortunately, I will need to handle more complex situations. I will look into creating a recursive subroutine to handle things.

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.

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"}

> 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