Getting JSON Array Element in Azure Stream Analytics Using CROSS APPLY

Problem:
How can I extract data from JSON array in the Azure Stream Analytics query?

Answer:
Let’s say we have a simple JSON input data like below.

[  
   {  
      'DeviceId':'TXE012345',
      'Time':'2018-10-15T16:01:00.0000000Z',
      'Temp':70,
      'Humidity':10,
      'AreaCount':12,
      'ActType':2,
      'ColInfo':[  
         {  
            'X':2,
            'Y':4,
            'Z':6,
            'A':8,
            'B':10
         }
      ],
      'sensor_01':7,
      'sensor_02':139,
      'sensor_03':15,
      'sensor_04':330
   }
]

And we want to have X,Z, and B from within the array to be extracted to the output as separates columns, along with several other fields.

To accomplish that, we can use GetArrayElements function and CROSS APPLY in the query.

Assuming we have inputblob as the input and outputblob as the output sink, we can write query as follow:

SELECT
I.DeviceId,
I.Time,
AR.ArrayValue.X as X_value,
AR.ArrayValue.Z as Z_value,
AR.ArrayValue.B as B_value,
I.sensor_01 as Sensor01,
I.sensor_03 as Sensor03
INTO outputblob
FROM "inputblob" as I
CROSS APPLY GetArrayElements(I.ColInfo) as AR

Testing the query with the JSON file as sample data, the result will be as follow. Here we can see that the values from array (X, Z and B) have been extracted from the array and placed at the same level as the other values (DeviceId, etc.)

[  
   {  
      "deviceid":"TXE012345",
      "time":"2018-10-15T16:01:00.0000000Z",
      "x_value":2,
      "z_value":6,
      "b_value":10,
      "sensor01":7,
      "sensor03":15
   }
]

Finally, the screenshot from the portal Test function shows the query along with the result. You can experiment further with a more structurally complex JSON file and query.

Azure Stream Analytics Cross Apply GetElements