无法在 Postgres 中访问 JSON 中包含的数组的内部元素-解网

问：

我有以下代码，我正在尝试提取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=="
}
     ');

然后，我尝试执行以下代码：

     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] 错误：“channelreadings”列不存在 [2023-11-17 00:09:25]位置： 398

我想得到以下结果，如下所示：

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
'''
How do i get this desired results?

json postgresql-16

SELECT (c ->> 'DeviceSerialNumber')     AS serialnumber
     , (cr ->> 'ChannelId')::INT        AS channelid
     , (c ->> 'RestartPointerNo')::INT  AS restartpointerno
     , dto ->> 'Si'                     AS si
     , dto ->> 'Raw'                    AS raw
     , (dto ->> 'TimeStamp')::timestamp AS timestamp
FROM tmp2
   CROSS JOIN LATERAL JSONB_ARRAY_ELEMENTS(c -> 'ChannelReadings') channelreadings(cr)
   CROSS JOIN LATERAL JSONB_ARRAY_ELEMENTS(cr -> 'ReadingsDto')    readingsdto(dto);

无法在 Postgres 中访问 JSON 中包含的数组的内部元素

Cannot access inner elements of array contained in JSON in Postgres

评论

评论