Query hierarchical data

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:
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!

Aggregate streaming data

Work with Arrays

Last modified 1yr ago

Copy link

Contents

Using nested fields in GROUP BY statement:

Calculations on hierarchical data: