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

2015-08-30 4:57 GMT+02:00 Andrew Dunstan <andrew(at)dunslane(dot)net>:

>
>
> On 08/29/2015 04:27 PM, Tom Lane wrote:
>
>> Jim Nasby <Jim(dot)Nasby(at)BlueTreble(dot)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.
>

I agree so there is not clear solution - and both possible solution can
have a real base. On second hand, the fix with COALESCE, NULLIF, .. is not
hard and it is has not a performance impact - so better documentation can
be good enough fix. The custom solution is ugly named but simple function
to_json2

CREATE OR REPLACE FUNCTION to_json2(anyelement)
RETURNS json AS $$
SELECT COALESCE(to_json($1), json 'null')
$$ LANGUAGE sql;

Regards

Pavel

>
> cheers
>
> andrew
>