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

On Mon, Feb 15, 2016 at 1:02 AM, <pravin(at)gida(dot)in> wrote:

> The following bug has been logged on the website:
>
> Bug reference: 13961
> Logged by: Pravin Carvalho
> Email address: pravin(at)gida(dot)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.
>
>
​Working as designed - though in retrospect I don't see why this particular
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)" ​as 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.

I think that this point could be better made in the documentation​ 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.