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?

Jcl · Accepted Answer · 2016-08-26 19:10:37Z

11

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

edited Aug 26, 2016 at 19:10

answered Aug 26, 2016 at 18:59

Jcl

28.4k5 gold badges66 silver badges98 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tom Corelis Over a year ago

This works exactly the way I was expecting it to. Thank you! The other comment mentioned this too but yours was posted first so I'll give this answer the credit.

Kerem · Accepted Answer · 2020-11-01 17:41:14Z

4

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"

edited Nov 1, 2020 at 17:41

Kerem

11.6k5 gold badges61 silver badges58 bronze badges

answered Aug 26, 2016 at 20:16

Michael Dillon

32.5k7 gold badges76 silver badges107 bronze badges

2 Comments

Tom Corelis Over a year ago

Thanks for the json_typeof note, unfortunately production is running 9.3 and this feature isn't available on that release

Lasse Over a year ago

The last part of this answer is incorrect. ->> will yield a proper SQL NULL value for a jsonb null value. Try e.g.: \pset null SQLNULL followed by

SELECT ('{"foo":null}'::jsonb->>'foo') IS NULL, ('{"foo":null}'::jsonb->>'foo'), ('{"foo":"null"}'::jsonb->>'foo') IS NULL, ('{"foo":"null"}'::jsonb->>'foo');

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related