Re: BUG #15140: Incorrect jsonb_set behavoir

The following bug has been logged on the website:

Bug reference: 15140
Logged by: Ivan Panchenko
Email address: i(dot)panchenko(at)postgrespro(dot)ru
PostgreSQL version: 10.3
Operating system: any
Description:

First. Impossible to create a nested structure for a path with more than one
new keys.

postgres => select jsonb_set('{}'::jsonb, array['x', 'y'],
to_jsonb(1::text), true);
jsonb_set
-----------
{}
(1 строка)

Expected {"x": { "y" : "1" }}

Second. Setting a NULL value nullifies the whole JSON

postgres=> select jsonb_set('{}'::jsonb, array['x'], null, true);
jsonb_insert
--------------

(1 строка)

Expected { "x" : null }

> On 2 April 2018 at 12:36, PG Bug reporting form <noreply(at)postgresql(dot)org> wrote:
> The following bug has been logged on the website:
>
> Bug reference: 15140
> Logged by: Ivan Panchenko
> Email address: i(dot)panchenko(at)postgrespro(dot)ru
> PostgreSQL version: 10.3
> Operating system: any
> Description:
>
> First. Impossible to create a nested structure for a path with more than one
> new keys.
>
> postgres => select jsonb_set('{}'::jsonb, array['x', 'y'],
> to_jsonb(1::text), true);
> jsonb_set
> -----------
> {}
> (1 строка)
>
> Expected {"x": { "y" : "1" }}
>
> Second. Setting a NULL value nullifies the whole JSON
>
> postgres=> select jsonb_set('{}'::jsonb, array['x'], null, true);
> jsonb_insert
> --------------
>
> (1 строка)
>
> Expected { "x" : null }

As far as I remember, these are not really bugs, but documented behavior.

About the first one, here is [1]:

All the items of the path parameter of jsonb_set as well as jsonb_insert
except the last item must be present in the target. If create_missing is
false, all items of the path parameter of jsonb_set must be present. If
these conditions are not met the target is returned unchanged.

Although I agree this can be confusing, and we may want to change this.

About the second one, `jsonb_set` is defined as a strict function, which means
`null` arguments will produce null as a result. To avoid this you can do:

=# select jsonb_set('{}'::jsonb, array['x'], 'null', true);
jsonb_set
-------------
{"x": null}
(1 row)

1: /docs/devel/static/functions-json.html

Hi,

02.04.2018 15:04, Dmitry Dolgov пишет:
>> On 2 April 2018 at 12:36, PG Bug reporting form <noreply(at)postgresql(dot)org> wrote:
>> The following bug has been logged on the website:
>>
>> Bug reference: 15140
>> Logged by: Ivan Panchenko
>> Email address: i(dot)panchenko(at)postgrespro(dot)ru
>> PostgreSQL version: 10.3
>> Operating system: any
>> Description:
>>
>> First. Impossible to create a nested structure for a path with more than one
>> new keys.
>>
>> postgres => select jsonb_set('{}'::jsonb, array['x', 'y'],
>> to_jsonb(1::text), true);
>> jsonb_set
>> -----------
>> {}
>> (1 строка)
>>
>> Expected {"x": { "y" : "1" }}
>>
>> Second. Setting a NULL value nullifies the whole JSON
>>
>> postgres=> select jsonb_set('{}'::jsonb, array['x'], null, true);
>> jsonb_insert
>> --------------
>>
>> (1 строка)
>>
>> Expected { "x" : null }
> As far as I remember, these are not really bugs, but documented behavior.
>
> About the first one, here is [1]:
>
> All the items of the path parameter of jsonb_set as well as jsonb_insert
> except the last item must be present in the target. If create_missing is
> false, all items of the path parameter of jsonb_set must be present. If
> these conditions are not met the target is returned unchanged.
>
> Although I agree this can be confusing, and we may want to change this.
Yes, I think it would be good.

Now I see it in the documentation, but unfortunately, it  is  a separate
note, which is not referenced from the main jsonb_set description which 
says,

        ...with /|new_value|/ added if /|create_missing|/ is true (
default is |true|) and the item designated by /|path|/ does not exist.

> About the second one, `jsonb_set` is defined as a strict function, which means
> `null` arguments will produce null as a result. To avoid this you can do:
>
> =# select jsonb_set('{}'::jsonb, array['x'], 'null', true);
> jsonb_set
> -------------
> {"x": null}
> (1 row)
>
> 1: /docs/devel/static/functions-json.html
Thanks for the workaround.  Nevertheless, this behavior looks
counter-intuitive, and I would vote for removing strictness from this
function.

Regards,
Ivan

"Ivan E. Panchenko" <i(dot)panchenko(at)postgrespro(dot)ru> writes:
> Thanks for the workaround.  Nevertheless, this behavior looks
> counter-intuitive, and I would vote for removing strictness from this
> function.

That would amount to assuming that SQL NULL and JSON 'null' are
interchangeable, which isn't the case elsewhere, and I'm not sure
we want it to be the case here. You can get the behavior you're
looking for with jsonb_set(..., COALESCE(fieldvalue, 'null')).

regards, tom lane