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)"?
Best Answer
Yes, that's possible:
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: