skip to Main Content

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


  1. (cto ->> 'ChannelId')::int 
    

    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.

    Login or Signup to reply.
  2. 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
    1. As already pointed out, you need parentheses to make sure it’s the output of 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.
    2. You can pretty safely trade CROSS JOIN LATERAL for just a comma , – the fact that the set-returning function relies on a field taken from tmp2 implies the rest.
    3. You need to double-quote all your mixed-case identifiers, otherwise they will all get folded to lowercase.
    4. It seems that you actually wanted cto->>'Name', because the json you showed doesn’t have a 'ChannelName' key in it.
    5. The --> in Channels.cto --> 'ChannelID', isn’t an operator. You just selected the entire Channels.cto and then -- started an end-of-line comment.

    Demo at db<>fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search