UNNEST

The UNNEST operator is useful for converting nested objects with arrays to flat tables. With Upsolver, UNNEST allows you to flatten arrays based on a full SELECT statement.

In certain cases, using UNNEST may produce a Cartesian product of the column's array values in your result. This means that the values in the flattened arrays will appear in every possible combination within your result.

For example. given the arrays [ 1, 2, 3 ] and [ 4, 5 ], their Cartesian product would be [ (1 , 4), (1, 5), (2, 4), (2, 5), (3, 4), (3, 5) ].

If this is the desired result, enable the job option ALLOW_CARTESIAN_PRODUCTS to allow the flattening of the arrays.

Example 1

Sample data

{
    "values": [ 1, 2, 3 ],
    "name": "Oleg",
    "id": 123
}

Sample query

UNNEST(SELECT data.values[] AS value,
              data.name AS name,
              data.id AS id
       FROM my_data_source)

Result

Since the data was flattened based on the values[] array that contained three values, the result contains three rows from one source event.

value	name	id
1	Oleg	123
2	Oleg	123
3	Oleg	123

Example 2

Sample data

{
    "values": ["apple", "NY"],
    "type": ["fruit", "city"]
}

Sample query with Cartesian product

UNNEST(SELECT data.values[] AS value,
              data.type[] AS type
       FROM my_data_source)

Result

Since the selected arrays are independent without any shared context, the result contains a Cartesian product.

value	type
apple	fruit
NY	fruit
apple	city
NY	city

In this case, we can see that it doesn't make sense to pair NY with fruit and apple with city, so the ZIP function should be used to first combine the arrays into a single context.

Sample query with ZIP

UNNEST(SELECT zipped[].value AS value
              zipped[].type AS type
       FROM my_data_source
       LET zipped = ZIP('type,value', data.type[], data.values[]))

Result

Now we have achieved our desired output for our data.

value	type
apple	fruit
NY	city

Example 3

Sample data

{
    "orders": [{ "order_lines": [ 1, 2, 3 ], "name": "a" }, 
               { "order_lines": [ 4, 5, 6 ], "name": "b" }]
    "refunds": [ 1, 2 ]
}

Sample query without Cartesian product

UNNEST(SELECT orders[].name AS name, 
              orders[].order_lines[] AS line
       FROM my_data_source)

Result

Since there is a natural pairing between orders[].name and orders[].order_lines[] where each name has a corresponding order_lines array, using UNNEST on this query does not result in a Cartesian product.

name	line
a	1
a	2
a	3
b	4
b	5
b	6

Sample query with Cartesian product

UNNEST(SELECT orders[].name AS name, 
              orders[].order_lines[] AS line, 
              refunds[] AS refund
       FROM my_data_source)

Result

Since there is no relationship between orders[] and refunds[] in our data, using UNNEST on this query will result in a Cartesian product between the result of pairing orders[] and refunds[].

name	line	refund
a	1	1
a	2	1
a	3	1
b	4	1
b	5	1
b	6	1
a	1	2
a	2	2
a	3	2
b	4	2
b	5	2
b	6	2

Example 4

Sample data

{
    "orders": [{ "order_lines": [ 1, 2, 3 ], 
                 "name": "a", 
                 "order_date": [ "07/30/2021", "11/27/2021" ]}, 
               { "order_lines": [ 4, 5, 6 ], 
                 "name": "b", 
                 "order_date": [ "03/21/2021", "09/13/2021" ]}]
}

Sample query without Cartesian product

UNNEST(SELECT orders[].name AS name, 
              orders[].order_lines[] AS line
       FROM my_data_source)

Result

Since there is a natural pairing between orders[].name and orders[].order_lines[] where each name has a corresponding order_lines array, using UNNEST on this query does not result in a Cartesian product.

name	line
a	1
a	2
a	3
b	4
b	5
b	6

Sample query with Cartesian product

UNNEST(SELECT orders[].name AS name, 
              orders[].order_lines[] AS line, 
              orders[].order_date[] AS order_date
       FROM my_data_source)

Result

While orders[].order_lines[] and orders[].order_date[] are both part of orders[], there is no natural pairing between the values in the two respective arrays. As such, using UNNEST on this query will result in a Cartesian product.

name	line	order_date
1	a	07/30/2021
2	a	07/30/2021
3	a	07/30/2021
1	a	11/27/2021
2	a	11/27/2021
3	a	11/27/2021
4	b	03/21/2021
5	b	3/21/2021
6	b	3/21/2021
4	b	09/13/2021
5	b	09/13/2021
6	b	09/13/2021

To run an UNNEST query that produces a Cartesian Product you must set SKIP_VALIDATIONS = ('ALLOW_CARTESIAN_PRODUCT').