Upsolver
Search…
Query hierarchical data
This article goes over how to query hierarchical data using SQL in Upsolver.
Transform with SQL enables to query hierarchical data.
For the following examples, we will assume that three events stream into the data source Purchases over time:
1
{
2
"purchase_id": 1, "customer_id": 1,
3
"products":
4
[
5
{ "name": "Orange", "quantity": 3, "unit_price": 0.25 },
6
{ "name": "Banana", "quantity": 4, "price": 0.1 }
7
]
8
}
9
{
10
"purchase_id": 2, "customer_id": 1,
11
"products":
12
[
13
{ "name": "Apple", "quantity": 1, "unit_price": 0.5 }
14
]
15
}
16
{
17
"purchase_id": 1, "customer_id": 1,
18
"products":
19
[
20
{ "name": "Orange", "quantity": -2, "unit_price": 0.25 }
21
]
22
}
Copied!

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:
1
SELECT customer_id,
2
products[].name product_name,
3
products[].quantity * products[].unit_price total_cost
4
FROM Purchases
5
GROUP BY customer_id, products[].name
Copied!
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:
1
SET products[].total_cost = products[].quantity * products[].unit_price;
2
SET number_of_purchased_products = SUM_VALUES(products[].quantity);
Copied!
Results in the following output:
1
{
2
"purchase_id": 1, "customer_id": 1, “number_of_purchased_products”: 7,
3
"products":
4
[
5
{ "name": "Orange", "quantity": 3, "unit_price": 0.25, “total_cost”: 0.75 },
6
{ "name": "Banana", "quantity": 4, "price": 0.1, “total_cost”: 0.4}
7
]
8
}
9
{
10
"purchase_id": 2, "customer_id": 1, “number_of_purchased_products”: 1,
11
"products":
12
[
13
{ "name": "Apple", "quantity": 1, "unit_price": 0.5, “total_cost”: 0.5 }
14
]
15
}
16
{
17
"purchase_id": 1, "customer_id": 1, “number_of_purchased_products”: -2,
18
"products":
19
[
20
{ "name": "Orange", "quantity": -2, "unit_price": 0.25, “total_cost”: -0.5 }
21
]
22
}
Copied!
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

1
SET product_indexes = MAP_WITH_INDEX(products[]);
Copied!
Results in the following data:
1
{
2
"purchase_id": 1, "customer_id": 1,
3
“product_indexes”:
4
[
5
{“index”: 0, "name": "Orange", "quantity": 3, "unit_price": 0.25},
6
{“index”: 1, "name": "Banana", "quantity": 4, "price": 0.1}
7
],
8
"products":
9
[
10
{ "name": "Orange", "quantity": 3, "unit_price": 0.25},
11
{ "name": "Banana", "quantity": 4, "price": 0.1}
12
]
13
}
14
{
15
"purchase_id": 2, "customer_id": 1,
16
“product_indexes”:
17
[
18
index” : 0, "name": "Apple", "quantity": 1, "unit_price": 0.5}
19
],
20
"products":
21
[
22
{ "name": "Apple", "quantity": 1, "unit_price": 0.5}
23
]
24
}
25
{
26
"purchase_id": 1, "customer_id": 1,
27
“product_indexes”:
28
[
29
{“index” : 0, "name": "Orange", "quantity": -2, "unit_price": 0.25}
30
],
31
"products":
32
[
33
{ "name": "Orange", "quantity": -2, "unit_price": 0.25}
34
]
35
}
Copied!

Example 3

The following query:
1
SET products_quantity = FROM_KEY_VALUE(products[].name, products[].quantity, “name,quantity” );
Copied!
Results in the following data:
1
{
2
"purchase_id": 1, "customer_id": 1,
3
"products":
4
[
5
{ "name": "Orange", "quantity": 3, "unit_price": 0.25},
6
{ "name": "Banana", "quantity": 4, "price": 0.1}
7
],
8
“products_quantity”:
9
[
10
{“Orange”: 3},
11
{“Banana”: 4}
12
]
13
}
14
{
15
"purchase_id": 2, "customer_id": 1,
16
"products":
17
[
18
{ "name": "Apple", "quantity": 1, "unit_price": 0.5}
19
],
20
“products_quantity”
21
[
22
{“Apple”: 1}
23
]
24
}
25
{
26
"purchase_id": 1, "customer_id": 1,
27
"products":
28
[
29
{ "name": "Orange", "quantity": -2, "unit_price": 0.25}
30
],
31
“products_quantity”:
32
[
33
{“Orange”: -2}
34
]
35
}
Copied!

Example 4

The following query:
1
SET indexed_products = ZIP(products);
Copied!
Results in the following data:
1
{
2
"purchase_id": 1, "customer_id": 1,
3
"products":
4
[
5
{ "name": "Orange", "quantity": 3, "unit_price": 0.25},
6
{ "name": "Banana", "quantity": 4, "price": 0.1}
7
],
8
“indexed_products”:
9
[
10
{
11
index”: 0,
12
“field_1”: { "name": "Orange", "quantity": 3, "unit_price": 0.25}
13
},
14
{
15
index”: 1,
16
“field_1”: { "name": "Banana", "quantity": 4, "price": 0.1}
17
}
18
]
19
}
20
{
21
"purchase_id": 2, "customer_id": 1**, **
22
"products":
23
[
24
{ "name": "Apple", "quantity": 1, "unit_price": 0.5}
25
],
26
“indexed_products”:
27
[
28
{
29
index”: 0,
30
“field_1”: { "name": "Apple", "quantity": 1, "unit_price": 0.5}
31
}
32
]
33
}
34
{
35
"purchase_id": 1, "customer_id": 1**,**
36
"products":
37
[
38
{ "name": "Orange", "quantity": -2, "unit_price": 0.25}
39
],
40
“Indexed_products”:
41
[
42
{
43
index”: 0,
44
“field_1”: { "name": "Orange", "quantity": -2, "unit_price": 0.25}
45
}
46
]
47
}
Copied!
Last modified 1yr ago