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
Sample query
Result
Since the data was flattened based on the values[]
array that contained three values, the result contains three rows from one source event.
Example 2
Sample data
Sample query with Cartesian product
Result
Since the selected arrays are independent without any shared context, the result contains a Cartesian product.
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
ZIP
Result
Now we have achieved our desired output for our data.
Example 3
Sample data
Sample query without Cartesian product
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.
Sample query with Cartesian product
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[]
.
Example 4
Sample data
Sample query without Cartesian product
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.
Sample query with Cartesian product
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.
To run an UNNEST
query that produces a Cartesian Product you must set SKIP_VALIDATIONS = ('ALLOW_CARTESIAN_PRODUCT')
.
Last updated