PostgreSQL中如何实现字符串数组的数组的多条件SQL搜索

原查询针对单层字符串数组的属性匹配逻辑没问题，但要适配二维数组（每个子数组需至少匹配一个值），可以修改HAVING子句的判断逻辑：

调整后的查询代码 #

AND EXISTS (
  SELECT 1
  FROM "attribute"
  WHERE "attribute"."productId" = p.id
  GROUP BY "attribute"."productId"
  HAVING ALL (
    SELECT array_agg(DISTINCT "attribute".value) && sub_arr
    FROM unnest(${attributes}) AS sub_arr
  )
)

逻辑说明 #

1. 先通过array_agg(DISTINCT "attribute".value)聚合当前产品的所有属性值，得到一个去重后的字符串数组
2. 用unnest(${attributes})把传入的二维数组拆分成一个个独立的子数组
3. &&操作符用来判断两个数组是否有交集（即产品属性数组包含子数组中的至少一个值）
4. ALL关键字确保所有子数组的交集判断结果都是true——也就是每个子数组里至少有一个值存在于产品的属性值数组中

示例验证 #

当传入[["Car", "Bike"], ["Blue", "Red"]]时：

• 先拆分出两个子数组：["Car", "Bike"]和["Blue", "Red"]
• 只有同时满足「属性数组包含Car/Bike」且「属性数组包含Blue/Red」的产品，才会通过ALL的校验被返回

备选写法（用计数判断） #

如果对ALL的逻辑不太习惯，也可以用计数匹配的方式实现：

AND EXISTS (
  SELECT 1
  FROM "attribute"
  WHERE "attribute"."productId" = p.id
  GROUP BY "attribute"."productId"
  HAVING (
    SELECT COUNT(*)
    FROM unnest(${attributes}) AS sub_arr
    WHERE array_agg(DISTINCT "attribute".value) && sub_arr
  ) = array_length(${attributes}, 1)
)

这个写法的逻辑是：统计能和产品属性数组产生交集的子数组数量，只有当这个数量等于传入的二维数组长度时（所有子数组都匹配），才返回该产品。

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