Parse JSON and Avro data in Azure Stream Analytics

Azure Stream Analytics supports processing events in CSV, JSON, and Avro data formats. Both JSON and Avro data can contain complex types such as nested objects (records) and arrays.

Array data types

Array data types are an ordered collection of values. Some typical operations on array values are detailed below. These examples assume the input events have a property named "arrayField" that is an array datatype.

These examples use the functions GetArrayElement, GetArrayElements, GetArrayLength, and the APPLY operator.

Examples

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 operator together with the GetArrayElements built-in function extracts 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

Record data types are used to represent JSON and Avro arrays when corresponding formats are used in the input data streams. These examples demonstrate 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
    }
}

Examples

Use dot notation (.) to access nested fields. For example, this query selects the Lat and Long coordinates under the Location property in the preceding JSON data:

SELECT
    DeviceID,
    Location.Lat,
    Location.Long
FROM input

Use the GetRecordPropertyValue 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:

{
    "DeviceId" : "12345",
    "SensorName" : "Temperature",
    "Value" : 75
}
SELECT
    input.DeviceID,
    thresholds.SensorName
FROM input
JOIN thresholds
ON
    input.DeviceId = thresholds.DeviceId
WHERE
    GetRecordPropertyValue(input.SensorReadings, thresholds.SensorName) > thresholds.Value

To convert record fields into separate events, use the APPLY operator together with the GetRecordProperties 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

You can select all the properties of a nested record using '*' wildcard. Consider the following example:

SELECT input.SensorReadings.*
FROM input

The result is:

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

See Also

Data Types in Azure Stream Analytics