Query for element of array in JSON column

Yes, that's possible:

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

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

More details in this related answer:

• How do I query using fields inside the new PostgreSQL JSON datatype?

More about the implicit CROSS JOIN LATERAL in the last paragraph of this related answer:

• PostgreSQL unnest() with element number

Index to support this kind of query:

• Index for finding an element in a JSON array

With a JSONB column in Postgres 9.4+ you can use the contains operator @> to query for an element in an array:

SELECT * FROM jsontest WHERE data @> '{ "emails": [{ "id": "123" }] }';

See Query for array elements inside JSON type for more details.

Here is a working example:

CREATE TABLE jsontest(data JSONB NOT NULL);INSERT INTO jsontest VALUES (  '{     "name": "foo",     "id": "123",     "emails":      [       {         "address": "somethinghere",         "id": "123"       },       {         "address": "soemthing",         "id": "456"       }     ]  }');SELECT * FROM jsontest WHERE data @> '{ "emails": [{ "id": "123" }] }';data----{"id": "123", "name": "foo", "emails": [{"id": "123", "address": "somethinghere"}, {"id": "456", "address": "soemthing"}]}

(1 row)

Came across this post and found that you can directly query on table like this:

SELECT *FROM   table_name, json_array_elements(json_column) AS dataWHERE  data->>'id' = 123;

Omitting this part:

json_array_elements(t.json_col->'emails')