Re: BUG #15140: Incorrect jsonb_set behavoir

Hi,

On 2 April 2018 at 12:36, PG Bug reporting form <noreply@postgresql.org> wrote:
The following bug has been logged on the website:

Bug reference:      15140
Logged by:          Ivan Panchenko
Email address:      i.panchenko@postgrespro.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