PostgreSQL中JSON转表仅serviceStatus有值其余为空，请求协助排查

你的SQL存在两个关键错误，导致除service_status外其余字段均为null：

1. 嵌套JSON字段提取方式错误 #

PostgreSQL的->>操作符仅能直接访问顶层JSON键，不能通过.拼接嵌套路径。原写法json_data->>'data.status'是在寻找名为data.status的顶层键，但你的JSON结构中根本不存在这个键，因此返回null。

正确的嵌套字段提取方式有两种：

• 逐层访问：先用->获取上层JSON对象，再用->>提取字符串值，例如json_data->'data'->>'status'
• 路径访问：用#>>加路径数组直接定位嵌套字段，例如json_data#>>'{data,status}'

2. 错误处理json_populate_recordset的返回结果 #

json_populate_recordset函数返回的是与目标表结构匹配的行类型数据，而非JSON对象。原写法用item->>'id'试图从行类型中提取JSON字段，会将整个行转为JSON后再查找键，自然无法匹配到对应值，返回null。正确做法是直接引用行的字段名，比如item.id。

修正后的完整SQL #

CREATE TABLE example_data (
    service_status TEXT,
    data_status TEXT,
    data_message TEXT,
    id INT,
    name TEXT,
    age INT
);

INSERT INTO example_data (service_status, data_status, data_message, id, name, age)
SELECT 
    json_data->>'serviceStatus' AS service_status, 
    json_data->'data'->>'status' AS data_status, 
    json_data->'data'->>'message' AS data_message, 
    item.id, 
    item.name, 
    item.age
FROM (
    SELECT '{"serviceStatus": "OK", "data": {"status": "OK", "message": "", "item": [{"id": 1, "name": "John", "age": 30},{"id": 2, "name": "Smith", "age": 40}]}}'::json AS json_data
) AS data
CROSS JOIN LATERAL json_populate_recordset(NULL::example_data, json_data->'data'->'item') AS item;

执行以上修正后的SQL，所有字段都会正确插入对应值。

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