Stream Analytics JSON Input query parsing

Question

I am doing a POC on ingesting a JSON though EventHub, processing it through Stream job and pushing it into a Azure SQL DW.

I have worked with JSON ingestion before but the difficulty I face now is with the naming structure used in JSON.

Here is the sample:

{
    "1-1": [{
            "Details": [{
                    "FirstName": "Super",
                    "LastName": "Man"                   
                }
            ]
        }
    ]
}

The root element has a hyphen (-) and I am having tough time parsing through this element to access the relevant items.

I have tried the following queries and I get NULLs in the SQL tables it outputs to:

--#1
SELECT
    ["2-1"].Details.FirstName AS First_Name
    ,["2-1"].Details.LastName AS Last_Name
INTO
    [SA-OUTPUT]
FROM
    [SA-INPUT]

--#2
SELECT
    [2-1].Details.FirstName AS First_Name
    ,[2-1].Details.LastName AS Last_Name
INTO
    [SA-OUTPUT]
FROM
    [SA-INPUT]

--#3
SELECT
    2-1.Details.FirstName AS First_Name
    ,2-1.Details.LastName AS Last_Name
INTO
    [SA-OUTPUT]
FROM
    [SA-INPUT]

--#4
SELECT
    SA-INPUT.["2-1"].Details.FirstName AS First_Name
    ,SA-INPUT.["2-1"].Details.LastName AS Last_Name
INTO
    [SA-OUTPUT]
FROM
    [SA-INPUT]

Would appreciate the correct way to do this.

Thanks in advance.

Rom Eh · Accepted Answer · 2019-02-05 11:23:17Z

1

Your JSON schema is nested but also has some arrays. In order to read the data you will need to use the GetArrayElement function.

Here's a query that will read your sample data:

WITH Step1 AS(
SELECT GetArrayElement([1-1], 0) as FirstLevel
FROM iothub),
Step2 AS(
SELECT GetArrayElement(FirstLevel.Details,0) SecondLevel
FROM Step1)
SELECT SecondLevel.FirstName, SecondLevel.LastName from Step2

For more info, you can have a look at our page Work with complex Data Types in JSON and AVRO.

Let me know if you have any question.

Thanks, JS (ASA team)

edited Feb 5, 2019 at 11:23

Rom Eh

2,0711 gold badge17 silver badges34 bronze badges

answered Jul 31, 2018 at 4:40

Jean-Sébastien

7373 silver badges7 bronze badges

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

2 Comments

Morpheus273 Over a year ago

Thanks for help. I got the gist of handling the arrays and nested structures.

Rom Eh Over a year ago

The link is nore valid. You can find information at : learn.microsoft.com/en-us/azure/stream-analytics/…

Morpheus273 · Accepted Answer · 2018-07-31 05:57:54Z

0

It tried and it worked beautifully. If lets say I have to generate data from two separate array elements, I would have to create two separate CTEs.

{
    "1-1": [{
            "Details": [{
                    "FirstName": "Super",
                    "LastName": "Man"                   
                }
            ]
        }
    ]
},
{
    "2-1": [{
            "Address": [{
                    "Street": "Main",
                    "Lane": "Second"                   
                }
            ]
        }
    ]
}

How do I merge elements from two CTEs into one output query? I can only refer CTE in the following line.

answered Jul 31, 2018 at 5:57

Morpheus273

472 silver badges7 bronze badges

2 Comments

Jean-Sébastien Over a year ago

You can make 2 calls to the GetArrayElement function in the first step (one for "1-1", another one for "2-1"). And extend from there the other steps. BTW it is also possible to use GetRecordPropertyValue if you want to reduce the number of steps.

Morpheus273 Over a year ago

Thanks for your response. I ended up using GetRecordPropertyValue with multiple GetArrayElement. Worked like a charm.

Collectives™ on Stack Overflow

Stream Analytics JSON Input query parsing

Here is the sample:

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Here is the sample:

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related