U-SQL-从json-array提取数据 [英] U-SQL - Extract data from json-array

问题描述

已经尝试了建议的JSONPath选项，但是JSONExtractor似乎只能识别根级别.就我而言，我必须处理嵌套的json结构以及数组(请参见下面的示例).在没有多个中间文件的情况下提取此文件的任何选项?

Already tried the suggested JSONPath option, but it seems the JSONExtractor only recognizes root level. In my case I have to deal with a nested json-structure, with an array as well (see example below). Any options for extracting this without multiple intermediate files?

"relation": {
"relationid": "123456",
"name": "relation1",
"addresses": {
    "address": [{
        "addressid": "1",
        "street": "Street 1",
        "postcode": "1234 AB",
        "city": "City 1"
        },
    {
        "addressid": "2",
        "street": "Street 2",
        "postcode": "5678 CD",
        "city": "City 2"
    }]
}}

SELECT关联ID，地址ID，街道，邮政编码，城市?

SELECT relationid, addressid, street, postcode, city ?

推荐答案

将JSON片段修复为:

After fixing your JSON fragment to:

{
    "relation": {
        "relationid": "123456",
        "name": "relation1",
        "addresses": {
            "address": [{
                "addressid": "1",
                "street": "Street 1",
                "postcode": "1234 AB",
                "city": "City 1"
            }, {
                "addressid": "2",
                "street": "Street 2",
                "postcode": "5678 CD",
                "city": "City 2"
            }]
        }
    }
}

并将其放入文件中，以下脚本将为您提供所需的内容.请注意，您需要向下浏览结构以携带更高级别的项目，一旦遇到数组，如果只需要那些具有数组的数组，请CROSS APPLY EXPLODE；如果想要缺少数组的行，请使用OUTER APPLY EXPLODE

and putting it into a file, the following script will get you what you want. Note that you need to navigate down the structure to carry along the higher level items and once you encounter an array, you CROSS APPLY EXPLODE it if you only need those that have the array, or OUTER APPLY EXPLODE it if you want rows with missing arrays.

DECLARE @input string = @"/temp/stackoverflow.json";

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

@json = 
EXTRACT relationid int, 
        name string, 
        addresses string
  FROM @input 
USING new Microsoft.Analytics.Samples.Formats.Json.JsonExtractor("relation");

@relation =
SELECT relationid,
       name,
       Microsoft.Analytics.Samples.Formats.Json.JsonFunctions.JsonTuple(addresses)["address"] AS address_array
FROM @json;

@addresses = 
SELECT relationid, 
       name, 
       Microsoft.Analytics.Samples.Formats.Json.JsonFunctions.JsonTuple(address) AS address
FROM @relation
     CROSS APPLY
        EXPLODE (Microsoft.Analytics.Samples.Formats.Json.JsonFunctions.JsonTuple(address_array).Values) AS A(address);

@result =
SELECT relationid,
       name,
       address["addressid"]AS addressid,
       address["street"]AS street,
       address["postcode"]AS postcode,
       address["city"]AS city
FROM @addresses;

OUTPUT @result
TO "/users/temp/st_out.csv"
USING Outputters.Csv();

这篇关于U-SQL-从json-array提取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！