Why doesn't JSON null apply to SQL null in postgres?

Question

So, the following PostgreSQL snippet returns nullas it should:

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

Intuitively, you can expect the following statement to return nulleither an empty string:

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

Instead, it returns the string "null". Why?

Besides,

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

returns cannot cast type json to integerand

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

returns invalid input syntax for integer: "null". (In this case, JSON null listing for SQL null in the INTEGER column is used.)

There is a similar question with a somewhat unintelligible answer that seems to boil down to “null JSON values ​​and null SQL values ​​are slightly different” and no further explanation. Can someone help me understand what is going on here? This apparent behavior seems crazy!

? "" node / "" , yuck. ?

Answer 1

->> json-.

null ( , ) :

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

,

PS: "null", json : {"id": "null"}

Answer 2

, json_typeof, , JSON, →

json_typeof ('{ "id" : 4}':: json → 'id'), json_typeof ('{ "id" : "null" }':: json → 'id'), json_typeof ( '{ "id" : null}':: json → 'id')

→ > , "null"