Array functions

**Example 1**

```
SELECT arrayRemove([1, 2, 2, 3], 2)
```

```
[1, 3]
```

**Example 2**

```
SELECT arrayRemove(['a', NULL, 'b', NULL], NULL)
```

```
['a', 'b']
```

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

Introduced in: v1.1.0

Changes the length of the array.

**Syntax**

```
arrayResize(arr, size[, extender])
```

**Arguments**

- `arr` — Array to resize. [`Array(T)`](/docs/sql-reference/data-types/array)
- `size` —
  -The new length of the array.
  If `size` is less than the original size of the array, the array is truncated from the right.
  If `size` is larger than the initial size of the array, the array is extended to the right with `extender` values or default values for the data type of the array items.
- `extender` — Value to use for extending the array. Can be `NULL`.

**Returned value**

An array of length `size`. [`Array(T)`](/docs/sql-reference/data-types/array)

**Examples**

**Example 1**

```
SELECT arrayResize([1], 3);
```

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

**Example 2**

```
SELECT arrayResize([1], 3, NULL);
```

```
[1,NULL,NULL]
```

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

Introduced in: v1.1.0

Reverses the order of elements of a given array.

Note

Function `reverse(arr)` performs the same functionality but works on other data-types
in addition to Arrays.

**Syntax**

```
arrayReverse(arr)
```

**Arguments**

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

**Returned value**

Returns an array of the same size as the original array containing the elements in reverse order [`Array(T)`](/docs/sql-reference/data-types/array)

**Examples**

**Usage example**

```
SELECT arrayReverse([1, 2, 3])
```

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

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

Introduced in: v20.1.0

The `arrayReverseFill` function sequentially processes a source array from the last
element to the first, evaluating a lambda condition at each position using elements
from the source and condition arrays. When the condition evaluates to false at
position i, the function replaces that element with the element at position i+1
from the current state of the array. The last element is always preserved
regardless of any condition.

**Syntax**

```
arrayReverseFill(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 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 an array with elements of the source array replaced by the results of the lambda. [`Array(T)`](/docs/sql-reference/data-types/array)

**Examples**

**Example with a single array**

```
SELECT arrayReverseFill(x -> not isNull(x), [1, null, 2, null]) AS res
```

```
[1, 2, 2, NULL]
```

**Example with two arrays**

```
SELECT arrayReverseFill(x, y, z -> x > y AND x < z, [5, 3, 6, 2], [4, 7, 1, 3], [10, 2, 8, 5]) AS res;
```

```
[5, 6, 6, 2]
```

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

Introduced in: v1.1.0

Sorts the elements of an array in descending order.
If a function `f` is specified, the provided array is sorted according to the result
of the function applied to the elements of the array, and then the sorted array is reversed.
If `f` accepts multiple arguments, the `arrayReverseSort` function is passed several arrays that
the arguments of `func` will correspond to.

If the array to sort contains `-Inf`, `NULL`, `NaN`, or `Inf` they will be sorted in the following order:

1. `-Inf`
2. `Inf`
3. `NaN`
4. `NULL`

`arrayReverseSort` is a [higher-order function](/docs/sql-reference/functions/overview#higher-order-functions).

**Syntax**

```
arrayReverseSort([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 `x` sorted in descending order if no lambda function is provided, otherwise
it returns an array sorted according to the logic of the provided lambda function, and then reversed. [`Array(T)`](/docs/sql-reference/data-types/array).

**Examples**

**Example 1**

```
SELECT arrayReverseSort((x, y) -> y, [4, 3, 5], ['a', 'b', 'c']) AS res;
```

```
[5,3,4]
```

**Example 2**

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

```
[4,3,5]
```

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

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 right of the element. The array will not be split after the last element.

**Syntax**

```
arrayReverseSplit(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 process. [`Lambda function`](/docs/sql-reference/functions/overview#arrow-operator-and-lambda)
- `[, 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 arrayReverseSplit((x, y) -> y, [1, 2, 3, 4, 5], [1, 0, 0, 1, 0]) AS res
```

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

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

Introduced in: v23.8.0

Rotates an array to the left by the specified number of elements. Negative values of `n` are treated as rotating to the right by the absolute value of the rotation.

**Syntax**

```
arrayRotateLeft(arr, n)
```

**Arguments**

- `arr` — The array for which to rotate the elements.[`Array(T)`](/docs/sql-reference/data-types/array). - `n` — Number of elements to rotate. [`(U)Int8/16/32/64`](/docs/sql-reference/data-types/int-uint).

**Returned value**

An array rotated to the left by the specified number of elements [`Array(T)`](/docs/sql-reference/data-types/array)

**Examples**

**Usage example**

```
SELECT arrayRotateLeft([1,2,3,4,5,6], 2) as res;
```

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

**Negative value of n**

```
SELECT arrayRotateLeft([1,2,3,4,5,6], -2) as res;
```

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

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

Introduced in: v23.8.0

Rotates an array to the right by the specified number of elements. Negative values of `n` are treated as rotating to the left by the absolute value of the rotation.

**Syntax**

```
arrayRotateRight(arr, n)
```

**Arguments**

- `arr` — The array for which to rotate the elements.[`Array(T)`](/docs/sql-reference/data-types/array). - `n` — Number of elements to rotate. [`(U)Int8/16/32/64`](/docs/sql-reference/data-types/int-uint).

**Returned value**

An array rotated to the right by the specified number of elements [`Array(T)`](/docs/sql-reference/data-types/array)

**Examples**

**Usage example**

```
SELECT arrayRotateRight([1,2,3,4,5,6], 2) as res;
```

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

**Negative value of n**

```
SELECT arrayRotateRight([1,2,3,4,5,6], -2) as res;
```

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

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

Introduced in: v23.8.0