Re: to_json(NULL) should to return JSON null instead NULL

On 08/29/2015 04:27 PM, Tom Lane wrote:

Jim Nasby <Jim.Nasby@BlueTreble.com> writes:

On 8/29/15 12:29 PM, Pavel Stehule wrote:

what is correct from JSON perspective? All fields with NULL

ISTM that the whole purpose of to_json is to properly jsonify something,
and the proper json form for "undefined" is 'null', is it not?

What's not entirely clear is what we should do with cases like

regression=#  select array_to_json(null::int[]);
  array_to_json
---------------
  (1 row)

regression=#  select row_to_json(null::record);
  row_to_json
-------------
  (1 row)

If we leave those alone (and in the latter case, in particular, there is
not enough information available to do much else) then it's not so clear
that changing to_json() is really improving consistency overall.
For instance, do we really want row_to_json(null::record) and
to_json(null::record) giving different results?  Or if we make them
both return "null", that breaks the previous invariant that row_to_json
always yields a JSON object.

An advantage of leaving these things as strict is that the user can easily
substitute whatever specific behavior she wants for NULLs via coalesce(),
as was shown upthread.  If we put in a different behavior, then the
only way to override it would be with a CASE, which is tedious and creates
multiple-evaluation issues.

I'm not necessarily against changing it --- but it doesn't seem entirely
black-and-white to me, and we do now have a couple of versions worth
of precedent we'd be breaking with.

If we do vote to change it, I'd want to do so now (ie in 9.5) rather than
create yet another year's worth of precedent.

                       

I agree with pretty much all of this. My fairly strong inclination is to leave it as it is and document the behaviour more clearly. Changing it seems likely to introduce a different inconsistency which is harder to understand.

cheers

andrew