PostgreSQL视图问题：如何显示含空JSON数组的订单记录

原视图使用json_array_elements直接展开JSON数组时，空数组（[]）会因为没有可返回的元素而被过滤掉，导致对应订单记录无法出现在视图结果中。要保留所有订单记录（包括空数组的情况），可以通过LEFT JOIN LATERAL关联数组元素，再结合条件判断处理空值。

修正后的视图创建语句 #

CREATE OR REPLACE VIEW public.orders_with_prices
AS
SELECT 
    o.id,
    -- 处理labtestgroup字段：空数组时显示[]，否则提取元素并去除引号
    COALESCE(REPLACE(lg.element::TEXT, '"', ''), '[]') AS labtestgroup,
    -- 处理price字段：空数组时显示[]，否则提取元素并去除引号
    COALESCE(REPLACE(p.element::TEXT, '"', ''), '[]') AS price
FROM orders o
-- 左关联展开labTestGroups数组，保留原订单记录
LEFT JOIN LATERAL json_array_elements(o."labTestGroups") lg(element) ON TRUE
-- 左关联展开prices数组，保留原订单记录
LEFT JOIN LATERAL json_array_elements(o.prices) p(element) ON TRUE
-- 确保空数组的订单只返回一行，同时保留有元素的订单的所有行
WHERE 
    (lg.element IS NOT NULL OR p.element IS NOT NULL)
    OR (json_array_length(o."labTestGroups") = 0 AND json_array_length(o.prices) = 0);

关键调整说明 #

1. 使用LEFT JOIN LATERAL：替代原查询中直接在FROM子句调用json_array_elements的方式，确保即使数组为空，原订单记录也能被保留，对应的数组元素字段会返回NULL。
2. COALESCE处理空值：将NULL（空数组时的返回值）替换为'[]'，也可以根据需求改为NULL或'0'。
3. WHERE子句过滤重复行：避免空数组的订单生成多行NULL记录，确保只返回一行；同时保留有数组元素的订单的所有拆分行。

自定义输出值 #

如果不需要显示[]，可以修改COALESCE的第二个参数：

• 显示NULL：将'[]'替换为NULL
• 显示0：将'[]'替换为'0'

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