PostgreSQL基于jsonb条件过滤结果的SQL查询修正需求

需求说明 #

数据表包含code字段和JSON数组类型的custom字段，需返回所有code及对应的customBreakdownName、customBreakdownGroupName，规则如下：

• 若custom数组中存在对象满足customBreakdown->>'name' = 'By SOW'且customBreakdownGroup->>'name' != 'Ungrouped'，返回该对象对应的两个字段值
• 不满足上述条件的code，返回code及两个NULL值

问题根源 #

原查询大概率使用了INNER JOIN或直接在WHERE子句中过滤数组元素，导致无匹配条件的code（比如AW）被过滤，无法返回对应的NULL行。

修正后的查询方案 #

方案一：LEFT JOIN LATERAL + 聚合（推荐） #

此方法通过左连接展开数组元素，再聚合筛选符合条件的记录，确保所有code都被保留：

SELECT 
    t.code,
    MAX(CASE 
        WHEN elem->'customBreakdown'->>'name' = 'By SOW' 
             AND elem->'customBreakdownGroup'->>'name' != 'Ungrouped'
        THEN elem->'customBreakdown'->>'name' 
    END) AS customBreakdownName,
    MAX(CASE 
        WHEN elem->'customBreakdown'->>'name' = 'By SOW' 
             AND elem->'customBreakdownGroup'->>'name' != 'Ungrouped'
        THEN elem->'customBreakdownGroup'->>'name' 
    END) AS customBreakdownGroupName
FROM your_table t
LEFT JOIN LATERAL jsonb_array_elements(t.custom) elem ON true
GROUP BY t.code;

注：若custom字段是json类型而非jsonb，将jsonb_array_elements替换为json_array_elements即可。

方案二：EXISTS子查询 + 条件判断 #

通过EXISTS先判断是否存在符合条件的数组元素，再选择性返回对应值或NULL：

SELECT 
    code,
    CASE 
        WHEN EXISTS (
            SELECT 1 
            FROM jsonb_array_elements(custom) elem
            WHERE elem->'customBreakdown'->>'name' = 'By SOW' 
              AND elem->'customBreakdownGroup'->>'name' != 'Ungrouped'
        ) THEN (
            SELECT elem->'customBreakdown'->>'name'
            FROM jsonb_array_elements(custom) elem
            WHERE elem->'customBreakdown'->>'name' = 'By SOW' 
              AND elem->'customBreakdownGroup'->>'name' != 'Ungrouped'
            LIMIT 1
        ) 
        ELSE NULL 
    END AS customBreakdownName,
    CASE 
        WHEN EXISTS (
            SELECT 1 
            FROM jsonb_array_elements(custom) elem
            WHERE elem->'customBreakdown'->>'name' = 'By SOW' 
              AND elem->'customBreakdownGroup'->>'name' != 'Ungrouped'
        ) THEN (
            SELECT elem->'customBreakdownGroup'->>'name'
            FROM jsonb_array_elements(custom) elem
            WHERE elem->'customBreakdown'->>'name' = 'By SOW' 
              AND elem->'customBreakdownGroup'->>'name' != 'Ungrouped'
            LIMIT 1
        ) 
        ELSE NULL 
    END AS customBreakdownGroupName
FROM your_table;

说明 #

• 方案一适合数组中可能存在多个符合条件对象的场景，MAX函数会自动忽略NULL，返回第一个匹配的值（若需全部匹配值可去掉聚合，改用DISTINCT）
• 方案二通过LIMIT 1确保每个code仅返回一行结果，避免多匹配时出现重复行

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