IoT Edge v2 with the modbus module sends data to IoT Hub in the format of:
[
{
"DisplayName": "Voltage",
"HwId": "",
"Address": "400001",
"Value": "200",
"SourceTimestamp": "2019-01-03 23:40:24"
},
{
"DisplayName": "Voltage",
"HwId": "",
"Address": "400002",
"Value": "24503",
"SourceTimestamp": "2019-01-03 23:40:24"
},
...
]
I want to convert this array to rows using a stream analytics query containing the CROSS APPLY GetArrayElements() but this function requires an array name. Obviously there is no name. Any suggestions?
https://learn.microsoft.com/en-us/stream-analytics-query/getarrayelements-azure-stream-analytics https://learn.microsoft.com/en-us/azure/stream-analytics/stream-analytics-parsing-json
Yes, it needs an array name. CROSS APPLY GetArrayElements()
is used for nested array.
Example:
[{
"source": "xda",
"data":
[{
"masterTag": "UNIFY1",
"speed": 180
},
{
"masterTag": "UNIFY2",
"speed": 180
}],
"EventEnqueuedUtcTime": "2018-07-20T19:28:18.5230000Z",
},
{
"source": "xda",
"data": [{
"masterTag": "UNIFY3",
"speed": 214
},
{
"masterTag": "UNIFY4",
"speed": 180
}],
"EventEnqueuedUtcTime": "2018-07-20T19:28:20.5550000Z",
}
]
You could use below sql to convert it to rows:
SELECT
jsoninput.source,
arrayElement.ArrayValue.masterTag
INTO
output
FROM jsoninput
CROSS APPLY GetArrayElements(jsoninput.data) AS arrayElement
However ,now the input data you provided is a pure array. If you want to convert this array to rows, just use sql:
select jsoninput.* from jsoninput