PostgreSQL中json_agg返回[null]如何替换为空数组？

你的COALESCE理解没错，但场景不匹配 #

COALESCE确实用于替换NULL值，但你当前的情况是json_agg返回的不是NULL，而是包含null元素的数组[null]——这是一个有效的JSON数组值，并非NULL，所以COALESCE不会触发替换逻辑。

原查询的核心问题 #

当所有被聚合的行都通过CASE返回NULL时，json_agg会把这些NULL打包成数组[null]，而非返回NULL。这就导致COALESCE的第二个参数'[]'永远不会被触发使用。

解决办法 #

方法1：先过滤NULL行再聚合 #

使用FILTER子句提前筛掉会生成NULL的行，这样如果没有符合条件的行，json_agg会返回NULL，此时COALESCE就能正常替换成空数组：

COALESCE(
  json_agg(
    json_build_object('id', socials.id, 'name', socials.social_id, 'url', socials.url)
  ) FILTER (WHERE socials.id IS NOT NULL), 
  '[]'::json
) AS socials

方法2：直接判断并替换[null]数组 #

如果需要保留原CASE逻辑，也可以直接判断聚合结果是否为[null]，再替换成空数组：

CASE 
  WHEN json_agg(
    CASE 
      WHEN socials.id IS NULL THEN NULL 
      ELSE json_build_object('id', socials.id, 'name', socials.social_id, 'url', socials.url)
    END
  ) = '[null]'::json 
  THEN '[]'::json 
  ELSE json_agg(
    CASE 
      WHEN socials.id IS NULL THEN NULL 
      ELSE json_build_object('id', socials.id, 'name', socials.social_id, 'url', socials.url)
    END
  )
END AS socials

注意：第二种方法会重复计算json_agg，性能不如第一种，优先推荐方法1。

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