Why does JSON null not cast to SQL null in postgres?

Question

So the following PostgreSQL snippet returns null, as it should:

select ('{"id": null}'::json->'id')

Intuitively, one would expect the following statement to return null or an empty string:

select ('{"id": null}'::json->'id')::TEXT

Instead it returns the string "null". Why?

Additionally,

select ('{"id": null}'::json->'id')::INTEGER

returns cannot cast type json to integer and

select ('{"id": null}'::json->'id')::TEXT::INTEGER

returns invalid input syntax for integer: "null". (The use case here is casting a JSON null to a SQL null in an INTEGER column.)

There's a similar question with a somewhat-unintelligible answer that seems to boil down to "JSON nulls and SQL nulls are slightly different" and no further explanation. Can someone help me understand what is going on here? This apparent behavior seems crazy!

How does one get around this cleanly? Testing for string "null" screams of code stink, and refactoring to test every single potential node for null/"null" before casting is equally yuck. Any other ideas?

Answer 1

Use the ->> operator for retrieving the json field.

This should work and return null (as in, no value) correctly for both:

select ('{"id": null}'::json->>'id')::text
select ('{"id": null}'::json->>'id')::integer

I've made a fiddle that demostrates it

PS: to get the string "null", you'd need to define your json as: {"id": "null"}

Answer 2

You probably need to use the json_typeof operator to figure out what you have in the JSON type that is returned by ->

select json_typeof('{"id": 4}'::json->'id'),
       json_typeof('{"id": "null"}'::json->'id'),
       json_typeof('{"id": null}'::json->'id');

Using ->> instead guarantees you a text string value, but then you cannot distinguish between null and "null"