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 (1)

  1. Nat says:

    Hi, thanks for this. In your example, how do you then retrieve the values of the weatherDesc array? I’m trying to work with nested arrays but getting stuck. Many thanks for your help.

Skip to main content