USQL Query to create a Table from Json Data

Question

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

This gives me json as a string.

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.

It gives me the error:

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

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

Thanks for your help.

The JSON looks like:

[{"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....]

The output I am trying to get is:

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

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

Answer 1

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.

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