如何修改SQL，筛选同时包含gender和age的jsonb字段分组数据？

要让查询仅返回同时包含gender和age的分组数据，你可以通过以下两种方式修改SQL：

方法一：双EXISTS验证 #

通过两个独立的EXISTS子查询，分别确认targets数组中存在name为gender和age的元素：

SELECT DISTINCT groups.* 
FROM groups 
WHERE groups.user_id = 1 
  AND EXISTS (
    SELECT * FROM jsonb_array_elements(targets) AS a 
    WHERE a->>'name' = 'gender'
  )
  AND EXISTS (
    SELECT * FROM jsonb_array_elements(targets) AS a 
    WHERE a->>'name' = 'age'
  );

方法二：聚合统计验证 #

将targets数组展开后，筛选出指定的name值，通过分组统计确保两个值都存在：

SELECT groups.* 
FROM groups 
JOIN jsonb_array_elements(targets) AS a ON true
WHERE groups.user_id = 1 
  AND a->>'name' IN ('gender', 'age')
GROUP BY groups.id  -- 替换为groups表的主键字段（确保唯一标识分组）
HAVING COUNT(DISTINCT a->>'name') = 2;

说明 #

• 方法一逻辑直观，适合明确验证多个条件的场景，性能稳定。
• 方法二通过聚合统计实现，若后续需要验证更多name值，只需调整IN列表和COUNT的目标数值即可。
• 替换原SQL中的正则匹配~*为精确匹配=，避免不必要的正则计算，提升查询效率。

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