Thread: 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.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 }

> 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.

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

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

"Ivan E. Panchenko" <i.panchenko@postgrespro.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