Search code examples
sqlarrayspostgresqlnulljsonb

postgres json array elements and null returns

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?

Solution

  • 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)