使用U-SQL查询JSON嵌套对象 [英] Query JSON nested objects using U-SQL

问题描述

我正在尝试从下面获取国家和类别. 我可以获取国家/地区信息，而不是类别.

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

示例输入:

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

我的查询:

@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);

推荐答案

您必须考虑到数组也包含复杂类型.另外，您不能仅使用EXTRACT [custom.dimensions] string这样的符号，还必须为此添加中间步骤:

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

这将输出:

国家"省"城市""OsVersion""RoleName"类别"
新加坡""N/A""N/A""Microsoft Windows NT 6.1.7601 Service Pack 1""Connexx""Noah版本"

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

这篇关于使用U-SQL查询JSON嵌套对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！