PostgreSQL使用array_agg与array_to_json时出现null值异常行为

问题根因 #

你遇到的这个现象是标准PostgreSQL聚合行为导致的：array_agg默认不会自动过滤输入的NULL值，所有NULL都会被保留到聚合生成的数组中。你给出的示例中id=2的array_agg标注结果{}属于显示误差，实际聚合结果是包含单个NULL元素的数组{NULL}，因此调用array_to_json转换后自然会输出[null]。

解决方案 #

方案1（推荐，PostgreSQL 9.4+支持） #

使用FILTER子句在聚合阶段直接过滤NULL值，语义清晰性能更好：

select id, 
       array_agg(alias) filter (where alias is not null), 
       array_to_json(array_agg(alias) filter (where alias is not null))
from (values (1, 'foo'), (1, 'bar'), (2, null)) t(id, alias)
group by id;

执行后id=2对应的JSON输出为[]，无多余null值。

方案2（兼容旧版本） #

如果使用9.4之前的PostgreSQL版本，可以通过array_remove函数移除聚合后数组中的NULL元素：

select id, 
       array_remove(array_agg(alias), null), 
       array_to_json(array_remove(array_agg(alias), null))
from (values (1, 'foo'), (1, 'bar'), (2, null)) t(id, alias)
group by id;

额外扩展 #

如果希望空数组也转为null，可以搭配nullif函数使用：

nullif(array_agg(alias) filter (where alias is not null), '{}')

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