Select the first element in a JSON array in Microsoft stream analytics query

Question

So I got a bit of a 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"
        }
      ]
    }
  }
}

Also some explanation on what it is I exactly need to do (not just an example or code answer) would be nice for the future. (The request element hast the same problem after all.)

Thanks in advance :)

var json; var value = json.data.data.current_condition[0]; — Jacky
– Jacky, Commented Feb 19, 2016 at 11:15
@Jacky I do not want to do this in the program, I want to do this in the stream analytics! I am making a generic program to retrieve date from a database or API and send it to an azure IoT hub. — Arastelion
– Arastelion, Commented Feb 19, 2016 at 11:45
I see, my bad, was commented on the phone and misleading your question — Jacky
– Jacky, Commented Feb 19, 2016 at 13:03
You need to use GetArrayElement function (msdn.microsoft.com/en-us/library/azure/mt270218.aspx). For example, SELECT GetRecordProperty(GetArrayElement(Current_Condition, 0), 'humidity'). — Konstantin Zoryn
– Konstantin Zoryn, Commented Feb 19, 2016 at 22:31

Konstantin Zoryn · Accepted Answer · 2016-02-19 22:38:53Z

5

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

answered Feb 19, 2016 at 22:38

Konstantin Zoryn

6214 silver badges3 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Arastelion Over a year ago

Thank you! :D This should help me out greatly,

viperguynaz Over a year ago

GetRecordProperty is now GetRecordPropertyValue

Collectives™ on Stack Overflow

Select the first element in a JSON array in Microsoft stream analytics query

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related