USQL查询从Json数据创建表 [英] USQL Query to create a Table from Json Data

问题描述

我有一个像[{}, {}, {}]的json，即可以有多行，并且每一行都有许多属性-值对，每一行保持不变.

I have a json which is like [{}, {}, {}], i.e. there can be multiple rows and each row has a number of property - value pairs, which remain fixed for each row.

@json =
    EXTRACT MainId string, Details string
    FROM @INPUT_FILE
    USING new Microsoft.Analytics.Samples.Formats.Json.JsonExtractor();

这给了我json作为字符串.

This gives me json as a string.

我不知道如何获得:row[3].property4诸如给定行的属性值之类的东西.复杂的事物，属性本身都以{Name:"XXX"，Value:"YYY"}

I don't know how to get: row[3].property4 things like a property's value of a given row. Complicating things the properties are all themselves arranged as {Name: "XXX", Value: "YYY"}

@jsonnodes =
    SELECT JsonApp.JsonFunctions.JsonTuple(@json, "event", "id") AS json_map
    FROM @json;

现在，我尝试了上述查询，但没有成功.

Now, the above query I tried but it did not work.

它给了我错误:

Rowset variable '@json' is not a scalar variable.

如何获取行属性对并将其放入csv文件或表中?

How could I get row-property pairs and put them in a csv file or table?

感谢您的帮助.

JSON如下:

[{"MainId":"24201803","System":[{"Name":"event","Value":"S"},{"Name":"id","Value":""}],
"Details":[{"Name":"EventName","Value":"W"},{"Name":"previd","Value":"88"}],
"ttl":8640000}, ....Multiple Rows of the Same type as before....]

我想要获得的输出是:

表列= MainId | System.event | System.id | Details.EventName | Details.previd | ttl

Table Columns = MainId | System.event | System.id | Details.EventName | Details.previd | ttl

表的行值将由具有相应属性值的行填充(如json行).

The table's rows' values will be filled by rows having the corresponding property's values (as in the json rows).

推荐答案

在下面的小型测试用例中找到了可行的解决方案-我同意{"Name:'XXX'，Value:'YYY'}格式可以解决问题有点棘手，但是您仍然可以在没有自定义提取器的情况下完成此任务-只需嵌套许多JSON.

Found a solution below that worked in my small test case - I agree that the {"Name: 'XXX', Value: 'YYY'} formatting makes things a little tricky but you can still get this done without a custom extractor - just lots of nested JSON.

基本上，您从System and Details JSON(在@parse_json中)提取一个SQLArray，然后将它们拆分成较小的JSON元组(在@get_nested中)，然后最终在@output中提取值".请参阅下面的代码.让我知道您是否有疑问！

Basically, you extract a SQLArray from the System and Details JSON (in @parse_json), then split those into smaller JSON tuples (in @get_nested), and then finally extract the "Values" in @output. See code below. Let me know if you have questions!

    CREATE ASSEMBLY [Microsoft.Analytics.Samples.Formats]
    FROM @FormatsAssembly;

    CREATE ASSEMBLY [Newtonsoft.Json]
    FROM @JSONAssembly;

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

    DECLARE @CommentsPath = "/JSONTest/rawJson.json";


    @get_json =
        EXTRACT 
            MainId          int,
            System          string,
            Details         string,
            ttl             int
        FROM @CommentsPath
        USING new Microsoft.Analytics.Samples.Formats.Json.JsonExtractor();


    @parse_json =
        SELECT MainId, 
               Microsoft.Analytics.Samples.Formats.Json.JsonFunctions.JsonTuple(System).Values AS SystemJson,
               Microsoft.Analytics.Samples.Formats.Json.JsonFunctions.JsonTuple(Details).Values AS DetailsJson,
               ttl
        FROM @get_json;


    @get_nested =
        SELECT 
               MainId,
               Microsoft.Analytics.Samples.Formats.Json.JsonFunctions.JsonTuple(SystemJson[0]) AS SystemName,
               Microsoft.Analytics.Samples.Formats.Json.JsonFunctions.JsonTuple(SystemJson[1]) AS SystemId,
               Microsoft.Analytics.Samples.Formats.Json.JsonFunctions.JsonTuple(DetailsJson[0]) AS DetailsName,
               Microsoft.Analytics.Samples.Formats.Json.JsonFunctions.JsonTuple(DetailsJson[1]) AS DetailsPrevid,
               ttl
        FROM @parse_json;

    @output =
        SELECT MainId,
               SystemName["Value"] AS SystemEvent,
               SystemId["Value"] AS SystemId,
               DetailsName["Value"] AS EventName,
               DetailsPrevid["Value"] AS PrevId,
               ttl
        FROM @get_nested;

    OUTPUT @output 
        TO @"/JSONTest/test1.csv"
        USING Outputters.Csv(outputHeader : true);

这篇关于USQL查询从Json数据创建表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！