I have a JSON type column. The JSON inside looks like this:
{
"user_info":
[
{"name":"Team member 1","email":"[email protected]","employee_id":"1"},
{"name":"Team member 2","email":"[email protected]","employee_id":"2"}
]
}
How do I check whether the email "[email protected]" exists within this JSON?
You can use a JSON path expression:
select *
from the_table
where the_column @@ '$.user_info[*].email == "[email protected]"';
This assumes that the_column is defined as jsonb (which it should be). If it's not, you need to cast it: the_column::jsonb
Assuming data type jsonb.
You can use plain containment with the @> operator in any version that supports jsonb (Postgres 9.4+):
SELECT *
FROM tbl
WHERE js @> '{"user_info":[{"email":"[email protected]"}]}';
See:
Or an SQL/JSON path expression with the @? operator in Postgres 12 or later:
SELECT *
FROM tbl
WHERE js @? '$.user_info[*].email ? (@ == "[email protected]")';
See:
Either query can use an index on the JSON column, equally efficient. The operator class jsonb_path_ops is smaller and faster than the default jsonb_ops but supports a smaller set of operators:
CREATE INDEX tbl_js_path_ops_idx ON tbl USING gin (js jsonb_path_ops);
db<>fiddle here