I would like 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?
Example of JSON to parse :
{
"Alert1": {
"Site": "11109370",
"Sensor": "UPS",
"Event": "Load Not Protected",
"Time": "08-28-2019 10:39:02",
"Value": "0",
"Threshold": 0,
"Severity": "2",
"Status": "Open"
},
"Alert2": {
"Site": "11109370",
"Sensor": "UPS",
"Event": "Emergency stop",
"Time": "08-28-2019 10:39:02",
"Value": "1",
"Threshold": 1,
"Severity": "2",
"Status": "Open"
},
"Alert3": {
"Site": "11109370",
"Sensor": "UPS",
"Event": "Load Protected(UPS Coupled)",
"Time": "08-28-2019 10:39:02",
"Value": "0",
"Threshold": 0,
"Severity": "1",
"Status": "Open"
},
"Alert4": {
"Site": "11109370",
"Sensor": "UPS",
"Event": "Battery Deep Discharge Protection",
"Time": "08-28-2019 10:39:02",
"Value": "0",
"Threshold": 0,
"Severity": "1",
"Status": "Open"
},
"Alert5": {
"Site": "11109370",
"Sensor": "UPS",
"Event": "Battery Present",
"Time": "08-28-2019 10:39:02",
"Value": "0",
"Threshold": 0,
"Severity": "1",
"Status": "Open"
},
"Alert6": {
"Site": "11109370",
"Sensor": "UPS",
"Event": "Sensor Communication Error",
"Time": "08-28-2019 10:39:02",
"Status": "Close"
}
}
I tried the code below but I encountered an error :
Error : Invalid column name: 'arrayvalue'. Column with such name does not exist.
SELECT message.ArrayValue
FROM INPUT as event
CROSS APPLY GetRecordProperties(event) AS message
Your direction is right,however, i think you made a little mistake about the usage of GetRecordProperties
function.
Please see the example stated in the official document,not any ArrayValue
properties:
SELECT
recordProperty.PropertyName,
recordProperty.PropertyValue
FROM input as event
CROSS APPLY GetRecordProperties(event.recordField) AS recordProperty
For your situation, you could execute below sql:
SELECT
recordProperty.PropertyName,
recordProperty.PropertyValue
FROM input as event
CROSS APPLY GetRecordProperties(event) AS recordProperty
Output:
As i asked that how you want to deal with Alert1,Alert2
properties,if you want to get rid of them,then use below sql:
SELECT
recordProperty.PropertyValue.Site, recordProperty.PropertyValue.Sensor....<more your columns>
FROM input as event
CROSS APPLY GetRecordProperties(event) AS recordProperty
Output: