CODEWITHSUNDEEP
azure-eventhubazure-stream-analyticsstream-analytics
jimsson
jimsson

Reputation: 13

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

Upvotes: 1

Views: 298

Answers (1)

Jay Gong
Jay Gong

Reputation: 23782

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

Upvotes: 1

Related Questions