Problem extracting Data from JSON string

Question

Post reply

Problem extracting Data from JSON string

Weegee71

SSChasing Mays

Points: 604
More actions
October 6, 2022 at 4:58 pm

#4096317

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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

drew.allen SSC Guru Points: 76985 More actions · Answer 1

A couple of things:

DeviceSerialNumber is a sibling--not child--of ChannelReadings.
1. Remove ChannelReadings from the path for DeviceSerialNumber
ChannelReadings is an ARRAY of JSON.
1. You need to provide an index to the array.
ReadingsDto is also an ARRAY of JSON.
1. 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

Weegee71 SSChasing Mays Points: 604 More actions · Answer 2

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

;

Weegee71 SSChasing Mays Points: 604 More actions · Answer 3

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

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 4

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

Weegee71 SSChasing Mays Points: 604 More actions · Answer 5

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