azure-eventhubazure-stream-analyticsstream-analytics

Stream analytics - Pocessing JSON with no array name


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

Solution

  • 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:

    enter image description here

    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:

    enter image description here