Re: BUG #15763: JSON nulls not handled properly

Jacob Crell <jacobcrell(at)gmail(dot)com> writes:
> Thanks for the response. I may have been a bit off in my diagnosis of what
> was going wrong. My bug report stemmed from the fact that the below returns
> different results, the first throwing an error and the second returning no
> rows:

> SELECT json_array_elements('{"key":null}'::json->'key')
> SELECT json_array_elements(null::json)

> This seems unintuitive. Is it potentially a bug?

No, because null::json is not the same thing as 'null'::json.
They're related ideas, but not interchangeable. In your
second example, json_array_elements() never gets called at all
because it's marked strict and strict functions are not invoked
on SQL nulls. In the first example, it is called and it complains
because the JSON value it's called on isn't an array, but a
scalar null value.

You could make a case that the function should have been defined
to return zero rows for a JSON-null input ... but it'd be at best
a debatable point, so we're unlikely to change the function
definition now.

If you need that behavior, it is available in the jsonb world,
with something like

regression=# SELECT jsonb_array_elements(nullif('{"key":null}'::jsonb->'key',
'null'::jsonb));
jsonb_array_elements
----------------------
(0 rows)

But nullif() doesn't work on plain json, for lack of an equality
operator :-(

regards, tom lane