u-sql - 使用 U-SQL 解析多级 Json

Question

使用 u-sql 解析下面的 JSON 后，我想将其保存为 CSV - id、type、name、ppu、battername、batterid、battertype、toppingDate、toppingid、toppingtype。

我的代码从 id 到 ppu 字段都有效，但不能超出此范围。

示例 Json- { "id": "0001", "type": "donut", "name": "Cake", "ppu": 0.55, "batters": {"battername":"NA", "batter": { { "batterid": "1001", "battertype": "Regular" }, { "batterid": "1002", "battertype": "Chocolate" }, { "batterid": "1003", "battertype": "Blueberry" }, { "batterid": "1004", "battertype": "Devil's Food" } } }, "topping": [{ "toppingDate": "2018-11-30T10:00:01", "toppingValues": [ { "toppingid": "5001", "toppingtype": "None" }, { "toppingid": "5002", "toppingtype": "Glazed" }, { "toppingid": "5005", "toppingtype": "Sugar" }, { "toppingid": "5007", "toppingtype": "Powdered Sugar" }, { "toppingid": "5006", "toppingtype": "Chocolate with" }, { "toppingid": "5003", "toppingtype": "Chocolate" }, { "toppingid": "5004", "toppingtype": "Maple" } ]} }


REFERENCE ASSEMBLY [Newtonsoft.Json];
REFERENCE ASSEMBLY [Microsoft.Analytics.Samples.Formats];
USING Microsoft.Analytics.Samples.Formats.Json;
DECLARE EXTERNAL @InputPath string = @"input.json"; 
DECLARE EXTERNAL @OutputPath string = @"output.csv";
@commonCookedInput =
EXTRACT id string,
        type string,
        name string,
        ppu int?,
        battername string,
        batterid int?,
        battertype string
FROM @InputPath
USING new MultiLevelJsonExtractor( "batters.id",
                                   "batters.type",
                                   "batters.name",
                                   "batters.ppu",
                                   "batters.battername",
                                   "batters.batterid", 
                                   "batters.battertype"
                                  );
OUTPUT @commonCookedInput
TO @OutputPath
USING Outputters.Csv(quoting: false,outputHeader:true);

标签： u-sql