1

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?

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
gokaysatir
  • 125
  • 2
  • 11

2 Answers2

1

You can do this for example: 

SELECT nullif(t.value::text,'null')::int
FROM jsonb_each(('{"a": null}'::JSONB)) AS t

Best regards,
Bjarni

Bjarni Ragnarsson
  • 1,731
  • 1
  • 6
  • 8
  • Both answers are better than mine. I tried to use row_to_json. I will accept the native one. Thank you! – gokaysatir Feb 29 '20 at 12:41
  • 1
    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 :-) – Bjarni Ragnarsson Feb 29 '20 at 13:48
1

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