How to get elements from Json array in PostgreSQL
I'm not sure you have a json[]
(PostgreSQL array of json
values) typed column, or a json
typed column, which appears to be a JSON array (like in your example).
Either case, you need to expand your array before querying. In case of json[]
, you need to use unnest(anyarray)
; in case of JSON arrays in a json
typed column, you need to use json_array_elements(json)
(and LATERAL
joins -- they are implicit in my examples):
select t.id,
each_section ->> 'name' section_name,
each_attribute ->> 'attrkey3' attrkey3
from t
cross join unnest(array_of_json) each_section
cross join json_array_elements(each_section -> 'attributes') each_attribute
where (each_attribute -> 'attrkey3') is not null;
-- use "where each_attribute ? 'attrkey3'" in case of jsonb
select t.id,
each_section ->> 'name' section_name,
each_attribute ->> 'attrkey3' attrkey3
from t
cross join json_array_elements(json_array) each_section
cross join json_array_elements(each_section -> 'attributes') each_attribute
where (each_attribute -> 'attrkey3') is not null;
SQLFiddle
Unfortunately, you cannot use any index with your data. You need to fix your schema first, in order to do that.
If you wish to access a single element then use json_array -> index
For example, if you have json_arr=[1,2,3]
then json_array -> 0
will return 1
And also, if there was a key value map data in array
:
select each_data -> 'value' as value3
from t cross join jsonb_array_elements(t.sections -> 'attributes') each_attribute
where each_attribute -> 'key' = '"attrkey3"'
I am mentioning this because the great answer also provided a perfect solution for my case. By the way, also be aware of jsonb_array
.. method for jsonb
type attribute.