Array functions

**Syntax**

```
arraySort([f,] arr [, arr1, ... ,arrN])
```

**Arguments**

- `f(y1[, y2 ... yN])` — The lambda function to apply to elements of array `x`. - `arr` — An array to be sorted. [`Array(T)`](/docs/sql-reference/data-types/array) - `arr1, ..., yN` — Optional. N additional arrays, in the case when `f` accepts multiple arguments.

**Returned value**

Returns the array `arr` sorted in ascending order if no lambda function is provided, otherwise
it returns an array sorted according to the logic of the provided lambda function. [`Array(T)`](/docs/sql-reference/data-types/array).

**Examples**

**Example 1**

```
SELECT arraySort([1, 3, 3, 0]);
```

```
[0,1,3,3]
```

**Example 2**

```
SELECT arraySort(['hello', 'world', '!']);
```

```
['!','hello','world']
```

**Example 3**

```
SELECT arraySort([1, nan, 2, NULL, 3, nan, -4, NULL, inf, -inf]);
```

```
[-inf,-4,1,2,3,inf,nan,nan,NULL,NULL]
```

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

Introduced in: v20.1.0

Split a source array into multiple arrays. When `func(x [, y1, ..., yN])` returns something other than zero, the array will be split to the left of the element. The array will not be split before the first element.

**Syntax**

```
arraySplit(func(x[, y1, ..., yN]), source_arr[, cond1_arr, ... , condN_arr])
```

**Arguments**

- `func(x[, y1, ..., yN])` — A lambda function which operates on elements of the source array (`x`) and condition arrays (`y`).[Lambda function](/docs/sql-reference/functions/overview#arrow-operator-and-lambda). - `source_arr` — The source array to split [`Array(T)`](/docs/sql-reference/data-types/array). - `[, cond1_arr, ... , condN_arr]` — Optional. N condition arrays providing additional arguments to the lambda function. [`Array(T)`](/docs/sql-reference/data-types/array).

**Returned value**

Returns an array of arrays [`Array(Array(T))`](/docs/sql-reference/data-types/array)

**Examples**

**Usage example**

```
SELECT arraySplit((x, y) -> y, [1, 2, 3, 4, 5], [1, 0, 0, 1, 0]) AS res
```

```
[[1, 2, 3], [4, 5]]
```

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

Introduced in: v21.1.0

Returns the sum of elements in the source array.

If a lambda function `func` is specified, returns the sum of elements of the lambda results.

**Syntax**

```
arraySum([func(x[, y1, ..., yN])], source_arr[, cond1_arr, ... , condN_arr])
```

**Arguments**

- `func(x[, y1, ..., yN])` — Optional. A lambda function which operates on elements of the source array (`x`) and condition arrays (`y`). [`Lambda function`](/docs/sql-reference/functions/overview#arrow-operator-and-lambda)
- `source_arr` — The source array to process. [`Array(T)`](/docs/sql-reference/data-types/array)
- `, cond1_arr, ... , condN_arr]` — Optional. N condition arrays providing additional arguments to the lambda function. [`Array(T)`](/docs/sql-reference/data-types/array)

**Returned value**

Returns the sum of elements in the source array, or the sum of elements of the lambda results if provided.

**Examples**

**Basic example**

```
SELECT arraySum([1, 2, 3, 4]);
```

```
10
```

**Usage with lambda function**

```
SELECT arraySum(x, y -> x+y, [1, 1, 1, 1], [1, 1, 1, 1]);
```

```
8
```

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

Introduced in: v25.4.0

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

Note

The symmetric difference of *more than two sets* is [mathematically defined](https://en.wikipedia.org/wiki/Symmetric_difference#n-ary_symmetric_difference)
as the set of all input elements which occur in an odd number of input sets.
In contrast, function `arraySymmetricDifference` simply returns the set of input elements which do not occur in all input sets.

**Syntax**

```
arraySymmetricDifference(arr1, arr2, ... , 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 of distinct elements not present in all source arrays [`Array(T)`](/docs/sql-reference/data-types/array)

**Examples**

**Usage example**

```
SELECT
arraySymmetricDifference([1, 2], [1, 2], [1, 2]) AS empty_symmetric_difference,
arraySymmetricDifference([1, 2], [1, 2], [1, 3]) AS non_empty_symmetric_difference;
```

```
┌─empty_symmetric_difference─┬─non_empty_symmetric_difference─┐
│ []                         │ [3,2]                          │
└────────────────────────────┴────────────────────────────────┘
```

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

Introduced in: v26.4.0

Transposes a two-dimensional array.

All inner arrays must have the same length.

**Syntax**

```
arrayTranspose(arr)
```

**Arguments**

- `arr` — A two-dimensional array to transpose. All inner arrays must have the same length. [`Array(Array(T))`](/docs/sql-reference/data-types/array)

**Returned value**

A transposed two-dimensional array where element `[i][j]` of the result equals element `[j][i]` of the input. [`Array(Array(T))`](/docs/sql-reference/data-types/array)

**Examples**

**Square matrix**

```
SELECT arrayTranspose([[1, 2], [3, 4]])
```

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

**Non-square matrix**

```
SELECT arrayTranspose([[1, 2, 3], [4, 5, 6]])
```

```
[[1, 4], [2, 5], [3, 6]]
```

**String elements**

```
SELECT arrayTranspose([['a', 'b'], ['c', 'd']])
```

```
[['a', 'c'], ['b', 'd']]
```

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

Introduced in: v24.10.0

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

**Syntax**

```
arrayUnion(arr1, arr2, ..., 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 from the source arrays [`Array(T)`](/docs/sql-reference/data-types/array)

**Examples**

**Usage example**

```
SELECT
arrayUnion([-2, 1], [10, 1], [-2], []) as num_example,
arrayUnion(['hi'], [], ['hello', 'hi']) as str_example,
arrayUnion([1, 3, NULL], [2, 3, NULL]) as null_example
```

```
┌─num_example─┬─str_example────┬─null_example─┐
│ [10,-2,1]   │ ['hello','hi'] │ [3,2,1,NULL] │
└─────────────┴────────────────┴──────────────┘
```

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

Introduced in: v1.1.0

For a single argument passed, counts the number of different elements in the array.
For multiple arguments passed, it counts the number of different **tuples** made of elements at matching positions across multiple arrays.

For example `SELECT arrayUniq([1,2], [3,4], [5,6])` will form the following tuples:

- Position 1: (1,3,5)
- Position 2: (2,4,6)

It will then count the number of unique tuples. In this case `2`.

All arrays passed must have the same length.

Tip

If you want to get a list of unique items in an array, you can use `arrayReduce('groupUniqArray', arr)`.

**Syntax**

```
arrayUniq(arr1[, arr2, ..., arrN])
```

**Arguments**

- `arr1` — Array for which to count the number of unique elements. [`Array(T)`](/docs/sql-reference/data-types/array)
- `[, arr2, ..., arrN]` — Optional. Additional arrays used to count the number of unique tuples of elements at corresponding positions in multiple arrays. [`Array(T)`](/docs/sql-reference/data-types/array)

**Returned value**

For a single argument returns the number of unique
elements. For multiple arguments returns the number of unique tuples made from
elements at corresponding positions across the arrays.
[`UInt32`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Single argument**

```
SELECT arrayUniq([1, 1, 2, 2])
```

```
2
```

**Multiple argument**

```
SELECT arrayUniq([1, 2, 3, 1], [4, 5, 6, 4])
```

```
3
```

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

Introduced in: v20.1.0

Creates an array of length `length` filled with the constant `x`.

**Syntax**

```
arrayWithConstant(N, x)
```

**Arguments**