locked
Stream Analytics JSON root has hyphen - can't parse RRS feed

  • Question

  •  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.


    Cheers!! Sumit Bhatnagar

    Tuesday, July 31, 2018 3:50 AM

Answers

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


    Tuesday, July 31, 2018 4:46 AM

All replies

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


    Tuesday, July 31, 2018 4:46 AM
  • Thanks for help. I got the gist of handling the arrays and nested structures.

    Cheers!! Sumit Bhatnagar

    Tuesday, July 31, 2018 5:40 AM
  • 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.


    Cheers!! Sumit Bhatnagar

    Tuesday, July 31, 2018 5:59 AM