Re: to_json(NULL) should to return JSON null instead NULL - Mailing list pgsql-hackers

On 08/29/2015 12:02 PM, Pavel Stehule wrote:

2015-08-29 15:43 GMT+02:00 Shulgin, Oleksandr <oleksandr.shulgin@zalando.de <mailto:oleksandr.shulgin@zalando.de>>:

    On Sat, Aug 29, 2015 at 3:39 PM, Tom Lane <tgl@sss.pgh.pa.us
    <mailto:tgl@sss.pgh.pa.us>> wrote:

        Andrew Dunstan <andrew@dunslane.net

        <mailto:andrew@dunslane.net>> writes:
        > On 08/29/2015 08:47 AM, Shulgin, Oleksandr wrote:
        >> Given there were no loud complaints about this, the current behavior
        >> is appropriate for most users, the rest can still work
        around using
        >> coalesce(to_json(...), json 'null').

        > I don't think it's necessarily more correct. But I do agree
        that it's
        > not a good idea to change the behaviour unless there is major
        > unhappiness with it.

        I'm not entirely convinced that JSON NULL and SQL NULL should
        be treated
        as the same concept, so I would say that the current behavior
        is fine ---
        at least when you think about it in isolation.  However,
        haven't we
        already bought into that equivalence in these examples?

        regression=# select row_to_json(row(1,null,2));
                row_to_json
        ---------------------------
         {"f1":1,"f2":null,"f3":2}
        (1 row)

        regression=# select array_to_json(array[1,null,2]);
         array_to_json
        ---------------
         [1,null,2]
        (1 row)

        or even in to_json itself:

        regression=# select to_json(array[1,null,2]);
          to_json
        ------------
         [1,null,2]
        (1 row)

        The scalar case is definitely failing to be consistent with these.


    Yes, that's my argument for correctness also: to_json() on a
    composite object should behave like distribution of to_json()
    calls over object/array elements.

        Is consistency a sufficient reason to change it?


    Not for me.


It is bug - and it should be fixed. I agree, so this change is too strong for fixing in minor version - but we can change it in unreleased major versions - 9.5 and master.

No, frankly that's being far too free with the word bug. It's not even unambiguously incorrect.

Note that all the to_json functions are strict. In this sense it's quite consistent. If we change it to being called on null input, what should we return if a null non-scalar is passed in?

cheers

andrew