Ndtyjky

I need to filter name and city from below nested json.

[

    {

        "Employee": [

            {

                "name": "John",

                "Gender": "Male"

            }

        ],

        "Info": {

            "location": {

                "city": "Kuvala",

                "continent": "Asia",

                "country": "Singapore"

            }

        }

    },

    {

        "Employee": [

            {

                "name": "Mike",

                "Gender": "Male"

            }

        ],

        "Info": {

            "location": {

                "city": "Dhabi",

                "continent": "Asia",

                "country": "Dubai"

            }

        }

    }

]

My code

CREATE ASSEMBLY IF NOT EXISTS [Newtonsoft.Json] FROM @"/Assemblies/Newtonsoft.Json.dll";

CREATE ASSEMBLY IF NOT EXISTS [Microsoft.Analytics.Samples.Formats] FROM @"/Assemblies/Microsoft.Analytics.Samples.Formats.dll";



REFERENCE ASSEMBLY [Newtonsoft.Json];

REFERENCE ASSEMBLY [Microsoft.Analytics.Samples.Formats];



USING Microsoft.Analytics.Samples.Formats.Json;



DECLARE @InputFile string = @"/Source/example.blob";

DECLARE @output string = @"/Output/output.csv";



@json  =

    EXTRACT Employee String,

            Info String

    FROM @InputFile

    USING new Microsoft.Analytics.Samples.Formats.Json.JsonExtractor("*");



@CreateJSONTuple =

    SELECT   JsonFunctions.JsonTuple(Employee) AS EmployeeData,

             JsonFunctions.JsonTuple(Info) AS InfoData

    FROM @json;



@result =

    SELECT EmployeeData["name"] AS Name,

           JsonFunctions.JsonTuple(InfoData, "location") AS LocationData

    FROM @CreateJSONTuple;



OUTPUT @result

TO @output

USING Outputters.Csv(outputHeader : true, quoting : true);

I ma getting error saying cannot convert from 'Microsoft.Analytics.Types.Sql.SqlArray' to 'string' please help.

Please help me to get Name, City from this.
This should outputs:
John Vulala
Mike Dhabi

I think you are getting this error because the contents of the Employee elements is defined as an array:

"Employee": [

        {

            "name": "Mike",

            "Gender": "Male"

        }

    ]

Try changing the Employee elements to something like this:

"Employee": {

            "name": "Mike",

            "Gender": "Male"

        }

Update

It seems you might need to perform JsonTuple on the array item, so this might work:

@result =

SELECT JsonFunctions.JsonTuple(EmployeeData[0])["name"] AS Name,

       JsonFunctions.JsonTuple(InfoData, "location") AS LocationData

FROM @CreateJSONTuple;

One thing to note, if the Employee array might hold more than one item, you'll probably need to use Cross Apply Explode.

Update 2

Unfortunately, this is the last thing I can think of. try replacing:

@CreateJSONTuple =

SELECT   JsonFunctions.JsonTuple(Employee) AS EmployeeData,

         JsonFunctions.JsonTuple(Info) AS InfoData

FROM @json;



@result =

SELECT EmployeeData["name"] AS Name,

       JsonFunctions.JsonTuple(InfoData, "location") AS LocationData

FROM @CreateJSONTuple;

With this:

@CreateJSONTuple =

SELECT   JsonFunctions.JsonTuple(Employee).Values AS EmployeeData,

         JsonFunctions.JsonTuple(Info) AS InfoData

FROM @json;

@result =

SELECT JsonFunctions.JsonTuple(employee)["name"] AS Name,

       JsonFunctions.JsonTuple(InfoData, "location") AS LocationData

FROM @CreateJSONTuple

CROSS APPLY

    EXPLODE(EmployeeData) AS employees(employee) ;

Hope it helps!