Tom Corelis Tom Corelis - 3 months ago 11
JSON Question

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

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"
.

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?

Jcl Jcl
Answer

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"}

Comments