2015-08-30 4:57 GMT+02:00 Andrew Dunstan <and...@dunslane.net>: > > > On 08/29/2015 04:27 PM, Tom Lane wrote: > >> Jim Nasby <jim.na...@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. >
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 >
