Thread: BUG #15763: JSON nulls not handled properly
The following bug has been logged on the website: Bug reference: 15763 Logged by: Jacob Crell Email address: jacobcrell@gmail.com PostgreSQL version: 9.6.8 Operating system: AWS RDS Description: SELECT '{"test":null}'::json->>'test' will return a null SELECT '{"test":null}'::json->'test' will return a string 'null' The 2nd option seems like it should also return a null.
PG Bug reporting form <noreply@postgresql.org> writes: > SELECT '{"test":null}'::json->>'test' will return a null > SELECT '{"test":null}'::json->'test' will return a string 'null' > The 2nd option seems like it should also return a null. I don't claim to be a JSON expert, but the -> operator is specified to give back a JSON value (not a text string). So 'null'::json seems like the right answer there. Also, if we had it return a NULL, then you couldn't distinguish the case where the field isn't present: regression=# SELECT '{"test":null}'::json->'notthere'; ?column? ---------- (1 row) regression=# SELECT '{"test":null}'::json->'notthere' is null; ?column? ---------- t (1 row) regards, tom lane
On Tuesday, April 16, 2019, PG Bug reporting form <noreply@postgresql.org> wrote:
The following bug has been logged on the website:
Bug reference: 15763
Logged by: Jacob Crell
Email address: jacobcrell@gmail.com
PostgreSQL version: 9.6.8
Operating system: AWS RDS
Description:
SELECT '{"test":null}'::json->>'test' will return a null
SELECT '{"test":null}'::json->'test' will return a string 'null'
The 2nd option seems like it should also return a null.
This seems under documented but I can confidently say the behavior shown is intended and thus not a bug. Nor should it be changed. The second example returns a json typed value that when printed as text is the character sequence null. It does not return a PostgreSQL string type.
Conversion of json null to PostgreSQL text results in a NULL of type text, which is indeed the first outcome. This is, however, a lossy one-way conversion since NULL::json is NULL, not ‘null’.
David J.
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?
On Tue, Apr 16, 2019 at 2:35 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Tuesday, April 16, 2019, PG Bug reporting form <noreply@postgresql.org> wrote:The following bug has been logged on the website:
Bug reference: 15763
Logged by: Jacob Crell
Email address: jacobcrell@gmail.com
PostgreSQL version: 9.6.8
Operating system: AWS RDS
Description:
SELECT '{"test":null}'::json->>'test' will return a null
SELECT '{"test":null}'::json->'test' will return a string 'null'
The 2nd option seems like it should also return a null.This seems under documented but I can confidently say the behavior shown is intended and thus not a bug. Nor should it be changed. The second example returns a json typed value that when printed as text is the character sequence null. It does not return a PostgreSQL string type.Conversion of json null to PostgreSQL text results in a NULL of type text, which is indeed the first outcome. This is, however, a lossy one-way conversion since NULL::json is NULL, not ‘null’.David J.
Jacob Crell <jacobcrell@gmail.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