PostgreSQL处理空JSON数组：保留空行并设置默认值

要解决空数组行不显示且设置默认值的问题，核心是用LEFT JOIN LATERAL替代CROSS JOIN LATERAL，确保主表所有行都被保留，再处理空数组的默认值。这里提供两种可行方案：

方案1：用COALESCE替换NULL值 #

这种方式最简洁，通过左连接保留主表行，再将空数组对应的NULL值替换为预设默认值：

select 
    id, 
    description, 
    coalesce(l.letter, '"x"'::json) as letter
from
(
    values
    (1, 'good', '["a","b", "c"]'::json),
    (2, 'bad', '[]'::json)
) as aTable(id,description,letters)
LEFT JOIN LATERAL (
    SELECT json_array_elements(letters) as letter
) as l ON true;

执行后结果：

方案2：在LATERAL子查询中处理空数组 #

如果需要更灵活的逻辑（比如空数组时返回多个默认值），可以用UNION ALL在子查询里分支处理：

select 
    id, 
    description, 
    l.letter
from
(
    values
    (1, 'good', '["a","b", "c"]'::json),
    (2, 'bad', '[]'::json)
) as aTable(id,description,letters)
LEFT JOIN LATERAL (
    -- 数组非空时展开元素
    SELECT json_array_elements(letters) as letter
    WHERE json_array_length(letters) > 0
    UNION ALL
    -- 数组为空时返回默认值
    SELECT '"x"'::json as letter
    WHERE json_array_length(letters) = 0
) as l ON true;

这个方案和方案1结果一致，但逻辑拆分更清晰，方便后续扩展。

为什么之前的方法行不通？ #

1. CASE语句不能包含返回多行的函数：json_array_elements是返回多行的集合函数，PostgreSQL不允许在CASE里使用这类函数，因为CASE期望返回单个值，而不是多行结果。
2. CROSS JOIN LATERAL会过滤空行：当数组为空时，子查询没有返回结果，CROSS JOIN会直接丢弃主表对应的行，改用LEFT JOIN LATERAL才能保留主表所有行。

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