Array functions

Function:

- Applies to any depth of nested arrays.
- Does not change arrays that are already flat.

The flattened array contains all the elements from all source arrays.

**Syntax**

```
arrayFlatten(arr)
```

**Aliases**: `flatten`

**Arguments**

- `arr` — A multidimensional array. [`Array(Array(T))`](/docs/sql-reference/data-types/array)

**Returned value**

Returns a flattened array from the multidimensional array [`Array(T)`](/docs/sql-reference/data-types/array)

**Examples**

**Usage example**

```
SELECT arrayFlatten([[[1]], [[2], [3]]]);
```

```
[1, 2, 3]
```

## arrayFold[​](#arrayFold "Direct link to arrayFold")

Introduced in: v23.10.0

Applies a lambda function to one or more equally-sized arrays and collects the result in an accumulator.

**Syntax**

```
arrayFold(λ(acc, x1 [, x2, x3, ... xN]), arr1 [, arr2, arr3, ... arrN], acc)
```

**Arguments**

- `λ(x, x1 [, x2, x3, ... xN])` — A lambda function `λ(acc, x1 [, x2, x3, ... xN]) → F(acc, x1 [, x2, x3, ... xN])` where `F` is an operation applied to `acc` and array values from `x` with the result of `acc` re-used. [`Lambda function`](/docs/sql-reference/functions/overview#arrow-operator-and-lambda)
- `arr1 [, arr2, arr3, ... arrN]` — N arrays over which to operate. [`Array(T)`](/docs/sql-reference/data-types/array)
- `acc` — Accumulator value with the same type as the return type of the Lambda function.

**Returned value**

Returns the final `acc` value.

**Examples**

**Usage example**

```
SELECT arrayFold(acc,x -> acc + x*2, [1, 2, 3, 4], 3::Int64) AS res;
```

```
23
```

**Fibonacci sequence**

```
SELECT arrayFold(acc, x -> (acc.2, acc.2 + acc.1),range(number),(1::Int64, 0::Int64)).1 AS fibonacci FROM numbers(1,10);
```

```
┌─fibonacci─┐
│         0 │
│         1 │
│         1 │
│         2 │
│         3 │
│         5 │
│         8 │
│        13 │
│        21 │
│        34 │
└───────────┘
```

**Example using multiple arrays**

```
SELECT arrayFold(
(acc, x, y) -> acc + (x * y),
[1, 2, 3, 4],
[10, 20, 30, 40],
0::Int64
) AS res;
```

```
300
```

## arrayIntersect[​](#arrayIntersect "Direct link to arrayIntersect")

Introduced in: v1.1.0

Takes multiple arrays and returns an array with elements which are present in all source arrays. The result contains only unique values.

**Syntax**

```
arrayIntersect(arr, arr1, ..., arrN)
```

**Arguments**

- `arrN` — N arrays from which to make the new array. [`Array(T)`](/docs/sql-reference/data-types/array).

**Returned value**

Returns an array with distinct elements that are present in all N arrays [`Array(T)`](/docs/sql-reference/data-types/array)

**Examples**

**Usage example**

```
SELECT
arrayIntersect([1, 2], [1, 3], [2, 3]) AS empty_intersection,
arrayIntersect([1, 2], [1, 3], [1, 4]) AS non_empty_intersection
```

```
┌─non_empty_intersection─┬─empty_intersection─┐
│ []                     │ [1]                │
└────────────────────────┴────────────────────┘
```

## arrayJaccardIndex[​](#arrayJaccardIndex "Direct link to arrayJaccardIndex")

Introduced in: v23.7.0

Returns the [Jaccard index](https://en.wikipedia.org/wiki/Jaccard_index) of two arrays.

**Syntax**

```
arrayJaccardIndex(arr_x, arr_y)
```

**Arguments**

- `arr_x` — First array. [`Array(T)`](/docs/sql-reference/data-types/array)
- `arr_y` — Second array. [`Array(T)`](/docs/sql-reference/data-types/array)

**Returned value**

Returns the Jaccard index of `arr_x` and `arr_y` [`Float64`](/docs/sql-reference/data-types/float)

**Examples**

**Usage example**

```
SELECT arrayJaccardIndex([1, 2], [2, 3]) AS res
```

```
0.3333333333333333
```

## arrayJoin[​](#arrayJoin "Direct link to arrayJoin")

Introduced in: v1.1.0

The `arrayJoin` function takes a row that contains an array and unfolds it, generating multiple rows – one for each element in the array.
This is in contrast to Regular Functions in ClickHouse which map input values to output values within the same row,
and Aggregate Functions which take a group of rows and "compress" or "reduce" them into a single summary row
(or a single value within a summary row if used with `GROUP BY`).

All the values in the columns are simply copied, except the values in the column where this function is applied;
these are replaced with the corresponding array value.

**Syntax**

```
arrayJoin(arr)
```

**Arguments**

- `arr` — An array to unfold. [`Array(T)`](/docs/sql-reference/data-types/array)

**Returned value**

Returns a set of rows unfolded from `arr`.

**Examples**

**Basic usage**

```
SELECT arrayJoin([1, 2, 3] AS src) AS dst, 'Hello', src
```

```
┌─dst─┬─\'Hello\'─┬─src─────┐
│   1 │ Hello     │ [1,2,3] │
│   2 │ Hello     │ [1,2,3] │
│   3 │ Hello     │ [1,2,3] │
└─────┴───────────┴─────────┘
```

**arrayJoin affects all sections of the query**

```
-- The arrayJoin function affects all sections of the query, including the WHERE section. Notice the result 2, even though the subquery returned 1 row.

SELECT sum(1) AS impressions
FROM
(
    SELECT ['Istanbul', 'Berlin', 'Bobruisk'] AS cities
)
WHERE arrayJoin(cities) IN ['Istanbul', 'Berlin'];
```

```
┌─impressions─┐
│           2 │
└─────────────┘
```

**Using multiple arrayJoin functions**

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

SELECT
    sum(1) AS impressions,
    arrayJoin(cities) AS city,
    arrayJoin(browsers) AS browser
FROM
(
    SELECT
        ['Istanbul', 'Berlin', 'Bobruisk'] AS cities,
        ['Firefox', 'Chrome', 'Chrome'] AS browsers
)
GROUP BY
    2,
    3
```

```
┌─impressions─┬─city─────┬─browser─┐
│           2 │ Istanbul │ Chrome  │
│           1 │ Istanbul │ Firefox │
│           2 │ Berlin   │ Chrome  │
│           1 │ Berlin   │ Firefox │
│           2 │ Bobruisk │ Chrome  │
│           1 │ Bobruisk │ Firefox │
└─────────────┴──────────┴─────────┘
```

**Unexpected results due to optimizations**

```
-- Using multiple arrayJoin with the same expression may not produce the expected result due to optimizations.
-- For these cases, consider modifying the repeated array expression with extra operations that do not affect join result.
- e.g. arrayJoin(arraySort(arr)), arrayJoin(arrayConcat(arr, []))

SELECT
    arrayJoin(dice) as first_throw,
    /* arrayJoin(dice) as second_throw */ -- is technically correct, but will annihilate result set
    arrayJoin(arrayConcat(dice, [])) as second_throw -- intentionally changed expression to force re-evaluation
FROM (
    SELECT [1, 2, 3, 4, 5, 6] as dice
);
```

```
┌─first_throw─┬─second_throw─┐
│           1 │            1 │
│           1 │            2 │
│           1 │            3 │
│           1 │            4 │
│           1 │            5 │
│           1 │            6 │
│           2 │            1 │
│           2 │            2 │
│           2 │            3 │
│           2 │            4 │
│           2 │            5 │
│           2 │            6 │
│           3 │            1 │
│           3 │            2 │
│           3 │            3 │
│           3 │            4 │
│           3 │            5 │
│           3 │            6 │
│           4 │            1 │
│           4 │            2 │
│           4 │            3 │
│           4 │            4 │
│           4 │            5 │
│           4 │            6 │
│           5 │            1 │
│           5 │            2 │
│           5 │            3 │
│           5 │            4 │
│           5 │            5 │
│           5 │            6 │
│           6 │            1 │
│           6 │            2 │
│           6 │            3 │
│           6 │            4 │
│           6 │            5 │
│           6 │            6 │
└─────────────┴──────────────┘
```

**Using the ARRAY JOIN syntax**

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