CODEWITHSUNDEEP
jsonazure-stream-analytics
wosscr
wosscr

Reputation: 31

Processing Array of Arrays in Azure Stream Analytics

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.

Upvotes: 0

Views: 271

Answers (1)

wosscr
wosscr

Reputation: 31

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

Upvotes: 3

Related Questions