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

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')::textselect ('{"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"}

json postgresql casting null

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"

CodeHunter