[SOLVED] Query for element of array in JSON column

Query for element of array in JSON column

Recently upgraded to using PostgreSQL 9.3.1 to leverage the JSONfunctionalities. In my table I have a json type column that has a structure like this:

{
   "id": "123",
   "name": "foo",
   "emails":[
      {
        "id": "123",
        "address": "somethinghere"
      },
      {
        "id": "456",
        "address": "soemthing"
      }
   ]
}

This is just dummy data for the purpose of the question.

Is it possible to query for a specific item in the emails array based on the id?
Pretty much: "return email where id=123)"?

Solution

For Postgres 9.4+ see adamc's later answer. Or:

Query for array elements inside JSON type

Original answer for Postgres 9.3

Yes, that's possible:

SELECT *
FROM   tbl t, json_array_elements(t.json_col->'emails') AS elem
WHERE  elem->>'id' = 123;

tbl being your table name, json_col the name of the JSON column.