Thread: BUG #13961: Unexpected behaviour of JSONB_SET if the new_value is null

The following bug has been logged on the website:

Bug reference:      13961
Logged by:          Pravin Carvalho
Email address:      pravin@gida.in
PostgreSQL version: 9.5.1
Operating system:   All
Description:

If the new_value is null, JSONB_SET evaluates to null. This is not mentioned
in the documentation and intuitively I would expect this set the value of
the key at the specified path as null.
eg. SELECT jsonb_set('{"f1":1,"f2":2}', '{f2}',null);

I was using JSONB_SET to update a JSONB column where the new_value was the
result of the query and this could have led to loss of data.

On Mon, Feb 15, 2016 at 1:02 AM, <pravin@gida.in> wrote:

> The following bug has been logged on the website:
>
> Bug reference:      13961
> Logged by:          Pravin Carvalho
> Email address:      pravin@gida.in
> PostgreSQL version: 9.5.1
> Operating system:   All
> Description:
>
> If the new_value is null, JSONB_SET evaluates to null. This is not
> mentioned
> in the documentation and intuitively I would expect this set the value of
> the key at the specified path as null.
> eg. SELECT jsonb_set('{"f1":1,"f2":2}', '{f2}',null);
>
> I was using JSONB_SET to update a JSONB column where the new_value was th=
e
> result of the query and this could have led to loss of data.
>
>
=E2=80=8BWorking as designed - though in retrospect I don't see why this pa=
rticular
function had to be defined "STRICT".

You will need to use:
COALESCE((SELECT ...), 'null') if you want to store a JSON null when the
subquery results in an SQL being returned.  The two are not the same thing.

The technical answer is that with jsonb_set defined as being "NULL ON NULL
INPUT (a.k.a., STRICT)" =E2=80=8Bas soon as any of its arguments are SQL NU=
LL the
executor replaces the function call with an SQL NULL without ever
attempting to execute the function.

I think that this point could be better made in the documentation=E2=80=8B =
for
these functions.  It is alluded to in the note for json_typeof (
http://www.postgresql.org/docs/9.5/interactive/functions-json.html) but
that is a bit detached from the situation you encountered.

David J.

Thanks for the explanation David. I had missed the distinction between =
the SQL null and the JSON null. It would be great if this could be =
mentioned more prominently in the JSON functions documentation.

You could also still consider making this function non strict and =
storing a JSON null if the value is a either a SQL/JSON null.=20

> On 15-Feb-2016, at 11:33 PM, David G. Johnston =
<david.g.johnston@gmail.com> wrote:
>=20
> On Mon, Feb 15, 2016 at 1:02 AM, <pravin@gida.in =
<mailto:pravin@gida.in>> wrote:
> The following bug has been logged on the website:
>=20
> Bug reference:      13961
> Logged by:          Pravin Carvalho
> Email address:      pravin@gida.in <mailto:pravin@gida.in>
> PostgreSQL version: 9.5.1
> Operating system:   All
> Description:
>=20
> If the new_value is null, JSONB_SET evaluates to null. This is not =
mentioned
> in the documentation and intuitively I would expect this set the value =
of
> the key at the specified path as null.
> eg. SELECT jsonb_set('{"f1":1,"f2":2}', '{f2}',null);
>=20
> I was using JSONB_SET to update a JSONB column where the new_value was =
the
> result of the query and this could have led to loss of data.
>=20
>=20
> =E2=80=8BWorking as designed - though in retrospect I don't see why =
this particular function had to be defined "STRICT".
>=20
> You will need to use:
> COALESCE((SELECT ...), 'null') if you want to store a JSON null when =
the subquery results in an SQL being returned.  The two are not the same =
thing.
>=20
> The technical answer is that with jsonb_set defined as being "NULL ON =
NULL INPUT (a.k.a., STRICT)" =E2=80=8Bas soon as any of its arguments =
are SQL NULL the executor replaces the function call with an SQL NULL =
without ever attempting to execute the function.
>=20
> I think that this point could be better made in the documentation=E2=80=8B=
 for these functions.  It is alluded to in the note for json_typeof =
(http://www.postgresql.org/docs/9.5/interactive/functions-json.html =
<http://www.postgresql.org/docs/9.5/interactive/functions-json.html>) =
but that is a bit detached from the situation you encountered.
>=20
> David J.
>=20
>=20