Problem extracting Data from JSON string
-
Hi there
I have a JSON sample and struggling to extract data from it.
This is my query
Declare @json varchar(max)
SET @json = N'
{
"ChannelReadings": [
{
"ReadingsDto": [
{
"Si": 54.03,
"Raw": 0,
"Conversion": 0,
"TimeStamp": "2022-04-22T14:20:57"
},
{
"Si": 53.97,
"Raw": 0,
"Conversion": 0,
"TimeStamp": "2022-04-22T14:22:57"
},
{
"Si": 54.01,
"Raw": 0,
"Conversion": 0,
"TimeStamp": "2022-04-22T14:24:57"
}
],
"ChannelId": 17,
"DataHashDto": ""
}
],
"DeviceSerialNumber": "894339"
}
'
SELECT
[device].DeviceSerialNumber,
channel_list.[Si],
channel_list.[Raw]
FROM
OPENJSON(@json)
WITH
(
DeviceSerialNumber NVARCHAR(100) '$.ChannelReadings.DeviceSerialNumber',
items NVARCHAR(MAX) '$.ChannelReadings.ReadingsDto' AS JSON
) [device]
CROSS APPLY
-- ITEMS
OPENJSON(device.items)
WITH
(
[Si] DECIMAL(10,2) ,
[Raw] DECIMAL(10,2)
) channel_list
;
what I want to have is the following details
Device Serial Number ChannelID SI Raw Conversion
894339 17 54.03 0 0
-
A couple of things:
- DeviceSerialNumber is a sibling--not child--of ChannelReadings.
- Remove ChannelReadings from the path for DeviceSerialNumber
- ChannelReadings is an ARRAY of JSON.
- You need to provide an index to the array.
- ReadingsDto is also an ARRAY of JSON.
- You need to provide an index to that array as well.
NOTE: There are other options for dealing with arrays, but this is the quickest way to get the desired results.
SELECT *
FROM OPENJSON(@json)
WITH
(
DeviceSerialNumber NVARCHAR(100) '$.DeviceSerialNumber',
items NVARCHAR(MAX) '$.ChannelReadings[0].ReadingsDto[0]' AS JSON
)
AS [Device]
OUTER APPLY
-- ITEMS
OPENJSON(device.items)
WITH
(
[Si] DECIMAL(10,2) ,
[Raw] DECIMAL(10,2),
[Conversion] INT
)
channel_list
;Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA - DeviceSerialNumber is a sibling--not child--of ChannelReadings.
-
Hi Drew
thansks vey much for that. I should have spotted it was 2 arrays of JSON
I made a slight alteration....removed the [0] from the end of the items array and
also brought in channelID, DataHashDto
as follows:
SELECT
device.DeviceSerialNumber,
device.ChannelId,
device.DataHashDto,
channel_list.*
FROM OPENJSON(@json)
WITH
(
DeviceSerialNumber NVARCHAR(100) '$.DeviceSerialNumber',
ChannelID INT '$.ChannelReadings[0].ChannelId',
DataHashDto NVARCHAR(1000) '$.ChannelReadings[0].DataHashDto',
items NVARCHAR(MAX) '$.ChannelReadings[0].ReadingsDto' AS JSON
)
AS [Device]
OUTER APPLY
-- ITEMS
OPENJSON(device.items)
WITH
(
[Si] DECIMAL(10,2) ,
[Raw] DECIMAL(10,2),
[Conversion] INT,
[TimeStamp] datetime2
)
channel_list
;
-
Hi Drew
If there is more than more collection of Channels, ie more than 1 channel ID, how do i get that out ?
because at the moment when i run this on a biggger dataset , I only get the first ChanneIID
-
This should handle multiple channels
SELECT device.DeviceSerialNumber,Readings.ChannelId,Readings.DataHashDto,
channel_list.Si,channel_list.Raw,channel_list.Conversion,channel_list.TimeStamp
FROM OPENJSON(@json)
WITH
(
ChannelReadings NVARCHAR(MAX) '$.ChannelReadings' AS JSON,
DeviceSerialNumber NVARCHAR(100) '$.DeviceSerialNumber'
) AS device
OUTER APPLY OPENJSON(device.ChannelReadings)
WITH
(
ChannelId INT '$.ChannelId',
DataHashDto NVARCHAR(MAX) '$.DataHashDto',
ReadingsDto NVARCHAR(MAX) '$.ReadingsDto' AS JSON
) AS Readings
OUTER APPLY OPENJSON(Readings.ReadingsDto)
WITH
(
Si DECIMAL(10,2) ,
Raw DECIMAL(10,2),
Conversion INT,
TimeStamp DATETIME
) channel_list;____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Hi marc
thanks for that. I guess the key to it, is when you see a JSON array that you treat it like this
ReadingsDto NVARCHAR(MAX) '$.ReadingsDto' AS JSON
And then use OpenJson to open ReadingsDto
That worked for me
thanks drew and mark for your help on this
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply