How to get elements from a Json array in PostgreSQL

Question

I searched quite a lot on this and is still irrefutable. I am using PostgreSQL. The column name is “sections” and the column type is json [] in the example below.

My column looks like this in the database:

sections [{"name" : "section1", "attributes": [{"attrkey1": "value1", "attrkey2": "value2"}, {"attrkey3": "value3", "attrkey4": "value4"}] }, {"name" : "section2", "attributes": [{"attrkey3": "value5", "attrkey6": "value6"}, {"attrkey1": "value7", "attrkey8": "value8"}] }] 

This is a json array, and I want to get "attrkey3" in my result. To get a specific key from Json, I can use json_extract_path_text(json_column, 'json_property') , which works fine. But I have no idea how to get any property from json [].

If I talk about the example above, I want to get the value of the "attrkey2" property, which will be shown in my result. I know this is an array, so it may work differently than usual. all the values ​​of my array will act as another string, so I may have to write a subquery, but I don’t know how to do it.

In addition, I cannot write the index statically and get the property of the json element from a specific index. My request will be generated dynamically, so I will never know how many elements are inside the json array.

I saw some static examples, but I don’t know how to implement them in my case. Can someone tell me how to do this in a request?

Answer 1

I'm not sure if you have a json[] array (a PostgreSQL array of json values) or a json column that represents a JSON array (for example, in your example).

In any case, you need to expand your array before the request. In case of json[] you need to use unnest(anyarray) ; in the case of JSON arrays in the json column, you should 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. First you need to fix your circuit to do this.

Answer 2

And also, if the array contained key value map data:

 select each_data -> 'value' as value3 from t cross join jsonb_array_elements(t.sections -> 'attributes') each_attribute where each_attribute -> 'key' = '"attrkey3"' 

I mention this because the excellent answer also provided the perfect solution for my case. By the way, also pay attention to the jsonb_array .. method for the jsonb attribute.