Re: BUG #15140: Incorrect jsonb_set behavoir

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: https://www.postgresql.org/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