PostgreSQL中json_agg的filter子句不生效问题排查与解决

问题描述 #

在PostgreSQL 11.3中编写SQL计算留存数据时，使用filter(where return_gap is null)意图排除return_gap为null的元素，但查询结果中仍包含此类元素。

原SQL语句 #

SELECT
    part_date,
    platid,
    json_agg(json_build_array(return_gap, users)) filter (where return_gap is null)  as return_retention_values
FROM (
SELECT
    part_date,
    platid,
    json_agg(json_build_array(return_gap, users)) filter (
        where
            return_gap is null
    ) as return_retention_values
FROM
    (
        select
            part_date,
            platid,
            return_gap,
            users
        from
            my_table
    ) a
GROUP BY
    grouping sets (
        (part_date, platid, return_gap),
        (part_date, platid)
    )
) a
group by
    part_date,
    platid
order by
    part_date asc

查询返回结果 #

part_date   platid  return_retention_values
2022-11-08  23  [[3, 345], [6, 248], [2, 408], [5, 286], [null, 1], [null, 1532], [1, 535], [4, 323], [7, 199], [0, 1531]]
2022-11-08  2   [[7, 902], [null, 5379], [5, 1382], [6, 1258], [3, 1666], [1, 2680], [0, 5379], [4, 1486], [2, 1961]]
2022-11-08  1   [[1, 1042], [7, 388], [4, 600], [3, 1600], [null, 3171], [2, 716], [0, 3171], [5, 1524], [6, 1485]]
2022-11-09  1   [[5, 1597], [0, 3086], [4, 1641], [2, 1767], [3, 578], [null, 1], [null, 3087], [1, 963], [6, 366]]

问题原因 #

原SQL的核心问题在于分组集（grouping sets）的逻辑与嵌套聚合的filter逻辑冲突：

1. 内层使用grouping sets ((part_date, platid, return_gap), (part_date, platid))时，会生成两类分组结果：
   • 一类是按part_date, platid, return_gap分组，此时return_gap为原始值；
   • 另一类是按part_date, platid分组，此时return_gap会被置为null，代表该层级的聚合结果。
2. 内层的filter(where return_gap is null)仅对当前分组内的行生效，但对于(part_date, platid)层级的分组来说，其本身的return_gap就是null，因此会聚合出该层级的结果；
3. 外层又将内层所有分组结果（包括带null的(part_date, platid)层级数据）重新聚合，最终导致结果中仍包含return_gap为null的元素。
4. 此外，原SQL的双层嵌套聚合完全冗余，进一步混淆了过滤逻辑。

修复方案 #

方案1：直接过滤原始数据（推荐） #

如果不需要保留(part_date, platid)层级的聚合结果，直接在查询原始表时过滤掉return_gap为null的行，再按part_date, platid分组聚合即可：

SELECT
    part_date,
    platid,
    json_agg(json_build_array(return_gap, users)) as return_retention_values
FROM my_table
WHERE return_gap IS NOT NULL  -- 提前过滤return_gap为null的原始数据
GROUP BY part_date, platid
ORDER BY part_date asc;

方案2：适配分组集逻辑 #

如果必须保留分组集的使用，需要在内层分组后过滤掉return_gap为null的分组行，再进行外层聚合：

SELECT
    part_date,
    platid,
    json_agg(json_build_array(return_gap, users)) as return_retention_values
FROM (
    SELECT
        part_date,
        platid,
        return_gap,
        users
    FROM my_table
    WHERE return_gap IS NOT NULL  -- 先过滤原始数据中的null值
) a
GROUP BY grouping sets (
    (part_date, platid, return_gap),
    (part_date, platid)
)
WHERE return_gap IS NOT NULL  -- 过滤掉分组集生成的null层级结果
GROUP BY part_date, platid
ORDER BY part_date asc;

说明 #

两种方案都能彻底排除return_gap为null的元素，其中方案1逻辑更简洁高效，适合大多数场景；方案2仅在需要利用分组集实现多维度聚合时使用。

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