Query JSON nested objects using U-SQL

Question

I am trying to get Country and Category from below. I am able to get country but not Category.

Example input:

[{
    "context": {
        "location": {
            "clientip": "0.0.0.0",
            "continent": "Asia",
            "country": "Singapore"
        },
        "custom": {
            "dimensions": [{
                "Category": "Noah Version"
            }]
        }
    }
}]

My Query:

@json =
EXTRACT
    [location] string,
    [device] string,
    [custom.dimensions] string
FROM
    @InputFile
USING new JsonExtractor("context");

@CreateJSONTuple = 
SELECT 
JsonFunctions.JsonTuple([location]) AS LocationData,
JsonFunctions.JsonTuple([device]) AS DeviceData,
JsonFunctions.JsonTuple([custom.dimensions]) AS DimensionData
FROM @json;

@Dataset =
SELECT
LocationData["country"] ?? "NA" AS Country
,LocationData["province"] ?? "NA" AS Province
,LocationData["city"] ?? "N/A" AS City
,DeviceData["osVersion"] ?? "N/A" AS OsVersion
,DeviceData["roleName"] ?? "N/A" AS RoleName
,DimensionData["Category"] ?? "N/A" AS Category
FROM @CreateJSONTuple;


OUTPUT @Dataset
TO @transBlobOutputFile
USING Outputters.Csv(outputHeader:true,quoting:true);

https://github.com/vatansoni89/RahulDataLake/blob/df060b77e31b91da7d32861cafd358de4dfc6060/GovPostcodes/GovPostcodes/SqlChick.usql — Vatan Soni, Oct 31 '18 at 07:25
Peter I am getting Country but not Category, it's coming as null. — Vatan Soni, Oct 31 '18 at 07:26
Please do not post a link to the script but put the code in the question, together with some sample data and required output sample. — Peter Bons, Oct 31 '18 at 07:47
Thanks Peter, I tried but i am getting error on save saying mostly code and formatting. So i kept the github link. Could you please follow the link and help me. — Vatan Soni, Oct 31 '18 at 08:22
Please take a look at the edited question and note that all relevant information is now included. Please pay attention to your next question to include all relevant information as people will downvote and not answer your question. — Peter Bons, Oct 31 '18 at 12:20

score 0 · Accepted Answer · answered Oct 31 '18 at 12:25

You have to take in account that the array consists of complex types as well. Also, you cannot just use a notation like EXTRACT [custom.dimensions] string, you have to add itermediate steps for that as well like this:

@json  =
    EXTRACT location String,
            device String,
            custom String
    FROM @InputFile
    USING new Microsoft.Analytics.Samples.Formats.Json.JsonExtractor("context");

@CreateJSONTuple =
    SELECT JsonFunctions.JsonTuple(location) AS LocationData,
           JsonFunctions.JsonTuple(device) AS DeviceData,
           JsonFunctions.JsonTuple(custom, "dimensions[0]") AS DimensionData
    FROM @json;

@result =
    SELECT LocationData["country"] ?? "N/A" AS Country,
           LocationData["province"] ?? "N/A" AS Province,
           LocationData["city"] ?? "N/A" AS City,
           DeviceData["osVersion"] ?? "N/A" AS OsVersion,
           DeviceData["roleName"] ?? "N/A" AS RoleName,
           DimensionData["dimensions[0]"] AS CategoryDimension
    FROM @CreateJSONTuple;

@result =
    SELECT Country,
           Province,
           City,
           OsVersion,
           RoleName,
           JsonFunctions.JsonTuple(CategoryDimension) AS CategoryDimension
    FROM @result;

@result =
    SELECT Country,
           Province,
           City,
           OsVersion,
           RoleName,
           CategoryDimension["Category"] AS Category
    FROM @result;

OUTPUT @result
TO @output
USING Outputters.Csv(outputHeader : true, quoting : true);

This outputs:

"Country" "Province" "City" "OsVersion" "RoleName" "Category"
"Singapore" "N/A" "N/A" "Microsoft Windows NT 6.1.7601 Service Pack 1" "Connexx" "Noah Version"

Peter this solution works if we have Category at index 0 but not working if we have Category at other index. please suggest the solution. Thanks a lot for the help. — Vatan Soni, Nov 01 '18 at 02:02

Query JSON nested objects using U-SQL

1 Answers1

Linked