代码之家  ›  专栏  ›  技术社区  ›  Randy Minder

多级单点抽取器-抽取所需级别

u-sql
  •  2
  • Randy Minder  · 技术社区  · 8 年前

    我有一个JSON文档,如下所示: 

    {
  "Region": "Main",
  "MarketLocations": [
    {
      "MarketName": "Central",
      "MarketId": 1,
      "SalesCategories": {
        "Produce": [
          {
            "Type": "Apple",
            "Name": "Granny Smith",
            "DatePicked": "2016-11-08T14:14:33.712Z",
            "ShelfLifeInDays": 24,
            "Calories": 45,
            "Price": 0.29
          }
        ],
        "BakedGoods": [
          {
            "DateMade": "2016-11-08T14:14:33.712Z",
            "Name": "Apple Pie",
            "Price": 14.25
          }
        ],
        "RestaurantItems": [
          {
            "Name": "Turkey Sandwich",
            "Price": 4.85,
            "PreparationTimeInMinutes": 20
          }
        ],
        "NonPerishable": [
          {
            "Name": "Honey Mustard",
            "Type": "Condiments"
          }
        ]
      }
    },
    {
      "MarketName": "Southern",
      "MarketId": 2,
      "SalesCategories": {
        "Produce": [
          {
            "Type": "Apple",
            "Name": "Granny Smith",
            "DatePicked": "2016-11-08T14:14:33.712Z",
            "ShelfLifeInDays": 24,
            "Calories": 45,
            "Price": 0.29
          },
          {
            "Type": "Plums",
            "Name": "Red Plums",
            "DatePicked": "2016-11-08T14:14:33.712Z",
            "ShelfLifeInDays": 12,
            "Calories": 21,
            "Price": 0.33
          },
          {
            "Type": "Pears",
            "Name": "Golden Nature",
            "DatePicked": "2016-11-08T14:14:33.712Z",
            "ShelfLifeInDays": 20,
            "Calories": 40,
            "Price": 0.45
          }
        ],
        "BakedGoods": [
          {
            "DateMade": "2016-11-08T14:14:33.712Z",
            "Name": "Apple Pie",
            "Price": 14.25
          }
        ],
        "RestaurantItems": [
          {
            "Name": "Turkey Sandwich",
            "Price": 4.85,
            "PreparationTimeInMinutes": 20
          }
        ],
        "NonPerishable": [
          {
            "Name": "Honey Mustard",
            "Type": "Condiments"
          }
        ]
      }
    },
    {
      "MarketName": "Western",
      "MarketId": 3,
      "SalesCategories": {
        "Produce": [
          {
            "Type": "Plums",
            "Name": "Red Plums",
            "DatePicked": "2016-11-08T14:14:33.712Z",
            "ShelfLifeInDays": 12,
            "Calories": 21,
            "Price": 0.33
          },
          {
            "Type": "Pears",
            "Name": "Golden Nature",
            "DatePicked": "2016-11-08T14:14:33.712Z",
            "ShelfLifeInDays": 20,
            "Calories": 40,
            "Price": 0.45
          }
        ],
        "BakedGoods": [
          {
            "DateMade": "2016-11-08T14:14:33.712Z",
            "Name": "Plum Pie",
            "Price": 18.25
          }
        ],
        "RestaurantItems": [
          {
            "Name": "Ham Sandwich",
            "Price": 4.85,
            "PreparationTimeInMinutes": 20
          },
          {
            "Name": "Chicken Soup",
            "Price": 2.25,
            "PreparationTimeInMinutes": 5
          }
        ],
        "NonPerishable": [
          {
            "Name": "Mayo",
            "Type": "Condiments"
          },
          {
            "Name": "Syrup",
            "Type": "Condiments"
          },
          {
            "Name": "Ginger",
            "Type": "Spices"
          }
        ]
      }
    }
  ]
}

    我有以下U-SQL,它处理这个JSON文件,运行在Visual Studio中: 

    DECLARE @in string=@"/JsonDoc2.json";
DECLARE @out string=@"Output/JsonDoc2.csv";

    @produce =
        EXTRACT Name string,
                DatePicked DateTime,
                ShelfLifeInDays int,
                Calories int,
                Price decimal,
                MarketId string,
                MarketName string
        FROM @in
        USING new MultiLevelJsonExtractor("MarketLocations[*].SalesCategories.Produce[*]",
              false,
              "Name",
              "DatePicked",
              "ShelfLifeInDays",
              "Calories",
              "Price",
              "MarketId",
              "MarketName");


    OUTPUT @produce
    TO @out
    USING Outputters.Csv(outputHeader : true);

    执行时不会出错。问题是我具体地指定了我想要的销售类别(“产品”)。我想更改这个查询,以便包括所有销售类别(农产品、烘焙食品等),包括类别名称。我一直想不出一个办法来做这件事。

    1 回复  |  直到 8 年前
        1
  •  1
  •   wBob    8 年前

    newtonsoft的 jsontype method jsonfunctions class,returns a map value which is a key-value pair.然后,您可以引用该键以获取JSON属性/对象/数组名称,至少在使用 交叉应用进行了一些其他操作之后, 和 explode. 。

    举个例子,我有以下几点要做: 

    reference assembly[newtonsoft.json];
引用程序集[microsoft.analytics.samples.formats];

使用microsoft.analytics.samples.formats.json;

declare@input string=@“/input/myinputfile.json”;
declare@output string=@“output/output.csv”;

@JSON公司=
提取区域字符串,
marketname字符串,
salescategories string//将salescategories作为json获取
来自@input
使用新的multilveljsonextractor(“marketlocations[*].salescategories”,
是的,
“区域”,
“市场名称”,
“销售类别”
)(二)


//将JSON字符串转换为tuple/map
@工作=
选择区域,
市场名称,
jsonFunctions.jsonTuple(SalesCategories)为x
来自@json;


//将元组分解为键值对;
@工作=
选择区域,
市场名称,
键,
价值
来自@working
交叉应用
将(x)分解为y(键,值);


//分解json值
@工作=
选择区域,
市场名称,
键,
jsonFunctions.jsonTuple(y)作为z
来自@working
交叉应用
explode(jsonfunctions.jsontuple(value).values)为x(y);


//准备结果,命名所需的项
@结果=
选择区域,
市场名称,
键,
Z[“类型”]作为类型,
Z[“名称”]作为名称,
Z[“datechicked”]作为datechicked,
Z[“谢尔菲恩德斯”]作为谢尔菲恩德斯,
Z[“卡路里”]作为卡路里,
Z[“价格”]作为价格,
Z[“日期制作”]作为日期制作,
Z[“PreparationTimeInMinutes”]作为PreparationTimeInMinutes
来自@working;


输出@结果
至@输出
使用outputters.csv(引用:false);

    

    我的结果:

    


    感觉好像可以简化,但是看看你是怎么做的。用于粉碎JSON的样本供应不足,但请尝试此处and此处。
    
类,返回MAP值,它是一个键值对。然后,您可以引用该键以获取JSON属性/对象/数组名称,至少在使用CROSS APPLY和EXPLODE.
    


    对于您的例子,我有以下几点要做:
    


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

USING Microsoft.Analytics.Samples.Formats.Json;

DECLARE @input string = @"/input/myinputfile.json";
DECLARE @output string = @"output/output.csv";

@json =
    EXTRACT Region string,
            MarketName string,
            SalesCategories string // get the SalesCategories as JSON
    FROM @input
    USING new MultiLevelJsonExtractor("MarketLocations[*].SalesCategories",
          true,
          "Region",
          "MarketName",
          "SalesCategories"
          );


// Convert the json string to tuple/MAP
@working =
    SELECT Region,
           MarketName,
           JsonFunctions.JsonTuple(SalesCategories) AS x
    FROM @json;


// Explode the tuple as key-value pair;
@working =
    SELECT Region,
           MarketName,
           key,
           value
    FROM @working
         CROSS APPLY
             EXPLODE(x) AS y(key, value);


// Explode the value which is JSON
@working =
    SELECT Region,
           MarketName,
           key,
           JsonFunctions.JsonTuple(y) AS z
    FROM @working
        CROSS APPLY
             EXPLODE(JsonFunctions.JsonTuple(value).Values) AS x(y);


// Prep the result, naming the items you want
@result =
    SELECT Region,
           MarketName,
           key,
           z["Type"] AS Type,
           z["Name"] AS Name,
           z["DatePicked"] AS DatePicked,
           z["ShelfLifeInDays"] AS ShelfLifeInDays,
           z["Calories"] AS Calories,
           z["Price"] AS Price,
           z["DateMade"] AS DateMade,
           z["PreparationTimeInMinutes"] AS PreparationTimeInMinutes
    FROM @working;


OUTPUT @result
TO @output
USING Outputters.Csv(quoting:false);

    


    我的结果:
Results
    


    感觉好像可以简化,但是看看你是怎么做的。用于粉碎JSON的样品供应不足,但请尝试here和here.
    推荐文章
    关于   移动版
    代码之家 - 一站式码农服务社区
    沪ICP备11025650号