Joe Joe - 11 months ago 51
JSON Question

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",
"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)"?


Yes, that's possible:

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

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

More details in this related answer:

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

Index to support this kind of query: