SQL array flattening: Why doesn’t CROSS JOIN UNNEST join every nested value with every row?

The best way to think about this is by looking at what happens on a row-by-row basis. Setting up some input data, we have:

WITH t1 AS (
  SELECT 1 AS id, [0, 1] AS numbers_array UNION ALL
  SELECT 2, [2, 4, 5]
)
...

(I’m using a third element for the second row to make things more interesting). If we just select from it, we get output that looks like this:

WITH t1 AS (
  SELECT 1 AS id, [0, 1] AS numbers_array UNION ALL
  SELECT 2, [2, 4, 5]
)
SELECT * FROM t1;
+----+---------------+
| id | numbers_array |
+----+---------------+
| 1  | [0, 1]        |
| 2  | [2, 4, 5]     |
+----+---------------+

Now let’s talk about unnesting. The UNNEST function takes an array and returns a value table of the array’s element type. Whereas most BigQuery tables are SQL tables defined as a collection of columns, a value table has rows of some value type. For numbers_array, UNNEST(numbers_array) returns a value table whose value type is INT64, since numbers_array is an array with an element type of INT64. This value table contains all of the elements in numbers_array for the current row from t1.

For the row with an id of 1, the contents of the value table returned by UNNEST(numbers_array) are:

+-----+
| f0_ |
+-----+
| 0   |
| 1   |
+-----+

This is the same as what we get with the following query:

SELECT * FROM UNNEST([0, 1]);

UNNEST([0, 1]) in this case means “create a value table from the INT64 values 0 and 1“.

Similarly, for the row with an id of 2, the contents of the value table returned by UNNEST(numbers_array) are:

+-----+
| f0_ |
+-----+
| 2   |
| 4   |
| 5   |
+-----+

Now let’s talk about how CROSS JOIN fits into the picture. In most cases, you use CROSS JOIN between two uncorrelated tables. In other words, the contents of the table on the right of the CROSS JOIN are not defined by the current contents of the table on the left.

In the case of arrays and UNNEST, however, the contents of the value table produced by UNNEST(numbers_array) change depending on the current row of t1. When we join the two tables, we get the cross product of the current row from t1 with all of the rows from UNNEST(numbers_array). For example:

WITH t1 AS (
  SELECT 1 AS id, [0, 1] AS numbers_array UNION ALL
  SELECT 2, [2, 4, 5]
)
SELECT id, number
FROM t1
CROSS JOIN UNNEST(numbers_array) AS number;
+----+--------+
| id | number |
+----+--------+
| 1  | 0      |
| 1  | 1      |
| 2  | 2      |
| 2  | 4      |
| 2  | 5      |
+----+--------+

numbers_array has two elements in the first row and three elements in the second, so we get 2 + 3 = 5 rows in the result of the query.

To answer the question about how this differs from flattening the numbers_array and then performing a CROSS JOIN, let’s look at the results of this query:

WITH t1 AS (
  SELECT 1 AS id, [0, 1] AS numbers_array UNION ALL
  SELECT 2, [2, 4, 5]
), t2 AS (
  SELECT number
  FROM t1
  CROSS JOIN UNNEST(numbers_array) AS number
)
SELECT number
FROM t2;
+--------+
| number |
+--------+
| 0      |
| 1      |
| 2      |
| 4      |
| 5      |
+--------+

In this case, t2 is is a SQL table with a column named number with those values. If we perform a CROSS JOIN between t1 and t2, we get a true cross product of all rows:

WITH t1 AS (
  SELECT 1 AS id, [0, 1] AS numbers_array UNION ALL
  SELECT 2, [2, 4, 5]
), t2 AS (
  SELECT number
  FROM t1
  CROSS JOIN UNNEST(numbers_array) AS number
)
SELECT id, numbers_array, number
FROM t1
CROSS JOIN t2;
+----+---------------+--------+
| id | numbers_array | number |
+----+---------------+--------+
| 1  | [0, 1]        | 0      |
| 1  | [0, 1]        | 1      |
| 1  | [0, 1]        | 2      |
| 1  | [0, 1]        | 4      |
| 1  | [0, 1]        | 5      |
| 2  | [2, 4, 5]     | 0      |
| 2  | [2, 4, 5]     | 1      |
| 2  | [2, 4, 5]     | 2      |
| 2  | [2, 4, 5]     | 4      |
| 2  | [2, 4, 5]     | 5      |
+----+---------------+--------+

So what’s the difference between this and the previous query with CROSS JOIN UNNEST(numbers_array)? In this case, the contents of t2 don’t change for each row from t1. For the first row in t1, there are five rows in t2. For the second row in t1, there are five rows in t2. As a result, the CROSS JOIN between the two of them returns 5 + 5 = 10 rows in total.