Search code examples
sqlazuret-sqluser-defined-functionsazure-stream-analytics

How to aggregate multiple arrays in Azure Stream Analytics

I have a table that looks like this:

"A" : [1,2,3],
"A" : [4,5,6],
"A" : [7,8,9],
"B" : [10,11,12]
"B" : [13,14,15]
"B" : [16,17,18]

and i need it to look likes this:

"A" : [1,2,3,4,5,6,7,8,9],
"B" : [10,11,12,13,14,15,16,17,18]

How can i merge the arrays based on their letter in stream analytics? All the aggregate functions i've tried do not help.

Thanks -

Solution

  • The best approach would be to GROUP BY <column with the "A", "B"> and use COLLECT() as an aggregate. This will give you {name : "A", val : [ [1,2,3], [4,5,6], [7,8,9] ] }, ...

    And then use a JavaScript UDF to flatten the array of arrays.

    Here are some links to documentation:

    https://learn.microsoft.com/en-us/stream-analytics-query/collect-azure-stream-analytics

    https://learn.microsoft.com/en-us/azure/stream-analytics/stream-analytics-javascript-user-defined-functions

    Thanks!