如何判断JSON字段数组是否含指定元素？PostgreSQL查询求助

针对你的需求（找出data字段中info.empid.$in数组包含1或2的记录），以下是几种可行的PostgreSQL查询方案：

方案1：使用JSONB操作符（推荐） #

将json类型转为jsonb后，利用数组包含判断操作符?|（匹配任意元素）：

SELECT t.id, t.data
FROM test t
CROSS JOIN jsonb_array_elements(t.data::jsonb) AS elem
WHERE (elem -> 'info' -> 'empid' -> '$in') ?| array['1', '2'];

如果需要判断数组同时包含1和2，则替换为?&操作符：

SELECT t.id, t.data
FROM test t
CROSS JOIN jsonb_array_elements(t.data::jsonb) AS elem
WHERE (elem -> 'info' -> 'empid' -> '$in') ?& array['1', '2'];

方案2：使用JSONPath查询 #

更简洁的路径表达式写法，无需展开数组：

SELECT *
FROM test
WHERE data @@ '$[*].info.empid.$in[*] in (1, 2)';

方案3：基础JSON函数判断 #

如果偏好使用基础JSON函数，也可以展开数组后逐一判断：

SELECT DISTINCT t.id, t.data
FROM test t
CROSS JOIN json_array_elements(t.data) AS elem
CROSS JOIN json_array_elements(elem -> 'info' -> 'empid' -> '$in') AS emp_id
WHERE emp_id::text IN ('1', '2');

你的CTE查询未得到预期结果，主要是以下几个错误导致：

1. 路径错误：原查询中使用data->'criteria'，但你的data字段本身就是一个包含条件对象的数组，不存在criteria这个key
2. 拼写错误：empoloyeeid是笔误，且实际JSON结构中对应的字段是empid
3. 操作符使用错误：@> '1,2'不符合JSON包含操作符的语法，@>要求右侧是合法的JSON值（比如'[1,2]'::jsonb），但这是判断数组包含整个目标数组，而非任意单个元素
4. 字段不存在：原查询中选择role字段，但你的表结构和数据中都没有这个字段

内容的提问来源于stack exchange，提问作者Madhu