Unable to parse list of Json blocks in U-SQL

Question

I have a file with list of json blocks and am stuck with processing/Reading them in U-Sql and writing to a text file.

{
    "id": "0001",
    "type": "donut",
    "name": "Cake",
    "ppu": 0.55,
    "batters":
        {
            "batter":
                [
                    { "id": "1001", "type": "Regular" },
                    { "id": "1002", "type": "Chocolate" },
                    { "id": "1003", "type": "Blueberry" },
                    { "id": "1004", "type": "Devil's Food" }
                ]
        },
    "topping":
        [
            { "id": "5001", "type": "None" },
            { "id": "5002", "type": "Glazed" },
            { "id": "5005", "type": "Sugar" },
            { "id": "5007", "type": "Powdered Sugar" },
            { "id": "5006", "type": "Chocolate with Sprinkles" },
            { "id": "5003", "type": "Chocolate" },
            { "id": "5004", "type": "Maple" }
        ]
}
{
    "id": "0002",
    "type": "nut",
    "name": "ake",
    "ppu": 1.55,
    "batters":
        {
            "batter":
                [
                    { "id": "1001", "type": "Regular" },
                    { "id": "1002", "type": "Chocolate" },
                    { "id": "1003", "type": "Blueberry" },
                    { "id": "1004", "type": "Devil's Food" }
                ]
        },
    "topping":
        [
            { "id": "5001", "type": "None" },
            { "id": "5002", "type": "Glazed" },
            { "id": "5005", "type": "Sugar" },
            { "id": "5007", "type": "Powdered Sugar" },
            { "id": "5006", "type": "Chocolate with Sprinkles" },
            { "id": "5003", "type": "Chocolate" },
            { "id": "5004", "type": "Maple" }
        ]
}

{
    "id": "0003",
    "type": "test",
    "name": "ake",
    "ppu": 1.55,
    "batters":
        {
            "batter":
                [

                ]
        },
    "topping":
        [

            { "id": "5003", "type": "Chocolate" },
            { "id": "5004", "type": "Maple" }
        ]
}

can someone help me on this.

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

DECLARE @Full_Path string = @"C:\Users\test\Desktop\File\JsonTest.json";

USING [Microsoft.Analytics.Samples.Formats];

@RawExtract = 
    EXTRACT 
        [RawString] string

    FROM
        @Full_Path
    USING 
        Extractors.Text(delimiter:'\n', quoting : false);

@ParsedJSONLines =
    SELECT JsonFunctions.JsonTuple([RawString]) AS JSONLine

    FROM @RawExtract;


@StagedData =
    SELECT 
        JSONLine["id"] AS Id,
        JSONLine["name"] AS Name,
        JSONLine["type"] AS Type,
        JSONLine["ppu"] AS PPU,
JSONLine["batters"] AS Batter
    FROM 
        @ParsedJSONLines;

DECLARE @Output_Path string = @"C:\Users\Test\Desktop\File\Test2.csv";

OUTPUT @StagedData
TO @Output_Path 
USING Outputters.Csv();

Am receiving error while evaluating expression .

Error while evaluating expression JsonFunctions.JsonTuple(RawString)

score 0 · Answer 1 · answered Jan 10 '19 at 16:17

You cant use an Text Extraxtor to extract Json, unless you use Json Lines.

Using the extractor will split the json and you will get the error.

Use JsonExtractor instead of Text extractor.

https://github.com/Azure/usql/blob/master/Examples/DataFormats/Microsoft.Analytics.Samples.Formats/Json/JsonExtractor.cs

Unable to parse list of Json blocks in U-SQL

1 Answers1