How to check whether a given value exists against a given key inside a JSON ARRAY?

Question

I have a JSON type column. The JSON inside looks like this:

{
"user_info":
   [
     {"name":"Team member 1","email":"abc@xyz.com","employee_id":"1"},
     {"name":"Team member 2","email":"def@xyz.com","employee_id":"2"}
   ]
}

How do I check whether the email "abc@xyz.com" exists within this JSON?

Answer 1

You can use a JSON path expression:

select *
from the_table
where the_column @@ '$.user_info[*].email == "abc@xyz.com"';

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

Answer 2

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":"abc@xyz.com"}]}';

See:

• Query for array elements inside JSON type

Or an SQL/JSON path expression with the @? operator in Postgres 12 or later:

SELECT *
FROM   tbl
WHERE  js @? '$.user_info[*].email ? (@ == "abc@xyz.com")';

See:

• Postgres jsonb query for dynamic values
• Pattern-matching for JSON values: slow EXISTS subquery on materialized view

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