Odtnhj

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!