Tom Corelis Tom Corelis -4 years ago 218
JSON Question

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

So the following PostgreSQL snippet returns

, as it should:

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

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

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

Instead it returns the string "null". Why?


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

cannot cast type json to integer

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

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?

Answer Source

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download