PostgreSQL查询JSONB对象数组匹配多嵌套条件的最优方案咨询

问题根因分析 #

你最初使用jsonb_to_recordset做多条件匹配失败的核心原因是：JSON数组展开后，每一行仅对应问卷中的一个问题条目，单条数据行不可能同时满足id='2'和id='3'两个互斥的条件，因此AND逻辑永远无法命中结果。

方案评估 #

你后续使用@>操作符的写法是精确匹配场景下的最优方案：

1. 天然适配value字段既可能是单值也可能是数组的特性，@>会自动匹配JSON结构：单值字符串会匹配等值，数组会匹配包含关系
2. 可以直接使用GIN索引加速查询，数据量较大时性能远高于展开JSON数组的方案，建议提前创建索引：

CREATE INDEX idx_form_responses_qr ON form_responses USING GIN (questionnaire_response);

仅有一种场景该方案不适用：你需要对value做模糊匹配（比如like '%关键字%'）的时候，@>只能做精确结构匹配，无法满足模糊查询需求。

模糊匹配多条件的解决方案 #

如果需要做模糊匹配，改用EXISTS子查询做独立条件校验即可，不需要展开整表：

SELECT * FROM form_responses
WHERE 
  -- 匹配问题2答案包含Yes
  EXISTS (
    SELECT 1 FROM jsonb_to_recordset(questionnaire_response) AS items(id text, value jsonb)
    WHERE id = '2' AND value::text LIKE '%Yes%'
  )
  -- 匹配问题3答案包含Black
  AND EXISTS (
    SELECT 1 FROM jsonb_to_recordset(questionnaire_response) AS items(id text, value jsonb)
    WHERE id = '3' AND value::text LIKE '%Black%'
  );

该写法中value定义为jsonb类型，不需要做额外的类型判断，转成text后无论是单值还是数组都可以直接做模糊匹配。

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