Cannot access inner elements of array contained in JSON in Postgres

Question

I have the following code in which I am trying to extract JSON as follows:

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=="
}
     ');

I then tried to do the following code:

     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;

and got the following error message:

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

I want to get the following results as follows:

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?

Answer 1

You have a nested array, you have to use jsonb_array_elements() twice:

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);