如何在PostgreSQL中追踪多个OR条件的匹配归属？

你需要在一次查询中区分每条记录匹配的具体搜索条件，同时避免在客户端重复处理ILIKE逻辑，以下是几种PostgreSQL的实现方案：

方案1：返回单条匹配的条件标识（优先匹配第一个符合的条件） #

适合一条记录只会匹配一个条件的场景，用CASE语句直接标记匹配的条件ID：

SELECT 
  users.*,
  CASE
    WHEN (name ILIKE '%mark smith%' AND country='US') THEN 'condition_1'
    WHEN (name ILIKE '%christine smith%') THEN 'condition_2'
    -- 依次添加剩余的查询条件
    ELSE NULL
  END AS matched_condition
FROM users
WHERE 
  (name ILIKE '%mark smith%' AND country='US')
  OR (name ILIKE '%christine smith%')
  -- 追加其他所有OR条件

查询结果会新增matched_condition字段，直接显示当前记录匹配的条件标识。

方案2：返回所有匹配的条件标识（支持多条件匹配） #

如果一条记录可能符合多个搜索条件，用数组收集所有匹配的标识：

SELECT 
  users.*,
  ARRAY(
    SELECT cond_id
    FROM (
      VALUES
        ('condition_1', (name ILIKE '%mark smith%' AND country='US')),
        ('condition_2', (name ILIKE '%christine smith%'))
        -- 继续添加其他条件的(标识, 判断逻辑)行
    ) AS conditions(cond_id, is_match)
    WHERE is_match
  ) AS matched_conditions
FROM users
WHERE 
  (name ILIKE '%mark smith%' AND country='US')
  OR (name ILIKE '%christine smith%')
  -- 追加所有OR条件

结果中的matched_conditions是一个数组，包含当前记录匹配的所有条件标识。

方案3：直接按条件分组返回用户集合 #

如果你想直接得到类似condition_1_users = [...]的分组结果，可以用JSON_AGG按条件ID聚合用户数据：

SELECT 
  cond_id,
  JSON_AGG(users.*) AS matching_users
FROM users
CROSS JOIN (
  VALUES
    ('condition_1'),
    ('condition_2')
    -- 添加所有条件ID
) AS conditions(cond_id)
WHERE 
  (cond_id = 'condition_1' AND name ILIKE '%mark smith%' AND country='US')
  OR (cond_id = 'condition_2' AND name ILIKE '%christine smith%')
  -- 为每个条件ID关联对应的判断逻辑
GROUP BY cond_id

查询结果的每一行对应一个条件，matching_users字段是该条件匹配的所有用户的JSON数组，直接满足你想要的分组结果需求。

注意事项 #

• 处理1000个条件时，手动编写SQL效率极低，建议用程序动态生成查询语句；
• 大量ILIKE查询可能导致性能问题，可通过pg_trgm扩展创建GIN索引优化模糊查询速度。

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