Stream analytics - Pocessing JSON with no array name

Question

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 example data is not array,it's an object? Any desired output you could share with me? — Jay Gong, Aug 29 '19 at 07:18

score 0 · Accepted Answer · answered Aug 29 '19 at 07:28

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:

Stream analytics - Pocessing JSON with no array name

1 Answers1