Re: jsonb_set() strictness considered harmful to data

On 10/24/19 2:17 PM, Tom Lane wrote:
> Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> writes:
>> On Wed, 2019-10-23 at 13:00 -0600, Stuart McGraw wrote:
>>> It is less sensible with compound values where the rule can apply to
>>> individual scalar components.
>
> I agree that JSON can sensibly be viewed as a composite value, but ...
>
>>> And indeed that is what Postgresql does
>>> for another compound type:
>>>
>>> # select array_replace(array[1,2,3],2,NULL);
>>> array_replace
>>> ---------------
>>> {1,NULL,3}
>>>
>>> The returned value is not NULL. Why the inconsistency between the array
>>> type and json type?
>
> ... the flaw in this argument is that the array element is actually
> a SQL NULL when we're done. To do something similar in the JSON case,
> we have to translate SQL NULL to JSON null, and that's cheating to
> some extent. They're not the same thing (and I'll generally resist
> proposals to, say, make SELECT 'null'::json IS NULL return true).
>
> Maybe it's okay to make this case work like that, but don't be too
> high and mighty about it being logically clean; it isn't.
>
> regards, tom lane

Sure, but my point was not that this was a perfect "logically clean"
answer, just that the argument, which was made multiple times, that
the entire result should be NULL because "that's the way SQL NULLs
work" is not really right.

It does seem to me that mapping NULL to "null" is likely a workable
approach but that's just my uninformed opinion.