Complex Data Types (Stream Analytics)

Published: November 12, 2015

Updated: May 24, 2016

Applies To: Azure

Azure Stream Analytics supports processing events in CSV, JSON and Avro data formats.
Both JSON and Avro may contain complex types such as nested objects (records) or arrays. Scenarios may contain complex data types that jobs must run against. Generally they are categorized as Array data types and Record data types.

Array data types are an ordered collection of values. Some typical operations on array values are detailed below. Note that these examples assume the input events have “arrayField” property of array type.

Select array element at a specified index (selecting the first array element):

SELECT   
    GetArrayElement(arrayField, 0) AS firstElement  
FROM input  

Select array length:

SELECT   
    GetArrayLength(arrayField) AS arrayLength  
FROM input  

Select all array element as individual events ( the APPLY (Azure Stream Analytics) operator together with the GetArrayElements (Azure Stream Analytics) built-in function extract all array elements as individual events):

SELECT   
    arrayElement.ArrayIndex,  
    arrayElement.ArrayValue  
FROM input as event  
CROSS APPLY GetArrayElements(event.arrayField) AS arrayElement  

Record data types are used to represent JSON and Avro arrays when corresponding formats are used in the input data streams. . All examples assume a sample sensor which is reading input events in JSON format. Here is example of a single event:

{  
    "DeviceId" : "12345",  
    "Location" : {"Lat": 47, "Long": 122 }  
  
    "SensorReadings" :  
    {  
        "Temperature" : 80,  
        "Humidity" : 70,  
        "CustomSensor01" : 5,  
        "CustomSensor02" : 99  
    }  
}  

Utilize dot notation to access nested fields. For example, this query selects the reported lat/long coordinates of the device:

SELECT  
    DeviceID,  
    Location.Latitude,  
    Location.Longitude  
FROM input  

Use the GetRecordPropertyValue (Azure Stream Analytics) function if the property name is unknown. For example, imagine a sample data stream needs to be joined with reference data containing thresholds for each device sensor:

SELECT  
    input.DeviceID,  
    thresholds.SensorName  
FROM input  
JOIN thresholds  
ON  
    input.DeviceId = thresholds.DeviceId  
WHERE  
    GetRecordPropertyValue(input.SensorReading, thresholds.SensorName) > thresholds.Value  

To convert record fields into separate events use the APPLY (Azure Stream Analytics) operator together with the GetRecordProperties (Azure Stream Analytics) function. For example, to convert a sample stream into a stream of events with individual sensor readings, this query could be used:

SELECT   
    event.DeviceID,  
    sensorReading.PropertyName,  
    sensorReading.PropertyValue  
FROM input as event  
CROSS APPLY GetRecordProperties(event.SensorReadings) AS sensorReading  

SELECT may also utilize '*' to access all the properties of a nested record. Consider the following query:

SELECT input.SensorReadings.*  
FROM input  

This would result in the following output:

{  
    "Temperature" : 80,  
    "Humidity" : 70,  
    "CustomSensor01" : 5,  
    "CustomSensor022" : 99  
}  

Data Types (Azure Stream Analytics)