PostgreSQL从JSON列提取数组报错：无法对标量调用json_array_elements

报错原因 #

报错ERROR: cannot call json_array_elements on a scalar的核心问题是：你的data字段本身是JSON数组类型（外层包含[]），而非单个JSON对象。原SQL直接通过a.data -> 'restoration_records'尝试获取属性，数组没有该属性，返回无效的标量值，导致json_array_elements无法处理。

修正后的SQL方案 #

方案1：分步展开嵌套数组（基础写法） #

先展开外层的data数组，再展开内部的restoration_records数组，最终提取目标字段：

SELECT
    restored_record ->> 'date_restored' AS date_restored
FROM members a
-- 展开外层data数组
CROSS JOIN LATERAL json_array_elements(a.data) AS x(data_obj)
-- 展开data对象中的restoration_records数组
CROSS JOIN LATERAL json_array_elements(x.data_obj -> 'restoration_records') AS y(restored_record);

方案2：合并结果为逗号分隔字符串（匹配期望输出） #

如果需要将所有date_restored值合并成单个逗号分隔的字符串，使用string_agg函数：

SELECT
    string_agg(restored_record ->> 'date_restored', ', ') AS date_restored_list
FROM members a
CROSS JOIN LATERAL json_array_elements(a.data) AS x(data_obj)
CROSS JOIN LATERAL json_array_elements(x.data_obj -> 'restoration_records') AS y(restored_record);

执行后会输出：2019-05-30, 2024-01-30，完全匹配你的期望结果。

可选优化：兼容空数据场景 #

如果data字段可能为空数组，或者部分行的restoration_records为空，将CROSS JOIN LATERAL替换为LEFT JOIN LATERAL，避免丢失无对应数据的行：

SELECT
    COALESCE(string_agg(restored_record ->> 'date_restored', ', '), '') AS date_restored_list
FROM members a
LEFT JOIN LATERAL json_array_elements(a.data) AS x(data_obj) ON true
LEFT JOIN LATERAL json_array_elements(x.data_obj -> 'restoration_records') AS y(restored_record) ON true;

方案3：使用JSONPath简化写法（PostgreSQL 12+） #

如果你的PostgreSQL版本在12及以上，支持JSONPath语法，可以用更简洁的方式实现：

SELECT
    string_agg(jsonb_path_query_first(a.data::jsonb, '$[*].restoration_records[*].date_restored') ->> 0, ', ') AS date_restored_list
FROM members a;

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