使用json_agg与json_build_object时如何返回空数组而非含null的数组

问题描述 #

原SQL查询在LEFT JOIN子产品表后，使用json_agg聚合子产品信息：

SELECT p.id, 
       json_agg(
                json_build_object('id', sp.id)
                ) AS subProducts
  FROM product p
  LEFT JOIN sub_product sp
    ON sp.product_id = p.id
 GROUP BY p.id

当某个product没有对应的sub_product时，subProducts字段会返回[{"id": null}]，但期望返回空数组[]。

优化方案 #

方案1：使用FILTER子句结合COALESCE #

这是最简洁优雅的写法，直接在聚合函数中过滤掉sp.id为null的行，再将聚合结果为null的情况替换为空数组：

SELECT p.id, 
       COALESCE(
         json_agg(json_build_object('id', sp.id)) FILTER (WHERE sp.id IS NOT NULL),
         '[]'::json
       ) AS subProducts
FROM product p
LEFT JOIN sub_product sp ON sp.product_id = p.id
GROUP BY p.id

方案2：用CASE判断子产品是否存在 #

通过EXISTS提前检查当前product是否有子产品，再决定返回聚合结果还是空数组：

SELECT p.id, 
       CASE 
         WHEN EXISTS (SELECT 1 FROM sub_product sp WHERE sp.product_id = p.id)
         THEN json_agg(json_build_object('id', sp.id))
         ELSE '[]'::json
       END AS subProducts
FROM product p
LEFT JOIN sub_product sp ON sp.product_id = p.id
GROUP BY p.id

原理说明 #

原查询中，LEFT JOIN会为无匹配子产品的product生成一行sp.id为null的记录，json_agg会将这条记录包含进去，因此得到[{"id": null}]。

• 方案1通过FILTER (WHERE sp.id IS NOT NULL)排除了null行，此时无匹配时json_agg返回null，再用COALESCE将其替换为[]。
• 方案2通过EXISTS判断是否存在子产品，避免聚合null记录，直接返回空数组。

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