Query for element of array in JSON column
Solution 1
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
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:
Solution 2
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)
Solution 3
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 data
WHERE data->>'id' = 123;
Omitting this part:
json_array_elements(t.json_col->'emails')
Admin
Updated on January 30, 2020Comments
-
Admin over 4 years
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)"?