I have been learning Stream Analytics recently and ran across an issue that I couldn’t find any good examples of how to solve the problem so I figured I would post my solution.

I fully expect to expand on this scenario in coming months, but for now will keep the scenario light.  What I am doing is getting device reads off of an Azure Event Hub.  These reads are being aggregated on the publisher side and placed into a single message/event.  Since the publisher is creating a message structure that contains many device reads for that specific interval I wanted to ensure I can process each element in the array within my Stream Analytics query.

My message payload looks like this:

{
    “interchangeID”: “94759e00-b7cf-4036-a2a5-827686caace2”,
    “processType”: “RT”,
    “tagDetails”: [

{“tagName”: “TAG1″,”tagTimestamp”: “15-Jan-2016 12:47:30″,”tagValue”: 2.756858951,”tagQuality”: “524481”},
{“tagName”: “TAG2″,”tagTimestamp”: “15-Jan-2016 12:47:30″,”tagValue”: 2.756858952,”tagQuality”: “524482”},
{“tagName”: “TAG3″,”tagTimestamp”: “15-Jan-2016 12:47:30″,”tagValue”: 2.7568589533,”tagQuality”: “524483”}
{“tagName”: “TAG4″,”tagTimestamp”: “15-Jan-2016 12:47:30″,”tagValue”: 2.7568589534,”tagQuality”: “524484”}

]

}

From a Stream Analytics perspective, here is what my query looks like:

SELECT tagDetails.ArrayValue AS tag
FROM inputeventhub AS e
CROSS APPLY GetArrayElements(e.tagDetails) AS tagDetails

When I execute my query, my result looks like this:

The key to making this query works is the “CROSS APPLY” operators.  MSDN describes these operators as: “The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query. The table-valued function acts as the right input and the outer table expression acts as the left input. The right input is evaluated for each row from the left input and the rows produced are combined for the final output. The list of columns produced by the APPLY operator is the set of columns in the left input followed by the list of columns returned by the right input.

There are two forms of APPLY: CROSS APPLY and OUTER APPLY. CROSS APPLY returns only rows from the outer table that produce a result set from the table-valued function. OUTER APPLY returns both rows that produce a result set, and rows that do not, with NULL values in the columns produced by the table-valued function.”

Then as part of the SELECT statement, we are able to iterate through each value in the array which gives us our columns.

Stay tuned for more info on Event Hubs and Stream Analytics as I continue to get more familiar with them.  One thing I have learned early is that they are very powerful together.

Also, as an additional resource, I encourage you to visit the MSDN Complex Data Types post which will give you some examples of other more complex scenarios.