mirrors/ClickHouse
0
0 Fork 0
mirror of https://github.com/ClickHouse/ClickHouse.git synced 2026-10-28 16:18:23 +08:00
Code Activity
Files
master
ClickHouse/docs/reference/functions/regular-functions/array-functions.mdx
T

6213 lines
164 KiB
Plaintext
Raw Permalink Blame History

This file contains ambiguous Unicode characters
This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
---
description: 'Documentation for Array Functions'
sidebarTitle: 'Arrays'
slug: /sql-reference/functions/array-functions
title: 'Array Functions'
doc_type: 'reference'
---
{/*
The inner content of the tags below are replaced at doc framework build time with
docs generated from system.functions. Please do not modify or remove the tags.
See: https://github.com/ClickHouse/clickhouse-docs/blob/main/contribute/autogenerated-documentation-from-source.md
*/}
{/*AUTOGENERATED_START*/}
## array {#array}
Introduced in: v1.1.0
Creates an array from the function arguments.
The arguments should be constants and have types that share a common supertype.
At least one argument must be passed, because otherwise it isn't clear which type of array to create.
This means that you can't use this function to create an empty array. To do so, use the `emptyArray*` function.
Use the `[ ]` operator for the same functionality.
**Syntax**
```sql
array(x1 [, x2, ..., xN])
```
**Arguments**
- `x1` — Constant value of any type T. If only this argument is provided, the array will be of type T. - `[, x2, ..., xN]` — Additional N constant values sharing a common supertype with `x1`
**Returned value**
Returns an array, where 'T' is the smallest common type out of the passed arguments. [`Array(T)`](/reference/data-types/array)
**Examples**
**Valid usage**
```sql title=Query
SELECT array(toInt32(1), toUInt16(2), toInt8(3)) AS a, toTypeName(a)
```
```response title=Response
┌─a───────┬─toTypeName(a)─┐
│ [1,2,3] │ Array(Int32) │
└─────────┴───────────────┘
```
**Invalid usage**
```sql title=Query
SELECT array(toInt32(5), toDateTime('1998-06-16'), toInt8(5)) AS a, toTypeName(a)
```
```response title=Response
┌─a───────────────────────────┬─toTypeName(a)─────────────────────────┐
│ [5,'1998-06-16 00:00:00',5] │ Array(Variant(DateTime, Int32, Int8)) │
└─────────────────────────────┴───────────────────────────────────────┘
```
## arrayAUCPR {#arrayAUCPR}
Introduced in: v20.4.0
Calculates the area under the precision-recall (PR) curve.
A precision-recall curve is created by plotting precision on the y-axis and recall on the x-axis across all thresholds.
The resulting value ranges from 0 to 1, with a higher value indicating better model performance, unless a score is `NaN`, in which case the result is `NaN`.
The PR AUC is particularly useful for imbalanced datasets, providing a clearer comparison of performance compared to ROC AUC on those cases.
For more details, please see [here](https://developers.google.com/machine-learning/glossary#pr-auc-area-under-the-pr-curve), [here](https://developers.google.com/machine-learning/crash-course/classification/roc-and-auc#expandable-1) and [here](https://en.wikipedia.org/wiki/Receiver_operating_characteristic#Area_under_the_curve).
**Syntax**
```sql
arrayAUCPR(scores, labels[, partial_offsets])
```
**Aliases**: `arrayPRAUC`
**Arguments**
- `cores` — Scores prediction model gives. [`Array((U)Int*)`](/reference/data-types/array) or [`Array(Float*)`](/reference/data-types/array)
- `labels` — Labels of samples, usually 1 for positive sample and 0 for negative sample. [`Array((U)Int*)`](/reference/data-types/array) or [`Array(Enum)`](/reference/data-types/array)
- `partial_offsets` —
- Optional. An [`Array(T)`](/reference/data-types/array) of three non-negative integers for calculating a partial area under the PR curve (equivalent to a vertical band of the PR space) instead of the whole AUC. This option is useful for distributed computation of the PR AUC. The array must contain the following elements [`higher_partitions_tp`, `higher_partitions_fp`, `total_positives`].
- `higher_partitions_tp`: The number of positive labels in the higher-scored partitions.
- `higher_partitions_fp`: The number of negative labels in the higher-scored partitions.
- `total_positives`: The total number of positive samples in the entire dataset.
<Note>
When `arr_partial_offsets` is used, the `arr_scores` and `arr_labels` should be only a partition of the entire dataset, containing an interval of scores.
The dataset should be divided into contiguous partitions, where each partition contains the subset of the data whose scores fall within a specific range.
For example:
- One partition could contain all scores in the range [0, 0.5).
- Another partition could contain scores in the range [0.5, 1.0].
</Note>
**Returned value**
Returns area under the precision-recall (PR) curve, or `NaN` if any score is `NaN`. [`Float64`](/reference/data-types/float)
**Examples**
**Usage example**
```sql title=Query
SELECT arrayAUCPR([0.1, 0.4, 0.35, 0.8], [0, 0, 1, 1]);
```
```response title=Response
┌─arrayAUCPR([0.1, 0.4, 0.35, 0.8], [0, 0, 1, 1])─┐
│ 0.8333333333333333 │
└─────────────────────────────────────────────────┘
```
## arrayAll {#arrayAll}
Introduced in: v1.1.0
Returns `1` if lambda `func(x [, y1, y2, ... yN])` returns true for all elements. Otherwise, it returns `0`.
**Syntax**
```sql
arrayAll(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`](/reference/functions/regular-functions/overview#arrow-operator-and-lambda)
- `source_arr` — The source array to process. [`Array(T)`](/reference/data-types/array)
- `cond1_arr, ...` — Optional. N condition arrays providing additional arguments to the lambda function. [`Array(T)`](/reference/data-types/array)
**Returned value**
Returns `1` if the lambda function returns true for all elements, `0` otherwise [`UInt8`](/reference/data-types/int-uint)
**Examples**
**All elements match**
```sql title=Query
SELECT arrayAll(x, y -> x=y, [1, 2, 3], [1, 2, 3])
```
```response title=Response
1
```
**Not all elements match**
```sql title=Query
SELECT arrayAll(x, y -> x=y, [1, 2, 3], [1, 1, 1])
```
```response title=Response
0
```
## arrayAutocorrelation {#arrayAutocorrelation}
Introduced in: v26.4.0
Calculates the autocorrelation of an array.
If `max_lag` is provided, calculates correlation only for lags in range `[0, max_lag)`.
If `max_lag` is not provided, calculates for all possible lags.
**Syntax**
```sql
arrayAutocorrelation(arr, [max_lag])
```
**Arguments**
- `arr` — Array of numbers. [`Array(T)`](/reference/data-types/array)
- `max_lag` — Optional. Maximum number of lags to compute. Must be a non-negative integer. [`Integer`](/reference/data-types/int-uint)
**Returned value**
Returns an array of Float64. Returns NaN if variance is 0. [`Array(Float64)`](/reference/data-types/array)
**Examples**
**Linear**
```sql title=Query
SELECT arrayAutocorrelation([1, 2, 3, 4, 5]);
```
```response title=Response
[1,0.4,-0.1,-0.4,-0.4]
```
**Symmetric**
```sql title=Query
SELECT arrayAutocorrelation([10, 20, 10]);
```
```response title=Response
[1,-0.6666666666666669,0.16666666666666674]
```
**Constant**
```sql title=Query
SELECT arrayAutocorrelation([5, 5, 5]);
```
```response title=Response
[nan,nan,nan]
```
**Limited**
```sql title=Query
SELECT arrayAutocorrelation([1, 2, 3, 4, 5], 2);
```
```response title=Response
[1,0.4]
```
## arrayAvg {#arrayAvg}
Introduced in: v21.1.0
Returns the average of elements in the source array.
If a lambda function `func` is specified, returns the average of elements of the lambda results.
**Syntax**
```sql
arrayAvg([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`](/reference/functions/regular-functions/overview#arrow-operator-and-lambda)
- `source_arr` — The source array to process. [`Array(T)`](/reference/data-types/array)
- `[, cond1_arr, ... , condN_arr]` — Optional. N condition arrays providing additional arguments to the lambda function. [`Array(T)`](/reference/data-types/array)
**Returned value**
Returns the average of elements in the source array, or the average of elements of the lambda results if provided. [`Float64`](/reference/data-types/float)
**Examples**
**Basic example**
```sql title=Query
SELECT arrayAvg([1, 2, 3, 4]);
```
```response title=Response
2.5
```
**Usage with lambda function**
```sql title=Query
SELECT arrayAvg(x, y -> x*y, [2, 3], [2, 3]) AS res;
```
```response title=Response
6.5
```
## arrayBottomK {#arrayBottomK}
Introduced in: v26.6.0
Returns an array of the K smallest elements of the input array, sorted in ascending order.
If a lambda function `f` is specified, elements are compared by the result of `f` applied to each element.
If `f` accepts multiple arguments, additional arrays are passed to `arrayBottomK`, their elements
correspond to the arguments of `f`.
`NULL` values are skipped and do not appear in the result. The result size is at most `K`
and may be smaller when the input array contains fewer non-null elements than `K`.
The element type of the result is the non-nullable counterpart of the input element type.
`arrayBottomK` is a [higher-order function](/reference/functions/regular-functions/overview#higher-order-functions).
See also:
- `arrayTopK`, which returns the K largest elements instead.
- `arrayPartialSort`, which produces the same K elements at positions `[1..K]` but
also keeps the remaining elements in unspecified order, and does not skip nulls.
**Syntax**
```sql
arrayBottomK([f,] K, arr [, arr1, ... ,arrN])
```
**Arguments**
- `f(arr[, arr1, ... ,arrN])` — Optional. A lambda function to compute the sort key for each element. [`Lambda function`](/reference/functions/regular-functions/overview#arrow-operator-and-lambda)
- `K` — The number of smallest elements to return. [`(U)Int8/16/32/64`](/reference/data-types/int-uint)
- `arr` — An array. [`Array(T)`](/reference/data-types/array)
- `arr1, ... ,arrN` — N additional arrays, in the case when `f` accepts multiple arguments. [`Array(T)`](/reference/data-types/array)
**Returned value**
Returns up to `K` elements of `arr` with the smallest values (or smallest lambda results), sorted in ascending order.
Nulls are skipped. The returned array has element type `T` even when the input has type `Nullable(T)`.
**Examples**
**simple_int**
```sql title=Query
SELECT arrayBottomK(3, [1, 5, 2, 7, 3])
```
```response title=Response
[1,2,3]
```
**skip_nulls**
```sql title=Query
SELECT arrayBottomK(3, [1, NULL, 5, 2, NULL, 7])
```
```response title=Response
[1,2,5]
```
**fewer_than_k**
```sql title=Query
SELECT arrayBottomK(5, [1, NULL, 2])
```
```response title=Response
[1,2]
```
**lambda_simple**
```sql title=Query
SELECT arrayBottomK((x) -> -x, 2, [5, 9, 1, 3])
```
```response title=Response
[9,5]
```
**lambda_multi**
```sql title=Query
SELECT arrayBottomK((x, y) -> y, 2, ['a', 'b', 'c'], [3, 1, 2])
```
```response title=Response
['b','c']
```
## arrayCombinations {#arrayCombinations}
Introduced in: v26.10.0
Returns all combinations (unordered selections without repeated indexes) of length `k` from the source array. Enumeration is done by indexes, so equal values may produce duplicate rows in the result. For `k = 0`, returns `[[]]`. The total result size per block is limited by `function_range_max_elements_in_block`; every generated array counts as its size plus one.
**Syntax**
```sql
arrayCombinations(arr, k)
```
**Arguments**
- `arr` — Source array. [`Array(T)`](/reference/data-types/array)
- `k` — Length of each generated combination. Must not exceed source array size. Supports `Int8`, `Int16`, `Int32`, `Int64`, `UInt8`, `UInt16`, `UInt32`, and `UInt64`. [`(U)Int*`](/reference/data-types/int-uint)
**Returned value**
All combinations of length `k`. [`Array(Array(T))`](/reference/data-types/array)
**Examples**
**Usage example**
```sql title=Query
SELECT arrayCombinations([1, 2, 3], 2);
```
```response title=Response
[[1,2],[1,3],[2,3]]
```
**`k = 0` example**
```sql title=Query
SELECT arrayCombinations([1, 2, 3], 0);
```
```response title=Response
[[]]
```
## arrayCompact {#arrayCompact}
Introduced in: v20.1.0
Removes consecutive duplicate elements from an array, including `null` values. The order of values in the resulting array is determined by the order in the source array.
**Syntax**
```sql
arrayCompact(arr)
```
**Arguments**
- `arr` — An array to remove duplicates from. [`Array(T)`](/reference/data-types/array)
**Returned value**
Returns an array without duplicate values [`Array(T)`](/reference/data-types/array)
**Examples**
**Usage example**
```sql title=Query
SELECT arrayCompact([1, 1, nan, nan, 2, 3, 3, 3]);
```
```response title=Response
[1,nan,2,3]
```
## arrayConcat {#arrayConcat}
Introduced in: v1.1.0
Combines arrays passed as arguments.
**Syntax**
```sql
arrayConcat(arr1 [, arr2, ... , arrN])
```
**Arguments**
- `arr1 [, arr2, ... , arrN]` — N number of arrays to concatenate. [`Array(T)`](/reference/data-types/array)
**Returned value**
Returns a single combined array from the provided array arguments. [`Array(T)`](/reference/data-types/array)
**Examples**
**Usage example**
```sql title=Query
SELECT arrayConcat([1, 2], [3, 4], [5, 6]) AS res
```
```response title=Response
[1,2,3,4,5,6]
```
## arrayCount {#arrayCount}
Introduced in: v1.1.0
Returns the number of elements for which `func(arr1[i], ..., arrN[i])` returns true.
If `func` is not specified, it returns the number of non-zero elements in the array.
`arrayCount` is a [higher-order function](/reference/functions/regular-functions/overview#higher-order-functions).
<Info>
**Use setting `array_count_legacy_uint32_result` to return `UInt32`**
Version 26.10 introduced a backward-incompatible change: `arrayCount` returns `UInt64` instead of `UInt32`, so that the result is exact for arrays with more than `4294967295` matching elements.
The constness of the result also changed: for a non-constant array and a predicate that folds to a constant false, the result was a constant column before 26.10 (`isConstant(arrayCount(...))` returned `1`), and now it is a full column (`isConstant` returns `0`), matching `length(arrayFilter(...))`.
To retain the previous behavior (both the `UInt32` result type and the result constness), set setting `array_count_legacy_uint32_result` (default: `false`) to `true`.
During a rolling upgrade of a cluster, a distributed query initiated by a not-yet-upgraded server does not forward this setting, so type-sensitive expressions evaluated locally on already-upgraded shards (for example, `byteSize(arrayCount(...))`) observe `UInt64` there. To keep such queries fully unchanged until the whole cluster is upgraded, set `array_count_legacy_uint32_result = 1` on the upgraded servers for the users under which shard-side queries execute, and remove it after the upgrade is complete. Which user that is depends on the cluster configuration: with an interserver `secret` configured, the shard runs the query as the initiator's current user; otherwise it is the user from the cluster definition or from the `remote` table function (`default` unless specified). The simplest robust approach is to enable the setting for all users of the upgraded servers.
</Info>
**Syntax**
```sql
arrayCount([func, ] arr1, ...)
```
**Arguments**
- `func` — Optional. Function to apply to each element of the array(s). [`Lambda function`](/reference/functions/regular-functions/overview#arrow-operator-and-lambda)
- `arr1, ..., arrN` — N arrays. [`Array(T)`](/reference/data-types/array)
**Returned value**
Returns the number of elements for which `func` returns true. Otherwise, returns the number of non-zero elements in the array. [`UInt64`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query
SELECT arrayCount(x -> (x % 2), groupArray(number)) FROM numbers(10)
```
```response title=Response
5
```
## arrayCumSum {#arrayCumSum}
Introduced in: v1.1.0
Returns an array of the partial (running) sums of the elements in the source array. If a lambda function is specified, the sum is computed from applying the lambda to the array elements at each position.
**Syntax**
```sql
arrayCumSum([func,] arr1[, arr2, ... , arrN])
```
**Arguments**
- `func` — Optional. A lambda function to apply to the array elements at each position. [`Lambda function`](/reference/functions/regular-functions/overview#arrow-operator-and-lambda)
- `arr1` — The source array of numeric values. [`Array(T)`](/reference/data-types/array)
- `[arr2, ..., arrN]` — Optional. Additional arrays of the same size, passed as arguments to the lambda function if specified. [`Array(T)`](/reference/data-types/array)
**Returned value**
Returns an array of the partial sums of the elements in the source array. The result type matches the input array's numeric type. [`Array(T)`](/reference/data-types/array)
**Examples**
**Basic usage**
```sql title=Query
SELECT arrayCumSum([1, 1, 1, 1]) AS res
```
```response title=Response
[1,2,3,4]
```
**With lambda**
```sql title=Query
SELECT arrayCumSum(x -> x * 2, [1, 2, 3]) AS res
```
```response title=Response
[2,6,12]
```
## arrayCumSumNonNegative {#arrayCumSumNonNegative}
Introduced in: v18.12.0
Returns an array of the partial (running) sums of the elements in the source array, replacing any negative running sum with zero. If a lambda function is specified, the sum is computed from applying the lambda to the array elements at each position.
**Syntax**
```sql
arrayCumSumNonNegative([func,] arr1[, arr2, ... , arrN])
```
**Arguments**
- `func` — Optional. A lambda function to apply to the array elements at each position. [`Lambda function`](/reference/functions/regular-functions/overview#arrow-operator-and-lambda)
- `arr1` — The source array of numeric values. [`Array(T)`](/reference/data-types/array)
- `[arr2, ..., arrN]` — Optional. Additional arrays of the same size, passed as arguments to the lambda function if specified. [`Array(T)`](/reference/data-types/array)
**Returned value**
Returns an array of the partial sums of the elements in the source array, with any negative running sum replaced by zero. The result type matches the input array's numeric type. [`Array(T)`](/reference/data-types/array)
**Examples**
**Basic usage**
```sql title=Query
SELECT arrayCumSumNonNegative([1, 1, -4, 1]) AS res
```
```response title=Response
[1,2,0,1]
```
**With lambda**
```sql title=Query
SELECT arrayCumSumNonNegative(x -> x * 2, [1, -2, 3]) AS res
```
```response title=Response
[2,0,6]
```
## arrayDifference {#arrayDifference}
Introduced in: v1.1.0
Calculates an array of differences between adjacent array elements.
The first element of the result array will be 0, the second `arr[1] - arr[0]`, the third `arr[2] - arr[1]`, etc.
The type of elements in the result array are determined by the type inference rules for subtraction (e.g. `UInt8` - `UInt8` = `Int16`).
**Syntax**
```sql
arrayDifference(arr)
```
**Arguments**
- `arr` — Array for which to calculate differences between adjacent elements. [`Array(T)`](/reference/data-types/array)
**Returned value**
Returns an array of differences between adjacent array elements [`UInt*`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query
SELECT arrayDifference([1, 2, 3, 4]);
```
```response title=Response
[0,1,1,1]
```
**Example of overflow due to result type Int64**
```sql title=Query
SELECT arrayDifference([0, 10000000000000000000]);
```
```response title=Response
┌─arrayDifference([0, 10000000000000000000])─┐
│ [0,-8446744073709551616] │
└────────────────────────────────────────────┘
```
## arrayDistinct {#arrayDistinct}
Introduced in: v1.1.0
Returns an array containing only the distinct elements of an array.
**Syntax**
```sql
arrayDistinct(arr)
```
**Arguments**
- `arr` — Array for which to extract distinct elements. [`Array(T)`](/reference/data-types/array)
**Returned value**
Returns an array containing the distinct elements [`Array(T)`](/reference/data-types/array)
**Examples**
**Usage example**
```sql title=Query
SELECT arrayDistinct([1, 2, 2, 3, 1]);
```
```response title=Response
[1,2,3]
```
## arrayDotProduct {#arrayDotProduct}
Introduced in: v23.5.0
Returns the dot product of two arrays.
<Note>
The sizes of the two vectors must be equal. Arrays and Tuples may also contain mixed element types.
</Note>
**Syntax**
```sql
arrayDotProduct(v1, v2)
```
**Arguments**
- `v1` — First vector. [`Array((U)Int* | Float* | BFloat16 | Decimal)`](/reference/data-types/array) or [`Tuple((U)Int* | Float* | Decimal)`](/reference/data-types/tuple)
- `v2` — Second vector. [`Array((U)Int* | Float* | BFloat16 | Decimal)`](/reference/data-types/array) or [`Tuple((U)Int* | Float* | Decimal)`](/reference/data-types/tuple)
**Returned value**
The dot product of the two vectors.
<Note>
The return type is determined by the type of the arguments. If Arrays or Tuples contain mixed element types then the result type is the supertype.
Two `BFloat16` arrays accumulate in and return `Float32` (the same rule as two `Float32` arrays).
</Note>
[`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float) or [`Decimal`](/reference/data-types/decimal)
**Examples**
**Array example**
```sql title=Query
SELECT arrayDotProduct([1, 2, 3], [4, 5, 6]) AS res, toTypeName(res);
```
```response title=Response
32 UInt16
```
**Tuple example**
```sql title=Query
SELECT dotProduct((1::UInt16, 2::UInt8, 3::Float32),(4::Int16, 5::Float32, 6::UInt8)) AS res, toTypeName(res);
```
```response title=Response
32 Float64
```
## arrayElement {#arrayElement}
Introduced in: v1.1.0
Gets the element of the provided array with index `n` where `n` can be any integer type.
If the index falls outside of the bounds of an array, it returns a default value (0 for numbers, an empty string for strings, etc.),
except for arguments of a non-constant array and a constant index 0. In this case there will be an error `Array indices are 1-based`.
When `n` is an array of integers, returns an array of the elements at the specified positions (a gather operation).
This is equivalent to `arrayMap(i -> arr[i], n)`, but has a separate, more efficient implementation.
Out-of-bounds positions produce the default value, the same as for a scalar index.
The index elements may be nullable. A `NULL` index produces `NULL` (and makes the result element type nullable) when the element type can be
wrapped in `Nullable`; for element types that cannot be inside `Nullable` (such as `Array`, `Map`), a `NULL` index produces the
default value instead. This is the same behavior as for a scalar `NULL` index.
<Note>
Arrays in ClickHouse are one-indexed.
</Note>
Negative indexes are supported. In this case, the corresponding element is selected, numbered from the end. For example, `arr[-1]` is the last item in the array.
Operator `[n]` provides the same functionality.
The first argument may also be:
- a [`Map`](/reference/data-types/map): `m['key']` returns the value stored for `key`, or the default value of the value type when the key is absent.
- a [`QBit`](/reference/data-types/qbit): `q[n]` reconstructs the n-th vector element at the full precision of the QBit element type, reading only the bit planes of the stride group that contains it.
- a [`JSON`](/reference/data-types/newjson) or a `Nullable(JSON)`: `json['key']` returns the value stored at the path `key`, which must be a constant string, exactly as the dot syntax `json.key` does. For a `Nullable(JSON)` the result follows the same nullability rules as `json.key`: a path that can represent `NULL` (`Dynamic`, or a typed path that can be wrapped into `Nullable`) gives `NULL` for the outer `NULL` rows, while a non-nullable typed path such as `Array` or `Map` keeps its default value there. The key may also be a constant `Nullable(String)`; a `NULL` key gives `NULL`. Nested access can be chained: `json['a']['b']`.
**Syntax**
```sql
arrayElement(arr, n)
```
**Arguments**
- `arr` — The value to read an element from. [`Array(T)`](/reference/data-types/array), [`Map(K, V)`](/reference/data-types/map), [`QBit`](/reference/data-types/qbit), [`JSON`](/reference/data-types/newjson) or `Nullable(JSON)`. - `n` — Position of the element to get, or an array of positions. The positions may be nullable. [`(U)Int*`](/reference/data-types/int-uint) or [`Array((U)Int*)`](/reference/data-types/array). For a `Map` argument, a key of type `K`; for a `JSON` argument, a constant [`String`](/reference/data-types/string) path.
**Returned value**
When `n` is a scalar, returns the element of type `T`. When `n` is an array, returns `Array(Nullable(T))` if the index elements are nullable and `T` can be wrapped in `Nullable`, otherwise `Array(T)`. For a `Map` returns the value of type `V`, for a `JSON` the value stored at the path. [`Any`](/reference/data-types/index) or [`Array(T)`](/reference/data-types/array) or [`Array(Nullable(T))`](/reference/data-types/array)
**Examples**
**Usage example**
```sql title=Query
SELECT arrayElement(arr, 2) FROM (SELECT [1, 2, 3] AS arr)
```
```response title=Response
2
```
**Negative indexing**
```sql title=Query
SELECT arrayElement(arr, -1) FROM (SELECT [1, 2, 3] AS arr)
```
```response title=Response
3
```
**Using [n] notation**
```sql title=Query
SELECT arr[2] FROM (SELECT [1, 2, 3] AS arr)
```
```response title=Response
2
```
**Index out of array bounds**
```sql title=Query
SELECT arrayElement(arr, 4) FROM (SELECT [1, 2, 3] AS arr)
```
```response title=Response
0
```
**Array of indices**
```sql title=Query
SELECT [10, 20, 30, 40][[2, 4, 1]]
```
```response title=Response
[20,40,10]
```
**Map key access**
```sql title=Query
SELECT map('a', 1, 'b', 2)['b']
```
```response title=Response
2
```
**JSON bracket access**
```sql title=Query
SELECT json['a'] FROM (SELECT '{"a" : 42}'::JSON AS json)
```
```response title=Response
42
```
## arrayElementOrNull {#arrayElementOrNull}
Introduced in: v1.1.0
Gets the element of the provided array with index `n` where `n` can be any integer type.
If the index falls outside of the bounds of an array, `NULL` is returned instead of a default value,
as long as the result type can be nullable. For element types that are not already nullable and cannot be
put inside `Nullable` (such as `Array`, `Map`), the default value of the element type is returned instead.
When `n` is an array of integers, returns an array of the elements at the specified positions.
This is equivalent to `arrayMap(i -> arrayElementOrNull(arr, i), n)`, but has a separate, more efficient implementation.
Out-of-bounds positions and `NULL` indexes produce `NULL` values in the result array, following the same rule as for a scalar index.
<Note>
Arrays in ClickHouse are one-indexed.
</Note>
Negative indexes are supported. In this case, it selects the corresponding element numbered from the end. For example, `arr[-1]` is the last item in the array.
The first argument may also be a [`Map`](/reference/data-types/map): an absent key gives `NULL` instead of the default value of the value type, as long as that type can be wrapped in `Nullable`.
[`JSON`](/reference/data-types/newjson) is not supported here, use `arrayElement` instead.
**Syntax**
```sql
arrayElementOrNull(arr, n)
```
**Arguments**
- `arr` — The value to read an element from. [`Array(T)`](/reference/data-types/array) or [`Map(K, V)`](/reference/data-types/map). - `n` — Position of the element to get, or an array of positions. The positions may be nullable. [`(U)Int*`](/reference/data-types/int-uint) or [`Array((U)Int*)`](/reference/data-types/array). For a `Map` argument, a key of type `K`.
**Returned value**
When `n` is a scalar, returns `Nullable(T)` if `T` can be wrapped in `Nullable`, otherwise `T`. When `n` is an array, returns `Array(Nullable(T))` if `T` can be wrapped in `Nullable`, otherwise `Array(T)`. For a `Map` the same rule applies to the value type `V`. [`Any`](/reference/data-types/index) or [`Nullable(T)`](/reference/data-types/nullable) or [`Array(T)`](/reference/data-types/array) or [`Array(Nullable(T))`](/reference/data-types/array)
**Examples**
**Usage example**
```sql title=Query
SELECT arrayElementOrNull(arr, 2) FROM (SELECT [1, 2, 3] AS arr)
```
```response title=Response
2
```
**Negative indexing**
```sql title=Query
SELECT arrayElementOrNull(arr, -1) FROM (SELECT [1, 2, 3] AS arr)
```
```response title=Response
3
```
**Index out of array bounds**
```sql title=Query
SELECT arrayElementOrNull(arr, 4) FROM (SELECT [1, 2, 3] AS arr)
```
```response title=Response
\N
```
**Array of indices**
```sql title=Query
SELECT arrayElementOrNull([10, 20, 30], [1, 5, 2])
```
```response title=Response
[10,NULL,20]
```
**Absent map key**
```sql title=Query
SELECT arrayElementOrNull(map('a', 1), 'z')
```
```response title=Response
\N
```
## arrayEnumerate {#arrayEnumerate}
Introduced in: v1.1.0
Returns the array `[1, 2, 3, ..., length (arr)]`
This function is normally used with the [`ARRAY JOIN`](/reference/statements/select/array-join) clause. It allows counting something just
once for each array after applying `ARRAY JOIN`.
This function can also be used in higher-order functions. For example, you can use it to get array indexes for elements that match a condition.
**Syntax**
```sql
arrayEnumerate(arr)
```
**Arguments**
- `arr` — The array to enumerate. [`Array`](/reference/data-types/array)
**Returned value**
Returns the array `[1, 2, 3, ..., length (arr)]`. [`Array(UInt32)`](/reference/data-types/array)
**Examples**
**Basic example with ARRAY JOIN**
```sql title=Query
CREATE TABLE test
(
`id` UInt8,
`tag` Array(String),
`version` Array(String)
)
ENGINE = MergeTree
ORDER BY id;
INSERT INTO test VALUES (1, ['release-stable', 'dev', 'security'], ['2.4.0', '2.6.0-alpha', '2.4.0-sec1']);
SELECT
id,
tag,
version,
seq
FROM test
ARRAY JOIN
tag,
version,
arrayEnumerate(tag) AS seq
```
```response title=Response
┌─id─┬─tag────────────┬─version─────┬─seq─┐
│ 1 │ release-stable │ 2.4.0 │ 1 │
│ 1 │ dev │ 2.6.0-alpha │ 2 │
│ 1 │ security │ 2.4.0-sec1 │ 3 │
└────┴────────────────┴─────────────┴─────┘
```
## arrayEnumerateDense {#arrayEnumerateDense}
Introduced in: v18.12.0
Returns an array of the same size as the source array, indicating where each element first appears in the source array.
**Syntax**
```sql
arrayEnumerateDense(arr)
```
**Arguments**
- `arr` — The array to enumerate. [`Array(T)`](/reference/data-types/array)
**Returned value**
Returns an array of the same size as `arr`, indicating where each element first appears in the source array [`Array(T)`](/reference/data-types/array)
**Examples**
**Usage example**
```sql title=Query
SELECT arrayEnumerateDense([10, 20, 10, 30])
```
```response title=Response
[1,2,1,3]
```
## arrayEnumerateDenseRanked {#arrayEnumerateDenseRanked}
Introduced in: v20.1.0
Returns an array the same size as the source array, indicating where each element first appears in the source array. It allows for enumeration of a multidimensional array with the ability to specify how deep to look inside the array.
**Syntax**
```sql
arrayEnumerateDenseRanked(clear_depth, arr, max_array_depth)
```
**Arguments**
- `clear_depth` — Enumerate elements at the specified level separately. Must be less than or equal to `max_arr_depth`. [`UInt*`](/reference/data-types/int-uint)
- `arr` — N-dimensional array to enumerate. [`Array(T)`](/reference/data-types/array)
- `max_array_depth` — The maximum effective depth. Must be less than or equal to the depth of `arr`. [`UInt*`](/reference/data-types/int-uint)
**Returned value**
Returns an array denoting where each element first appears in the source array [`Array`](/reference/data-types/array)
**Examples**
**Basic usage**
```sql title=Query
-- With clear_depth=1 and max_array_depth=1, the result is identical to what arrayEnumerateDense would give.
SELECT arrayEnumerateDenseRanked(1,[10, 20, 10, 30],1);
```
```response title=Response
[1,2,1,3]
```
**Usage with a multidimensional array**
```sql title=Query
-- In this example, arrayEnumerateDenseRanked is used to obtain an array indicating, for each element of the
-- multidimensional array, what its position is among elements of the same value.
-- For the first row of the passed array, [10, 10, 30, 20], the corresponding first row of the result is [1, 1, 2, 3],
-- indicating that 10 is the first number encountered in position 1 and 2, 30 the second number encountered in position 3
-- and 20 is the third number encountered in position 4.
-- For the second row, [40, 50, 10, 30], the corresponding second row of the result is [4,5,1,2], indicating that 40
-- and 50 are the fourth and fifth numbers encountered in position 1 and 2 of that row, that another 10
-- (the first encountered number) is in position 3 and 30 (the second number encountered) is in the last position.
SELECT arrayEnumerateDenseRanked(1,[[10,10,30,20],[40,50,10,30]],2);
```
```response title=Response
[[1,1,2,3],[4,5,1,2]]
```
**Example with increased clear_depth**
```sql title=Query
-- Changing clear_depth=2 results in the enumeration occurring separately for each row anew.
SELECT arrayEnumerateDenseRanked(2,[[10,10,30,20],[40,50,10,30]],2);
```
```response title=Response
[[1,1,2,3],[1,2,3,4]]
```
## arrayEnumerateUniq {#arrayEnumerateUniq}
Introduced in: v1.1.0
Returns an array the same size as the source array, indicating for each element what its position is among elements with the same value.
This function is useful when using `ARRAY JOIN` and aggregation of array elements.
The function can take multiple arrays of the same size as arguments. In this case, uniqueness is considered for tuples of elements in the same positions in all the arrays.
**Syntax**
```sql
arrayEnumerateUniq(arr1[, arr2, ... , arrN])
```
**Arguments**
- `arr1` — First array to process. [`Array(T)`](/reference/data-types/array)
- `arr2, ...` — Optional. Additional arrays of the same size for tuple uniqueness. [`Array(UInt32)`](/reference/data-types/array)
**Returned value**
Returns an array where each element is the position among elements with the same value or tuple. [`Array(T)`](/reference/data-types/array)
**Examples**
**Basic usage**
```sql title=Query
SELECT arrayEnumerateUniq([10, 20, 10, 30]);
```
```response title=Response
[1,1,2,1]
```
**Multiple arrays**
```sql title=Query
SELECT arrayEnumerateUniq([1, 1, 1, 2, 2, 2], [1, 1, 2, 1, 1, 2]);
```
```response title=Response
[1,2,1,1,2,1]
```
**ARRAY JOIN aggregation**
```sql title=Query
-- 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.
CREATE TABLE visits (CounterID UInt32, Sign Int8, Goals Nested(ID UInt32)) ENGINE = Memory;
INSERT INTO visits VALUES
(160656, 1, [53225, 53225, 56600]),
(160656, 1, [53225, 2825062]),
(160656, 1, [56600, 56600, 2825062]),
(160657, 1, [53225]);
SELECT
Goals.ID AS GoalID,
sum(Sign) AS Reaches,
sumIf(Sign, num = 1) AS Visits
FROM visits
ARRAY JOIN
Goals,
arrayEnumerateUniq(Goals.ID) AS num
WHERE CounterID = 160656
GROUP BY GoalID
ORDER BY Reaches DESC, GoalID
LIMIT 10
```
```response title=Response
┌──GoalID─┬─Reaches─┬─Visits─┐
│ 53225 │ 3 │ 2 │
│ 56600 │ 3 │ 2 │
│ 2825062 │ 2 │ 2 │
└─────────┴─────────┴────────┘
```
## arrayEnumerateUniqRanked {#arrayEnumerateUniqRanked}
Introduced in: v20.1.0
Returns an array (or multi-dimensional array) with the same dimensions as the source array,
indicating for each element what it's position is among elements with the same value.
It allows for enumeration of a multi-dimensional array with the ability to specify how deep to look inside the array.
**Syntax**
```sql
arrayEnumerateUniqRanked(clear_depth, arr, max_array_depth)
```
**Arguments**
- `clear_depth` — Enumerate elements at the specified level separately. Positive integer less than or equal to `max_arr_depth`. [`UInt*`](/reference/data-types/int-uint)
- `arr` — N-dimensional array to enumerate. [`Array(T)`](/reference/data-types/array)
- `max_array_depth` — The maximum effective depth. Positive integer less than or equal to the depth of `arr`. [`UInt*`](/reference/data-types/int-uint)
**Returned value**
Returns an N-dimensional array the same size as `arr` with each element showing the position of that element in relation to other elements of the same value. [`Array(T)`](/reference/data-types/array)
**Examples**
**Example 1**
```sql title=Query
-- With clear_depth=1 and max_array_depth=1, the result of arrayEnumerateUniqRanked
-- is identical to that which arrayEnumerateUniq would give for the same array.
SELECT arrayEnumerateUniqRanked(1, [1, 2, 1], 1);
```
```response title=Response
[1,1,2]
```
**Example 2**
```sql title=Query
-- with clear_depth=1 and max_array_depth=1, the result of arrayEnumerateUniqRanked
-- is identical to that which arrayEnumerateUniqwould give for the same array.
SELECT arrayEnumerateUniqRanked(1, [[1, 2, 3], [2, 2, 1], [3]], 2);
```
```response title=Response
[[1,1,1],[2,3,2],[2]]
```
**Example 3**
```sql title=Query
-- In this example, arrayEnumerateUniqRanked is used to obtain an array indicating,
-- for each element of the multidimensional array, what its position is among elements
-- of the same value. For the first row of the passed array, [1, 2, 3], the corresponding
-- result is [1, 1, 1], indicating that this is the first time 1, 2 and 3 are encountered.
-- For the second row of the provided array, [2, 2, 1], the corresponding result is [2, 3, 3],
-- indicating that 2 is encountered for a second and third time, and 1 is encountered
-- for the second time. Likewise, for the third row of the provided array [3] the
-- corresponding result is [2] indicating that 3 is encountered for the second time.
SELECT arrayEnumerateUniqRanked(1, [[1, 2, 3], [2, 2, 1], [3]], 2);
```
```response title=Response
[[1,1,1],[2,3,2],[2]]
```
**Example 4**
```sql title=Query
-- Changing clear_depth=2, results in elements being enumerated separately for each row.
SELECT arrayEnumerateUniqRanked(2,[[1, 2, 3],[2, 2, 1],[3]], 2);
```
```response title=Response
[[1,1,1],[1,2,1],[1]]
```
## arrayExcept {#arrayExcept}
Introduced in: v25.9.0
Returns an array containing elements from `source` that are not present in `except`, preserving the original order.
This function performs a set difference operation between two arrays. For each element in `source`, it checks if the element exists in `except` (using exact comparison). If not, the element is included in the result.
The operation maintains these properties:
1. Order of elements from `source` is preserved
2. Duplicates in `source` are preserved if they don't exist in `except`
3. NULL is handled as a separate value
**Syntax**
```sql
arrayExcept(source, except)
```
**Arguments**
- `source` — The source array containing elements to filter. [`Array(T)`](/reference/data-types/array)
- `except` — The array containing elements to exclude from the result. [`Array(T)`](/reference/data-types/array)
**Returned value**
Returns an array of the same type as the input array containing elements from `source` that weren't found in `except`. [`Array(T)`](/reference/data-types/array)
**Examples**
**basic**
```sql title=Query
SELECT arrayExcept([1, 2, 3, 2, 4], [3, 5])
```
```response title=Response
[1,2,2,4]
```
**with_nulls1**
```sql title=Query
SELECT arrayExcept([1, NULL, 2, NULL], [2])
```
```response title=Response
[1,NULL,NULL]
```
**with_nulls2**
```sql title=Query
SELECT arrayExcept([1, NULL, 2, NULL], [NULL, 2, NULL])
```
```response title=Response
[1]
```
**strings**
```sql title=Query
SELECT arrayExcept(['apple', 'banana', 'cherry'], ['banana', 'date'])
```
```response title=Response
['apple','cherry']
```
## arrayExists {#arrayExists}
Introduced in: v1.1.0
Returns `1` if there is at least one element in a source array for which `func(x[, y1, y2, ... yN])` returns true. Otherwise, it returns `0`.
**Syntax**
```sql
arrayExists(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`](/reference/functions/regular-functions/overview#arrow-operator-and-lambda)
- `source_arr` — The source array to process. [`Array(T)`](/reference/data-types/array)
- `[, cond1_arr, ... , condN_arr]` — Optional. N condition arrays providing additional arguments to the lambda function. [`Array(T)`](/reference/data-types/array)
**Returned value**
Returns `1` if the lambda function returns true for at least one element, `0` otherwise [`UInt8`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query
SELECT arrayExists(x, y -> x=y, [1, 2, 3], [0, 0, 0])
```
```response title=Response
0
```
## arrayFill {#arrayFill}
Introduced in: v20.1.0
The `arrayFill` function sequentially processes a source array from the first element
to the last, evaluating a lambda condition at each position using elements from
the source and condition arrays. When the lambda function 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 first element is always preserved
regardless of any condition.
**Syntax**
```sql
arrayFill(func(x [, y1, ..., yN]), source_arr[, cond1_arr, ... , condN_arr])
```
**Arguments**
- `func(x [, y1, ..., yN])` — A lambda function `func(x [, y1, y2, ... yN]) → F(x [, y1, y2, ... yN])` which operates on elements of the source array (`x`) and condition arrays (`y`). [`Lambda function`](/reference/functions/regular-functions/overview#arrow-operator-and-lambda)
- `source_arr` — The source array to process. [`Lambda function`](/reference/functions/regular-functions/overview#arrow-operator-and-lambda)
- `[, cond1_arr, ... , condN_arr]` — Optional. N condition arrays providing additional arguments to the lambda function. [`Array(T)`](/reference/data-types/array)
**Returned value**
Returns an array [`Array(T)`](/reference/data-types/array)
**Examples**
**Example with single array**
```sql title=Query
SELECT arrayFill(x -> not isNull(x), [1, null, 2, null]) AS res
```
```response title=Response
[1,1,2,2]
```
**Example with two arrays**
```sql title=Query
SELECT arrayFill(x, y, z -> x > y AND x < z, [5, 3, 6, 2], [4, 7, 1, 3], [10, 2, 8, 5]) AS res
```
```response title=Response
[5,5,6,6]
```
## arrayFilter {#arrayFilter}
Introduced in: v1.1.0
Returns an array containing only the elements in the source array for which a lambda function returns true.
**Syntax**
```sql
arrayFilter(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`](/reference/functions/regular-functions/overview#arrow-operator-and-lambda)
- `source_arr` — The source array to process. [`Array(T)`](/reference/data-types/array)
- `[, cond1_arr, ... , condN_arr]` — Optional. N condition arrays providing additional arguments to the lambda function. [`Array(T)`](/reference/data-types/array)
**Returned value**
Returns a subset of the source array [`Array(T)`](/reference/data-types/array)
**Examples**
**Example 1**
```sql title=Query
SELECT arrayFilter(x -> x LIKE '%World%', ['Hello', 'abc World']) AS res
```
```response title=Response
['abc World']
```
**Example 2**
```sql title=Query
SELECT
arrayFilter(
(i, x) -> x LIKE '%World%',
arrayEnumerate(arr),
['Hello', 'abc World'] AS arr)
AS res
```
```response title=Response
[2]
```
## arrayFirst {#arrayFirst}
Introduced in: v1.1.0
Returns the first element in the source array for which `func(x[, y1, y2, ... yN])` returns true, otherwise it returns a default value.
**Syntax**
```sql
arrayFirst(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](/reference/functions/regular-functions/overview#arrow-operator-and-lambda). - `source_arr` — The source array to process. [`Array(T)`](/reference/data-types/array). - `[, cond1_arr, ... , condN_arr]` — Optional. N condition arrays providing additional arguments to the lambda function. [`Array(T)`](/reference/data-types/array).
**Returned value**
Returns the first element of the source array for which `λ` is true, otherwise returns the default value of `T`.
**Examples**
**Usage example**
```sql title=Query
SELECT arrayFirst(x, y -> x=y, ['a', 'b', 'c'], ['c', 'b', 'a'])
```
```response title=Response
b
```
**No match**
```sql title=Query
SELECT arrayFirst(x, y -> x=y, [0, 1, 2], [3, 3, 3]) AS res, toTypeName(res)
```
```response title=Response
0 UInt8
```
## arrayFirstIndex {#arrayFirstIndex}
Introduced in: v1.1.0
Returns the index of the first element in the source array for which `func(x[, y1, y2, ... yN])` returns true, otherwise it returns '0'.
**Syntax**
```sql
arrayFirstIndex(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](/reference/functions/regular-functions/overview#arrow-operator-and-lambda). - `source_arr` — The source array to process. [`Array(T)`](/reference/data-types/array). - `[, cond1_arr, ... , condN_arr]` — Optional. N condition arrays providing additional arguments to the lambda function. [`Array(T)`](/reference/data-types/array).
**Returned value**
Returns the index of the first element of the source array for which `func` is true, otherwise returns `0` [`UInt32`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query
SELECT arrayFirstIndex(x, y -> x=y, ['a', 'b', 'c'], ['c', 'b', 'a'])
```
```response title=Response
2
```
**No match**
```sql title=Query
SELECT arrayFirstIndex(x, y -> x=y, ['a', 'b', 'c'], ['d', 'e', 'f'])
```
```response title=Response
0
```
## arrayFirstOrNull {#arrayFirstOrNull}
Introduced in: v1.1.0
Returns the first element in the source array for which `func(x[, y1, y2, ... yN])` returns true, otherwise it returns `NULL`.
**Syntax**
```sql
arrayFirstOrNull(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`](/reference/functions/regular-functions/overview#arrow-operator-and-lambda)
- `source_arr` — The source array to process. [`Array(T)`](/reference/data-types/array)
- `[, cond1_arr, ... , condN_arr]` — Optional. N condition arrays providing additional arguments to the lambda function. [`Array(T)`](/reference/data-types/array)
**Returned value**
Returns the first element of the source array for which `func` is true, otherwise returns `NULL`.
**Examples**
**Usage example**
```sql title=Query
SELECT arrayFirstOrNull(x, y -> x=y, ['a', 'b', 'c'], ['c', 'b', 'a'])
```
```response title=Response
b
```
**No match**
```sql title=Query
SELECT arrayFirstOrNull(x, y -> x=y, [0, 1, 2], [3, 3, 3]) AS res, toTypeName(res)
```
```response title=Response
\N Nullable(UInt8)
```
## arrayFlatten {#arrayFlatten}
Introduced in: v20.1.0
Converts an array of arrays to a flat array.
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**
```sql
arrayFlatten(arr)
```
**Aliases**: `flatten`
**Arguments**
- `arr` — A multidimensional array. [`Array(Array(T))`](/reference/data-types/array)
**Returned value**
Returns a flattened array from the multidimensional array [`Array(T)`](/reference/data-types/array)
**Examples**
**Usage example**
```sql title=Query
SELECT arrayFlatten([[[1]], [[2], [3]]]);
```
```response title=Response
[1,2,3]
```
## arrayFlattenedLength {#arrayFlattenedLength}
Introduced in: v26.9.0
Returns the total number of elements of a multidimensional array, as if it was flattened with the [`arrayFlatten`](/reference/functions/regular-functions/array-functions#arrayFlatten) function.
Function:
- Applies to any depth of nested arrays.
- Is equivalent to [`length`](/reference/functions/regular-functions/array-functions#length) for arrays that are already flat.
- Follows only `Array` nesting. The elements are counted at the first level that is not an array, so elements of a nested [`Map`](/reference/data-types/map), [`Tuple`](/reference/data-types/tuple), [`Dynamic`](/reference/data-types/dynamic) or [`Variant`](/reference/data-types/variant) are not counted individually, even when they hold arrays themselves.
This is the equivalent of `cardinality` in PostgreSQL, whereas in ClickHouse `cardinality` is an alias of `length` and counts only the elements of the outermost array.
**Syntax**
```sql
arrayFlattenedLength(arr)
```
**Arguments**
- `arr` — A possibly multidimensional array. [`Array(T)`](/reference/data-types/array)
**Returned value**
Returns the number of elements of the array after all nested arrays are flattened. [`UInt64`](/reference/data-types/int-uint)
**Examples**
**Nested array**
```sql title=Query
SELECT arrayFlattenedLength([[1, 2], [3, 4]]);
```
```response title=Response
4
```
**Deeper nesting**
```sql title=Query
SELECT arrayFlattenedLength([[[1]], [[2], [3]]]);
```
```response title=Response
3
```
**Flat array**
```sql title=Query
SELECT arrayFlattenedLength([1, 2, 3]);
```
```response title=Response
3
```
**Non-array elements are not traversed**
```sql title=Query
SELECT arrayFlattenedLength([map('a', [1, 2, 3])]);
```
```response title=Response
1
```
## arrayFold {#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**
```sql
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`](/reference/functions/regular-functions/overview#arrow-operator-and-lambda)
- `arr1 [, arr2, arr3, ... arrN]` — N arrays over which to operate. [`Array(T)`](/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**
```sql title=Query
SELECT arrayFold(acc,x -> acc + x*2, [1, 2, 3, 4], 3::Int64) AS res;
```
```response title=Response
23
```
**Fibonacci sequence**
```sql title=Query
SELECT arrayFold(acc, x -> (acc.2, acc.2 + acc.1),range(number),(1::Int64, 0::Int64)).1 AS fibonacci FROM numbers(1,10);
```
```response title=Response
┌─fibonacci─┐
│ 0 │
│ 1 │
│ 1 │
│ 2 │
│ 3 │
│ 5 │
│ 8 │
│ 13 │
│ 21 │
│ 34 │
└───────────┘
```
**Example using multiple arrays**
```sql title=Query
SELECT arrayFold(
(acc, x, y) -> acc + (x * y),
[1, 2, 3, 4],
[10, 20, 30, 40],
0::Int64
) AS res;
```
```response title=Response
300
```
## arrayIntersect {#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**
```sql
arrayIntersect(arr, arr1, ..., arrN)
```
**Arguments**
- `arrN` — N arrays from which to make the new array. [`Array(T)`](/reference/data-types/array).
**Returned value**
Returns an array with distinct elements that are present in all N arrays [`Array(T)`](/reference/data-types/array)
**Examples**
**Usage example**
```sql title=Query
SELECT
arrayIntersect([1, 2], [1, 3], [2, 3]) AS empty_intersection,
arrayIntersect([1, 2], [1, 3], [1, 4]) AS non_empty_intersection
```
```response title=Response
┌─empty_intersection─┬─non_empty_intersection─┐
│ [] │ [1] │
└────────────────────┴────────────────────────┘
```
## arrayJaccardIndex {#arrayJaccardIndex}
Introduced in: v23.7.0
Returns the [Jaccard index](https://en.wikipedia.org/wiki/Jaccard_index) of two arrays.
**Syntax**
```sql
arrayJaccardIndex(arr_x, arr_y)
```
**Arguments**
- `arr_x` — First array. [`Array(T)`](/reference/data-types/array)
- `arr_y` — Second array. [`Array(T)`](/reference/data-types/array)
**Returned value**
Returns the Jaccard index of `arr_x` and `arr_y` [`Float64`](/reference/data-types/float)
**Examples**
**Usage example**
```sql title=Query
SELECT arrayJaccardIndex([1, 2], [2, 3]) AS res
```
```response title=Response
0.3333333333333333
```
## arrayJoin {#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.
`unnest` (since version 26.5) is a case-insensitive alias of `arrayJoin` in function-call form (`SELECT unnest(arr)`). PostgreSQL table-source syntax is not supported (`FROM unnest(...)`, `CROSS JOIN UNNEST(...)` and `LATERAL unnest(...)`). Use the `ARRAY JOIN` clause for those queries. `JOIN LATERAL` over a correlated subquery is a separate experimental feature and does not make `unnest` usable as a table source.
`arrayJoin`, including via its `unnest` alias, cannot be used in a `JOIN ON` condition that is evaluated during the join, because such a condition must
preserve the number of rows. A condition that applies to one side only, and an equality key over `arrayJoin`, are
extracted before the join and are unaffected. A non-disjunctive `ALL INNER JOIN` condition is also unaffected,
because there the condition is applied after the join instead.
Where the expansion depends on one side only, move it into an `ARRAY JOIN` in a subquery before the join; a condition whose `arrayJoin` argument reads columns from both sides has to be restructured.
<Note>
This function is non-deterministic: it can return different results for the same arguments.
</Note>
**Syntax**
```sql
arrayJoin(arr)
```
**Aliases**: `unnest`
**Arguments**
- `arr` — An array to unfold. [`Array(T)`](/reference/data-types/array)
**Returned value**
Returns a set of rows unfolded from `arr`.
**Examples**
**Basic usage**
```sql title=Query
SELECT arrayJoin([1, 2, 3] AS src) AS dst, 'Hello', src
```
```response title=Response
┌─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**
```sql title=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'];
```
```response title=Response
┌─impressions─┐
│ 2 │
└─────────────┘
```
**Using multiple arrayJoin functions**
```sql title=Query
-- 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
ORDER BY
city,
browser
```
```response title=Response
┌─impressions─┬─city─────┬─browser─┐
│ 2 │ Berlin │ Chrome │
│ 1 │ Berlin │ Firefox │
│ 2 │ Bobruisk │ Chrome │
│ 1 │ Bobruisk │ Firefox │
│ 2 │ Istanbul │ Chrome │
│ 1 │ Istanbul │ Firefox │
└─────────────┴──────────┴─────────┘
```
**Unexpected results due to optimizations**
```sql title=Query
-- 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
);
```
```response title=Response
┌─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**
```sql title=Query
-- 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.
SELECT
sum(1) AS impressions,
city,
browser
FROM
(
SELECT
['Istanbul', 'Berlin', 'Bobruisk'] AS cities,
['Firefox', 'Chrome', 'Chrome'] AS browsers
)
ARRAY JOIN
cities AS city,
browsers AS browser
GROUP BY
2,
3
ORDER BY
2,
3
```
```response title=Response
┌─impressions─┬─city─────┬─browser─┐
│ 1 │ Berlin │ Chrome │
│ 1 │ Bobruisk │ Chrome │
│ 1 │ Istanbul │ Firefox │
└─────────────┴──────────┴─────────┘
```
**Using Tuple**
```sql title=Query
-- You can also use Tuple
SELECT
sum(1) AS impressions,
(arrayJoin(arrayZip(cities, browsers)) AS t).1 AS city,
t.2 AS browser
FROM
(
SELECT
['Istanbul', 'Berlin', 'Bobruisk'] AS cities,
['Firefox', 'Chrome', 'Chrome'] AS browsers
)
GROUP BY
2,
3
ORDER BY
2,
3
```
```response title=Response
┌─impressions─┬─city─────┬─browser─┐
│ 1 │ Berlin │ Chrome │
│ 1 │ Bobruisk │ Chrome │
│ 1 │ Istanbul │ Firefox │
└─────────────┴──────────┴─────────┘
```
## arrayLast {#arrayLast}
Introduced in: v1.1.0
Returns the last element in the source array for which a lambda `func(x [, y1, y2, ... yN])` returns true, otherwise it returns a default value.
**Syntax**
```sql
arrayLast(func(x[, y1, ..., yN]), source[, cond1, ... , 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](/reference/functions/regular-functions/overview#arrow-operator-and-lambda). - `source` — The source array to process. [`Array(T)`](/reference/data-types/array). - `[, cond1, ... , condN]` — Optional. N condition arrays providing additional arguments to the lambda function. [`Array(T)`](/reference/data-types/array).
**Returned value**
Returns the last element of the source array for which `func` is true, otherwise returns the default value of `T`.
**Examples**
**Usage example**
```sql title=Query
SELECT arrayLast(x, y -> x=y, ['a', 'b', 'c'], ['a', 'b', 'c'])
```
```response title=Response
c
```
**No match**
```sql title=Query
SELECT arrayFirst(x, y -> x=y, [0, 1, 2], [3, 3, 3]) AS res, toTypeName(res)
```
```response title=Response
0 UInt8
```
## arrayLastIndex {#arrayLastIndex}
Introduced in: v1.1.0
Returns the index of the last element in the source array for which `func(x[, y1, y2, ... yN])` returns true, otherwise it returns '0'.
**Syntax**
```sql
arrayLastIndex(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`](/reference/functions/regular-functions/overview#arrow-operator-and-lambda)
- `source_arr` — The source array to process. [`Array(T)`](/reference/data-types/array)
- `[, cond1_arr, ... , condN_arr]` — Optional. N condition arrays providing additional arguments to the lambda function. [`Array(T)`](/reference/data-types/array)
**Returned value**
Returns the index of the last element of the source array for which `func` is true, otherwise returns `0` [`UInt32`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query
SELECT arrayLastIndex(x, y -> x=y, ['a', 'b', 'c'], ['a', 'b', 'c']);
```
```response title=Response
3
```
**No match**
```sql title=Query
SELECT arrayLastIndex(x, y -> x=y, ['a', 'b', 'c'], ['d', 'e', 'f']);
```
```response title=Response
0
```
## arrayLastOrNull {#arrayLastOrNull}
Introduced in: v1.1.0
Returns the last element in the source array for which a lambda `func(x [, y1, y2, ... yN])` returns true, otherwise it returns `NULL`.
**Syntax**
```sql
arrayLastOrNull(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](/reference/functions/regular-functions/overview#arrow-operator-and-lambda). - `source_arr` — The source array to process. [`Array(T)`](/reference/data-types/array). - `[, cond1_arr, ... , condN_arr]` — Optional. N condition arrays providing additional arguments to the lambda function. [`Array(T)`](/reference/data-types/array).
**Returned value**
Returns the last element of the source array for which `λ` is not true, otherwise returns `NULL`.
**Examples**
**Usage example**
```sql title=Query
SELECT arrayLastOrNull(x, y -> x=y, ['a', 'b', 'c'], ['a', 'b', 'c'])
```
```response title=Response
c
```
**No match**
```sql title=Query
SELECT arrayLastOrNull(x, y -> x=y, [0, 1, 2], [3, 3, 3]) AS res, toTypeName(res)
```
```response title=Response
\N Nullable(UInt8)
```
## arrayLevenshteinDistance {#arrayLevenshteinDistance}
Introduced in: v25.4.0
Calculates the Levenshtein distance for two arrays.
**Syntax**
```sql
arrayLevenshteinDistance(from, to)
```
**Arguments**
- `from` — The first array. [`Array(T)`](/reference/data-types/array). - `to` — The second array. [`Array(T)`](/reference/data-types/array).
**Returned value**
Levenshtein distance between the first and the second arrays. [`Float64`](/reference/data-types/float)
**Examples**
**Usage example**
```sql title=Query
SELECT arrayLevenshteinDistance([1, 2, 4], [1, 2, 3])
```
```response title=Response
1
```
## arrayLevenshteinDistanceWeighted {#arrayLevenshteinDistanceWeighted}
Introduced in: v25.4.0
Calculates Levenshtein distance for two arrays with custom weights for each element.
The number of elements for the array and its weights should match.
**Syntax**
```sql
arrayLevenshteinDistanceWeighted(from, to, from_weights, to_weights)
```
**Arguments**
- `from` — first array. [`Array(T)`](/reference/data-types/array). - `to` — second array. [`Array(T)`](/reference/data-types/array). - `from_weights` — weights for the first array. [`Array((U)Int*|Float*)`](/reference/data-types/array)
- `to_weights` — weights for the second array. [`Array((U)Int*|Float*)`](/reference/data-types/array)
**Returned value**
Levenshtein distance between the first and the second arrays with custom weights for each element [`Float64`](/reference/data-types/float)
**Examples**
**Usage example**
```sql title=Query
SELECT arrayLevenshteinDistanceWeighted(['A', 'B', 'C'], ['A', 'K', 'L'], [1.0, 2, 3], [3.0, 4, 5])
```
```response title=Response
14
```
## arrayMap {#arrayMap}
Introduced in: v1.1.0
Returns an array obtained from the original arrays by applying a lambda function to each element.
**Syntax**
```sql
arrayMap(func, arr)
```
**Arguments**
- `func` — A lambda function which operates on elements of the source array (`x`) and condition arrays (`y`). [`Lambda function`](/reference/functions/regular-functions/overview#arrow-operator-and-lambda)
- `arr` — N arrays to process. [`Array(T)`](/reference/data-types/array)
**Returned value**
Returns an array from the lambda results [`Array(T)`](/reference/data-types/array)
**Examples**
**Usage example**
```sql title=Query
SELECT arrayMap(x -> (x + 2), [1, 2, 3]) as res;
```
```response title=Response
[3,4,5]
```
**Creating a tuple of elements from different arrays**
```sql title=Query
SELECT arrayMap((x, y) -> (x, y), [1, 2, 3], [4, 5, 6]) AS res
```
```response title=Response
[(1,4),(2,5),(3,6)]
```
## arrayMax {#arrayMax}
Introduced in: v21.1.0
Returns the maximum element in the source array.
If a lambda function `func` is specified, returns the maximum element of the lambda results.
**Syntax**
```sql
arrayMax([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`](/reference/functions/regular-functions/overview#arrow-operator-and-lambda)
- `source_arr` — The source array to process. [`Array(T)`](/reference/data-types/array)
- `[, cond1_arr, ... , condN_arr]` — Optional. N condition arrays providing additional arguments to the lambda function. [`Array(T)`](/reference/data-types/array)
**Returned value**
Returns the maximum element in the source array, or the maximum element of the lambda results if provided.
**Examples**
**Basic example**
```sql title=Query
SELECT arrayMax([5, 3, 2, 7]);
```
```response title=Response
7
```
**Usage with lambda function**
```sql title=Query
SELECT arrayMax(x, y -> x/y, [4, 8, 12, 16], [1, 2, 1, 2]);
```
```response title=Response
12
```
## arrayMaxIndex {#arrayMaxIndex}
Introduced in: v26.10.0
Returns the 1-based index of the maximum element in the source array, or `0` if the array is empty.
If a lambda function `func` is specified, returns the index of the element corresponding to the maximum lambda result. If multiple elements have the same maximum value, returns the index of the first one.
**Syntax**
```sql
arrayMaxIndex([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`](/reference/functions/regular-functions/overview#arrow-operator-and-lambda)
- `source_arr` — The source array to process. [`Array(T)`](/reference/data-types/array)
- `cond1_arr, ...` — Optional. N condition arrays providing additional arguments to the lambda function. [`Array(T)`](/reference/data-types/array)
**Returned value**
Returns the 1-based index of the first maximum element, or `0` for an empty array. [`UInt32`](/reference/data-types/int-uint)
**Examples**
**Basic example**
```sql title=Query
SELECT arrayMaxIndex([5, 3, 2, 7]);
```
```response title=Response
4
```
**First equal maximum**
```sql title=Query
SELECT arrayMaxIndex([5, 7, 7, 3]);
```
```response title=Response
2
```
**Usage with lambda function**
```sql title=Query
SELECT arrayMaxIndex(x, y -> x/y, [4, 8, 12, 16], [1, 2, 1, 2]);
```
```response title=Response
3
```
## arrayMin {#arrayMin}
Introduced in: v21.1.0
Returns the minimum element in the source array.
If a lambda function `func` is specified, returns the minimum element of the lambda results.
**Syntax**
```sql
arrayMin([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`](/reference/functions/regular-functions/overview#arrow-operator-and-lambda)
- `source_arr` — The source array to process. [`Array(T)`](/reference/data-types/array)
- `cond1_arr, ...` — Optional. N condition arrays providing additional arguments to the lambda function. [`Array(T)`](/reference/data-types/array)
**Returned value**
Returns the minimum element in the source array, or the minimum element of the lambda results if provided.
**Examples**
**Basic example**
```sql title=Query
SELECT arrayMin([5, 3, 2, 7]);
```
```response title=Response
2
```
**Usage with lambda function**
```sql title=Query
SELECT arrayMin(x, y -> x/y, [4, 8, 12, 16], [1, 2, 1, 2]);
```
```response title=Response
4
```
## arrayMinIndex {#arrayMinIndex}
Introduced in: v26.10.0
Returns the 1-based index of the minimum element in the source array, or `0` if the array is empty.
If a lambda function `func` is specified, returns the index of the element corresponding to the minimum lambda result. If multiple elements have the same minimum value, returns the index of the first one.
**Syntax**
```sql
arrayMinIndex([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`](/reference/functions/regular-functions/overview#arrow-operator-and-lambda)
- `source_arr` — The source array to process. [`Array(T)`](/reference/data-types/array)
- `cond1_arr, ...` — Optional. N condition arrays providing additional arguments to the lambda function. [`Array(T)`](/reference/data-types/array)
**Returned value**
Returns the 1-based index of the first minimum element, or `0` for an empty array. [`UInt32`](/reference/data-types/int-uint)
**Examples**
**Basic example**
```sql title=Query
SELECT arrayMinIndex([5, 3, 2, 7]);
```
```response title=Response
3
```
**First equal minimum**
```sql title=Query
SELECT arrayMinIndex([5, 3, 3, 7]);
```
```response title=Response
2
```
**Usage with lambda function**
```sql title=Query
SELECT arrayMinIndex(x, y -> x/y, [4, 8, 12, 16], [1, 2, 1, 2]);
```
```response title=Response
1
```
## arrayNormalizedGini {#arrayNormalizedGini}
Introduced in: v25.1.0
Calculates the normalized Gini coefficient.
**Syntax**
```sql
arrayNormalizedGini(predicted, label)
```
**Arguments**
- `predicted` — The predicted value. [`Array(T)`](/reference/data-types/array)
- `label` — The actual value. [`Array(T)`](/reference/data-types/array)
**Returned value**
A tuple containing the Gini coefficients of the predicted values, the Gini coefficient of the normalized values, and the normalized Gini coefficient (= the ratio of the former two Gini coefficients) [`Tuple(Float64, Float64, Float64)`](/reference/data-types/tuple)
**Examples**
**Usage example**
```sql title=Query
SELECT arrayNormalizedGini([0.9, 0.3, 0.8, 0.7],[6, 1, 0, 2]);
```
```response title=Response
(0.18055555555555558,0.2638888888888889,0.6842105263157896)
```
## arrayPackBitGroupsToFixedString {#arrayPackBitGroupsToFixedString}
Introduced in: v26.10.0
The same as `arrayPackBitsToFixedString`, but the lambda returns an integer whose low `g` bits form a group, and the groups
are packed contiguously (most-significant-bit first) into a `FixedString(n)`. The result is the first `n` bytes of
`arrayPackBitGroupsToString` (a group that straddles the boundary contributes only its leading bits), zero-padded to `n`
bytes. Both the size `n` and the group size `g` must be positive constants, and `g` must not exceed 64.
**Syntax**
```sql
arrayPackBitGroupsToFixedString(f, n, g, arr1[, arr2, ...])
```
**Arguments**
- `f` — Lambda function producing the group value for each element. [`Lambda function`](/reference/functions/regular-functions/overview#arrow-operator-and-lambda)
- `n` — The size of the resulting `FixedString` in bytes. [`(U)Int8/16/32/64`](/reference/data-types/int-uint)
- `g` — The number of bits per group. [`(U)Int8/16/32/64`](/reference/data-types/int-uint)
- `arr1[, arr2, ...]` — One or more arrays of equal size to pack. The lambda takes one argument per array. [`Array(T)`](/reference/data-types/array)
**Returned value**
Returns the packed bits. [`FixedString(n)`](/reference/data-types/fixedstring)
**Examples**
**Example**
```sql title=Query
SELECT arrayPackBitGroupsToFixedString(x -> x, 2, 4, [3, 0, 3, 1])
```
```response title=Response
01
```
## arrayPackBitGroupsToString {#arrayPackBitGroupsToString}
Introduced in: v26.10.0
The same as `arrayPackBitsToString`, but the lambda returns an integer whose low `g` bits form a group, and the groups
are packed contiguously (most-significant-bit first). The length of the result is `ceil(size_of_array * g / 8)` bytes.
The group size `g` must be a constant between 1 and 64.
**Syntax**
```sql
arrayPackBitGroupsToString(f, g, arr1[, arr2, ...])
```
**Arguments**
- `f` — Lambda function producing the group value for each element. [`Lambda function`](/reference/functions/regular-functions/overview#arrow-operator-and-lambda)
- `g` — The number of bits per group. [`(U)Int8/16/32/64`](/reference/data-types/int-uint)
- `arr1[, arr2, ...]` — One or more arrays of equal size to pack. The lambda takes one argument per array. [`Array(T)`](/reference/data-types/array)
**Returned value**
Returns the packed bits. [`String`](/reference/data-types/string)
**Examples**
**Example**
```sql title=Query
SELECT arrayPackBitGroupsToString(x -> x, 4, [3, 0, 3, 1])
```
```response title=Response
01
```
## arrayPackBitGroupsToUInt64 {#arrayPackBitGroupsToUInt64}
Introduced in: v26.10.0
The same as `arrayPackBitsToUInt64`, but the lambda returns an integer whose low `g` bits form a group, and the groups
are packed contiguously (most-significant-bit first within each byte, bytes interpreted in little-endian order, so
`arrayPackBitGroupsToUInt64(f, g, arr) = reinterpretAsUInt64(arrayPackBitGroupsToString(f, g, arr))`). The result
holds the first 64 bits of the packed stream: if `g` does not divide 64, the group that straddles the boundary
contributes only its leading bits. The group size `g` must be a constant between 1 and 64.
**Syntax**
```sql
arrayPackBitGroupsToUInt64(f, g, arr1[, arr2, ...])
```
**Arguments**
- `f` — Lambda function producing the group value for each element. [`Lambda function`](/reference/functions/regular-functions/overview#arrow-operator-and-lambda)
- `g` — The number of bits per group. [`(U)Int8/16/32/64`](/reference/data-types/int-uint)
- `arr1[, arr2, ...]` — One or more arrays of equal size to pack. The lambda takes one argument per array. [`Array(T)`](/reference/data-types/array)
**Returned value**
Returns the packed bits. [`UInt64`](/reference/data-types/int-uint)
**Examples**
**Example**
```sql title=Query
SELECT arrayPackBitGroupsToUInt64(x -> x, 4, [1, 2, 3])
```
```response title=Response
12306
```
## arrayPackBitsToFixedString {#arrayPackBitsToFixedString}
Introduced in: v26.10.0
Applies a lambda function to each element of the array and packs the resulting bits into a `FixedString(n)`.
For each element the lambda returns an integer that is treated as a single bit (zero or non-zero).
Bits are written most-significant-bit first, so the result is the first `n` bytes of `arrayPackBitsToString`.
If the array has more than `n * 8` elements the rest are ignored; if it has fewer, the result is zero-padded to `n` bytes.
The size `n` must be a positive constant.
**Syntax**
```sql
arrayPackBitsToFixedString(f, n, arr1[, arr2, ...])
```
**Arguments**
- `f` — Lambda function producing the bit for each element. [`Lambda function`](/reference/functions/regular-functions/overview#arrow-operator-and-lambda)
- `n` — The size of the resulting `FixedString` in bytes. [`(U)Int8/16/32/64`](/reference/data-types/int-uint)
- `arr1[, arr2, ...]` — One or more arrays of equal size to pack. The lambda takes one argument per array. [`Array(T)`](/reference/data-types/array)
**Returned value**
Returns the packed bits. [`FixedString(n)`](/reference/data-types/fixedstring)
**Examples**
**Example**
```sql title=Query
SELECT arrayPackBitsToFixedString(x -> x, 2, [0, 0, 1, 1, 0, 0, 0, 0, 0, 0, 1, 1, 0, 0, 0, 1])
```
```response title=Response
01
```
## arrayPackBitsToString {#arrayPackBitsToString}
Introduced in: v26.10.0
Applies a lambda function to each element of the array and packs the resulting bits into a `String`.
For each element the lambda returns an integer that is treated as a single bit (zero or non-zero).
Bits are written most-significant-bit first; the length of the result is `ceil(size_of_array / 8)` bytes.
**Syntax**
```sql
arrayPackBitsToString(f, arr1[, arr2, ...])
```
**Arguments**
- `f` — Lambda function producing the bit for each element. [`Lambda function`](/reference/functions/regular-functions/overview#arrow-operator-and-lambda)
- `arr1[, arr2, ...]` — One or more arrays of equal size to pack. The lambda takes one argument per array. [`Array(T)`](/reference/data-types/array)
**Returned value**
Returns the packed bits. [`String`](/reference/data-types/string)
**Examples**
**Example**
```sql title=Query
SELECT arrayPackBitsToString(x -> x, [0, 0, 1, 1, 0, 0, 0, 0])
```
```response title=Response
0
```
## arrayPackBitsToUInt64 {#arrayPackBitsToUInt64}
Introduced in: v26.10.0
Applies a lambda function to each element of the array and packs the resulting bits into a `UInt64`.
For each element the lambda returns an integer that is treated as a single bit (zero or non-zero).
The bit stream is laid out across bytes most-significant-bit first (the same bytes as `arrayPackBitsToString`),
and the bytes are interpreted in little-endian order, so
`arrayPackBitsToUInt64(f, arr) = reinterpretAsUInt64(arrayPackBitsToString(f, arr))`.
Only the first 64 elements are used; if there are fewer, the remaining bits are zero.
**Syntax**
```sql
arrayPackBitsToUInt64(f, arr1[, arr2, ...])
```
**Arguments**
- `f` — Lambda function producing the bit for each element. [`Lambda function`](/reference/functions/regular-functions/overview#arrow-operator-and-lambda)
- `arr1[, arr2, ...]` — One or more arrays of equal size to pack. The lambda takes one argument per array. [`Array(T)`](/reference/data-types/array)
**Returned value**
Returns the packed bits. [`UInt64`](/reference/data-types/int-uint)
**Examples**
**Example**
```sql title=Query
SELECT arrayPackBitsToUInt64(x -> x, [1, 0, 0, 0, 0, 0])
```
```response title=Response
128
```
## arrayPartialPermutations {#arrayPartialPermutations}
Introduced in: v26.10.0
Returns all partial permutations (ordered selections without repeated indexes) of length `k` from the source array. Enumeration is done by indexes, so equal values may produce duplicate rows in the result. For `k = 0`, returns `[[]]`. The total result size per block is limited by `function_range_max_elements_in_block`; every generated array counts as its size plus one.
**Syntax**
```sql
arrayPartialPermutations(arr, k)
```
**Arguments**
- `arr` — Source array. [`Array(T)`](/reference/data-types/array)
- `k` — Length of each generated partial permutation. Must not exceed source array size. Supports `Int8`, `Int16`, `Int32`, `Int64`, `UInt8`, `UInt16`, `UInt32`, and `UInt64`. [`(U)Int*`](/reference/data-types/int-uint)
**Returned value**
All partial permutations of length `k`. [`Array(Array(T))`](/reference/data-types/array)
**Examples**
**Usage example**
```sql title=Query
SELECT arrayPartialPermutations([1, 2, 3], 2);
```
```response title=Response
[[1,2],[1,3],[2,1],[2,3],[3,1],[3,2]]
```
**`k = 0` example**
```sql title=Query
SELECT arrayPartialPermutations([1, 2, 3], 0);
```
```response title=Response
[[]]
```
## arrayPartialReverseSort {#arrayPartialReverseSort}
Introduced in: v23.2.0
This function is the same as `arrayReverseSort` but with an additional `limit` argument allowing partial sorting.
<Tip>
To retain only the sorted elements use `arrayResize`.
</Tip>
<Note>
This function is non-deterministic: it can return different results for the same arguments.
</Note>
**Syntax**
```sql
arrayPartialReverseSort([f,] limit, arr [, arr1, ... ,arrN])
```
**Arguments**
- `f(arr[, arr1, ... ,arrN])` — The lambda function to apply to elements of array `x`. [`Lambda function`](/reference/functions/regular-functions/overview#arrow-operator-and-lambda)
- `limit` — Index value up until which sorting will occur. [`(U)Int*`](/reference/data-types/int-uint)
- `arr` — Array to be sorted. [`Array(T)`](/reference/data-types/array)
- `arr1, ... ,arrN` — N additional arrays, in the case when `f` accepts multiple arguments. [`Array(T)`](/reference/data-types/array)
**Returned value**
Returns an array of the same size as the original array where elements in the range `[1..limit]` are sorted
in descending order. The remaining elements `(limit..N]` are in an unspecified order.
**Examples**
**simple_int**
```sql title=Query
SELECT arrayPartialReverseSort(2, [5, 9, 1, 3])
```
```response title=Response
[9,5,1,3]
```
**simple_string**
```sql title=Query
SELECT arrayPartialReverseSort(2, ['expenses','lasso','embolism','gladly'])
```
```response title=Response
['lasso','gladly','expenses','embolism']
```
**retain_sorted**
```sql title=Query
SELECT arrayResize(arrayPartialReverseSort(2, [5, 9, 1, 3]), 2)
```
```response title=Response
[9,5]
```
**lambda_simple**
```sql title=Query
SELECT arrayPartialReverseSort((x) -> -x, 2, [5, 9, 1, 3])
```
```response title=Response
[1,3,5,9]
```
**lambda_complex**
```sql title=Query
SELECT arrayPartialReverseSort((x, y) -> -y, 1, [0, 1, 2], [1, 2, 3]) as res
```
```response title=Response
[0,1,2]
```
## arrayPartialShuffle {#arrayPartialShuffle}
Introduced in: v23.2.0
Returns an array of the same size as the original array where elements in range `[1..limit]` are a random
subset of the original array. Remaining `(limit..n]` shall contain the elements not in `[1..limit]` range in undefined order.
Value of limit shall be in range `[1..n]`. Values outside of that range are equivalent to performing full `arrayShuffle`:
<Note>
This function will not materialize constants.
The value of `limit` should be in the range `[1..N]`. Values outside of that range are equivalent to performing full [`arrayShuffle`](#arrayShuffle).
</Note>
<Note>
This function is non-deterministic: it can return different results for the same arguments.
</Note>
**Syntax**
```sql
arrayPartialShuffle(arr [, limit[, seed]])
```
**Arguments**
- `arr` — The array to shuffle. [`Array(T)`](/reference/data-types/array)
- `seed` — Optional. The seed to be used with random number generation. If not provided, a random one is used. [`(U)Int*`](/reference/data-types/int-uint)
- `limit` — Optional. The number to limit element swaps to, in the range `[1..N]`. [`(U)Int*`](/reference/data-types/int-uint)
**Returned value**
Array with elements partially shuffled. [`Array(T)`](/reference/data-types/array)
**Examples**
**no_limit1**
```sql title=Query
SELECT arrayPartialShuffle([1, 2, 3, 4], 0)
```
```response title=Response
[2, 4, 3, 1]
```
**no_limit2**
```sql title=Query
SELECT arrayPartialShuffle([1, 2, 3, 4])
```
```response title=Response
[2,3,4,1]
```
**random_seed**
```sql title=Query
SELECT arrayPartialShuffle([1, 2, 3, 4], 2)
```
```response title=Response
[3, 4, 1, 2]
```
**explicit_seed**
```sql title=Query
SELECT arrayPartialShuffle([1, 2, 3, 4], 2, 41)
```
```response title=Response
[3,2,1,4]
```
**materialize**
```sql title=Query
SELECT arrayPartialShuffle(materialize([1, 2, 3, 4]), 2, 42), arrayPartialShuffle([1, 2, 3], 2, 42) FROM numbers(10)
```
```response title=Response
┌─arrayPartialShuffle(materialize([1, 2, 3, 4]), 2, 42)─┬─arrayPartialShuffle([1, 2, 3], 2, 42)─┐
│ [3,2,1,4] │ [3,2,1] │
│ [3,2,1,4] │ [3,1,2] │
│ [4,3,2,1] │ [1,3,2] │
│ [1,4,3,2] │ [2,1,3] │
│ [3,4,1,2] │ [3,2,1] │
│ [1,2,3,4] │ [3,2,1] │
│ [1,4,3,2] │ [1,2,3] │
│ [1,4,3,2] │ [3,2,1] │
│ [3,1,2,4] │ [3,2,1] │
│ [1,3,2,4] │ [2,1,3] │
└───────────────────────────────────────────────────────┴───────────────────────────────────────┘
```
## arrayPartialSort {#arrayPartialSort}
Introduced in: v23.2.0
This function is the same as `arraySort` but with an additional `limit` argument allowing partial sorting.
<Tip>
To retain only the sorted elements use `arrayResize`.
</Tip>
<Note>
This function is non-deterministic: it can return different results for the same arguments.
</Note>
**Syntax**
```sql
arrayPartialSort([f,] limit, arr [, arr1, ... ,arrN])
```
**Arguments**
- `f(arr[, arr1, ... ,arrN])` — The lambda function to apply to elements of array `x`. [`Lambda function`](/reference/functions/regular-functions/overview#arrow-operator-and-lambda)
- `limit` — Index value up until which sorting will occur. [`(U)Int*`](/reference/data-types/int-uint)
- `arr` — Array to be sorted. [`Array(T)`](/reference/data-types/array)
- `arr1, ... ,arrN` — N additional arrays, in the case when `f` accepts multiple arguments. [`Array(T)`](/reference/data-types/array)
**Returned value**
Returns an array of the same size as the original array where elements in the range `[1..limit]` are sorted
in ascending order. The remaining elements `(limit..N]` are in an unspecified order.
**Examples**
**simple_int**
```sql title=Query
SELECT arrayPartialSort(2, [5, 9, 1, 3])
```
```response title=Response
[1,3,5,9]
```
**simple_string**
```sql title=Query
SELECT arrayPartialSort(2, ['expenses', 'lasso', 'embolism', 'gladly'])
```
```response title=Response
['embolism','expenses','gladly','lasso']
```
**retain_sorted**
```sql title=Query
SELECT arrayResize(arrayPartialSort(2, [5, 9, 1, 3]), 2)
```
```response title=Response
[1,3]
```
**lambda_simple**
```sql title=Query
SELECT arrayPartialSort((x) -> -x, 2, [5, 9, 1, 3])
```
```response title=Response
[9,5,1,3]
```
**lambda_complex**
```sql title=Query
SELECT arrayPartialSort((x, y) -> -y, 1, [0, 1, 2], [1, 2, 3]) as res
```
```response title=Response
[2,1,0]
```
## arrayPermutations {#arrayPermutations}
Introduced in: v26.10.0
Returns all permutations of elements from the source array. Enumeration is done by indexes, so equal values may produce duplicate rows in the result. For an empty source array, returns `[[]]`. The total result size per block is limited by `function_range_max_elements_in_block`; every generated array counts as its size plus one.
**Syntax**
```sql
arrayPermutations(arr)
```
**Arguments**
- `arr` — Source array. [`Array(T)`](/reference/data-types/array)
**Returned value**
All permutations of the source array. [`Array(Array(T))`](/reference/data-types/array)
**Examples**
**Usage example**
```sql title=Query
SELECT arrayPermutations([1, 2, 3]);
```
```response title=Response
[[1,2,3],[1,3,2],[2,1,3],[2,3,1],[3,1,2],[3,2,1]]
```
## arrayPopBack {#arrayPopBack}
Introduced in: v1.1.0
Removes the last element from the array.
**Syntax**
```sql
arrayPopBack(arr)
```
**Arguments**
- `arr` — The array for which to remove the last element from. [`Array(T)`](/reference/data-types/array)
**Returned value**
Returns an array identical to `arr` but without the last element of `arr` [`Array(T)`](/reference/data-types/array)
**Examples**
**Usage example**
```sql title=Query
SELECT arrayPopBack([1, 2, 3]) AS res;
```
```response title=Response
[1,2]
```
## arrayPopFront {#arrayPopFront}
Introduced in: v1.1.0
Removes the first item from the array.
**Syntax**
```sql
arrayPopFront(arr)
```
**Arguments**
- `arr` — The array for which to remove the first element from. [`Array(T)`](/reference/data-types/array)
**Returned value**
Returns an array identical to `arr` but without the first element of `arr` [`Array(T)`](/reference/data-types/array)
**Examples**
**Usage example**
```sql title=Query
SELECT arrayPopFront([1, 2, 3]) AS res;
```
```response title=Response
[2,3]
```
## arrayProduct {#arrayProduct}
Introduced in: v21.1.0
Returns the product of elements in the source array.
If a lambda function `func` is specified, returns the product of elements of the lambda results.
**Syntax**
```sql
arrayProduct([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`](/reference/functions/regular-functions/overview#arrow-operator-and-lambda)
- `source_arr` — The source array to process. [`Array(T)`](/reference/data-types/array)
- `[, cond1_arr, ... , condN_arr]` — Optional. N condition arrays providing additional arguments to the lambda function. [`Array(T)`](/reference/data-types/array)
**Returned value**
Returns the product of elements in the source array, or the product of elements of the lambda results if provided. [`Float64`](/reference/data-types/float)
**Examples**
**Basic example**
```sql title=Query
SELECT arrayProduct([1, 2, 3, 4]);
```
```response title=Response
24
```
**Usage with lambda function**
```sql title=Query
SELECT arrayProduct(x, y -> x+y, [2, 2], [2, 2]) AS res;
```
```response title=Response
16
```
## arrayPushBack {#arrayPushBack}
Introduced in: v1.1.0
Adds one item to the end of the array.
**Syntax**
```sql
arrayPushBack(arr, x)
```
**Arguments**
- `arr` — The array for which to add value `x` to the end of. [`Array(T)`](/reference/data-types/array)
- `x` —
- Single value to add to the end of the array. [`Array(T)`](/reference/data-types/array).
<Note>
- Only numbers can be added to an array with numbers, and only strings can be added to an array of strings.
- When adding numbers, ClickHouse automatically sets the type of `x` for the data type of the array.
- Can be `NULL`. The function adds a `NULL` element to an array, and the type of array elements converts to `Nullable`.
For more information about the types of data in ClickHouse, see [Data types](/reference/data-types/index).
</Note>
**Returned value**
Returns an array identical to `arr` but with an additional value `x` at the end of the array [`Array(T)`](/reference/data-types/array)
**Examples**
**Usage example**
```sql title=Query
SELECT arrayPushBack(['a'], 'b') AS res;
```
```response title=Response
['a','b']
```
## arrayPushFront {#arrayPushFront}
Introduced in: v1.1.0
Adds one element to the beginning of the array.
**Syntax**
```sql
arrayPushFront(arr, x)
```
**Arguments**
- `arr` — The array for which to add value `x` to the end of. [`Array(T)`](/reference/data-types/array). - `x` —
- Single value to add to the start of the array. [`Array(T)`](/reference/data-types/array).
<Note>
- Only numbers can be added to an array with numbers, and only strings can be added to an array of strings.
- When adding numbers, ClickHouse automatically sets the type of `x` for the data type of the array.
- Can be `NULL`. The function adds a `NULL` element to an array, and the type of array elements converts to `Nullable`.
For more information about the types of data in ClickHouse, see [Data types](/reference/data-types/index).
</Note>
**Returned value**
Returns an array identical to `arr` but with an additional value `x` at the beginning of the array [`Array(T)`](/reference/data-types/array)
**Examples**
**Usage example**
```sql title=Query
SELECT arrayPushFront(['b'], 'a') AS res;
```
```response title=Response
['a','b']
```
## arrayROCAUC {#arrayROCAUC}
Introduced in: v20.4.0
Calculates the area under the receiver operating characteristic (ROC) curve.
A ROC curve is created by plotting True Positive Rate (TPR) on the y-axis and False Positive Rate (FPR) on the x-axis across all thresholds.
With `scale` set to true, the default, the resulting value ranges from zero to one, with a higher value indicating better model performance, and the result is `NaN` when the ROC AUC is undefined, for example if there are no positive or no negative labels.
A `NaN` score always produces a `NaN` result.
The ROC AUC (also known as simply AUC) is a concept in machine learning.
For more details, please see [here](https://developers.google.com/machine-learning/glossary#pr-auc-area-under-the-pr-curve), [here](https://developers.google.com/machine-learning/crash-course/classification/roc-and-auc#expandable-1) and [here](https://en.wikipedia.org/wiki/Receiver_operating_characteristic#Area_under_the_curve).
**Syntax**
```sql
arrayROCAUC(scores, labels[, scale[, partial_offsets]])
```
**Aliases**: `arrayAUC`
**Arguments**
- `scores` — Scores prediction model gives. [`Array((U)Int*)`](/reference/data-types/array) or [`Array(Float*)`](/reference/data-types/array)
- `labels` — Labels of samples, usually 1 for positive sample and 0 for negative sample. [`Array((U)Int*)`](/reference/data-types/array) or [`Enum`](/reference/data-types/enum)
- `scale` — Optional. Decides whether to return the normalized area. If false, returns the area under the TP (true positives) x FP (false positives) curve instead. Default value: true. [`Bool`](/reference/data-types/boolean)
- `partial_offsets` —
- An array of four non-negative integers for calculating a partial area under the ROC curve (equivalent to a vertical band of the ROC space) instead of the whole AUC. This option is useful for distributed computation of the ROC AUC. The array must contain the following elements [`higher_partitions_tp`, `higher_partitions_fp`, `total_positives`, `total_negatives`]. [Array](/reference/data-types/array) of non-negative [Integers](/reference/data-types/int-uint). Optional.
- `higher_partitions_tp`: The number of positive labels in the higher-scored partitions.
- `higher_partitions_fp`: The number of negative labels in the higher-scored partitions.
- `total_positives`: The total number of positive samples in the entire dataset.
- `total_negatives`: The total number of negative samples in the entire dataset.
<Note>
When `arr_partial_offsets` is used, the `arr_scores` and `arr_labels` should be only a partition of the entire dataset, containing an interval of scores.
The dataset should be divided into contiguous partitions, where each partition contains the subset of the data whose scores fall within a specific range.
For example:
- One partition could contain all scores in the range [0, 0.5).
- Another partition could contain scores in the range [0.5, 1.0].
</Note>
**Returned value**
Returns area under the receiver operating characteristic (ROC) curve. Returns `NaN` if a score is `NaN`, and, when `scale` is true, also when the ROC AUC is undefined because there are no positive or no negative labels. [`Float64`](/reference/data-types/float)
**Examples**
**Usage example**
```sql title=Query
SELECT arrayROCAUC([0.1, 0.4, 0.35, 0.8], [0, 0, 1, 1]);
```
```response title=Response
0.75
```
## arrayRandomSample {#arrayRandomSample}
Introduced in: v23.10.0
Returns a subset with `samples`-many random elements of an input array. If `samples` exceeds the size of the input array, the sample size is limited to the size of the array, i.e. all array elements are returned but their order is not guaranteed. The function can handle both flat arrays and nested arrays.
<Note>
This function is non-deterministic: it can return different results for the same arguments.
</Note>
**Syntax**
```sql
arrayRandomSample(arr, samples)
```
**Arguments**
- `arr` — The input array or multidimensional array from which to sample elements. [`Array(T)`](/reference/data-types/array)
- `samples` — The number of elements to include in the random sample. [`(U)Int*`](/reference/data-types/int-uint)
**Returned value**
An array containing a random sample of elements from the input array [`Array(T)`](/reference/data-types/array)
**Examples**
**Usage example**
```sql title=Query
SELECT arrayRandomSample(['apple', 'banana', 'cherry', 'date'], 2) as res;
```
```response title=Response
['cherry','apple']
```
**Using a multidimensional array**
```sql title=Query
SELECT arrayRandomSample([[1, 2], [3, 4], [5, 6]], 2) as res;
```
```response title=Response
[[3,4],[5,6]]
```
## arrayReduce {#arrayReduce}
Introduced in: v1.1.0
Applies an aggregate function to array elements and returns its result.
The name of the aggregation function is passed as a string in single quotes `'max'`, `'sum'`.
When using parametric aggregate functions, the parameter is indicated after the function name in parentheses `'uniqUpTo(6)'`.
**Syntax**
```sql
arrayReduce(agg_f, arr1[, arr2, ... , arrN])
```
**Arguments**
- `agg_f` — The name of an aggregate function which should be a constant. [`String`](/reference/data-types/string)
- `arr1[, arr2, ... , arrN]` — N arrays corresponding to the arguments of `agg_f`. [`Array(T)`](/reference/data-types/array)
**Returned value**
Returns the result of the aggregate function
**Examples**
**Usage example**
```sql title=Query
SELECT arrayReduce('max', [1, 2, 3]);
```
```response title=Response
┌─arrayReduce('max', [1, 2, 3])─┐
│ 3 │
└───────────────────────────────┘
```
**Example with aggregate function using multiple arguments**
```sql title=Query
--If an aggregate function takes multiple arguments, then this function must be applied to multiple arrays of the same size.
SELECT arrayReduce('maxIf', [3, 5], [1, 0]);
```
```response title=Response
┌─arrayReduce('maxIf', [3, 5], [1, 0])─┐
│ 3 │
└──────────────────────────────────────┘
```
**Example with a parametric aggregate function**
```sql title=Query
SELECT arrayReduce('uniqUpTo(3)', [1, 2, 3, 4, 5, 6, 7, 8, 9, 10]);
```
```response title=Response
┌─arrayReduce('uniqUpTo(3)', [1, 2, 3, 4, 5, 6, 7, 8, 9, 10])─┐
│ 4 │
└─────────────────────────────────────────────────────────────┘
```
## arrayReduceInRanges {#arrayReduceInRanges}
Introduced in: v20.4.0
Applies an aggregate function to array elements in the given ranges and returns an array containing the result corresponding to each range.
The function will return the same result as multiple `arrayReduce(agg_func, arraySlice(arr1, index, length), ...)`.
The only difference is that the length is unsigned: a length greater than the maximum `Int64` value selects the rest of the array,
while `arraySlice` interprets such a value as a negative length.
**Syntax**
```sql
arrayReduceInRanges(agg_f, ranges, arr1[, arr2, ... ,arrN])
```
**Arguments**
- `agg_f` — The name of the aggregate function to use. [`String`](/reference/data-types/string)
- `ranges` — The range over which to aggregate. An array of tuples, `(i, r)` containing the index `i` from which to begin from and the range `r` over which to aggregate. [`Array(T)`](/reference/data-types/array) or [`Tuple(T)`](/reference/data-types/tuple)
- `arr1[, arr2, ... ,arrN]` — N arrays as arguments to the aggregate function. [`Array(T)`](/reference/data-types/array)
**Returned value**
Returns an array containing results of the aggregate function over the specified ranges [`Array(T)`](/reference/data-types/array)
**Examples**
**Usage example**
```sql title=Query
SELECT arrayReduceInRanges(
'sum',
[(1, 5), (2, 3), (3, 4), (4, 4)],
[1000000, 200000, 30000, 4000, 500, 60, 7]
) AS res
```
```response title=Response
┌─res─────────────────────────┐
│ [1234500,234000,34560,4567] │
└─────────────────────────────┘
```
## arrayRemove {#arrayRemove}
Introduced in: v25.11.0
Removes all elements equal to a given value from an array.
NULLs are treated as equal.
**Syntax**
```sql
arrayRemove(arr, elem)
```
**Aliases**: `array_remove`
**Arguments**
- `arr` — Array(T) - `elem` — T
**Returned value**
Returns a subset of the source array [`Array(T)`](/reference/data-types/array)
**Examples**
**Example 1**
```sql title=Query
SELECT arrayRemove([1, 2, 2, 3], 2)
```
```response title=Response
[1,3]
```
**Example 2**
```sql title=Query
SELECT arrayRemove(['a', NULL, 'b', NULL], NULL)
```
```response title=Response
['a','b']
```
## arrayRemoveAt {#arrayRemoveAt}
Introduced in: v26.10.0
Removes the element at the specified index from an array.
Indexes are 1-based. Negative indexes count from the end of the array.
If the index is outside the array bounds, the array is returned unchanged.
Index 0 is invalid.
**Syntax**
```sql
arrayRemoveAt(arr, index)
```
**Arguments**
- `arr` — Source array. [`Array(T)`](/reference/data-types/array)
- `index` — Non-Nullable integer index of the element to remove. Negative indexes count from the end. [`(U)Int8/16/32/64`](/reference/data-types/int-uint)
**Returned value**
Returns the source array without the element at `index`, or the original array if `index` is out of bounds. [`Array(T)`](/reference/data-types/array)
**Examples**
**Positive index**
```sql title=Query
SELECT arrayRemoveAt([1, 2, 3, 4], 2)
```
```response title=Response
[1,3,4]
```
**Negative index**
```sql title=Query
SELECT arrayRemoveAt([1, 2, 3, 4], -1)
```
```response title=Response
[1,2,3]
```
**Out of bounds**
```sql title=Query
SELECT arrayRemoveAt([1, 2, 3, 4], 10)
```
```response title=Response
[1,2,3,4]
```
## arrayResize {#arrayResize}
Introduced in: v1.1.0
Changes the length of the array.
**Syntax**
```sql
arrayResize(arr, size[, extender])
```
**Arguments**
- `arr` — Array to resize. [`Array(T)`](/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)`](/reference/data-types/array)
**Examples**
**Example 1**
```sql title=Query
SELECT arrayResize([1], 3);
```
```response title=Response
[1,0,0]
```
**Example 2**
```sql title=Query
SELECT arrayResize([1], 3, NULL);
```
```response title=Response
[1,NULL,NULL]
```
## arrayReverse {#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.
</Note>
**Syntax**
```sql
arrayReverse(arr)
```
**Arguments**
- `arr` — The array to reverse. [`Array(T)`](/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)`](/reference/data-types/array)
**Examples**
**Usage example**
```sql title=Query
SELECT arrayReverse([1, 2, 3])
```
```response title=Response
[3,2,1]
```
## arrayReverseFill {#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**
```sql
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`](/reference/functions/regular-functions/overview#arrow-operator-and-lambda)
- `source_arr` — The source array to process. [`Array(T)`](/reference/data-types/array)
- `[, cond1_arr, ... , condN_arr]` — Optional. N condition arrays providing additional arguments to the lambda function. [`Array(T)`](/reference/data-types/array)
**Returned value**
Returns an array with elements of the source array replaced by the results of the lambda. [`Array(T)`](/reference/data-types/array)
**Examples**
**Example with a single array**
```sql title=Query
SELECT arrayReverseFill(x -> not isNull(x), [1, null, 2, null]) AS res
```
```response title=Response
[1,2,2,NULL]
```
**Example with two arrays**
```sql title=Query
SELECT arrayReverseFill(x, y, z -> x > y AND x < z, [5, 3, 6, 2], [4, 7, 1, 3], [10, 2, 8, 5]) AS res;
```
```response title=Response
[5,6,6,2]
```
## arrayReverseSort {#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](/reference/functions/regular-functions/overview#higher-order-functions).
**Syntax**
```sql
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)`](/reference/data-types/array) - `arr1, ..., arrN` — 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)`](/reference/data-types/array).
**Examples**
**Example 1**
```sql title=Query
SELECT arrayReverseSort((x, y) -> y, [4, 3, 5], ['a', 'b', 'c']) AS res;
```
```response title=Response
[5,3,4]
```
**Example 2**
```sql title=Query
SELECT arrayReverseSort((x, y) -> -y, [4, 3, 5], [1, 2, 3]) AS res;
```
```response title=Response
[4,3,5]
```
## arrayReverseSplit {#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**
```sql
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`](/reference/functions/regular-functions/overview#arrow-operator-and-lambda)
- `source_arr` — The source array to process. [`Lambda function`](/reference/functions/regular-functions/overview#arrow-operator-and-lambda)
- `[, cond1_arr, ... , condN_arr]` — Optional. N condition arrays providing additional arguments to the lambda function. [`Array(T)`](/reference/data-types/array)
**Returned value**
Returns an array of arrays. [`Array(Array(T))`](/reference/data-types/array)
**Examples**
**Usage example**
```sql title=Query
SELECT arrayReverseSplit((x, y) -> y, [1, 2, 3, 4, 5], [1, 0, 0, 1, 0]) AS res
```
```response title=Response
[[1],[2,3,4],[5]]
```
## arrayRotateLeft {#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**
```sql
arrayRotateLeft(arr, n)
```
**Arguments**
- `arr` — The array for which to rotate the elements.[`Array(T)`](/reference/data-types/array). - `n` — Number of elements to rotate. [`(U)Int8/16/32/64`](/reference/data-types/int-uint).
**Returned value**
An array rotated to the left by the specified number of elements [`Array(T)`](/reference/data-types/array)
**Examples**
**Usage example**
```sql title=Query
SELECT arrayRotateLeft([1,2,3,4,5,6], 2) as res;
```
```response title=Response
[3,4,5,6,1,2]
```
**Negative value of n**
```sql title=Query
SELECT arrayRotateLeft([1,2,3,4,5,6], -2) as res;
```
```response title=Response
[5,6,1,2,3,4]
```
## arrayRotateRight {#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**
```sql
arrayRotateRight(arr, n)
```
**Arguments**
- `arr` — The array for which to rotate the elements.[`Array(T)`](/reference/data-types/array). - `n` — Number of elements to rotate. [`(U)Int8/16/32/64`](/reference/data-types/int-uint).
**Returned value**
An array rotated to the right by the specified number of elements [`Array(T)`](/reference/data-types/array)
**Examples**
**Usage example**
```sql title=Query
SELECT arrayRotateRight([1,2,3,4,5,6], 2) as res;
```
```response title=Response
[5,6,1,2,3,4]
```
**Negative value of n**
```sql title=Query
SELECT arrayRotateRight([1,2,3,4,5,6], -2) as res;
```
```response title=Response
[3,4,5,6,1,2]
```
## arrayShiftLeft {#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**
```sql
arrayShiftLeft(arr, n[, default])
```
**Arguments**
- `arr` — The array for which to shift the elements.[`Array(T)`](/reference/data-types/array). - `n` — Number of elements to shift.[`(U)Int8/16/32/64`](/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)`](/reference/data-types/array)
**Examples**
**Usage example**
```sql title=Query
SELECT arrayShiftLeft([1,2,3,4,5,6], 2) as res;
```
```response title=Response
[3,4,5,6,0,0]
```
**Negative value of n**
```sql title=Query
SELECT arrayShiftLeft([1,2,3,4,5,6], -2) as res;
```
```response title=Response
[0,0,1,2,3,4]
```
**Using a default value**
```sql title=Query
SELECT arrayShiftLeft([1,2,3,4,5,6], 2, 42) as res;
```
```response title=Response
[3,4,5,6,42,42]
```
## arrayShiftRight {#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**
```sql
arrayShiftRight(arr, n[, default])
```
**Arguments**
- `arr` — The array for which to shift the elements. [`Array(T)`](/reference/data-types/array)
- `n` — Number of elements to shift. [`(U)Int8/16/32/64`](/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)`](/reference/data-types/array)
**Examples**
**Usage example**
```sql title=Query
SELECT arrayShiftRight([1, 2, 3, 4, 5, 6], 2) as res;
```
```response title=Response
[0,0,1,2,3,4]
```
**Negative value of n**
```sql title=Query
SELECT arrayShiftRight([1, 2, 3, 4, 5, 6], -2) as res;
```
```response title=Response
[3,4,5,6,0,0]
```
**Using a default value**
```sql title=Query
SELECT arrayShiftRight([1, 2, 3, 4, 5, 6], 2, 42) as res;
```
```response title=Response
[42,42,1,2,3,4]
```
## arrayShingles {#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**
```sql
arrayShingles(arr, l)
```
**Arguments**
- `arr` — Array for which to generate an array of shingles. [`Array(T)`](/reference/data-types/array)
- `l` — The length of each shingle. [`(U)Int*`](/reference/data-types/int-uint)
**Returned value**
An array of generated shingles [`Array(T)`](/reference/data-types/array)
**Examples**
**Usage example**
```sql title=Query
SELECT arrayShingles([1, 2, 3, 4], 3) as res;
```
```response title=Response
[[1,2,3],[2,3,4]]
```
## arrayShuffle {#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.
</Note>
<Note>
This function is non-deterministic: it can return different results for the same arguments.
</Note>
**Syntax**
```sql
arrayShuffle(arr [, seed])
```
**Arguments**
- `arr` — The array to shuffle. [`Array(T)`](/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*`](/reference/data-types/int-uint)
**Returned value**
Array with elements shuffled [`Array(T)`](/reference/data-types/array)
**Examples**
**Example without seed (unstable results)**
```sql title=Query
SELECT arrayShuffle([1, 2, 3, 4]);
```
```response title=Response
[1,4,2,3]
```
**Example without seed (stable results)**
```sql title=Query
SELECT arrayShuffle([1, 2, 3, 4], 41);
```
```response title=Response
[3,2,1,4]
```
## arraySimilarity {#arraySimilarity}
Introduced in: v25.4.0
Calculates the similarity of two arrays from `0` to `1` based on weighted Levenshtein distance.
**Syntax**
```sql
arraySimilarity(from, to, from_weights, to_weights)
```
**Arguments**
- `from` — first array [`Array(T)`](/reference/data-types/array)
- `to` — second array [`Array(T)`](/reference/data-types/array)
- `from_weights` — weights for the first array. [`Array((U)Int*|Float*)`](/reference/data-types/array)
- `to_weights` — weights for the second array. [`Array((U)Int*|Float*)`](/reference/data-types/array)
**Returned value**
Returns the similarity between `0` and `1` of the two arrays based on the weighted Levenshtein distance [`Float64`](/reference/data-types/float)
**Examples**
**Usage example**
```sql title=Query
SELECT arraySimilarity(['A', 'B', 'C'], ['A', 'K', 'L'], [1.0, 2, 3], [3.0, 4, 5]);
```
```response title=Response
0.2222222222222222
```
## arraySlice {#arraySlice}
Introduced in: v1.1.0
Returns a slice of the array, with `NULL` elements included.
The first argument may also be a [QBit](/reference/data-types/qbit): the result is a QBit over the selected dimensions (a projection to a subset of dimensions). In that case `offset` and `length` must be constants, because the dimension of a QBit is part of its type, and the slice must select at least one dimension. A slice aligned to the QBit's stride-group boundaries keeps the stride and reuses the stored bit-plane streams without copying.
**Syntax**
```sql
arraySlice(arr, offset [, length])
```
**Aliases**: `slice`
**Arguments**
- `arr` — Array to slice. [`Array(T)`](/reference/data-types/array) or [`QBit`](/reference/data-types/qbit)
- `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*`](/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*`](/reference/data-types/int-uint)
**Returned value**
Returns a slice of the array with `length` elements from the specified `offset` [`Array(T)`](/reference/data-types/array)
**Examples**
**Usage example**
```sql title=Query
SELECT arraySlice([1, 2, NULL, 4, 5], 2, 3) AS res;
```
```response title=Response
[2,NULL,4]
```
## arraySort {#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](/reference/functions/regular-functions/overview#higher-order-functions).
**Syntax**
```sql
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)`](/reference/data-types/array) - `arr1, ..., arrN` — 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)`](/reference/data-types/array).
**Examples**
**Example 1**
```sql title=Query
SELECT arraySort([1, 3, 3, 0]);
```
```response title=Response
[0,1,3,3]
```
**Example 2**
```sql title=Query
SELECT arraySort(['hello', 'world', '!']);
```
```response title=Response
['!','hello','world']
```
**Example 3**
```sql title=Query
SELECT arraySort([1, nan, 2, NULL, 3, nan, -4, NULL, inf, -inf]);
```
```response title=Response
[-inf,-4,1,2,3,inf,nan,nan,NULL,NULL]
```
## arraySplit {#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**
```sql
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](/reference/functions/regular-functions/overview#arrow-operator-and-lambda). - `source_arr` — The source array to split [`Array(T)`](/reference/data-types/array). - `[, cond1_arr, ... , condN_arr]` — Optional. N condition arrays providing additional arguments to the lambda function. [`Array(T)`](/reference/data-types/array).
**Returned value**
Returns an array of arrays [`Array(Array(T))`](/reference/data-types/array)
**Examples**
**Usage example**
```sql title=Query
SELECT arraySplit((x, y) -> y, [1, 2, 3, 4, 5], [1, 0, 0, 1, 0]) AS res
```
```response title=Response
[[1,2,3],[4,5]]
```
## arraySum {#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**
```sql
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`](/reference/functions/regular-functions/overview#arrow-operator-and-lambda)
- `source_arr` — The source array to process. [`Array(T)`](/reference/data-types/array)
- `, cond1_arr, ... , condN_arr]` — Optional. N condition arrays providing additional arguments to the lambda function. [`Array(T)`](/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**
```sql title=Query
SELECT arraySum([1, 2, 3, 4]);
```
```response title=Response
10
```
**Usage with lambda function**
```sql title=Query
SELECT arraySum(x, y -> x+y, [1, 1, 1, 1], [1, 1, 1, 1]);
```
```response title=Response
8
```
## arraySymmetricDifference {#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.
</Note>
**Syntax**
```sql
arraySymmetricDifference(arr1, arr2, ... , arrN)
```
**Arguments**
- `arrN` — N arrays from which to make the new array. [`Array(T)`](/reference/data-types/array).
**Returned value**
Returns an array of distinct elements not present in all source arrays [`Array(T)`](/reference/data-types/array)
**Examples**
**Usage example**
```sql title=Query
SELECT
arraySymmetricDifference([1, 2], [1, 2], [1, 2]) AS empty_symmetric_difference,
arraySymmetricDifference([1, 2], [1, 2], [1, 3]) AS non_empty_symmetric_difference;
```
```response title=Response
┌─empty_symmetric_difference─┬─non_empty_symmetric_difference─┐
│ [] │ [3,2] │
└────────────────────────────┴────────────────────────────────┘
```
## arrayTopK {#arrayTopK}
Introduced in: v26.6.0
Returns an array of the K largest elements of the input array, sorted in descending order.
If a lambda function `f` is specified, elements are compared by the result of `f` applied to each element.
If `f` accepts multiple arguments, additional arrays are passed to `arrayTopK`, their elements
correspond to the arguments of `f`.
`NULL` values are skipped and do not appear in the result. The result size is at most `K`
and may be smaller when the input array contains fewer non-null elements than `K`.
The element type of the result is the non-nullable counterpart of the input element type.
`arrayTopK` is a [higher-order function](/reference/functions/regular-functions/overview#higher-order-functions).
See also `arrayBottomK`, which returns the K smallest elements instead.
**Syntax**
```sql
arrayTopK([f,] K, arr [, arr1, ... ,arrN])
```
**Arguments**
- `f(arr[, arr1, ... ,arrN])` — Optional. A lambda function to compute the sort key for each element. [`Lambda function`](/reference/functions/regular-functions/overview#arrow-operator-and-lambda)
- `K` — The number of largest elements to return. [`(U)Int8/16/32/64`](/reference/data-types/int-uint)
- `arr` — An array. [`Array(T)`](/reference/data-types/array)
- `arr1, ... ,arrN` — N additional arrays, in the case when `f` accepts multiple arguments. [`Array(T)`](/reference/data-types/array)
**Returned value**
Returns up to `K` elements of `arr` with the largest values (or largest lambda results), sorted in descending order.
Nulls are skipped. The returned array has element type `T` even when the input has type `Nullable(T)`.
**Examples**
**simple_int**
```sql title=Query
SELECT arrayTopK(3, [1, 5, 2, 7, 3])
```
```response title=Response
[7,5,3]
```
**skip_nulls**
```sql title=Query
SELECT arrayTopK(3, [1, NULL, 5, 2, NULL, 7])
```
```response title=Response
[7,5,2]
```
**fewer_than_k**
```sql title=Query
SELECT arrayTopK(5, [1, NULL, 2])
```
```response title=Response
[2,1]
```
**lambda_simple**
```sql title=Query
SELECT arrayTopK((x) -> -x, 2, [5, 9, 1, 3])
```
```response title=Response
[1,3]
```
**lambda_multi**
```sql title=Query
SELECT arrayTopK((x, y) -> y, 2, ['a', 'b', 'c'], [3, 1, 2])
```
```response title=Response
['a','c']
```
## arrayTranspose {#arrayTranspose}
Introduced in: v26.4.0
Transposes a two-dimensional array.
All inner arrays must have the same length.
**Syntax**
```sql
arrayTranspose(arr)
```
**Arguments**
- `arr` — A two-dimensional array to transpose. All inner arrays must have the same length. [`Array(Array(T))`](/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))`](/reference/data-types/array)
**Examples**
**Square matrix**
```sql title=Query
SELECT arrayTranspose([[1, 2], [3, 4]])
```
```response title=Response
[[1,3],[2,4]]
```
**Non-square matrix**
```sql title=Query
SELECT arrayTranspose([[1, 2, 3], [4, 5, 6]])
```
```response title=Response
[[1,4],[2,5],[3,6]]
```
**String elements**
```sql title=Query
SELECT arrayTranspose([['a', 'b'], ['c', 'd']])
```
```response title=Response
[['a','c'],['b','d']]
```
## arrayUnion {#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**
```sql
arrayUnion(arr1, arr2, ..., arrN)
```
**Aliases**: `array_union`
**Arguments**
- `arrN` — N arrays from which to make the new array. [`Array(T)`](/reference/data-types/array)
**Returned value**
Returns an array with distinct elements from the source arrays [`Array(T)`](/reference/data-types/array)
**Examples**
**Usage example**
```sql title=Query
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
```
```response title=Response
┌─num_example─┬─str_example────┬─null_example─┐
│ [10,-2,1] │ ['hello','hi'] │ [3,2,1,NULL] │
└─────────────┴────────────────┴──────────────┘
```
## arrayUniq {#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)`.
</Tip>
**Syntax**
```sql
arrayUniq(arr1[, arr2, ..., arrN])
```
**Arguments**
- `arr1` — Array for which to count the number of unique elements. [`Array(T)`](/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)`](/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`](/reference/data-types/int-uint)
**Examples**
**Single argument**
```sql title=Query
SELECT arrayUniq([1, 1, 2, 2])
```
```response title=Response
2
```
**Multiple argument**
```sql title=Query
SELECT arrayUniq([1, 2, 3, 1], [4, 5, 6, 4])
```
```response title=Response
3
```
## arrayWithConstant {#arrayWithConstant}
Introduced in: v20.1.0
Creates an array of length `length` filled with the constant `x`.
**Syntax**
```sql
arrayWithConstant(N, x)
```
**Arguments**
- `length` — Number of elements in the array. [`(U)Int*`](/reference/data-types/int-uint)
- `x` — The value of the `N` elements in the array, of any type.
**Returned value**
Returns an Array with `N` elements of value `x`. [`Array(T)`](/reference/data-types/array)
**Examples**
**Usage example**
```sql title=Query
SELECT arrayWithConstant(3, 1)
```
```response title=Response
[1,1,1]
```
## arrayZip {#arrayZip}
Introduced in: v20.1.0
Combines multiple arrays into a single array. The resulting array contains the corresponding elements of the source arrays grouped into tuples in the listed order of arguments.
**Syntax**
```sql
arrayZip(arr1, arr2, ... , arrN)
```
**Arguments**
- `arr1, arr2, ... , arrN` — N arrays to combine into a single array. [`Array(T)`](/reference/data-types/array)
**Returned value**
Returns an array with elements from the source arrays grouped in tuples. Data types in the tuple are the same as types of the input arrays and in the same order as arrays are passed [`Array(T)`](/reference/data-types/array)
**Examples**
**Usage example**
```sql title=Query
SELECT arrayZip(['a', 'b', 'c'], [5, 2, 1]);
```
```response title=Response
[('a',5),('b',2),('c',1)]
```
## arrayZipUnaligned {#arrayZipUnaligned}
Introduced in: v20.1.0
Combines multiple arrays into a single array, allowing for unaligned arrays (arrays of differing lengths). The resulting array contains the corresponding elements of the source arrays grouped into tuples in the listed order of arguments.
**Syntax**
```sql
arrayZipUnaligned(arr1, arr2, ..., arrN)
```
**Aliases**: `zip`
**Arguments**
- `arr1, arr2, ..., arrN` — N arrays to combine into a single array. [`Array(T)`](/reference/data-types/array)
**Returned value**
Returns an array with elements from the source arrays grouped in tuples. Data types in the tuple are the same as types of the input arrays and in the same order as arrays are passed. [`Array(T)`](/reference/data-types/array) or [`Tuple(T1, T2, ...)`](/reference/data-types/tuple)
**Examples**
**Usage example**
```sql title=Query
SELECT arrayZipUnaligned(['a'], [1, 2, 3]);
```
```response title=Response
[('a',1),(NULL,2),(NULL,3)]
```
## array_position {#array_position}
Introduced in: v26.8.0
PostgreSQL compatibility function. Returns the 1-based position of the first occurrence of the element in the array, or `NULL` when the element does not occur. A `NULL` element searches for the first `NULL` of the array, as in PostgreSQL. The native ClickHouse counterpart is `indexOf`, which returns 0 instead of `NULL`.
**Syntax**
```sql
array_position(array, element)
```
**Arguments**
- `array` — The array to search. [`Array(T)`](/reference/data-types/array)
- `element` — The element to search for. [`Any`](/reference/data-types/index)
**Returned value**
The 1-based position of the first occurrence, or `NULL` when the element does not occur. [`Nullable(UInt64)`](/reference/data-types/nullable)
**Examples**
**Example**
```sql title=Query
SELECT array_position(['a', 'b'], 'b')
```
```response title=Response
2
```
## countEqual {#countEqual}
Introduced in: v1.1.0
Returns the number of elements in the array equal to `x`. Equivalent to `arrayCount(elem -> elem = x, arr)`.
`NULL` elements are handled as separate values.
**Syntax**
```sql
countEqual(arr, x)
```
**Arguments**
- `arr` — Array to search. [`Array(T)`](/reference/data-types/array)
- `x` — Value in the array to count. Any type.
**Returned value**
Returns the number of elements in the array equal to `x` [`UInt64`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query
SELECT countEqual([1, 2, NULL, NULL], NULL)
```
```response title=Response
2
```
## empty {#empty}
Introduced in: v1.1.0
Checks whether the input array is empty.
An array is considered empty if it does not contain any elements.
<Note>
Can be optimized by enabling the [`optimize_functions_to_subcolumns` setting](/reference/settings/session-settings/optimize#optimize_functions_to_subcolumns). With `optimize_functions_to_subcolumns = 1` the function reads only [size0](/reference/data-types/array#array-size) subcolumn instead of reading and processing the whole array column. The query `SELECT empty(arr) FROM TABLE;` transforms to `SELECT arr.size0 = 0 FROM TABLE;`.
</Note>
The function also works for Strings or UUIDs.
**Syntax**
```sql
empty(arr)
```
**Arguments**
- `arr` — Input array. [`Array(T)`](/reference/data-types/array)
**Returned value**
Returns `1` for an empty array or `0` for a non-empty array [`UInt8`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query
SELECT empty([]);
```
```response title=Response
1
```
## emptyArrayDate {#emptyArrayDate}
Introduced in: v1.1.0
Returns an empty Date array
**Syntax**
```sql
emptyArrayDate()
```
**Arguments**
- None.
**Returned value**
An empty Date array. [`Array(T)`](/reference/data-types/array)
**Examples**
**Usage example**
```sql title=Query
SELECT emptyArrayDate()
```
```response title=Response
[]
```
## emptyArrayDateTime {#emptyArrayDateTime}
Introduced in: v1.1.0
Returns an empty DateTime array
**Syntax**
```sql
emptyArrayDateTime()
```
**Arguments**
- None.
**Returned value**
An empty DateTime array. [`Array(T)`](/reference/data-types/array)
**Examples**
**Usage example**
```sql title=Query
SELECT emptyArrayDateTime()
```
```response title=Response
[]
```
## emptyArrayFloat32 {#emptyArrayFloat32}
Introduced in: v1.1.0
Returns an empty Float32 array
**Syntax**
```sql
emptyArrayFloat32()
```
**Arguments**
- None.
**Returned value**
An empty Float32 array. [`Array(T)`](/reference/data-types/array)
**Examples**
**Usage example**
```sql title=Query
SELECT emptyArrayFloat32()
```
```response title=Response
[]
```
## emptyArrayFloat64 {#emptyArrayFloat64}
Introduced in: v1.1.0
Returns an empty Float64 array
**Syntax**
```sql
emptyArrayFloat64()
```
**Arguments**
- None.
**Returned value**
An empty Float64 array. [`Array(T)`](/reference/data-types/array)
**Examples**
**Usage example**
```sql title=Query
SELECT emptyArrayFloat64()
```
```response title=Response
[]
```
## emptyArrayInt16 {#emptyArrayInt16}
Introduced in: v1.1.0
Returns an empty Int16 array
**Syntax**
```sql
emptyArrayInt16()
```
**Arguments**
- None.
**Returned value**
An empty Int16 array. [`Array(T)`](/reference/data-types/array)
**Examples**
**Usage example**
```sql title=Query
SELECT emptyArrayInt16()
```
```response title=Response
[]
```
## emptyArrayInt32 {#emptyArrayInt32}
Introduced in: v1.1.0
Returns an empty Int32 array
**Syntax**
```sql
emptyArrayInt32()
```
**Arguments**
- None.
**Returned value**
An empty Int32 array. [`Array(T)`](/reference/data-types/array)
**Examples**
**Usage example**
```sql title=Query
SELECT emptyArrayInt32()
```
```response title=Response
[]
```
## emptyArrayInt64 {#emptyArrayInt64}
Introduced in: v1.1.0
Returns an empty Int64 array
**Syntax**
```sql
emptyArrayInt64()
```
**Arguments**
- None.
**Returned value**
An empty Int64 array. [`Array(T)`](/reference/data-types/array)
**Examples**
**Usage example**
```sql title=Query
SELECT emptyArrayInt64()
```
```response title=Response
[]
```
## emptyArrayInt8 {#emptyArrayInt8}
Introduced in: v1.1.0
Returns an empty Int8 array
**Syntax**
```sql
emptyArrayInt8()
```
**Arguments**
- None.
**Returned value**
An empty Int8 array. [`Array(T)`](/reference/data-types/array)
**Examples**
**Usage example**
```sql title=Query
SELECT emptyArrayInt8()
```
```response title=Response
[]
```
## emptyArrayString {#emptyArrayString}
Introduced in: v1.1.0
Returns an empty String array
**Syntax**
```sql
emptyArrayString()
```
**Arguments**
- None.
**Returned value**
An empty String array. [`Array(T)`](/reference/data-types/array)
**Examples**
**Usage example**
```sql title=Query
SELECT emptyArrayString()
```
```response title=Response
[]
```
## emptyArrayToSingle {#emptyArrayToSingle}
Introduced in: v1.1.0
Accepts an empty array and returns a one-element array that is equal to the default value.
**Syntax**
```sql
emptyArrayToSingle(arr)
```
**Arguments**
- `arr` — An empty array. [`Array(T)`](/reference/data-types/array)
**Returned value**
An array with a single value of the Array's default type. [`Array(T)`](/reference/data-types/array)
**Examples**
**Basic example**
```sql title=Query
CREATE TABLE test (
a Array(Int32),
b Array(String),
c Array(DateTime)
)
ENGINE = MergeTree
ORDER BY tuple();
INSERT INTO test VALUES ([], [], []);
SELECT emptyArrayToSingle(a), emptyArrayToSingle(b), emptyArrayToSingle(c) FROM test;
```
```response title=Response
┌─emptyArrayToSingle(a)─┬─emptyArrayToSingle(b)─┬─emptyArrayToSingle(c)───┐
│ [0] │ [''] │ ['1970-01-01 00:00:00'] │
└───────────────────────┴───────────────────────┴─────────────────────────┘
```
## emptyArrayUInt16 {#emptyArrayUInt16}
Introduced in: v1.1.0
Returns an empty UInt16 array
**Syntax**
```sql
emptyArrayUInt16()
```
**Arguments**
- None.
**Returned value**
An empty UInt16 array. [`Array(T)`](/reference/data-types/array)
**Examples**
**Usage example**
```sql title=Query
SELECT emptyArrayUInt16()
```
```response title=Response
[]
```
## emptyArrayUInt32 {#emptyArrayUInt32}
Introduced in: v1.1.0
Returns an empty UInt32 array
**Syntax**
```sql
emptyArrayUInt32()
```
**Arguments**
- None.
**Returned value**
An empty UInt32 array. [`Array(T)`](/reference/data-types/array)
**Examples**
**Usage example**
```sql title=Query
SELECT emptyArrayUInt32()
```
```response title=Response
[]
```
## emptyArrayUInt64 {#emptyArrayUInt64}
Introduced in: v1.1.0
Returns an empty UInt64 array
**Syntax**
```sql
emptyArrayUInt64()
```
**Arguments**
- None.
**Returned value**
An empty UInt64 array. [`Array(T)`](/reference/data-types/array)
**Examples**
**Usage example**
```sql title=Query
SELECT emptyArrayUInt64()
```
```response title=Response
[]
```
## emptyArrayUInt8 {#emptyArrayUInt8}
Introduced in: v1.1.0
Returns an empty UInt8 array
**Syntax**
```sql
emptyArrayUInt8()
```
**Arguments**
- None.
**Returned value**
An empty UInt8 array. [`Array(T)`](/reference/data-types/array)
**Examples**
**Usage example**
```sql title=Query
SELECT emptyArrayUInt8()
```
```response title=Response
[]
```
## has {#has}
Introduced in: v1.1.0
Returns whether the array contains the specified element, the map contains the specified key, or the JSON object contains the specified path.
For JSON, nested paths are supported using dot notation (e.g., 'a.b.c').
When the first argument is a constant array and the second argument is a column or expression, `has(constant_array, column)` behaves like `column IN (constant_array)` and can use primary key and data-skipping indexes for optimization. For example, `has([1, 10, 100], id)` can leverage the primary key index if `id` is part of the `PRIMARY KEY`.
This optimization also applies when the column is wrapped in monotonic functions (e.g., `has([...], toDate(ts))`).
**Syntax**
```sql
has(haystack, needle)
```
**Arguments**
- `haystack` — The source array, map, or JSON. [`Array`](/reference/data-types/array) or [`Map`](/reference/data-types/map) or [`JSON`](/reference/data-types/newjson)
- `needle` — The value to search for (element in array, key in map, or path string in JSON).
**Returned value**
Returns `1` if the haystack contains the specified needle, otherwise `0`. [`UInt8`](/reference/data-types/int-uint)
**Examples**
**Array basic usage**
```sql title=Query
SELECT has([1, 2, 3], 2)
```
```response title=Response
1
```
**Array not found**
```sql title=Query
SELECT has([1, 2, 3], 4)
```
```response title=Response
0
```
**Map basic usage**
```sql title=Query
SELECT has(map('a', 1, 'b', 2), 'b')
```
```response title=Response
1
```
**JSON path**
```sql title=Query
SELECT has('{"a": {"b": 1}}'::JSON, 'a.b')
```
```response title=Response
1
```
## hasAll {#hasAll}
Introduced in: v1.1.0
Checks whether one array is a subset of another.
- An empty array is a subset of any array.
- `Null` is processed as a value.
- The order of values in both the arrays does not matter.
**Syntax**
```sql
hasAll(set, subset)
```
**Arguments**
- `set` — Array of any type with a set of elements. [`Array(T)`](/reference/data-types/array)
- `subset` — Array of any type that shares a common supertype with `set` containing elements that should be tested to be a subset of `set`. [`Array(T)`](/reference/data-types/array)
**Returned value**
- `1`, if `set` contains all of the elements from `subset`.
- `0`, otherwise.
Raises a `NO_COMMON_TYPE` exception if the set and subset elements do not share a common supertype.
**Examples**
**Empty arrays**
```sql title=Query
SELECT hasAll([], [])
```
```response title=Response
1
```
**Arrays containing NULL values**
```sql title=Query
SELECT hasAll([1, Null], [Null])
```
```response title=Response
1
```
**Arrays containing values of a different type**
```sql title=Query
SELECT hasAll([1.0, 2, 3, 4], [1, 3])
```
```response title=Response
1
```
**Arrays containing String values**
```sql title=Query
SELECT hasAll(['a', 'b'], ['a'])
```
```response title=Response
1
```
**Arrays without a common type**
```sql title=Query
SELECT hasAll([1], ['a'])
```
```response title=Response
Received exception:
Code: 386. DB::Exception: There is no supertype for types UInt8, String because some of them are String/FixedString/Enum and some of them are not. (NO_COMMON_TYPE)
```
**Array of arrays**
```sql title=Query
SELECT hasAll([[1, 2], [3, 4]], [[1, 2], [3, 5]])
```
```response title=Response
0
```
## hasAny {#hasAny}
Introduced in: v1.1.0
Checks whether two arrays have intersection by some elements.
- `Null` is processed as a value.
- The order of the values in both of the arrays does not matter.
**Syntax**
```sql
hasAny(arr_x, arr_y)
```
**Arguments**
- `arr_x` — Array of any type with a set of elements. [`Array(T)`](/reference/data-types/array)
- `arr_y` — Array of any type that shares a common supertype with array `arr_x`. [`Array(T)`](/reference/data-types/array)
**Returned value**
- `1`, if `arr_x` and `arr_y` have one similar element at least.
- `0`, otherwise.
Raises a `NO_COMMON_TYPE` exception if any of the elements of the two arrays do not share a common supertype.
**Examples**
**One array is empty**
```sql title=Query
SELECT hasAny([1], [])
```
```response title=Response
0
```
**Arrays containing NULL values**
```sql title=Query
SELECT hasAny([Null], [Null, 1])
```
```response title=Response
1
```
**Arrays containing values of a different type**
```sql title=Query
SELECT hasAny([-128, 1., 512], [1])
```
```response title=Response
1
```
**Arrays without a common type**
```sql title=Query
SELECT hasAny([[1, 2], [3, 4]], ['a', 'c'])
```
```response title=Response
Received exception:
Code: 386. DB::Exception: There is no supertype for types Array(UInt8), String because some of them are Array and some of them are not. (NO_COMMON_TYPE)
```
**Array of arrays**
```sql title=Query
SELECT hasAll([[1, 2], [3, 4]], [[1, 2], [1, 2]])
```
```response title=Response
1
```
## hasSubstr {#hasSubstr}
Introduced in: v20.6.0
Checks whether all the elements of array2 appear in a array1 in the same exact order.
Therefore, the function will return `1`, if and only if array1 = prefix + array2 + suffix.
In other words, the functions will check whether all the elements of array2 are contained in array1 like the `hasAll` function.
In addition, it will check that the elements are observed in the same order in both array1 and array2.
- The function will return `1` if array2 is empty.
- `Null` is processed as a value. In other words `hasSubstr([1, 2, NULL, 3, 4], [2,3])` will return `0`. However, `hasSubstr([1, 2, NULL, 3, 4], [2,NULL,3])` will return `1`
- The order of values in both the arrays does matter.
Raises a `NO_COMMON_TYPE` exception if any of the elements of the two arrays do not share a common supertype.
**Syntax**
```sql
hasSubstr(arr1, arr2)
```
**Arguments**
- `arr1` — Array of any type with a set of elements. [`Array(T)`](/reference/data-types/array)
- `arr2` — Array of any type with a set of elements. [`Array(T)`](/reference/data-types/array)
**Returned value**
Returns `1` if array `arr1` contains array `arr2`. Otherwise, returns `0`. [`UInt8`](/reference/data-types/int-uint)
**Examples**
**Both arrays are empty**
```sql title=Query
SELECT hasSubstr([], [])
```
```response title=Response
1
```
**Arrays containing NULL values**
```sql title=Query
SELECT hasSubstr([1, Null], [Null])
```
```response title=Response
1
```
**Arrays containing values of a different type**
```sql title=Query
SELECT hasSubstr([1.0, 2, 3, 4], [1, 3])
```
```response title=Response
0
```
**Arrays containing strings**
```sql title=Query
SELECT hasSubstr(['a', 'b'], ['a'])
```
```response title=Response
1
```
**Arrays with valid ordering**
```sql title=Query
SELECT hasSubstr(['a', 'b' , 'c'], ['a', 'b'])
```
```response title=Response
1
```
**Arrays with invalid ordering**
```sql title=Query
SELECT hasSubstr(['a', 'b' , 'c'], ['a', 'c'])
```
```response title=Response
0
```
**Array of arrays**
```sql title=Query
SELECT hasSubstr([[1, 2], [3, 4], [5, 6]], [[1, 2], [3, 4]])
```
```response title=Response
1
```
**Arrays without a common type**
```sql title=Query
SELECT hasSubstr([1, 2, NULL, 3, 4], ['a'])
```
```response title=Response
Received exception:
Code: 386. DB::Exception: There is no supertype for types UInt8, String because some of them are String/FixedString/Enum and some of them are not. (NO_COMMON_TYPE)
```
## indexOf {#indexOf}
Introduced in: v1.1.0
Returns the index of the first element with value 'x' (starting from 1) if it is in the array.
If the array does not contain the searched-for value, the function returns `0`.
Elements set to `NULL` are handled as normal values.
`NaN` elements do not follow the `NULL` rule above.
A scalar comparison of two `NaN` values (`NaN = NaN`) is `0`, but `indexOf` may still report a `NaN` element as found.
Whether it does is an implementation detail. Do not rely on a specific behavior.
To find one, test for it: `arrayFirstIndex(x -> isNaN(x), arr)` returns its position, `arrayExists(x -> isNaN(x), arr)` a flag.
**Syntax**
```sql
indexOf(arr, x)
```
**Arguments**
- `arr` — An array to search in for `x`. [`Array(T)`](/reference/data-types/array)
- `x` — Value of the first matching element in `arr` for which to return an index. [`UInt64`](/reference/data-types/int-uint)
**Returned value**
Returns the index (numbered from one) of the first `x` in `arr` if it exists. Otherwise, returns `0`. [`UInt64`](/reference/data-types/int-uint)
**Examples**
**Basic example**
```sql title=Query
SELECT indexOf([5, 4, 1, 3], 3)
```
```response title=Response
4
```
**Array with nulls**
```sql title=Query
SELECT indexOf([1, 3, NULL, NULL], NULL)
```
```response title=Response
3
```
## indexOfAssumeSorted {#indexOfAssumeSorted}
Introduced in: v24.12.0
Returns the index of the first element with value 'x' (starting from `1`) if it is in the array.
If the array does not contain the searched-for value, the function returns `0`.
<Note>
Unlike the `indexOf` function, this function assumes that the array is sorted in
ascending order. If the array is not sorted, results are undefined.
</Note>
**Syntax**
```sql
indexOfAssumeSorted(arr, x)
```
**Arguments**
- `arr` — A sorted array to search. [`Array(T)`](/reference/data-types/array)
- `x` — Value of the first matching element in sorted `arr` for which to return an index. [`UInt64`](/reference/data-types/int-uint)
**Returned value**
Returns the index (numbered from one) of the first `x` in `arr` if it exists. Otherwise, returns `0`. [`UInt64`](/reference/data-types/int-uint)
**Examples**
**Basic example**
```sql title=Query
SELECT indexOfAssumeSorted([1, 3, 3, 3, 4, 4, 5], 4)
```
```response title=Response
5
```
## length {#length}
Introduced in: v1.1.0
Calculates the length of a string or array.
- For String or FixedString arguments: calculates the number of bytes in the string.
- For Array arguments: calculates the number of elements in the array.
- For QBit arguments: calculates the dimension of the vector.
- If applied to a FixedString or QBit argument, the function is a constant expression.
Please note that the number of bytes in a string is not the same as the number of
Unicode "code points" and it is not the same as the number of Unicode "grapheme clusters"
(what we usually call "characters") and it is not the same as the visible string width.
It is ok to have ASCII NULL bytes in strings, and they will be counted as well.
**Syntax**
```sql
length(x)
```
**Aliases**: `CARDINALITY`, `OCTET_LENGTH`
**Arguments**
- `x` — Value for which to calculate the number of bytes (for String/FixedString), elements (for Array), or the dimension (for QBit). [`String`](/reference/data-types/string) or [`FixedString`](/reference/data-types/fixedstring) or [`Array(T)`](/reference/data-types/array) or [`QBit`](/reference/data-types/qbit)
**Returned value**
Returns the number of bytes in the String/FixedString `x`, the number of elements in array `x`, or the dimension of the QBit `x` [`UInt64`](/reference/data-types/int-uint)
**Examples**
**String example**
```sql title=Query
SELECT length('Hello, world!')
```
```response title=Response
13
```
**Array example**
```sql title=Query
SELECT length(['Hello', 'world'])
```
```response title=Response
2
```
**QBit example**
```sql title=Query
SELECT length([0, 0, 0, 0, 0, 0, 0, 0]::QBit(Float32, 8))
```
```response title=Response
8
```
**constexpr example**
```sql title=Query
WITH 'hello' || toString(number) AS str
SELECT str,
isConstant(length(str)) AS str_length_is_constant,
isConstant(length(str::FixedString(6))) AS fixed_str_length_is_constant
FROM numbers(3)
```
```response title=Response
┌─str────┬─str_length_is_constant─┬─fixed_str_length_is_constant─┐
│ hello0 │ 0 │ 1 │
│ hello1 │ 0 │ 1 │
│ hello2 │ 0 │ 1 │
└────────┴────────────────────────┴──────────────────────────────┘
```
**unicode example**
```sql title=Query
SELECT 'ёлка' AS str1, length(str1), lengthUTF8(str1), normalizeUTF8NFKD(str1) AS str2, length(str2), lengthUTF8(str2)
```
```response title=Response
┌─str1─┬─length(str1)─┬─lengthUTF8(str1)─┬─str2─┬─length(str2)─┬─lengthUTF8(str2)─┐
│ ёлка │ 8 │ 4 │ ёлка │ 10 │ 5 │
└──────┴──────────────┴──────────────────┴──────┴──────────────┴──────────────────┘
```
**ascii_vs_utf8 example**
```sql title=Query
SELECT 'ábc' AS str, length(str), lengthUTF8(str)
```
```response title=Response
┌─str─┬─length(str)─┬─lengthUTF8(str)─┐
│ ábc │ 4 │ 3 │
└─────┴─────────────┴─────────────────┘
```
## notEmpty {#notEmpty}
Introduced in: v1.1.0
Checks whether the input array is non-empty.
An array is considered non-empty if it contains at least one element.
<Note>
Can be optimized by enabling the [`optimize_functions_to_subcolumns`](/reference/settings/session-settings/optimize#optimize_functions_to_subcolumns) setting. With `optimize_functions_to_subcolumns = 1` the function reads only [size0](/reference/data-types/array#array-size) subcolumn instead of reading and processing the whole array column. The query `SELECT notEmpty(arr) FROM table` transforms to `SELECT arr.size0 != 0 FROM TABLE`.
</Note>
The function also works for Strings or UUIDs.
**Syntax**
```sql
notEmpty(arr)
```
**Arguments**
- `arr` — Input array. [`Array(T)`](/reference/data-types/array)
**Returned value**
Returns `1` for a non-empty array or `0` for an empty array [`UInt8`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query
SELECT notEmpty([1,2]);
```
```response title=Response
1
```
## notHas {#notHas}
Introduced in: v26.8.0
Returns whether the array does not contain the specified element, the map does not contain the specified key, or the JSON object does not contain the specified path. The negation of `has`.
**Syntax**
```sql
notHas(haystack, needle)
```
**Arguments**
- `haystack` — The source array, map, or JSON. [`Array`](/reference/data-types/array) or [`Map`](/reference/data-types/map) or [`JSON`](/reference/data-types/newjson)
- `needle` — The value to search for (element in array, key in map, or path string in JSON).
**Returned value**
Returns `1` if the haystack does not contain the specified needle, otherwise `0`. [`UInt8`](/reference/data-types/int-uint)
**Examples**
**Array basic usage**
```sql title=Query
SELECT notHas([1, 2, 3], 2)
```
```response title=Response
0
```
**Array not found**
```sql title=Query
SELECT notHas([1, 2, 3], 4)
```
```response title=Response
1
```
**Map basic usage**
```sql title=Query
SELECT notHas(map('a', 1, 'b', 2), 'c')
```
```response title=Response
1
```
**JSON basic usage**
```sql title=Query
SELECT notHas('{"a" : 1, "b" : {"c" : 2}}'::JSON, 'b.c')
```
```response title=Response
0
```
## randomHadamardTransform {#randomHadamardTransform}
Introduced in: v26.7.0
Applies a randomized Hadamard transform to a float vector: `y = (1/sqrt(k)) * (H * D * x)`, where
`D` is a diagonal matrix of deterministic +/-1 signs chosen by `seed` and `H` is the Walsh-Hadamard
transform.
The transform is an orthogonal, **norm-preserving** rotation that spreads a vector's energy evenly
across coordinates, making the per-coordinate distribution approximately Gaussian and
data-independent. It is useful as a preprocessing step before scalar quantization, and -- when
truncated -- as a Johnson-Lindenstrauss / subsampled-randomized-Hadamard (SRHT) random projection.
The full transform (no truncation) keeps the input length. It is exact for the following lengths:
- a power of two (a plain fast Walsh-Hadamard transform);
- `2^k * m` with `m` in `{12, 20}` (orders that have a `+/-1` Hadamard matrix, e.g. `768 = 64 * 12`,
`1536 = 128 * 12`, `3072 = 256 * 12`, `2560 = 128 * 20`), applied as the exact Kronecker transform
`H_(2^k) (x) H_m` (no float multiplies);
- `2^k * m` with any other odd `m` up to `64` (orders that have no `+/-1` Hadamard matrix -- those
exist only for orders `1`, `2`, and multiples of `4`), applied as the exact Kronecker transform
`H_(2^k) (x) C_m`, where `C_m` is a real orthogonal Discrete Hartley Transform matrix. This covers
the remaining embedding families such as `3584 = 512 * 7`, `1152 = 128 * 9`, `1408 = 128 * 11`.
A full transform of a length whose largest odd factor exceeds `64` has no exact form and would need
zero-padding to a longer power of two; it is **rejected with an exception** rather than silently
returning a longer vector. Use a supported length, or pass `output_dims` to compute a truncated
projection of an arbitrary length (see below).
The result has the same element type as the input; an empty input array returns an empty array
(`output_dims` does not apply to it).
- `seed` (optional, default `0`): selects the sign pattern; the same seed always yields the same
transform.
- `output_dims` (optional, default the transform length): keeps only the first `output_dims`
coordinates, turning the transform into a random projection that accepts **any** length. The
`1/sqrt(output_dims)` scaling keeps the result norm-preserving for the full transform and
norm-preserving in expectation when truncated. It must not exceed the transform length. Because a
truncated prefix of the Discrete Hartley factor `C_m` does not have the uniform leverage of a
`+/-1` Hadamard matrix, a truncating `output_dims` on a Hartley-family length (or on any length
without an exact factor) uses the zero-padded power-of-two transform, which does keep that property.
**Syntax**
```sql
randomHadamardTransform(vector[, seed[, output_dims]])
```
**Arguments**
- `vector` — Vector to transform. [`Array(BFloat16)`](/reference/data-types/array) or [`Array(Float32)`](/reference/data-types/array) or [`Array(Float64)`](/reference/data-types/array)
- `seed` — Optional. Seed for the deterministic +/-1 signs (default 0). [`UInt*`](/reference/data-types/int-uint)
- `output_dims` — Optional. Truncate the result to this many leading coordinates (default: the full transform length, which equals the input length for every supported dimension). Passing output_dims also enables the transform for lengths that have no exact full-transform form. [`UInt*`](/reference/data-types/int-uint)
**Returned value**
The transformed vector (same element type as the input). Its length is the transform length (the input length for a full transform of a supported dimension), optionally truncated to output_dims. [`Array(BFloat16)`](/reference/data-types/array) or [`Array(Float32)`](/reference/data-types/array) or [`Array(Float64)`](/reference/data-types/array)
**Examples**
**Full transform of a power-of-two length**
```sql title=Query
SELECT length(randomHadamardTransform([1, 2, 3, 4]::Array(Float32)))
```
```response title=Response
4
```
**Norm is preserved**
```sql title=Query
SELECT round(arraySum(x -> x * x, randomHadamardTransform([1, 2, 3, 4]::Array(Float32))) - 30, 4)
```
```response title=Response
0
```
**Truncated projection to 3 dimensions (accepts any length)**
```sql title=Query
SELECT length(randomHadamardTransform([1, 2, 3, 4, 5, 6, 7, 8]::Array(Float32), 42, 3))
```
```response title=Response
3
```
**Exact transform of a 2^k * m dimension keeps the input length (no padding)**
```sql title=Query
SELECT length(randomHadamardTransform(CAST(range(1152), 'Array(Float32)')))
```
```response title=Response
1152
```
## range {#range}
Introduced in: v1.1.0
Returns an array of numbers from `start` to `end - 1` by `step`.
The supported types are:
- `UInt8/16/32/64`
- `Int8/16/32/64]`
- All arguments `start`, `end`, `step` must be one of the above supported types. Elements of the returned array will be a super type of the arguments.
- An exception is thrown if the function returns an array with a total length more than the number of elements specified by setting [`function_range_max_elements_in_block`](/reference/settings/session-settings/function#function_range_max_elements_in_block).
- Returns `NULL` if any argument has Nullable(nothing) type. An exception is thrown if any argument has `NULL` value (Nullable(T) type).
**Syntax**
```sql
range([start, ] end [, step])
```
**Arguments**
- `start` — Optional. The first element of the array. Required if `step` is used. Default value: `0`. - `end` — Required. The number before which the array is constructed. - `step` — Optional. Determines the incremental step between each element in the array. Default value: `1`.
**Returned value**
Array of numbers from `start` to `end - 1` by `step`. [`Array(T)`](/reference/data-types/array)
**Examples**
**Usage example**
```sql title=Query
SELECT range(5), range(1, 5), range(1, 5, 2), range(-1, 5, 2);
```
```response title=Response
┌─range(5)────┬─range(1, 5)─┬─range(1, 5, 2)─┬─range(-1, 5, 2)─┐
│ [0,1,2,3,4] │ [1,2,3,4] │ [1,3] │ [-1,1,3] │
└─────────────┴─────────────┴────────────────┴─────────────────┘
```
## replicate {#replicate}
Introduced in: v1.1.0
Creates an array with a single value.
**Syntax**
```sql
replicate(x, arr)
```
**Arguments**
- `x` — The value to fill the result array with. [`Any`](/reference/data-types/index)
- `arr` — An array. [`Array(T)`](/reference/data-types/array)
**Returned value**
Returns an array of the same length as `arr` filled with value `x`. [`Array(T)`](/reference/data-types/array)
**Examples**
**Usage example**
```sql title=Query
SELECT replicate(1, ['a', 'b', 'c']);
```
```response title=Response
┌─replicate(1, ['a', 'b', 'c'])─┐
│ [1,1,1] │
└───────────────────────────────┘
```
## reverse {#reverse}
Introduced in: v1.1.0
Reverses the order of the elements in the input array or the characters in the input string.
**Syntax**
```sql
reverse(arr | str)
```
**Arguments**
- `arr | str` — The source array or string. [`Array(T)`](/reference/data-types/array) or [`String`](/reference/data-types/string)
**Returned value**
Returns an array or string with the order of elements or characters reversed.
**Examples**
**Reverse array**
```sql title=Query
SELECT reverse([1, 2, 3, 4]);
```
```response title=Response
[4,3,2,1]
```
**Reverse string**
```sql title=Query
SELECT reverse('abcd');
```
```response title=Response
dcba
```
{/*AUTOGENERATED_END*/}
## Distance functions {#distance-functions}
All supported functions are described in [distance functions documentation](/reference/functions/regular-functions/distance-functions).
View Git Blame Copy Permalink