current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Join the Stack Overflow Community
Stack Overflow is a community of 6.3 million programmers, just like you, helping each other.
Join them; it only takes a minute:
Sign up
up vote 3 down vote favorite
2

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?

share|improve this question
    
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). Please clarify. – pozs Jan 8 '15 at 9:46
up vote 4 down vote accepted

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.

share|improve this answer
    
Perfect !! That's what I wanted. Your code in SQLFiddle is perfect. Thanks for solving my issue. – Mital Pritmani Jan 9 '15 at 6:26

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.