CODEWITHSUNDEEP
azureazure-stream-analytics
Morpheus273
Morpheus273

Reputation: 47

Stream Analytics JSON Input query parsing

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.

Upvotes: 0

Views: 2521

Answers (2)

Jean-Sébastien
Jean-Sébastien

Reputation: 737

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)

Upvotes: 1

Morpheus273
Morpheus273

Reputation: 47

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.

Upvotes: 0

Related Questions