Azure Stream Analytics Query for Power BI

Question

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

Answer 1

it seems the "data" field is formatted as a string and not as a JSON object. You may want to parse it to JSON using the JavaScript UDF functions. For this you can create this function in Azure Stream Analytics:

function main(input, key) {
   //  return key
    var myinput = String(input)
    myinput = myinput.substring(1, myinput.length - 1);
    values = JSON.parse(myinput);
    return values[key]
 }

Then your query will be:

select udf.extract(data,'Temperature') as temp, udf.extract(data,'Humidity') as hmdt from PhotonEventData

Let me know if you have any further question.

Answer 2

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 the Temperature property:

SELECT GetRecordPropertyValue(GetArrayElement(data, 0), 'Temperature') AS Temperature
INTO PowerBI
FROM PhotonEventData

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:

{
    "data": [{ "Temperature":74.15750885, "Humidity":26.32574463}],
    "device_id": "1e0037000d47353136383631",
    "event": "Photon_WeatherData",
    "published_at": "2017-12-12T16:52:18.405Z"
}