I'm having an issue extracting data out of events coming from Azure IOT Hub to Azure Stream Analytics (ASA) to Power BI.
Here is an example of the feed coming into Azure IOT Hub:
"data": "[{\"Temperature\":74.15750885,\"Humidity\":26.32574463}]",
"device_id": "1e0037000d47353136383631",
"event": "Photon_WeatherData",
"published_at": "2017-12-12T16:52:18.405Z"
I can get this pushed into Power BI through ASA with a direct query:
SELECT *
INTO PowerBI
FROM PhotonEventData
Which results in this: Screenshot of PBI Dataset
[{"Temperature":73.81002045,"Humidity":26.56988525}]
But I can't seem to peel out the data points in ASA so that I can pass each key/value pair to Power BI. I've tried a few different things such as:
SELECT data.Temperature
INTO PowerBI
FROM PhotonEventData
Since data is an array and not a complex type
SELECT data.Temperate
won't work.You need to us the
GetArrayElement
method, see the docs. And since the array consists complex types you need GetRecordPropertyValue to get the value of theTemperature
property:EDIT: As the other answer points out your data property contains an array of strings instead of an array of json objects. If you can fix that so it properly represents an object like below you can use my solution: