Array functions

**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

Shifts an array to the left by the specified number of elements.
New elements are filled with the provided argument or the default value of the array element type.
If the number of elements is negative, the array is shifted to the right.

**Syntax**

```
arrayShiftLeft(arr, n[, default])
```

**Arguments**

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

**Returned value**

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

**Examples**

**Usage example**

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

```
[3,4,5,6,0,0]
```

**Negative value of n**

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

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

**Using a default value**

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

```
[3,4,5,6,42,42]
```

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

Introduced in: v23.8.0

Shifts an array to the right by the specified number of elements.
New elements are filled with the provided argument or the default value of the array element type.
If the number of elements is negative, the array is shifted to the left.

**Syntax**

```
arrayShiftRight(arr, n[, default])
```

**Arguments**

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

**Returned value**

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

**Examples**

**Usage example**

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

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

**Negative value of n**

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

```
[3, 4, 5, 6, 0, 0]
```

**Using a default value**

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

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

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

Introduced in: v24.1.0

Generates an array of shingles (similar to ngrams for strings), i.e. consecutive sub-arrays with a specified length of the input array.

**Syntax**

```
arrayShingles(arr, l)
```

**Arguments**

- `arr` — Array for which to generate an array of shingles. [`Array(T)`](/docs/sql-reference/data-types/array)
- `l` — The length of each shingle. [`(U)Int*`](/docs/sql-reference/data-types/int-uint)

**Returned value**

An array of generated shingles [`Array(T)`](/docs/sql-reference/data-types/array)

**Examples**

**Usage example**

```
SELECT arrayShingles([1, 2, 3, 4], 3) as res;
```

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

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

Introduced in: v23.2.0

Returns an array of the same size as the original array containing the elements in shuffled order.
Elements are reordered in such a way that each possible permutation of those elements has equal probability of appearance.

Note

This function will not materialize constants.

**Syntax**

```
arrayShuffle(arr [, seed])
```

**Arguments**

- `arr` — The array to shuffle. [`Array(T)`](/docs/sql-reference/data-types/array)
- `seed (optional)` — Optional. The seed to be used with random number generation. If not provided a random one is used. [`(U)Int*`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Array with elements shuffled [`Array(T)`](/docs/sql-reference/data-types/array)

**Examples**

**Example without seed (unstable results)**

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

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

**Example without seed (stable results)**

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

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

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

Introduced in: v25.4.0

Calculates the similarity of two arrays from `0` to `1` based on weighted Levenshtein distance.

**Syntax**

```
arraySimilarity(from, to, from_weights, to_weights)
```

**Arguments**

- `from` — first array [`Array(T)`](/docs/sql-reference/data-types/array)
- `to` — second array [`Array(T)`](/docs/sql-reference/data-types/array)
- `from_weights` — weights for the first array. [`Array((U)Int*|Float*)`](/docs/sql-reference/data-types/array)
- `to_weights` — weights for the second array. [`Array((U)Int*|Float*)`](/docs/sql-reference/data-types/array)

**Returned value**

Returns the similarity between `0` and `1` of the two arrays based on the weighted Levenshtein distance [`Float64`](/docs/sql-reference/data-types/float)

**Examples**

**Usage example**

```
SELECT arraySimilarity(['A', 'B', 'C'], ['A', 'K', 'L'], [1.0, 2, 3], [3.0, 4, 5]);
```

```
0.2222222222222222
```

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

Introduced in: v1.1.0

Returns a slice of the array, with `NULL` elements included.

**Syntax**

```
arraySlice(arr, offset [, length])
```

**Arguments**

- `arr` — Array to slice. [`Array(T)`](/docs/sql-reference/data-types/array)
- `offset` — Indent from the edge of the array. A positive value indicates an offset on the left, and a negative value is an indent on the right. Numbering of the array items begins with `1`. [`(U)Int*`](/docs/sql-reference/data-types/int-uint)
- `length` — The length of the required slice. If you specify a negative value, the function returns an open slice `[offset, array_length - length]`. If you omit the value, the function returns the slice `[offset, the_end_of_array]`. [`(U)Int*`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns a slice of the array with `length` elements from the specified `offset` [`Array(T)`](/docs/sql-reference/data-types/array)

**Examples**

**Usage example**

```
SELECT arraySlice([1, 2, NULL, 4, 5], 2, 3) AS res;
```

```
[2, NULL, 4]
```

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

Introduced in: v1.1.0

Sorts the elements of the provided array in ascending order.
If a lambda function `f` is specified, sorting order is determined by the result of
the lambda applied to each element of the array.
If the lambda accepts multiple arguments, the `arraySort` function is passed several
arrays that the arguments of `f` 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`

`arraySort` is a [higher-order function](/docs/sql-reference/functions/overview#higher-order-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']
```