Re: jsonb_set() strictness considered harmful to data

On Sun, 20 Oct 2019 at 08:32, Andrew Dunstan <andrew(dot)dunstan(at)2ndquadrant(dot)com>
wrote:

>
> Understood. I think the real question here is what it should do instead
> when the value is NULL. Your behaviour above is one suggestion, which I
> personally find intuitive. Another has been to remove the associated
> key. Another is to return the original target. And yet another is to
> raise an exception, which is easy to write but really punts the issue
> back to the application programmer who will have to decide how to ensure
> they never pass in a NULL parameter. Possibly we could even add an extra
> parameter to specify what should be done.
>

I vote for remove the key. If we make NULL and 'null'::jsonb the same,
we're missing an opportunity to provide more functionality. Sometimes it's
convenient to be able to handle both the "update" and "remove" cases with
one function, just depending on the parameter value supplied.

Also, the question will arise what to do when any of the other
> parameters are NULL. Should we return NULL in those cases as we do now?
>

I would argue that only if the target parameter (the actual json value) is
NULL should the result be NULL. The function is documented as returning the
target, with modifications to a small part of its structure as specified by
the other parameters. It is strange for the result to suddenly collapse
down to NULL just because another parameter is NULL. Perhaps if the path is
NULL, that can mean "don't update". And if create_missing is NULL, that
should mean the same as not specifying it. I think. At a minimum, if we
don't change it, the documentation needs to get one of those warning boxes
alerting people that the functions will destroy their input entirely rather
than slightly modifying it if any of the other parameters are NULL.

My only doubt about any of this is that by the same argument, functions
like replace() should not return NULL if the 2nd or 3rd parameter is NULL.
I'm guessing replace() is specified by SQL and also unchanged in many
versions so therefore not eligible for re-thinking but it still gives me
just a bit of pause.