How find all rows where one of jsonArray elements has give property equal to something?

Question

In my table I have json column media which is set to array (json array) like this:

media: [
   {},
   {},
   {},
   ...,
   { key: 'thumbnail', metaData: { width: 123, height: 321 } }
]

I have to find all rows that contains (in media column) object with key = 'thumbnail' AND metaData.width = 123 AND metaData.height = 321 properties.

How I can do this with Postgres 9.3?

Answer 1

select id, a
from (
    select id, json_array_elements((j ->> 'media')::json) as a
    from (values(1, '
        {"media": [
           {},
           {},
           {},
           { "key": "thumbnail", "metaData": { "width": 123, "height": 321 } }
        ]}'::json
    )) s(id, j)
) s
where
    a ->> 'key' = 'thumbnail'
    and
    (a #>> '{metaData, width}')::integer = 123
    and
    (a #>> '{metaData, height}')::integer = 321
;
 id |                                  a                                  
----+---------------------------------------------------------------------
  1 | { "key": "thumbnail", "metaData": { "width": 123, "height": 321 } }