PostgreSQL无法访问JSON嵌套数组元素，如何提取目标数据？

问题场景 #

已创建存储JSON文本的临时表tmp2并插入嵌套数组结构的JSON数据：

DROP TABLE IF EXISTS tmp2;

CREATE TEMP table tmp2 (
      c TEXT
);

insert into tmp2 values
(' {"ChannelReadings": [
    { "ReadingsDto": [
            {
                "Si": 47.67,
                "Raw": 0,
                "Conversion": 0,
                "TimeStamp": "2023-01-24T12:57:43"
            },
            {
                "Si": 47.22,
                "Raw": 0,
                "Conversion": 0,
                "TimeStamp": "2023-01-24T13:02:43"
            },
            {
                "Si": 47.6,
                "Raw": 0,
                "Conversion": 0,
                "TimeStamp": "2023-01-24T13:07:43"
            },
            {
                "Si": 47.5,
                "Raw": 0,
                "Conversion": 0,
                "TimeStamp": "2023-01-24T13:12:43"
            }
        ],
        "ChannelId": 14
    },
    {
        "ReadingsDto": [
            {
                "Si": 2.893605,
                "Raw": 0,
                "Conversion": 0,
                "TimeStamp": "2023-01-24T13:07:43"
            }
        ],
        "ChannelId": 12
    },
    {
        "ReadingsDto": [
            {
                "Si": 3.294233,
                "Raw": 0,
                "Conversion": 0,
                "TimeStamp": "2023-01-24T13:07:43"
            }
        ],
        "ChannelId": 13
    },
    {
        "ReadingsDto": [
            {
                "Si": 3.294233,
                "Raw": 0,
                "Conversion": 0,
                "TimeStamp": "2023-01-24T13:07:43"
            }
        ],
        "ChannelId": 16
    }
],
"DeviceSerialNumber": "894339",
"RestartPointerNo": 5514732,
"NewDownloadTable": false,
"DataHashDto": "5Mckxoq42EeLHmLnimXv6A=="
}
 ');

尝试执行以下SQL提取数据时触发错误：

select
        c::json ->> 'DeviceSerialNumber' as SerialNumber,
        c::json ->> 'ReadingsDto.ChannelID'::int as ChannelID,
        (c::json ->> 'RestartPointerNo')::int as RestartPointerNo,
        Readings.SI::Real,
        Readings.RAW::Real,
        Readings.Timestamp::timestamp as TimeStamp2

from tmp2
CROSS JOIN LATERAL jsonb_array_elements(ChannelReadings ->'ReadingsDto') Readings;

错误信息：

[2023-11-17 00:09:25] [42703] ERROR: column "channelreadings" does not exist

期望得到的结果集格式：

DeviceSerialNumber channelID   Si                                      Raw         TimeStamp
------------------ ----------- --------------------------------------- ----------- -----------------------
894339             12          2.89                                    0           2023-01-24 13:07:43.000
894339             13          3.29                                    0           2023-01-24 13:07:43.000
894339             14          47.67                                   0           2023-01-24 12:57:43.000
894339             14          47.22                                   0           2023-01-24 13:02:43.000
894339             14          47.60                                   0           2023-01-24 13:07:43.000
894339             14          47.50                                   0           2023-01-24 13:12:43.000
894339             16          3.29                                    0           2023-01-24 13:07:43.000

正确SQL语句 #

SELECT
    json_data ->> 'DeviceSerialNumber' AS DeviceSerialNumber,
    channel ->> 'ChannelId' AS channelID,
    ROUND((reading ->> 'Si')::numeric, 2) AS Si,
    (reading ->> 'Raw')::int AS Raw,
    (reading ->> 'TimeStamp')::timestamp AS TimeStamp
FROM tmp2
CROSS JOIN LATERAL (SELECT c::jsonb AS json_data) AS j
CROSS JOIN LATERAL jsonb_array_elements(json_data -> 'ChannelReadings') AS channel
CROSS JOIN LATERAL jsonb_array_elements(channel -> 'ReadingsDto') AS reading;

错误原因及说明 #

1. 未正确引用JSON字段：原SQL中直接使用ChannelReadings作为列名，而它是JSON结构内的数组，需要从c字段转换后的JSON对象中提取，即json_data -> 'ChannelReadings'。
2. 嵌套数组需逐层解析：JSON包含两层嵌套数组（ChannelReadings和ReadingsDto），需要通过两次LATERAL jsonb_array_elements来逐层展开。
3. 字段路径错误：原SQL中c::json ->> 'ReadingsDto.ChannelID'的路径不存在，ChannelId属于ChannelReadings数组中的每个对象，而非ReadingsDto下的字段。
4. 类型转换与格式化：使用ROUND函数将Si字段保留两位小数，匹配期望结果的格式；同时确保Raw和时间戳的类型转换正确。

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