Working with complex data types in Azure Stream Analytics

Azure Stream Analytics supports processing events in a variety of data formats (CSV, JSON, Avro).

JSON and Avro can contain complex types such as nested objects (records) or arrays. Many Stream Analytics customers use nested data and we have received various questions related to processing events containing records and/or arrays.

This blog post provides an overview of available record and array functions and includes query examples for typical operations.

Record data type

A Record is a collection of name/value pairs. The Record data type is used to represent JSON objects and Avro records when corresponding formats are used in the input data streams.

Let’s assume we are processing data events with sensor readings in JSON format and walk through few common operations over records.

Here is example of a single event:


     "DeviceId" : "12345",

     "Location" : {"Lat": 47, "Long": 122 }

     "SensorReadings" :


          "Temperature" : 80,

          "Humidity" : 70,

           "CustomSensor01" : 5,

           "CustomSensor02" : 99




Access nested record fields

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





FROM input


Access nested record fields when the filed name is dynamic

If property name is unknown at query authoring time, use the GetRecordPropertyValue

For example, imagine our sample stream is joined with reference data containing thresholds for each device sensor:




FROM input

JOIN thresholds


    input.DeviceId = thresholds.DeviceId


    GetRecordPropertyValue(input.SensorReading, thresholds.SensorName) > thresholds.Value


Extract record fields as separate events

To convert record fields into separate events use CROSS APPLY operator with the GetRecordProperties function. For example, to convert our sample stream into stream of event with individual sensor readings use this query:





FROM input as event

CROSS APPLY GetRecordProperties(event.SensorReadings) AS sensorReading

Array data type

An Array is an ordered collection of values. The Array data type is used to represent JSON and Avro arrays when corresponding formats are used in the input data streams.

In this section we will explain how to perform common operations over array values. Examples assume input events that have “arrayField” property of array type. 

Select array element at specified index

For example, to select the first array element use the GetArrayElement function: 


    GetArrayElement(arrayField, 0) AS firstElement

FROM input


Select array length

To retrieve the length of an array, use the GetArrayLength function:


    GetArrayLength(arrayField) AS arrayLength

FROM input


Select all array elements as individual events

To extract all array elements as individual events use CROSS APPLY operator with the GetArrayElements function:  




FROM input as event

CROSS APPLY GetArrayElements(event.arrayField) AS arrayElement\

Comments (7)

  1. Shashank says:

    I wrote a query to CAST hexadecimal string to decimal. While testing the query in the portal, i get expected output:

    CAST('0xa' AS bigInt) = 10,

    CAST('0x10' AS bigInt) = 16

    CAST('0x1' AS bigInt) = 1

    but at runtime when i am doing substring from my original input to get similar string as above, the CAST is failing with following error.

    Same query works from "Test" buttin in portal and gives expected output when i tested it giving the same input data.


    Correlation ID:



    Cannot cast value '0x1' to type 'bigint' in expression 'CAST ( CONCAT ( '0x' , SUBSTRING ( SUBSTRING (inputhex , 5 , 4 ) , 2 , 1 ) ) AS bigInt )'.


    Runtime exception occurred while processing events, Cannot cast value '0x1' to type 'bigint' in expression 'CAST ( CONCAT ( '0x' , SUBSTRING ( SUBSTRING ( inputhex , 5 , 4 ) , 2 , 1 ) ) AS bigInt )'.

    , : OutputSourceAlias:parseddata;


    Any ideas?

  2. ArminH9 says:

    This is a great feature, but what if I want to save my nested json as a string(nvarchar) directly into the field without any changes?

    For example:



    Data              //This will be the json





    1. Francesco says:

      Hi ArminH9,
      did you find a solution for trasfer the json payload?

    2. I need to save JSON (in my case an array property) as a string, too. Did you ever find a solution, ArminH9 ?

    3. Re: ArminH9
      Converting records to JSON string is not directly supported in the language. However you can define custom JavaScript function to do that.

      function main(x) {
      return JSON.stringify(x);

      Please see more examples on this page:

  3. Steven Lawler says:

    CROSS APPLY GetArrayElements(event.arrayField) AS arrayElement\
    Is that \ at the end a typo?

  4. Ahmed says:

    I have a problem writing a query to extract a table out of the arrays from a json file: The problem is how to get the information of the array “data packets” and its contents of arrays and then make them all in a normal sql table.

    One hard issue there is the “CrashNotification” and “CrashMaxModuleAccelerations”, I dont know how to define and use them.

    The file looks like this:

    { “imei”: { “imei”: “351631044527130F”, “imeiNotEncoded”:
    “dataPackets”: [ [ “CrashNotification”, { “version”: 1, “id”: 28 } ], [
    “CrashMaxModuleAccelerations”, { “version”: 1, “module”: [ -1243, -626,
    14048 ] } ] ]}
    I tried to use Get array elements method and other ways but I am never able to access 2nd level arrays like elements of “CrashNotification” of the “dataPackets” or elements of “module” of the array “CrashMaxModuleAccelerations” of the “dataPackets”.

    I looked also here (Select the first element in a JSON array in Microsoft stream analytics query) and it doesnt work. I would appreciate any help :)

Skip to main content