postgres json array elements and null returns

Question

I have a json column in a table in postgres that includes an array of objects e.g.

{"BlockData":[{"Name":"George","Age":"54","Height":"1.75"}, {"Name":"Mario","Age":"35","Height":"1.90"}]}

I am using a Select query and want to access the Name object and the value pair of the Name (George and Mario). What I am trying to to is the following:

select jsonb_array_elements(jsondoc_->'BlockData')->>'Name' from BlockData;

What I get in return is

"ERROR:  cannot extract elements from a scalar

SQL state: 22023"

From what I could discover is that this issue occurs because at some rows the return is NULL. Can you please advise how can I overlap this issue?

Vao Tsun · Accepted Answer · 2017-12-14 08:20:04Z

3

did you try to Filter them?

t=# with t(jsondoc_) as (values('{"BlockData":[{"Name":"George","Age":"54","Height":"1.75"}, {"Name":"Mario","Age":"35","Height":"1.90"}]}'::jsonb),('{"BlockData":null}'))
select jsonb_array_elements(jsondoc_->'BlockData')->>'Name' from t
where jsondoc_->'BlockData' <> 'null';
 ?column?
----------
 George
 Mario
(2 rows)

answered Dec 14, 2017 at 8:20

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

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

1 Comment

charilaos13 Over a year ago

Hi Vao no i haven't tried it. I did now and it works fine thank you.

Collectives™ on Stack Overflow

postgres json array elements and null returns

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related