arrayJoin function

This is a very unusual function.

Normal functions do not change a set of rows, but just change the values in each row (map). Aggregate functions compress a set of rows (fold or reduce). The arrayJoin function takes each row and generates a set of rows (unfold).

This function takes an array as an argument, and propagates the source row to multiple rows for the number of elements in the array. All the values in columns are simply copied, except the values in the column where this function is applied; it is replaced with the corresponding array value.

For example:

The arrayJoin function affects all sections of the query, including the WHERE section. Notice in that the result of the query below is 2, even though the subquery returned 1 row.

A query can use multiple arrayJoin functions. In this case, the transformation is performed multiple times and the rows are multiplied. For example:

Best practice

Using multiple arrayJoin with same expression may not produce expected results due to the elimination of common subexpressions. In those cases, consider modifying repeated array expressions with extra operations that do not affect the join result. For example, arrayJoin(arraySort(arr)), arrayJoin(arrayConcat(arr, []))

Example:

Note the ARRAY JOIN syntax in the SELECT query, which provides broader possibilities. ARRAY JOIN allows you to convert multiple arrays with the same number of elements at a time.

Example:

Or you can use Tuple

Example:

The name arrayJoin in ClickHouse comes from its conceptual similarity to the JOIN operation, but applied to arrays within a single row. While traditional JOINs combine rows from different tables, arrayJoin "joins" each element of an array in a row, producing multiple rows - one for each array element - while duplicating the other column values. ClickHouse also provides the ARRAY JOIN clause syntax, which makes this relationship to traditional JOIN operations even more explicit by using familiar SQL JOIN terminology. This process is also referred to as "unfolding" the array, but the term "join" is used in both the function name and clause because it resembles joining the table with the array elements, effectively expanding the dataset in a way similar to a JOIN operation.