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?
You have a nested array, you have to use jsonb_array_elements() twice: