U-SQL Extract data from Json that contain Array

Question

I want to get from my json records that containt always the same PartnerId and Name from users array. I'm currently trying with this code:

@jsonFile =
    EXTRACT partnerId int,
            users string
    FROM @INPUT_FILE
    USING new Microsoft.Analytics.Samples.Formats.Json.JsonExtractor();

@followingUsersArray =
    SELECT partnerId,
           Microsoft.Analytics.Samples.Formats.Json.JsonFunctions.JsonTuple(users) AS following_array
    FROM @jsonFile;

@followingUsers =
    SELECT partnerId AS PartnerId,
           following_array["name"] AS FriendName
    FROM @followingUsersArray;

But i'm not get any result. Here's my json example file:

{
    "partnerId": 2,
    "users": [{
            "name": "Anna ROGOWSKA",
            "profile_image_url": "http://pbs.twimg.com/profile_images/884844399338901504/0OYl8JA6_normal.jpg",
            "created_at": "2012-09-30T19:52:15+02:00",
            "location": "Sopot,Poland",
            "id_str": "855093368"
        },
        {
            "name": "Anna BARAŃSKA",
            "profile_image_url": "http://pbs.twimg.com/profile_images/884844399338901504/0OYl8JA6_normal.jpg",
            "created_at": "2012-09-30T19:52:15+02:00",
            "location": "Sopot,Poland",
            "id_str": "855093368"
        }
    ]
}

The result what i want is : 2,"Anna ROGOWSKA" 2,"Anna BARAŃSKA"

candidson · Accepted Answer · 2017-07-23 11:45:59Z

4

You should leverage the CROSS APPLY EXPLODE functionality of U-SQL.

I tested this with your json file and it worked:

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

USING Microsoft.Analytics.Samples.Formats.Json;

DECLARE @path string = @"C:\Users\testUser\Documents\Visual Studio 2015\Projects\USQL_Json\";
DECLARE @input string = @path + @"sample.json";
DECLARE @to string = @path + @"output.csv";

@jsonFile =
 EXTRACT partnerId int,
        users string
FROM @input
USING new JsonExtractor();

@followingUsers =
 SELECT partnerId AS PartnerId,
       JsonFunctions.JsonTuple(users).Values AS user_array
 FROM @jsonFile;

@tabUsers =
 SELECT PartnerId,
       JsonFunctions.JsonTuple(t_user)["name"] AS FriendName
 FROM @followingUsers
     CROSS APPLY
         EXPLODE(user_array) AS A(t_user);


OUTPUT @tabUsers
TO @to
USING Outputters.Csv();

The Output is:

2,"Anna ROGOWSKA"
2,"Anna BARANSKA"

edited Jul 23, 2017 at 11:45

answered Jul 23, 2017 at 0:25

candidson

5264 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Guido Dizioli Over a year ago

your answer works beautifully, but im having trouble understanding the A on the line EXPLODE(user_array) AS A(t_user);. On the docs didn't find anything useful if this stands for an array or if it doesn't affect the result. Thx for any explanation. Docs i searched explode doc sql identifiers

candidson Over a year ago

You can derive it from the usql github repo... github.com/Azure/usql/tree/master/Examples/DataFormats/…

Collectives™ on Stack Overflow

U-SQL Extract data from Json that contain Array

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related