Handling Json array in Stream Analytics Query


This week, I am sharing a query question asked in stackoverflow to illustrate how to handle json array in Stream Analytics Query:

 

Problem

 

I retrieve some weatherdata from an external API. This is returned as JSON and send to an Azure IoT hub. Stream analytics processes the json into a proper format, but I got a problem here.

The element: Current_Condition, is of an array format. It always has one element on the [0] position. I only need to get the data of that array from that very first position, without a filter for things like id etc.

Under here is the complete data

{

"deviceId":"aNewDevice",

"data":

{"data":

{"current_condition":[

{

"cloudcover":"0",

"FeelsLikeC":"0",

"FeelsLikeF":"32",

"humidity":"100",

"observation_time":"10:00 AM",

"precipMM":"0.0",

"pressure":"1020",

"temp_C":"2",

"temp_F":"36",

"visibility":"0",

"weatherCode":"143",

"weatherDesc":[{"value":"Fog, Mist"}],

"weatherIconUrl":[{"value":"http://cdn.worldweatheronline.net/images/wsymbols01_png_64/wsymbol_0006_mist.png"}],

"winddir16Point":"SSW",

"winddirDegree":"210",

"windspeedKmph":"7",

"windspeedMiles":"4"

 

}

],

"request":[

{

"query":"Nijmegen, Netherlands",

"type":"City"

}

]

}

}

}

 

Solution:

 

You need to use GetArrayElement function. For example:

SELECT GetRecordProperty(GetArrayElement(Current_Condition,0),'humidity')

To make it a bit nicer you can split query into 2 steps:

WITH CurrentConditions AS
(
SELECT
deviceId,
GetArrayElement(Current_Condition,0)as conditions 
FROM input 
)
SELECT
deviceID,
conditions.humidity 
FROM CurrentConditions
 
Comments (0)

Skip to main content