GetArrayElement (Azure Stream Analytics)

Article
08/02/2024

Returns the array element at the specified index. This function is useful for parsing arrays and nested objects in JSON and AVRO formatted input event data. For more examples, see Parsing JSON and AVRO data. If you need to return all nested elements in an array, use GetArrayElements instead.

Syntax

GetArrayElement ( array_expression, bigint_expression )

Arguments

array_expression

Is the array expression to be evaluated as a source array. array_expression can be a column of type Array or result of another function call.

bigint_expression

Is the bigint expression to be evaluated as array index. The ordinal position in the array of elements, starting at 0.

Return Types

Return type is determined by the array element type and can be any of the supported types.

Examples

Sample data

[
{
    "DeviceId" : "123",
    "SensorReadings" :
    {
        "Temperature" : 80,
        "Humidity" : 70,
        "CustomSensor": [1,1,0]
    }
},
{
    "DeviceId" : "631",
    "SensorReadings" :
    {
        "Temperature" : 81,
        "Humidity" : 69,
        "CustomSensor": [0,1,0]
    }
}
]

The sample dataset above is an array of two records. When used as local input in a JSON file, the top-level array is interpreted for the generation of rows/events by Azure Stream Analytics. There is no need to take it into consideration in the query syntax.

At the individual record level, there are two properties with different types. DeviceId is of type nvarchar(max), SensorReadings is of type record (object). GetType can be used to determine the type when necessary.

SensorReadings has three properties: two are of type bigint: Temperature and Humidity, and CustomSensor is of type array (of bigint). If this array was more complex (itself containing records or arrays), a combination of GetArrayElements (plural) and GetRecordPropertyValue could be used.

Queries

This query returns fields at the root of the record (DeviceId), nested fields using the dot notation (Temperature,Humidity), including an array (CustomSensor), and finally, the first and second elements of that array via GetArrayElement (index 0 and 1):

SELECT   
    i.DeviceId,
    i.SensorReadings.Temperature,
    i.SensorReadings.Humidity,
    i.SensorReadings.CustomSensor as CustomSensorArray,
    GetArrayElement(i.SensorReadings.CustomSensor,0) AS FirstCustomSensorValue,
    GetArrayElement(i.SensorReadings.CustomSensor,1) AS SecondCustomSensorValue
FROM input i

Returns the following output:

DeviceId	Temperature	Humidity	CustomSensorArray	FirstCustomSensorValue	SecondCustomSensorValue
631	81	69	0,1,0	0	1
123	80	70	1,1,0	1	1

Use CROSS APPLY to unfold the array:

SELECT   
    i.DeviceId,
    CustomerSensorValue.ArrayValue AS CustomerSensorValue
FROM input AS i
CROSS APPLY GetArrayElements(i.SensorReadings.CustomSensor) AS CustomerSensorValue

Returns the following output:

DeviceId	CustomerSensorValue
631	0
631	1
631	0
123	0
123	1
123	1

From there, you can easily aggregate the content if necessary:

SELECT   
    i.DeviceId,
    SUM(CustomerSensorValue.ArrayValue) AS CustomerSensorTotal 
FROM input AS i
CROSS APPLY GetArrayElements(i.SensorReadings.CustomSensor) AS CustomerSensorValue 
GROUP BY i.DeviceId, TumblingWindow(minute, 1)

DeviceId	CustomerSensorTotal
123	2
631	1

Share via