Re: jsonb_set for nested new item?

> On Sep 23, 2016 5:12 PM, "Vitaly Burovoy" <vitaly(dot)burovoy(at)gmail(dot)com> wrote:
> 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

On 9/23/16, Deven Phillips <deven(dot)phillips(at)gmail(dot)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