Let me explain:
[1] [no error]
SELECT (('{"a": null}'::JSONB)->>'a')::INT;
[2] [error here]
SELECT (t.value::TEXT)::INT FROM jsonb_each(('{"a": null}'::JSONB)) AS t
I have to use jsonb_each function.
How can i make SQL number 2 return null?
-
PostgreSQL version: "PostgreSQL 10.12"gokaysatir– gokaysatir2020-02-29 12:00:21 +00:00Commented Feb 29, 2020 at 12:00
Add a comment
|
2 Answers
In your first example you use ->> which returns the value as a text data type. The equivalent "for each" function is jsonb_each_text() which also returns the value as text. jsonb_each returns each value as a JSONB value
SELECT t.value::INT
FROM jsonb_each_text(('{"a": null}'::JSONB)) AS t
Comments
You can do this for example:
SELECT nullif(t.value::text,'null')::int
FROM jsonb_each(('{"a": null}'::JSONB)) AS t
Best regards,
Bjarni
2 Comments
gokaysatir
Both answers are better than mine. I tried to use row_to_json. I will accept the native one. Thank you!
Bjarni Ragnarsson
Yes. @a_horse_without_name does this the right way - as usually. I used jsonb_each as you specified you needed to use that function for some reason :-)