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

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.

> On 15-Feb-2016, at 11:33 PM, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>
> On Mon, Feb 15, 2016 at 1:02 AM, <pravin(at)gida(dot)in <mailto: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 <mailto: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 <http://www.postgresql.org/docs/9.5/interactive/functions-json.html>) but that is a bit detached from the situation you encountered.
>
> David J.
>
>