Working with Arrays in Azure Streaming Analytics

When working with Azure Streaming Analytics (ASA) it is extremely common to use JSON for your events.  JSON allows us to express data in a rich way with things like nested objects and arrays.  This though can cause some head scratching further along the chain when we need to work with that data,  In this post I want to look at working with arrays in ASA.

 

Here is my event data

As you can see I have an array of objects which describe samples.  In this format they are not much use to me.  What I need to do is Pivot this data as it comes through ASA ready to be put into my destination.

Here’s the query that does that

SELECT
source___timestamp,
source.time,
flattened.arrayindex,
flattened.arrayvalue.sample,
flattened.arrayvalue.x,
flattened.arrayvalue.y,
flattened.arrayvalue.z
INTO
output
FROM
source
CROSS APPLY GetElements(source.samples) AS flattened

And here is what it looks like on the other side

Perfect. It is now in a format I can use more easily.