PostgreSQL中如何将[NULL]转换为NULL？

问题原因 #

当照片没有关联标签时，LEFT JOIN会生成一条t.tag为NULL的记录，array_agg(t.tag)会将这个NULL聚合为包含单个NULL元素的数组[NULL]，而非直接返回NULL——这也是你之前用coalesce/ifnull无效的核心原因：这两个函数仅对NULL值生效，而[NULL]是一个非NULL的数组对象。

解决方案 #

推荐两种实用的处理方式，优先选择第一种更简洁优雅的写法：

方法1：用FILTER过滤NULL后转NULL

通过array_agg的FILTER子句先排除t.tag为NULL的记录，无标签时会得到空数组[]，再用COALESCE将空数组转为NULL：

SELECT 
  p.photo_id, 
  p.name, 
  p.path, 
  COALESCE(array_agg(t.tag) FILTER (WHERE t.tag IS NOT NULL), NULL) AS tags 
FROM photos p
JOIN users u USING (user_id)
LEFT JOIN photo_tags pt USING (photo_id)
LEFT JOIN tags t USING (tag_id)
WHERE u.user_id = 'some_uuid'
GROUP BY p.photo_id, p.name, p.path
ORDER BY date(p.date_created) DESC, p.date_created ASC

方法2：用CASE判断数组内容

直接判断聚合后的数组是否等于[NULL]，如果是则返回NULL，否则返回原数组：

SELECT 
  p.photo_id, 
  p.name, 
  p.path, 
  CASE 
    WHEN array_agg(t.tag) = ARRAY[NULL::text] THEN NULL 
    ELSE array_agg(t.tag) 
  END AS tags 
FROM photos p
JOIN users u USING (user_id)
LEFT JOIN photo_tags pt USING (photo_id)
LEFT JOIN tags t USING (tag_id)
WHERE u.user_id = 'some_uuid'
GROUP BY p.photo_id, p.name, p.path
ORDER BY date(p.date_created) DESC, p.date_created ASC

补充说明 #

• 方法1的优势：即使标签表中存在业务上的NULL值（虽然标签通常不会为空），也能正确过滤无效值，同时覆盖无标签的场景。
• 方法2的特点：直接针对你遇到的[NULL]场景，但如果标签表存在合法的NULL标签，会出现误判。

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