JSON documents are basically upside down trees. Working with structures like this can present challenges in any programming language. Microsoft Azure has a language called U-SQL that is used
As we look at the JSON document below you have the representation of the items that are for sale in a region of food markets. There is an array of locations and four sales categories; produce, baked goods, restaurant items, and non perishable. Those each have an array inside of them. Now suppose I want to see all of the produce items with the MarketId and the MarketName in a set of rows in a table. I would need to start at the produce level of the document and get everything within it and then also get the MarketId and MarketName from the grand parent (parent's parent) of Produce.
To support this, I added the MultiLevelJsonExtractor. This is now part of the GitHub examples for U-SQL located here. This extractor inherits from the normal JsonExtractor that was originally provided but also will look up at the ancestors in the tree for pre-defined properties.
The main difference in the code is that you need to provide the properties you are looking for and what level they are at as you would with the normal extractor. In order to extract the query I describe above, I would need to write the following U-SQL:
REFERENCE ASSEMBLY master.[CodeGenerationSample.Json];
REFERENCE ASSEMBLY master.[Newtonsoft.Json];
@produce =
EXTRACT Name string,
DatePicked DateTime,
ShelfLifeInDays int,
Calories int,
Price decimal,
MarketId string,
MarketName string
FROM "/Samples/Data/MarketLocations.json"
USING new CodeGenerationSample.Json.MultiLevelJsonExtractor("MarketLocations[*].SalesCategories.Produce[*]",
false,
"Name",
"DatePicked",
"ShelfLifeInDays",
"Calories",
"Price",
"MarketId",
"MarketName");
OUTPUT @produce
TO "/Samples/Output/produce.csv"
USING Outputters.Csv(outputHeader : true);
NOTE:
If you have the same property name in the iteration point as the parent you won't be able to locate it. I haven't found a good way to achieve this yet.
If you have the same property name in the iteration point as the parent you won't be able to locate it. I haven't found a good way to achieve this yet.
{
"Region": "Main",
"MarketLocations": [
{
"MarketName": "Central",
"MarketId": 1,
"SalesCategories": {
"Produce": [
{
"Type": "Apple",
"Name": "Granny Smith",
"DatePicked": "2016-11-08T14:14:33.712Z",
"ShelfLifeInDays": 24,
"Calories": 45,
"Price": 0.29
}
],
"BakedGoods": [
{
"DateMade": "2016-11-08T14:14:33.712Z",
"Name": "Apple Pie",
"Price": 14.25
}
],
"RestaurantItems": [
{
"Name": "Turkey Sandwich",
"Price": 4.85,
"PreparationTimeInMinutes": 20
}
],
"NonPerishable": [
{
"Name": "Honey Mustard",
"Type": "Condiments"
}
]
}
},
{
"MarketName": "Southern",
"MarketId": 2,
"SalesCategories": {
"Produce": [
{
"Type": "Apple",
"Name": "Granny Smith",
"DatePicked": "2016-11-08T14:14:33.712Z",
"ShelfLifeInDays": 24,
"Calories": 45,
"Price": 0.29
},
{
"Type": "Plums",
"Name": "Red Plums",
"DatePicked": "2016-11-08T14:14:33.712Z",
"ShelfLifeInDays": 12,
"Calories": 21,
"Price": 0.33
},
{
"Type": "Pears",
"Name": "Golden Nature",
"DatePicked": "2016-11-08T14:14:33.712Z",
"ShelfLifeInDays": 20,
"Calories": 40,
"Price": 0.45
}
],
"BakedGoods": [
{
"DateMade": "2016-11-08T14:14:33.712Z",
"Name": "Apple Pie",
"Price": 14.25
}
],
"RestaurantItems": [
{
"Name": "Turkey Sandwich",
"Price": 4.85,
"PreparationTimeInMinutes": 20
}
],
"NonPerishable": [
{
"Name": "Honey Mustard",
"Type": "Condiments"
}
]
}
},
{
"MarketName": "Western",
"MarketId": 3,
"SalesCategories": {
"Produce": [
{
"Type": "Plums",
"Name": "Red Plums",
"DatePicked": "2016-11-08T14:14:33.712Z",
"ShelfLifeInDays": 12,
"Calories": 21,
"Price": 0.33
},
{
"Type": "Pears",
"Name": "Golden Nature",
"DatePicked": "2016-11-08T14:14:33.712Z",
"ShelfLifeInDays": 20,
"Calories": 40,
"Price": 0.45
}
],
"BakedGoods": [
{
"DateMade": "2016-11-08T14:14:33.712Z",
"Name": "Plum Pie",
"Price": 18.25
}
],
"RestaurantItems": [
{
"Name": "Ham Sandwich",
"Price": 4.85,
"PreparationTimeInMinutes": 20
},
{
"Name": "Chicken Soup",
"Price": 2.25,
"PreparationTimeInMinutes": 5
}
],
"NonPerishable": [
{
"Name": "Mayo",
"Type": "Condiments"
},
{
"Name": "Syrup",
"Type": "Condiments"
},
{
"Name": "Ginger",
"Type": "Spices"
}
]
}
}
]
}
