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.

Answer 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)