Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

DROP TABLE IF EXISTS tmp2;

 CREATE TEMP  table tmp2 (
      c TEXT
    );


insert into tmp2 values
(                   N'{"SerialNumber":"907578","SoftwareVersion":"1.2.777","Build":"4829","ProductCode":"TR-3500-A","BuildDate":null,"Description":"Study desk","ConnectionType":2,"Capabilities":[2,3],
                        "ChannelListDto":[
                                            {"ChannelId":0,"ConversionType":0,"DeviceSerialNumber":null,"Dimension":"","FixedName":null,"Name":"test2","InstrumentationChannel":-1,"IsAlarmable":false,"IsInternal":true,"IsEnableable":false,"IsEnabled":false,"JournalledReadingBytes":0,"LowerLimit":null,"Precision":null,"Symbol":"","TypeId":5,"UpperLimit":null},
                                            {"ChannelId":1,"ConversionType":0,"DeviceSerialNumber":null,"Dimension":"","FixedName":null,"Name":null,"InstrumentationChannel":-1,"IsAlarmable":false,"IsInternal":true,"IsEnableable":false,"IsEnabled":false,"JournalledReadingBytes":0,"LowerLimit":null,"Precision":null,"Symbol":"","TypeId":5,"UpperLimit":null},
                                            {"ChannelId":2,"ConversionType":0,"DeviceSerialNumber":null,"Dimension":"","FixedName":null,"Name":null,"InstrumentationChannel":-1,"IsAlarmable":false,"IsInternal":true,"IsEnableable":false,"IsEnabled":false,"JournalledReadingBytes":0,"LowerLimit":null,"Precision":null,"Symbol":"","TypeId":5,"UpperLimit":null}
                                         ]
                        }'
)
returning jsonb_pretty(c::jsonb);

DROP TABLE



CREATE TABLE




jsonb_pretty


{
    "Build": "4829",
    "BuildDate": null,
    "Description": "Study desk",
    "ProductCode": "TR-3500-A",
    "Capabilities": [
        2,
        3
    ],
    "SerialNumber": "907578",
    "ChannelListDto": [
        {
            "Name": "test2",
            "Symbol": "",
            "TypeId": 5,
            "ChannelId": 0,
            "Dimension": "",
            "FixedName": null,
            "IsEnabled": false,
            "Precision": null,
            "IsInternal": true,
            "LowerLimit": null,
            "UpperLimit": null,
            "IsAlarmable": false,
            "IsEnableable": false,
            "ConversionType": 0,
            "DeviceSerialNumber": null,
            "InstrumentationChannel": -1,
            "JournalledReadingBytes": 0
        },
        {
            "Name": null,
            "Symbol": "",
            "TypeId": 5,
            "ChannelId": 1,
            "Dimension": "",
            "FixedName": null,
            "IsEnabled": false,
            "Precision": null,
            "IsInternal": true,
            "LowerLimit": null,
            "UpperLimit": null,
            "IsAlarmable": false,
            "IsEnableable": false,
            "ConversionType": 0,
            "DeviceSerialNumber": null,
            "InstrumentationChannel": -1,
            "JournalledReadingBytes": 0
        },
        {
            "Name": null,
            "Symbol": "",
            "TypeId": 5,
            "ChannelId": 2,
            "Dimension": "",
            "FixedName": null,
            "IsEnabled": false,
            "Precision": null,
            "IsInternal": true,
            "LowerLimit": null,
            "UpperLimit": null,
            "IsAlarmable": false,
            "IsEnableable": false,
            "ConversionType": 0,
            "DeviceSerialNumber": null,
            "InstrumentationChannel": -1,
            "JournalledReadingBytes": 0
        }
    ],
    "ConnectionType": 2,
    "SoftwareVersion": "1.2.777"
}



INSERT 0 1


        select
               c::json ->> 'SerialNumber'                               as SerialNumber,
               cto ->> 'ChannelId'::INT                                 AS channelid,
               cto ->> 'ChannelName'                                    AS ChannelName
        from tmp2
        CROSS JOIN LATERAL    json_array_elements(c::json ->'ChannelListDto') Channels(cto);

ERROR:  invalid input syntax for type integer: "ChannelId"
LINE 3:                cto ->> 'ChannelId'::INT                     ...
                               ^


select c::json ->> 'SerialNumber' as SerialNumber,
       Channels.cto --> 'ChannelID',
from tmp2
CROSS JOIN LATERAL json_array_elements(c::json ->'ChannelListDto') Channels(cto);


serialnumber
cto


907578
{"ChannelId":0,"ConversionType":0,"DeviceSerialNumber":null,"Dimension":"","FixedName":null,"Name":"test2","InstrumentationChannel":-1,"IsAlarmable":false,"IsInternal":true,"IsEnableable":false,"IsEnabled":false,"JournalledReadingBytes":0,"LowerLimit":null,"Precision":null,"Symbol":"","TypeId":5,"UpperLimit":null}

907578
{"ChannelId":1,"ConversionType":0,"DeviceSerialNumber":null,"Dimension":"","FixedName":null,"Name":null,"InstrumentationChannel":-1,"IsAlarmable":false,"IsInternal":true,"IsEnableable":false,"IsEnabled":false,"JournalledReadingBytes":0,"LowerLimit":null,"Precision":null,"Symbol":"","TypeId":5,"UpperLimit":null}

907578
{"ChannelId":2,"ConversionType":0,"DeviceSerialNumber":null,"Dimension":"","FixedName":null,"Name":null,"InstrumentationChannel":-1,"IsAlarmable":false,"IsInternal":true,"IsEnableable":false,"IsEnabled":false,"JournalledReadingBytes":0,"LowerLimit":null,"Precision":null,"Symbol":"","TypeId":5,"UpperLimit":null}



SELECT 3


SELECT c::json ->> 'SerialNumber' AS "SerialNumber",
       (cto ->> 'ChannelId')::INT AS "ChannelId",
       cto ->> 'Name'             AS "ChannelName"
FROM tmp2, json_array_elements(c::json ->'ChannelListDto') AS "Channels"(cto);


SerialNumber
ChannelId
ChannelName


907578
0
test2

907578
1
null

907578
2
null



SELECT 3

serialnumber	cto
907578	{"ChannelId":0,"ConversionType":0,"DeviceSerialNumber":null,"Dimension":"","FixedName":null,"Name":"test2","InstrumentationChannel":-1,"IsAlarmable":false,"IsInternal":true,"IsEnableable":false,"IsEnabled":false,"JournalledReadingBytes":0,"LowerLimit":null,"Precision":null,"Symbol":"","TypeId":5,"UpperLimit":null}
907578	{"ChannelId":1,"ConversionType":0,"DeviceSerialNumber":null,"Dimension":"","FixedName":null,"Name":null,"InstrumentationChannel":-1,"IsAlarmable":false,"IsInternal":true,"IsEnableable":false,"IsEnabled":false,"JournalledReadingBytes":0,"LowerLimit":null,"Precision":null,"Symbol":"","TypeId":5,"UpperLimit":null}
907578	{"ChannelId":2,"ConversionType":0,"DeviceSerialNumber":null,"Dimension":"","FixedName":null,"Name":null,"InstrumentationChannel":-1,"IsAlarmable":false,"IsInternal":true,"IsEnableable":false,"IsEnabled":false,"JournalledReadingBytes":0,"LowerLimit":null,"Precision":null,"Symbol":"","TypeId":5,"UpperLimit":null}