Query hierarchical data

This article goes over how to query hierarchical data using SQL in Upsolver.
Transform with SQL enables to query hierarchical data. 
See: SQL syntax reference - Hierarchical data​
For the following examples, we will assume that three events stream into the data source Purchases over time:
{ 
    "purchase_id": 1, "customer_id": 1, 
    "products": 
    [ 
        { "name": "Orange", "quantity": 3, "unit_price": 0.25 }, 
        { "name": "Banana", "quantity": 4, "price": 0.1 } 
    ] 
}
{ 
    "purchase_id": 2, "customer_id": 1, 
    "products": 
    [ 
        { "name": "Apple", "quantity": 1, "unit_price": 0.5 } 
    ] 
}
{ 
    "purchase_id": 1, "customer_id": 1, 
    "products": 
    [ 
        { "name": "Orange", "quantity": -2, "unit_price": 0.25 } 
    ] 
}
Using nested fields in GROUP BY statement:
With Transform with SQL, accessing nested fields in hierarchical data is simple and intuitive.
Fields in nested records can be accessed using the dot syntax. If a field is in an array, we use square braces [] to denote that.
Let’s have a look at the following query:
SELECT customer_id,
       products[].name product_name,
       products[].quantity * products[].unit_price total_cost
  FROM Purchases
 GROUP BY customer_id, products[].name
The result would be be:
customer_id
product_name
total_cost
1
"Orange"
0.25
1
"Banana"
0.4
1
"Apple"
0.5
Calculations on hierarchical data:
When doing calculations on hierarchical data, the result is placed back in the nested hierarchy. This "target location" affects how an operation works when dealing with arrays.
Example 1
The following query:
SET products[].total_cost = products[].quantity * products[].unit_price;
SET number_of_purchased_products = SUM_VALUES(products[].quantity);
Results in the following output:
{ 
    "purchase_id": 1, "customer_id": 1, “number_of_purchased_products”: 7, 
    "products": 
    [ 
        { "name": "Orange", "quantity": 3, "unit_price": 0.25, “total_cost”: 0.75 }, 
        { "name": "Banana", "quantity": 4, "price": 0.1, “total_cost”: 0.4} 
    ]
}
{ 
    "purchase_id": 2, "customer_id": 1, “number_of_purchased_products”: 1, 
    "products": 
    [ 
        { "name": "Apple", "quantity": 1, "unit_price": 0.5, “total_cost”: 0.5 } 
    ]
}
{ 
    "purchase_id": 1, "customer_id": 1, “number_of_purchased_products”: -2, 
    "products": 
    [    
        { "name": "Orange", "quantity": -2, "unit_price": 0.25, “total_cost”: -0.5 } 
    ] 
}
Note: total_cost resulted in an array but number_of_purchased_products didn't. This is because some operations like SUM_VALUE return a single value, regardless of how many inputs they have.
Inline operations use the deepest possible location in the nesting as their target location.
Example 2
SET product_indexes = MAP_WITH_INDEX(products[]);
Results in the following data:
{ 
    "purchase_id": 1, "customer_id": 1, 
    “product_indexes”: 
    [ 
        {“index”: 0, "name": "Orange", "quantity": 3, "unit_price": 0.25}, 
        {“index”: 1, "name": "Banana", "quantity": 4, "price": 0.1} 
    ],
    "products": 
    [ 
        { "name": "Orange", "quantity": 3, "unit_price": 0.25}, 
        { "name": "Banana", "quantity": 4, "price": 0.1} 
    ]
}
{ 
    "purchase_id": 2, "customer_id": 1,
    “product_indexes”: 
    [
        “index” : 0, "name": "Apple", "quantity": 1, "unit_price": 0.5} 
    ],
    "products": 
    [ 
        { "name": "Apple", "quantity": 1, "unit_price": 0.5} 
    ]
}
{ 
    "purchase_id": 1, "customer_id": 1,
    “product_indexes”: 
    [
        {“index” : 0, "name": "Orange", "quantity": -2, "unit_price": 0.25} 
    ],
    "products": 
    [ 
        { "name": "Orange", "quantity": -2, "unit_price": 0.25} 
    ] 
}
Example 3
The following query:
SET products_quantity = FROM_KEY_VALUE(products[].name, products[].quantity, “name,quantity” );
Results in the following data:
{ 
    "purchase_id": 1, "customer_id": 1, 
    "products": 
    [ 
        { "name": "Orange", "quantity": 3, "unit_price": 0.25}, 
        { "name": "Banana", "quantity": 4, "price": 0.1} 
    ],
    “products_quantity”: 
    [
        {“Orange”: 3},
        {“Banana”: 4}
    ]
}
{ 
    "purchase_id": 2, "customer_id": 1,
    "products": 
    [ 
        { "name": "Apple", "quantity": 1, "unit_price": 0.5} 
    ],
    “products_quantity”
    [
    	{“Apple”: 1}
    ]
}
{ 
    "purchase_id": 1, "customer_id": 1,
    "products": 
    [ 
        { "name": "Orange", "quantity": -2, "unit_price": 0.25} 
    ],
    “products_quantity”:
    [
    	{“Orange”: -2}
    ] 
}
Example 4
The following query:
SET indexed_products = ZIP(products);
Results in the following data:
{ 
    "purchase_id": 1, "customer_id": 1, 
    "products": 
    [ 
        { "name": "Orange", "quantity": 3, "unit_price": 0.25}, 
        { "name": "Banana", "quantity": 4, "price": 0.1} 
    ],
    “indexed_products”:
    [
        {
            “index”: 0, 
            “field_1”: { "name": "Orange", "quantity": 3, "unit_price": 0.25}
        }, 
        {	
            “index”: 1,
            “field_1”: { "name": "Banana", "quantity": 4, "price": 0.1} 
        }
    ]
}
{ 
"purchase_id": 2, "customer_id": 1**, **
"products": 
    [ 
        { "name": "Apple", "quantity": 1, "unit_price": 0.5} 
    ],
    “indexed_products”:
    [
        {
            “index”: 0,
    	    “field_1”: { "name": "Apple", "quantity": 1, "unit_price": 0.5} 
        }
    ]
}
{ 
    "purchase_id": 1, "customer_id": 1**,** 
    "products": 
    [ 
        { "name": "Orange", "quantity": -2, "unit_price": 0.25} 
    ],
    “Indexed_products”:
    [
        {
            “index”: 0,
            “field_1”: { "name": "Orange", "quantity": -2, "unit_price": 0.25} 
        }
    ]
}
Aggregate streaming data
Work with Arrays
Last modified 2yr ago
customer_id	product_name	total_cost
1	"Orange"	0.25
1	"Banana"	0.4
1	"Apple"	0.5