ARRAY JOIN

It is a common operation for tables that contain an array column to produce a new table that has a row with each individual array element of that initial column, while values of other columns are duplicated. This is the basic case of what ARRAY JOIN clause does. Its name comes from the fact that it can be looked at as executing JOIN with an array or nested data structure. The intent is similar to the arrayJoin function, but the clause functionality is broader.

PostgreSQL’s FROM unnest(...), CROSS JOIN UNNEST(...) and LATERAL unnest(...) table-source forms are not supported. Use ARRAY JOIN instead. The unnest name (since version 26.5) is a function-call alias of arrayJoin (SELECT unnest(arr)), not a table function. JOIN LATERAL <subquery> over a correlated subquery is supported separately as an experimental feature, see JOIN.

Syntax:

SELECT <expr_list>
FROM <left_subquery>
[LEFT] ARRAY JOIN <array>
[WHERE|PREWHERE <expr>]
...

Supported types of ARRAY JOIN are listed below:

ARRAY JOIN - In base case, empty arrays are not included in the result of JOIN.
LEFT ARRAY JOIN - The result of JOIN contains rows with empty arrays. The value for an empty array is set to the default value for the array element type (usually 0, empty string or NULL).

Basic ARRAY JOIN Examples

ARRAY JOIN and LEFT ARRAY JOIN

The examples below demonstrate the usage of the ARRAY JOIN and LEFT ARRAY JOIN clauses. Let’s create a table with an Array type column and insert values into it:

CREATE TABLE arrays_test
(
    s String,
    arr Array(UInt8)
) ENGINE = Memory;

INSERT INTO arrays_test
VALUES ('Hello', [1,2]), ('World', [3,4,5]), ('Goodbye', []);

┌─s───────────┬─arr─────┐
│ Hello       │ [1,2]   │
│ World       │ [3,4,5] │
│ Goodbye     │ []      │
└─────────────┴─────────┘

The example below uses the ARRAY JOIN clause:

SELECT s, arr
FROM arrays_test
ARRAY JOIN arr;

┌─s─────┬─arr─┐
│ Hello │   1 │
│ Hello │   2 │
│ World │   3 │
│ World │   4 │
│ World │   5 │
└───────┴─────┘

The next example uses the LEFT ARRAY JOIN clause:

SELECT s, arr
FROM arrays_test
LEFT ARRAY JOIN arr;

┌─s───────────┬─arr─┐
│ Hello       │   1 │
│ Hello       │   2 │
│ World       │   3 │
│ World       │   4 │
│ World       │   5 │
│ Goodbye     │   0 │
└─────────────┴─────┘

ARRAY JOIN and arrayEnumerate function

This function is normally used with ARRAY JOIN. It allows counting something just once for each array after applying ARRAY JOIN. Example:

SELECT
    count() AS Reaches,
    countIf(num = 1) AS Hits
FROM test.hits
ARRAY JOIN
    GoalsReached,
    arrayEnumerate(GoalsReached) AS num
WHERE CounterID = 160656
LIMIT 10

┌─Reaches─┬──Hits─┐
│   95606 │ 31406 │
└─────────┴───────┘

In this example, Reaches is the number of conversions (the strings received after applying ARRAY JOIN), and Hits is the number of pageviews (strings before ARRAY JOIN). In this particular case, you can get the same result in an easier way:

SELECT
    sum(length(GoalsReached)) AS Reaches,
    count() AS Hits
FROM test.hits
WHERE (CounterID = 160656) AND notEmpty(GoalsReached)

┌─Reaches─┬──Hits─┐
│   95606 │ 31406 │
└─────────┴───────┘

ARRAY JOIN and arrayEnumerateUniq

This function is useful when using ARRAY JOIN and aggregating array elements. In this example, each goal ID has a calculation of the number of conversions (each element in the Goals nested data structure is a goal that was reached, which we refer to as a conversion) and the number of sessions. Without ARRAY JOIN, we would have counted the number of sessions as sum(Sign). But in this particular case, the rows were multiplied by the nested Goals structure, so in order to count each session one time after this, we apply a condition to the value of the arrayEnumerateUniq(Goals.ID) function.

SELECT
    Goals.ID AS GoalID,
    sum(Sign) AS Reaches,
    sumIf(Sign, num = 1) AS Visits
FROM test.visits
ARRAY JOIN
    Goals,
    arrayEnumerateUniq(Goals.ID) AS num
WHERE CounterID = 160656
GROUP BY GoalID
ORDER BY Reaches DESC
LIMIT 10

┌──GoalID─┬─Reaches─┬─Visits─┐
│   53225 │    3214 │   1097 │
│ 2825062 │    3188 │   1097 │
│   56600 │    2803 │    488 │
│ 1989037 │    2401 │    365 │
│ 2830064 │    2396 │    910 │
│ 1113562 │    2372 │    373 │
│ 3270895 │    2262 │    812 │
│ 1084657 │    2262 │    345 │
│   56599 │    2260 │    799 │
│ 3271094 │    2256 │    812 │
└─────────┴─────────┴────────┘

Using Aliases

An alias can be specified for an array in the ARRAY JOIN clause. In this case, an array item can be accessed by this alias, but the array itself is accessed by the original name. Example:

SELECT s, arr, a
FROM arrays_test
ARRAY JOIN arr AS a;

┌─s─────┬─arr─────┬─a─┐
│ Hello │ [1,2]   │ 1 │
│ Hello │ [1,2]   │ 2 │
│ World │ [3,4,5] │ 3 │
│ World │ [3,4,5] │ 4 │
│ World │ [3,4,5] │ 5 │
└───────┴─────────┴───┘

Using aliases, you can perform ARRAY JOIN with an external array. For example:

SELECT s, arr_external
FROM arrays_test
ARRAY JOIN [1, 2, 3] AS arr_external;

┌─s───────────┬─arr_external─┐
│ Hello       │            1 │
│ Hello       │            2 │
│ Hello       │            3 │
│ World       │            1 │
│ World       │            2 │
│ World       │            3 │
│ Goodbye     │            1 │
│ Goodbye     │            2 │
│ Goodbye     │            3 │
└─────────────┴──────────────┘

Multiple arrays can be comma-separated in the ARRAY JOIN clause. In this case, JOIN is performed with them simultaneously (the direct sum, not the cartesian product). Note that all the arrays must have the same size by default. Example:

SELECT s, arr, a, num, mapped
FROM arrays_test
ARRAY JOIN arr AS a, arrayEnumerate(arr) AS num, arrayMap(x -> x + 1, arr) AS mapped;

┌─s─────┬─arr─────┬─a─┬─num─┬─mapped─┐
│ Hello │ [1,2]   │ 1 │   1 │      2 │
│ Hello │ [1,2]   │ 2 │   2 │      3 │
│ World │ [3,4,5] │ 3 │   1 │      4 │
│ World │ [3,4,5] │ 4 │   2 │      5 │
│ World │ [3,4,5] │ 5 │   3 │      6 │
└───────┴─────────┴───┴─────┴────────┘

The example below uses the arrayEnumerate function:

SELECT s, arr, a, num, arrayEnumerate(arr)
FROM arrays_test
ARRAY JOIN arr AS a, arrayEnumerate(arr) AS num;

┌─s─────┬─arr─────┬─a─┬─num─┬─arrayEnumerate(arr)─┐
│ Hello │ [1,2]   │ 1 │   1 │ [1,2]               │
│ Hello │ [1,2]   │ 2 │   2 │ [1,2]               │
│ World │ [3,4,5] │ 3 │   1 │ [1,2,3]             │
│ World │ [3,4,5] │ 4 │   2 │ [1,2,3]             │
│ World │ [3,4,5] │ 5 │   3 │ [1,2,3]             │
└───────┴─────────┴───┴─────┴─────────────────────┘

Multiple arrays with different sizes can be joined by using: SETTINGS enable_unaligned_array_join = 1. Example:

SELECT s, arr, a, b
FROM arrays_test ARRAY JOIN arr AS a, [['a','b'],['c']] AS b
SETTINGS enable_unaligned_array_join = 1;

┌─s───────┬─arr─────┬─a─┬─b─────────┐
│ Hello   │ [1,2]   │ 1 │ ['a','b'] │
│ Hello   │ [1,2]   │ 2 │ ['c']     │
│ World   │ [3,4,5] │ 3 │ ['a','b'] │
│ World   │ [3,4,5] │ 4 │ ['c']     │
│ World   │ [3,4,5] │ 5 │ []        │
│ Goodbye │ []      │ 0 │ ['a','b'] │
│ Goodbye │ []      │ 0 │ ['c']     │
└─────────┴─────────┴───┴───────────┘

ARRAY JOIN with Nested Data Structure

ARRAY JOIN also works with nested data structures:

CREATE TABLE nested_test
(
    s String,
    nest Nested(
    x UInt8,
    y UInt32)
) ENGINE = Memory;

INSERT INTO nested_test
VALUES ('Hello', [1,2], [10,20]), ('World', [3,4,5], [30,40,50]), ('Goodbye', [], []);

┌─s───────┬─nest.x──┬─nest.y─────┐
│ Hello   │ [1,2]   │ [10,20]    │
│ World   │ [3,4,5] │ [30,40,50] │
│ Goodbye │ []      │ []         │
└─────────┴─────────┴────────────┘

SELECT s, `nest.x`, `nest.y`
FROM nested_test
ARRAY JOIN nest;

┌─s─────┬─nest.x─┬─nest.y─┐
│ Hello │      1 │     10 │
│ Hello │      2 │     20 │
│ World │      3 │     30 │
│ World │      4 │     40 │
│ World │      5 │     50 │
└───────┴────────┴────────┘

When specifying names of nested data structures in ARRAY JOIN, the meaning is the same as ARRAY JOIN with all the array elements that it consists of. Examples are listed below:

SELECT s, `nest.x`, `nest.y`
FROM nested_test
ARRAY JOIN `nest.x`, `nest.y`;

┌─s─────┬─nest.x─┬─nest.y─┐
│ Hello │      1 │     10 │
│ Hello │      2 │     20 │
│ World │      3 │     30 │
│ World │      4 │     40 │
│ World │      5 │     50 │
└───────┴────────┴────────┘

This variation also makes sense:

SELECT s, `nest.x`, `nest.y`
FROM nested_test
ARRAY JOIN `nest.x`;

┌─s─────┬─nest.x─┬─nest.y─────┐
│ Hello │      1 │ [10,20]    │
│ Hello │      2 │ [10,20]    │
│ World │      3 │ [30,40,50] │
│ World │      4 │ [30,40,50] │
│ World │      5 │ [30,40,50] │
└───────┴────────┴────────────┘

An alias may be used for a nested data structure, in order to select either the JOIN result or the source array. Example:

SELECT s, `n.x`, `n.y`, `nest.x`, `nest.y`
FROM nested_test
ARRAY JOIN nest AS n;

┌─s─────┬─n.x─┬─n.y─┬─nest.x──┬─nest.y─────┐
│ Hello │   1 │  10 │ [1,2]   │ [10,20]    │
│ Hello │   2 │  20 │ [1,2]   │ [10,20]    │
│ World │   3 │  30 │ [3,4,5] │ [30,40,50] │
│ World │   4 │  40 │ [3,4,5] │ [30,40,50] │
│ World │   5 │  50 │ [3,4,5] │ [30,40,50] │
└───────┴─────┴─────┴─────────┴────────────┘

Example of using the arrayEnumerate function:

SELECT s, `n.x`, `n.y`, `nest.x`, `nest.y`, num
FROM nested_test
ARRAY JOIN nest AS n, arrayEnumerate(`nest.x`) AS num;

┌─s─────┬─n.x─┬─n.y─┬─nest.x──┬─nest.y─────┬─num─┐
│ Hello │   1 │  10 │ [1,2]   │ [10,20]    │   1 │
│ Hello │   2 │  20 │ [1,2]   │ [10,20]    │   2 │
│ World │   3 │  30 │ [3,4,5] │ [30,40,50] │   1 │
│ World │   4 │  40 │ [3,4,5] │ [30,40,50] │   2 │
│ World │   5 │  50 │ [3,4,5] │ [30,40,50] │   3 │
└───────┴─────┴─────┴─────────┴────────────┴─────┘

Row Order and Paging

ARRAY JOIN keeps the order of the rows that it receives. For each row, it produces the elements in their order in the array. If a query has more than one ARRAY JOIN clause, the first clause is the outer loop:

SELECT x, y
FROM (SELECT ['a', 'b'] AS xs, [1, 2] AS ys)
ARRAY JOIN xs AS x
ARRAY JOIN ys AS y;

┌─x─┬─y─┐
│ a │ 1 │
│ a │ 2 │
│ b │ 1 │
│ b │ 2 │
└───┴───┘

If one ARRAY JOIN clause has more than one array, for example ARRAY JOIN xs AS x, ys AS y, the elements at the same position go to the same row. In this example, the result is the two rows ('a', 1) and ('b', 2). Without ORDER BY, the order of the rows that ARRAY JOIN receives is not guaranteed. For example, a query can read a table in parallel streams. To get a stable order, add ORDER BY with the columns that identify the source row and the position of the element. You must do this to read the result in pages with LIMIT and OFFSET:

SELECT id, num, x
FROM t
ARRAY JOIN xs AS x, arrayEnumerate(xs) AS num
ORDER BY id, num
LIMIT 10 OFFSET 20;

If ORDER BY starts with the columns of the sorting key of a MergeTree table, the query reads the table in the order of the key. In the example, this is true when the table has ORDER BY id. The query then stops when it has the rows for LIMIT, and it does not expand the arrays of the other rows. For more information, see Optimization of Data Reading and the setting optimize_read_in_order. If ORDER BY starts with an element of the array, for example ORDER BY num, id, the query expands and sorts all the rows. The arrayJoin function also produces the elements of each array in their order in the array. But if a query has more than one arrayJoin call, the first call is not always the outer loop. For more information, see Row order.

Implementation Details

The query execution order is optimized when running ARRAY JOIN. Although ARRAY JOIN must always be specified before the WHERE/PREWHERE clause in a query, technically they can be performed in any order, unless result of ARRAY JOIN is used for filtering. The processing order is controlled by the query optimizer.

Incompatibility with short-circuit function evaluation

Short-circuit function evaluation is a feature that optimizes the execution of complex expressions in specific functions such as if, multiIf, and, and or. It prevents potential exceptions, such as division by zero, from occurring during the execution of these functions. arrayJoin is always executed and not supported for short circuit function evaluation. That’s because it’s a unique function processed separately from all other functions during query analysis and execution and requires additional logic that doesn’t work with short circuit function execution. The reason is that the number of rows in the result depends on the arrayJoin result, and it’s too complex and expensive to implement lazy execution of arrayJoin.

Blog: Working with time series data in ClickHouse

SQL Reference

Data Types

Engines

Functions

Formats

Settings

System Tables

Data Lakes

Basic ARRAY JOIN Examples

ARRAY JOIN and LEFT ARRAY JOIN

ARRAY JOIN and arrayEnumerate function

ARRAY JOIN and arrayEnumerateUniq

Using Aliases

ARRAY JOIN with Nested Data Structure

Row Order and Paging

Implementation Details

Incompatibility with short-circuit function evaluation

​Basic ARRAY JOIN Examples

​ARRAY JOIN and LEFT ARRAY JOIN

​ARRAY JOIN and arrayEnumerate function

​ARRAY JOIN and arrayEnumerateUniq

​Using Aliases

​ARRAY JOIN with Nested Data Structure

​Row Order and Paging

​Implementation Details

​Incompatibility with short-circuit function evaluation

​Related content

Basic ARRAY JOIN Examples

ARRAY JOIN and LEFT ARRAY JOIN

ARRAY JOIN and arrayEnumerate function

ARRAY JOIN and arrayEnumerateUniq

Using Aliases

ARRAY JOIN with Nested Data Structure

Row Order and Paging

Implementation Details

Incompatibility with short-circuit function evaluation

Related content