Mital Pritmani Mital Pritmani - 1 year ago 89
JSON Question

How to get elements from Json array in PostgreSQL

I have searched quite much on this and still unanswerable. I'm using PostgreSQL. Column name is "sections" and column type is json[] in below example.

My column looks like this in database:

sections
[{"name" : "section1",
"attributes": [{"attrkey1": "value1",
"attrkey2": "value2"},

{"attrkey3": "value3",
"attrkey4": "value4"}]
},
{"name" : "section2",
"attributes": [{"attrkey3": "value5",
"attrkey6": "value6"},

{"attrkey1": "value7",
"attrkey8": "value8"}]
}]


It's json array and I want to get "attrkey3" in my result. For getting particular key from Json, I can use
json_extract_path_text(json_column, 'json_property')
which is working perfectly fine. But I have no idea how to get some property from json[].

If I talk about above example, I want to get value of property "attrkey2" to be shown in my result. I know it's an array so it might work differently than usual, e.g. all the values of my array would act as a different row so I might have to write subquery but no idea how to do it.

Also, I can't write index statically and get property of the json element from some particular index. My query will be generated dynamically so I would never know how many elements are inside json array.

I saw some static examples but don't know how to implement it in my case. Can someone tell me how to do this in query?

Answer Source

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.