Processing Array of Arrays in Azure Stream Analytics

Question

I have this JSON Structure fed to my ASA:

[{
  "Stages": [
    {
      "Name": "Stage 1",
      "Count": 45,
      "First": "2018-12-17T11:31:12.7448439-04:00",
      "Average": 1.0,
      "Max": 0.0
    },
    {
      "Name": "Stage 2",
      "Count": 7,
      "First": "2018-12-17T11:31:12.7448469-04:00",
      "Average": 0.0,
      "Max": 0.0
    }
  ],
  "DateTimeET": "2018-12-17T11:31:12.7448477-04:00",
  "Division": "One"
}]

I'm stuck on how to get the Name, Count, First, Average and Max for each element within the Stages Array.

I did this:

WITH CTE AS (
    SELECT  
        event.Division
        ,event.DateTimeET
        ,StageElement
    FROM
        StageSummary AS event
    CROSS APPLY getarrayelements(event.Stages) AS StageElement
)

SELECT
    event2.Division
    ,event2.DateTimeET
    ,event2.StageElement
FROM
    CTE AS event2

and I can get the array using the GetRecordProperties, but I get the full array again, I can't get something specific as 'Name' or 'Count'

Any help is appreciated.

Update:

I'm using the query as follows:

WITH CTE AS (
    SELECT  
        event.Division
        ,event.DateTimeET
        ,StageElement
    FROM
        StageSummary AS event
    CROSS APPLY getarrayelements(event.Stages) AS StageElement
)

SELECT
    event2.Division
    ,event2.DateTimeET
    ,getrecordpropertyvalue(Elements,'Name') AS NameValue
FROM
    CTE AS event2
    CROSS APPLY getrecordproperties(event2.StageElement) AS Elements

but NameValue returns empty.

wosscr · Accepted Answer · 2018-12-17 18:27:49Z

3

Since the structure of my Stages array is fixed, the solution is to use the ArrayValue to query each element like this:

    SELECT  
        event.Division
        ,event.DateTimeET
        ,StageElement.ArrayValue.Name
        ,StageElement.ArrayValue.Count
        ,StageElement.ArrayValue.First
        ,StageElement.ArrayValue.Average
        ,StageElement.ArrayValue.Max

    FROM
        StageSummary AS event
    CROSS APPLY getarrayelements(event.Stages) AS StageElement

That gives me the values I needed. I was missing the ArrayValue to reference the actual data, as the GetArrayElements return both ArrayValue and ArrayIndex

answered Dec 17, 2018 at 18:27

wosscr

313 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Processing Array of Arrays in Azure Stream Analytics

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related