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
(
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}
]
}'
)
Now i want to use some code to extract the information from each of the elements in the ChannelListDTo as follows:
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);
However I am getting the following error:
[2024-01-11 17:54:14] [22P02] ERROR: invalid input syntax for type integer: "ChannelId" [2024-01-11 17:54:14] Position: 122
I want to get the following results:
DeviceSerialNumber channelID ChannelName IsInternal
------------------ ----------- --------------------------------------- -----------
907578 0 test2 1
907578 1 1
907578 2 1
I have tried the following :
select c::json ->> 'SerialNumber' as SerialNumber,
Channels.cto --> 'ChannelID',
from tmp2
CROSS JOIN LATERAl json_array_elements(c::json ->'ChannelListDto') Channels(cto);
And this yields the following:
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}" |
So how can I extract the individual elements (ChannelID, ConversionType, IsInternal etc) out of the ChannelListDTO array of JSON Objects
2
Answers
You are trying to cast the key text ‘ChannelId’ to
integer
instead of the returned value. The above fetches the value first then casts it to an integer.cto->>'ChannelId'
that gets cast to::int
. Otherwise the cast takes precendence, so you end up first attempting this'ChannelId'::int
before it’s given to->>
, which gives you the error since that text won’t work as a number.CROSS JOIN LATERAL
for just a comma,
– the fact that the set-returning function relies on a field taken fromtmp2
implies the rest.cto->>'Name'
, because the json you showed doesn’t have a'ChannelName'
key in it.-->
inChannels.cto --> 'ChannelID',
isn’t an operator. You just selected the entireChannels.cto
and then--
started an end-of-line comment.Demo at db<>fiddle