PostgreSQL中array_agg()忽略空JSON列？如何查询保留空JSON行

问题背景 #

现有PostgreSQL表table1定义如下：

CREATE TABLE table1 (
 id INT PRIMARY KEY,
 name TEXT,
 skills JSON
);

已插入3行测试数据：

INSERT INTO table1 (id, name, skills) VALUES
 (1, 'Alice', '[
                 {"sid" : 11, "description" : "Cardio"}, 
                 {"sid" : 13, "description" : "Anesthist"}
              ]'
 ),
 (2, 'Bob', '[
               {"sid" : 10, "description" : "Gastro"}, 
               {"sid" : 9, "description" : "Oncology"}
              ]'
 ),
 (3, 'Sam', '[]'
 );

期望查询输出包含所有行（包括空JSON数组的Sam行），格式如下：

id   name     skill
---------------------
1   Alice     ["Cardio","Anesthist"]
2   Bob       ["Gastro","Oncology"]
3   Sam       []

但执行以下查询时，会缺失第三行（空JSON数组的行）：

select
  id,
  name,
  array_agg(skill ->> 'description') as skill
from table1, json_array_elements(skills) AS skill
group by 1, 2

问题原因 #

原查询使用了隐式内连接（逗号分隔表和json_array_elements函数），当skills为空数组时，json_array_elements返回0行，内连接会自动过滤掉原表中对应行，导致Sam的记录丢失。

解决方案 #

使用LEFT JOIN LATERAL保留原表所有行，同时用COALESCE将空聚合结果转为空数组：

SELECT
  id,
  name,
  COALESCE(array_agg(skill ->> 'description'), '{}'::TEXT[]) AS skill
FROM table1
LEFT JOIN LATERAL json_array_elements(skills) AS skill ON true
GROUP BY 1, 2;

说明 #

• LEFT JOIN LATERAL：PostgreSQL的LATERAL连接允许引用左侧表的列，并且会保留左侧表的所有行，即使右侧函数返回0行。
• COALESCE：当array_agg没有聚合到任何元素时会返回NULL，用COALESCE将其转换为空数组'{}'::TEXT[]，符合期望输出格式。

也可以用CASE语句显式判断空JSON数组，效果一致：

SELECT
  id,
  name,
  CASE
    WHEN skills = '[]'::JSON THEN '{}'::TEXT[]
    ELSE array_agg(skill ->> 'description')
  END AS skill
FROM table1
LEFT JOIN LATERAL json_array_elements(skills) AS skill ON true
GROUP BY 1, 2;

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