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

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?

CodePudding user response：

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

CodePudding user response：

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:

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 ? (@ == "[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