Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE tbl(id int, js jsonb);
INSERT INTO tbl VALUES
  (1, jsonb '{"user_info": [{"name":"Team member 1","email":"abc@xyz.com","employee_id":"1"}
                          , {"name":"Team member 2","email":"def@xyz.com","employee_id":"2"}]}')
, (2, jsonb '{"user_info": [{"name":"Team member 1","email":"foo@xyz.com","employee_id":"1"}
                          , {"name":"Team member 2","email":"bar@xyz.com","employee_id":"2"}]}')
;
2 rows affected

-- plain containment, Postgres 9.4+
SELECT *
FROM   tbl
WHERE  js @> '{"user_info":[{"email":"abc@xyz.com"}]}';

id
js

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

-- SQL/JSON path expression, Postgres 12+
SELECT *
FROM   tbl
WHERE  js @? '$.user_info[*].email ? (@ == "abc@xyz.com")';

id
js

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

-- Either can use an index efficiently
CREATE INDEX tbl_js_path_ops_idx ON tbl USING gin (js jsonb_path_ops);

SET enable_seqscan = off; -- only for demonstration with minimal table!

EXPLAIN
SELECT * FROM tbl WHERE js @> '{"user_info":[{"email":"abc@xyz.com"}]}';

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

QUERY PLAN

Bitmap Heap Scan on tbl  (cost=8.00..12.01 rows=1 width=36)

  Recheck Cond: (js @> '{"user_info": [{"email": "abc@xyz.com"}]}'::jsonb)

  ->  Bitmap Index Scan on tbl_js_path_ops_idx  (cost=0.00..8.00 rows=1 width=0)

        Index Cond: (js @> '{"user_info": [{"email": "abc@xyz.com"}]}'::jsonb)

QUERY PLAN

Bitmap Heap Scan on tbl  (cost=8.00..12.01 rows=1 width=36)

  Recheck Cond: (js @? '$."user_info"[*]."email"?(@ == "abc@xyz.com")'::jsonpath)

  ->  Bitmap Index Scan on tbl_js_path_ops_idx  (cost=0.00..8.00 rows=1 width=0)

        Index Cond: (js @? '$."user_info"[*]."email"?(@ == "abc@xyz.com")'::jsonpath)