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