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.