October 6, 2022 at 4:58 pm
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
October 6, 2022 at 6:38 pm
A couple of things:
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
October 6, 2022 at 7:44 pm
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
;
October 6, 2022 at 7:52 pm
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
October 6, 2022 at 8:01 pm
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/61537October 6, 2022 at 9:18 pm
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy