PostgreSQL 15技术问询：如何通过两个userid查询对应的roomid

你当前使用的@>操作符思路是对的，但可能存在类型不匹配或逻辑细节问题，以下是几种可靠的实现方式：

方法一：使用jsonb包含操作符（@>） #

如果你的users字段是jsonb类型，且数组中的userid存储格式与查询条件一致（比如都是字符串或都是数字），可以直接优化原语句：

-- 如果userid是字符串类型
SELECT roomid
FROM room 
WHERE users @> '[{"userid":"1"},{"userid":"2"}]'::jsonb;

-- 如果userid是数字类型，注意去掉引号
SELECT roomid
FROM room 
WHERE users @> '[{"userid":1},{"userid":2}]'::jsonb;

说明：@>操作符会检查users数组是否包含所有指定的用户对象（不关心数组顺序和其他额外元素），如果给users字段创建GIN索引，查询性能会非常高效。

方法二：展开jsonb数组后聚合筛选 #

如果需要更灵活的匹配逻辑（比如只检查userid值，不关心对象其他字段），可以用jsonb_array_elements展开数组后聚合：

-- userid为字符串类型的情况
SELECT roomid
FROM room, jsonb_array_elements(users) AS user_obj
WHERE user_obj->>'userid' IN ('1', '2')
GROUP BY roomid
HAVING COUNT(DISTINCT user_obj->>'userid') = 2;

-- userid为数字类型的情况，转换类型后筛选
SELECT roomid
FROM room, jsonb_array_elements(users) AS user_obj
WHERE (user_obj->>'userid')::int IN (1, 2)
GROUP BY roomid
HAVING COUNT(DISTINCT (user_obj->>'userid')::int) = 2;

说明：这种方法会把每个room的用户数组拆分成单行记录，筛选出包含目标userid的记录后，通过分组和计数确保两个userid都存在于同一个room中。

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