USql Call data в многомерном массиве JSON

Question

У меня есть этот файл JSON в озере данных, который выглядит следующим образом:

{
"id":"398507",
"contenttype":"POST",
"posttype":"post",
"uri":"http://twitter.com/etc",
"title":null,
"profile":{
   "@class":"PublisherV2_0",
   "name":"Company",
   "id":"2163171",
   "profileIcon":"https://pbs.twimg.com/image",
   "profileLocation":{
      "@class":"DocumentLocation",
      "locality":"Toronto",
      "adminDistrict":"ON",
      "countryRegion":"Canada",
      "coordinates":{
         "latitude":43.7217,
         "longitude":-31.432},
         "quadKey":"000000000000000"},
      "displayName":"Name",
      "externalId":"00000000000"},
   "source":{
       "name":"blogs",
       "id":"18",
       "param":"Twitter"},
   "content":{
       "text":"Description of post"},
       "language":{
           "name":"English",
           "code":"en"},
       "abstracttext":"More Text and links",
       "score":{}
   }
}

чтобы вызвать данные в мое приложение, я должен превратить JSON в строку, используя этот код:

DECLARE @input string = @"/MSEStream/{*}.json";

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


@allposts = 
EXTRACT 
    jsonString  string
FROM @input
USING Extractors.Text(delimiter:'\b', quoting:true);

@extractedrows = SELECT Microsoft.Analytics.Samples.Formats.Json.JsonFunctions.JsonTuple(jsonString) AS er FROM @allposts;


@result = 
SELECT er["id"] AS postID,
       er["contenttype"] AS contentType,
       er["posttype"] AS postType,
       er["uri"] AS uri,
       er["title"] AS Title,
       er["acquisitiondate"] AS acquisitionDate,
       er["modificationdate"] AS modificationDate,
       er["publicationdate"] AS publicationDate,
       er["profile"] AS profile
FROM @extractedrows;

OUTPUT  @result
TO  "/ProcessedQueries/all_posts.csv"
USING Outputters.Csv();

Это выводит JSON в файл.csv, который доступен для чтения, и когда я загружаю файл, все данные отображаются правильно. Моя проблема в том, когда мне нужно получить данные внутри профиля. Поскольку JSON теперь является строкой, я не могу извлечь какие-либо из этих данных и поместить их в переменную для использования. Есть какой-либо способ сделать это? или мне нужно искать другие варианты для чтения данных?

Answer 1

Вы можете использовать JsonTuple в строке профиля, чтобы дополнительно извлечь нужные свойства. Пример кода U-SQL для обработки вложенного Json представлен по этой ссылке - https://github.com/Azure/usql/blob/master/Examples/JsonSample/JsonSample/NestedJsonParsing.usql.

Вы можете использовать JsonTuple в столбце профиля для дальнейшего извлечения определенных узлов.

Например, используйте JsonTuple, чтобы получить все дочерние узлы узла профиля и извлечь конкретные значения, как в коде.

@childnodesofprofile = 
SELECT
   Microsoft.Analytics.Samples.Formats.Json.JsonFunctions.JsonTuple(profile) AS childnodes_map
FROM @result;

@values =
SELECT
   childnodes_map["name"] AS name,
   childnodes_map["id"] AS id
FROM @result;

В качестве альтернативы, если вас интересуют конкретные значения, вы также можете передать параметры в функцию JsonTuple, чтобы получить нужные вам узлы. Приведенный ниже код получает узел локальности из рекурсивно вложенных узлов (как описано конструкцией "$..value".

@locality = 
SELECT Microsoft.Analytics.Samples.Formats.Json.JsonFunctions.JsonTuple(profile, "$..locality").Values AS locality
FROM @result;

Другие поддерживаемые конструкции от JsonTuple

    JsonTuple(json, "id", "name")              // field names          
    JsonTuple(json, "$.address.zip")           // nested fields        
    JsonTuple(json, "$..address")              // recursive children   
    JsonTuple(json, "$[?(@.id > 1)].id")       // path expression      
    JsonTuple(json)                            // all children

Надеюсь это поможет.