PostgreSQL中提取JSON数组数据的问题求助

问题分析 #

你写的两段代码都存在语法或用法错误：

1. unnest用法错误：unnest仅适用于PostgreSQL原生数组（如text[]），无法直接处理JSON类型的数组，所以unnest(json_data.record)会报错。
2. json_to_recordset参数错误：你用json_data->>'record'提取的是字符串类型的JSON文本，而json_to_recordset需要接收JSON类型的输入；同时函数调用语法有误，列定义应紧跟在函数别名之后。

正确实现代码 #

方案一：使用json_to_recordset（推荐） #

with raw_data as (
    select '{ "id" : 1, "record" : [   { "field1" : "data1" , "field2":"data2"},  { "field1" : "data3" , "field2":"data4"}  ] }'::json json_data
    union all
    select '{ "id" : 2,"record" : [   { "field1" : "data1" , "field2":"data2"},  { "field1" : "data3" , "field2":"data4"}  ] }'::json as json_data
)
SELECT
    json_data->>'id' as id,
    record.field1,
    record.field2
FROM
    raw_data,
    json_to_recordset(json_data->'record') as record(field1 text, field2 text);

方案二：使用json_array_elements配合字段提取 #

如果你的PostgreSQL版本较低（不支持json_to_recordset），可以用json_array_elements先展开JSON数组，再逐个提取字段：

with raw_data as (
    select '{ "id" : 1, "record" : [   { "field1" : "data1" , "field2":"data2"},  { "field1" : "data3" , "field2":"data4"}  ] }'::json json_data
    union all
    select '{ "id" : 2,"record" : [   { "field1" : "data1" , "field2":"data2"},  { "field1" : "data3" , "field2":"data4"}  ] }'::json as json_data
)
SELECT
    json_data->>'id' as id,
    json_record->>'field1' as field1,
    json_record->>'field2' as field2
FROM
    raw_data,
    json_array_elements(json_data->'record') as json_record;

关键修正点说明 #

• 用json_data->'record'替代json_data->>'record'：->操作符返回JSON类型，符合json_to_recordset/json_array_elements的参数要求；->>返回字符串类型，无法被JSON处理函数识别。
• json_to_recordset的语法：列定义需要写在as record(...)里，与函数调用紧密关联，不能换行拆分。
• json_array_elements专门用于展开JSON数组，返回每行一个JSON对象，再通过->>提取字段值。

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