Skip to main content

Documentation Index

Fetch the complete documentation index at: https://private-7c7dfe99-page-updates.mintlify.app/llms.txt

Use this file to discover all available pages before exploring further.

​
Array functions

​
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 
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) Examples Valid usage
Query
SELECT array(toInt32(1), toUInt16(2), toInt8(3)) AS a, toTypeName(a)
Response
┌─a───────┬─toTypeName(a)─┐
│ [1,2,3] │ Array(Int32)  │
└─────────┴───────────────┘
Invalid usage
Query
SELECT array(toInt32(5), toDateTime('1998-06-16'), toInt8(5)) AS a, toTypeName(a)
Response
Received exception from server (version 25.4.3):
Code: 386. DB::Exception: Received from localhost:9000. DB::Exception:
There is no supertype for types Int32, DateTime, Int8 ...

​
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. 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, here and here. Syntax 
arrayAUCPR(scores, labels[, partial_offsets])
Aliases: arrayPRAUC Arguments
  • cores — Scores prediction model gives. Array((U)Int*) or Array(Float*)
  • labels — Labels of samples, usually 1 for positive sample and 0 for negative sample. Array((U)Int*) or Array(Enum)
  • partial_offsets —
  • Optional. An Array(T) 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.
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].
Returned value Returns area under the precision-recall (PR) curve. Float64 Examples Usage example
Query
SELECT arrayAUCPR([0.1, 0.4, 0.35, 0.8], [0, 0, 1, 1]);
Response
┌─arrayAUCPR([0.1, 0.4, 0.35, 0.8], [0, 0, 1, 1])─┐
│                              0.8333333333333333 │
└─────────────────────────────────────────────────┘

​
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 
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
  • source_arr — The source array to process. Array(T)
  • cond1_arr, ... — Optional. N condition arrays providing additional arguments to the lambda function. Array(T)
Returned value Returns 1 if the lambda function returns true for all elements, 0 otherwise UInt8 Examples All elements match
Query
SELECT arrayAll(x, y -> x=y, [1, 2, 3], [1, 2, 3])
Response
1
Not all elements match
Query
SELECT arrayAll(x, y -> x=y, [1, 2, 3], [1, 1, 1])
Response
0

​
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 
arrayAutocorrelation(arr, [max_lag])
Arguments
  • arr — Array of numbers. Array(T)
  • max_lag — Optional. Maximum number of lags to compute. Must be a non-negative integer. Integer
Returned value Returns an array of Float64. Returns NaN if variance is 0. Array(Float64) Examples Linear
Query
SELECT arrayAutocorrelation([1, 2, 3, 4, 5]);
Response
[1, 0.4, -0.1, -0.4, -0.4]
Symmetric
Query
SELECT arrayAutocorrelation([10, 20, 10]);
Response
[1, -0.6666666666666669, 0.16666666666666674]
Constant
Query
SELECT arrayAutocorrelation([5, 5, 5]);
Response
[nan, nan, nan]
Limited
Query
SELECT arrayAutocorrelation([1, 2, 3, 4, 5], 2);
Response
[1, 0.4]

​
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 
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
  • source_arr — The source array to process. Array(T)
  • [, cond1_arr, ... , condN_arr] — Optional. N condition arrays providing additional arguments to the lambda function. Array(T)
Returned value Returns the average of elements in the source array, or the average of elements of the lambda results if provided. Float64 Examples Basic example
Query
SELECT arrayAvg([1, 2, 3, 4]);
Response
2.5
Usage with lambda function
Query
SELECT arrayAvg(x, y -> x*y, [2, 3], [2, 3]) AS res;
Response
6.5

​
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 
arrayCompact(arr)
Arguments
  • arr — An array to remove duplicates from. Array(T)
Returned value Returns an array without duplicate values Array(T) Examples Usage example
Query
SELECT arrayCompact([1, 1, nan, nan, 2, 3, 3, 3]);
Response
[1,nan,2,3]

​
arrayConcat

Introduced in: v1.1.0 Combines arrays passed as arguments. Syntax 
arrayConcat(arr1 [, arr2, ... , arrN])
Arguments
  • arr1 [, arr2, ... , arrN] — N number of arrays to concatenate. Array(T)
Returned value Returns a single combined array from the provided array arguments. Array(T) Examples Usage example
Query
SELECT arrayConcat([1, 2], [3, 4], [5, 6]) AS res
Response
[1, 2, 3, 4, 5, 6]

​
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. Syntax 
arrayCount([func, ] arr1, ...)
Arguments
  • func — Optional. Function to apply to each element of the array(s). Lambda function
  • arr1, ..., arrN — N arrays. Array(T)
Returned value Returns the number of elements for which func returns true. Otherwise, returns the number of non-zero elements in the array. UInt32 Examples Usage example
Query
SELECT arrayCount(x -> (x % 2), groupArray(number)) FROM numbers(10)
Response
5

​
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 
arrayCumSum([func,] arr1[, arr2, ... , arrN])
Arguments
  • func — Optional. A lambda function to apply to the array elements at each position. Lambda function
  • arr1 — The source array of numeric values. Array(T)
  • [arr2, ..., arrN] — Optional. Additional arrays of the same size, passed as arguments to the lambda function if specified. Array(T)
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) Examples Basic usage
Query
SELECT arrayCumSum([1, 1, 1, 1]) AS res
Response
[1, 2, 3, 4]
With lambda
Query
SELECT arrayCumSum(x -> x * 2, [1, 2, 3]) AS res
Response
[2, 6, 12]

​
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 
arrayCumSumNonNegative([func,] arr1[, arr2, ... , arrN])
Arguments
  • func — Optional. A lambda function to apply to the array elements at each position. Lambda function
  • arr1 — The source array of numeric values. Array(T)
  • [arr2, ..., arrN] — Optional. Additional arrays of the same size, passed as arguments to the lambda function if specified. Array(T)
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) Examples Basic usage
Query
SELECT arrayCumSumNonNegative([1, 1, -4, 1]) AS res
Response
[1, 2, 0, 1]
With lambda
Query
SELECT arrayCumSumNonNegative(x -> x * 2, [1, -2, 3]) AS res
Response
[2, 0, 6]

​
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 
arrayDifference(arr)
Arguments
  • arr — Array for which to calculate differences between adjacent elements. Array(T)
Returned value Returns an array of differences between adjacent array elements UInt* Examples Usage example
Query
SELECT arrayDifference([1, 2, 3, 4]);
Response
[0,1,1,1]
Example of overflow due to result type Int64
Query
SELECT arrayDifference([0, 10000000000000000000]);
Response
┌─arrayDifference([0, 10000000000000000000])─┐
│ [0,-8446744073709551616]                   │
└────────────────────────────────────────────┘

​
arrayDistinct

Introduced in: v1.1.0 Returns an array containing only the distinct elements of an array. Syntax 
arrayDistinct(arr)
Arguments
  • arr — Array for which to extract distinct elements. Array(T)
Returned value Returns an array containing the distinct elements Array(T) Examples Usage example
Query
SELECT arrayDistinct([1, 2, 2, 3, 1]);
Response
[1,2,3]

​
arrayDotProduct

Introduced in: v23.5.0 Returns the dot product of two arrays.
The sizes of the two vectors must be equal. Arrays and Tuples may also contain mixed element types.
Syntax 
arrayDotProduct(v1, v2)
Arguments Returned value The dot product of the two vectors.
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.
(U)Int* or Float* or Decimal Examples Array example
Query
SELECT arrayDotProduct([1, 2, 3], [4, 5, 6]) AS res, toTypeName(res);
Response
32    UInt16
Tuple example
Query
SELECT dotProduct((1::UInt16, 2::UInt8, 3::Float32),(4::Int16, 5::Float32, 6::UInt8)) AS res, toTypeName(res);
Response
32    Float64

​
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.
Arrays in ClickHouse are one-indexed.
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. Syntax 
arrayElement(arr, n)
Arguments
  • arr — The array to search. Array(T). - n — Position of the element to get. (U)Int*.
Returned value Returns a single combined array from the provided array arguments Array(T) Examples Usage example
Query
SELECT arrayElement(arr, 2) FROM (SELECT [1, 2, 3] AS arr)
Response
2
Negative indexing
Query
SELECT arrayElement(arr, -1) FROM (SELECT [1, 2, 3] AS arr)
Response
3
Using [n] notation
Query
SELECT arr[2] FROM (SELECT [1, 2, 3] AS arr)
Response
2
Index out of array bounds
Query
SELECT arrayElement(arr, 4) FROM (SELECT [1, 2, 3] AS arr)
Response
0

​
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.
Arrays in ClickHouse are one-indexed.
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. Syntax 
arrayElementOrNull(arrays)
Arguments
  • arrays — Arbitrary number of array arguments. Array
Returned value Returns a single combined array from the provided array arguments. Array(T) Examples Usage example
Query
SELECT arrayElementOrNull(arr, 2) FROM (SELECT [1, 2, 3] AS arr)
Response
2
Negative indexing
Query
SELECT arrayElementOrNull(arr, -1) FROM (SELECT [1, 2, 3] AS arr)
Response
3
Index out of array bounds
Query
SELECT arrayElementOrNull(arr, 4) FROM (SELECT [1, 2, 3] AS arr)
Response
NULL

​
arrayEnumerate

Introduced in: v1.1.0 Returns the array [1, 2, 3, ..., length (arr)] This function is normally used with the 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 
arrayEnumerate(arr)
Arguments
  • arr — The array to enumerate. Array
Returned value Returns the array [1, 2, 3, ..., length (arr)]. Array(UInt32) Examples Basic example with ARRAY JOIN
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
┌─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

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 
arrayEnumerateDense(arr)
Arguments
  • arr — The array to enumerate. Array(T)
Returned value Returns an array of the same size as arr, indicating where each element first appears in the source array Array(T) Examples Usage example
Query
SELECT arrayEnumerateDense([10, 20, 10, 30])
Response
[1,2,1,3]

​
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 
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*
  • arr — N-dimensional array to enumerate. Array(T)
  • max_array_depth — The maximum effective depth. Must be less than or equal to the depth of arr. UInt*
Returned value Returns an array denoting where each element first appears in the source array Array Examples Basic usage
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
[1,2,1,3]
Usage with a multidimensional array
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
[[1,1,2,3],[4,5,1,2]]
Example with increased clear_depth
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
[[1, 1, 2, 3], [1, 2, 3, 4]]

​
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 
arrayEnumerateUniq(arr1[, arr2, ... , arrN])
Arguments
  • arr1 — First array to process. Array(T)
  • arr2, ... — Optional. Additional arrays of the same size for tuple uniqueness. Array(UInt32)
Returned value Returns an array where each element is the position among elements with the same value or tuple. Array(T) Examples Basic usage
Query
SELECT arrayEnumerateUniq([10, 20, 10, 30]);
Response
[1, 1, 2, 1]
Multiple arrays
Query
SELECT arrayEnumerateUniq([1, 1, 1, 2, 2, 2], [1, 1, 2, 1, 1, 2]);
Response
[1,2,1,1,2,1]
ARRAY JOIN aggregation
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.

SELECT
    Goals.ID AS GoalID,
    sum(Sign) AS Reaches,
    sumIf(Sign, num = 1) AS Visits
FROM test.visits
ARRAY JOIN
    Goals,
    arrayEnumerateUniq(Goals.ID) AS num
WHERE CounterID = 160656
GROUP BY GoalID
ORDER BY Reaches DESC
LIMIT 10
Response
┌──GoalID─┬─Reaches─┬─Visits─┐
│   53225 │    3214 │   1097 │
│ 2825062 │    3188 │   1097 │
│   56600 │    2803 │    488 │
│ 1989037 │    2401 │    365 │
│ 2830064 │    2396 │    910 │
│ 1113562 │    2372 │    373 │
│ 3270895 │    2262 │    812 │
│ 1084657 │    2262 │    345 │
│   56599 │    2260 │    799 │
│ 3271094 │    2256 │    812 │
└─────────┴─────────┴────────┘

​
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 
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*
  • arr — N-dimensional array to enumerate. Array(T)
  • max_array_depth — The maximum effective depth. Positive integer less than or equal to the depth of arr. 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) Examples Example 1
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
[1, 1, 2]
Example 2
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);", "[[1, 1, 1], [2, 3, 2], [2]]
Response
[1, 1, 2]
Example 3
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
[[1, 1, 1], [2, 3, 2], [2]]
Example 4
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
[[1, 1, 1], [1, 2, 1], [1]]

​
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 
arrayExcept(source, except)
Arguments
  • source — The source array containing elements to filter. Array(T)
  • except — The array containing elements to exclude from the result. Array(T)
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) Examples basic
Query
SELECT arrayExcept([1, 2, 3, 2, 4], [3, 5])
Response
[1, 2, 2, 4]
with_nulls1
Query
SELECT arrayExcept([1, NULL, 2, NULL], [2])
Response
[1, NULL, NULL]
with_nulls2
Query
SELECT arrayExcept([1, NULL, 2, NULL], [NULL, 2, NULL])
Response
[1]
strings
Query
SELECT arrayExcept(['apple', 'banana', 'cherry'], ['banana', 'date'])
Response
['apple', 'cherry']

​
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 
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
  • source_arr — The source array to process. Array(T)
  • [, cond1_arr, ... , condN_arr] — Optional. N condition arrays providing additional arguments to the lambda function. Array(T)
Returned value Returns 1 if the lambda function returns true for at least one element, 0 otherwise UInt8 Examples Usage example
Query
SELECT arrayExists(x, y -> x=y, [1, 2, 3], [0, 0, 0])
Response
0

​
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 
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
  • source_arr — The source array to process. Lambda function
  • [, cond1_arr, ... , condN_arr] — Optional. N condition arrays providing additional arguments to the lambda function. Array(T)
Returned value Returns an array Array(T) Examples Example with single array
Query
SELECT arrayFill(x -> not isNull(x), [1, null, 2, null]) AS res
Response
[1, 1, 2, 2]
Example with two arrays
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
[5, 5, 6, 6]

​
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 
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
  • source_arr — The source array to process. Array(T)
  • [, cond1_arr, ... , condN_arr] — Optional. N condition arrays providing additional arguments to the lambda function. Array(T)
Returned value Returns a subset of the source array Array(T) Examples Example 1
Query
SELECT arrayFilter(x -> x LIKE '%World%', ['Hello', 'abc World']) AS res
Response
['abc World']
Example 2
Query
SELECT
    arrayFilter(
        (i, x) -> x LIKE '%World%',
        arrayEnumerate(arr),
        ['Hello', 'abc World'] AS arr)
    AS res
Response
[2]

​
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 
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. - source_arr — The source array to process. Array(T). - [, cond1_arr, ... , condN_arr] — Optional. N condition arrays providing additional arguments to the lambda function. Array(T).
Returned value Returns the first element of the source array for which λ is true, otherwise returns the default value of T. Examples Usage example
Query
SELECT arrayFirst(x, y -> x=y, ['a', 'b', 'c'], ['c', 'b', 'a'])
Response
b
No match
Query
SELECT arrayFirst(x, y -> x=y, [0, 1, 2], [3, 3, 3]) AS res, toTypeName(res)
Response
0 UInt8

​
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 
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. - source_arr — The source array to process. Array(T). - [, cond1_arr, ... , condN_arr] — Optional. N condition arrays providing additional arguments to the lambda function. Array(T).
Returned value Returns the index of the first element of the source array for which func is true, otherwise returns 0 UInt32 Examples Usage example
Query
SELECT arrayFirstIndex(x, y -> x=y, ['a', 'b', 'c'], ['c', 'b', 'a'])
Response
2
No match
Query
SELECT arrayFirstIndex(x, y -> x=y, ['a', 'b', 'c'], ['d', 'e', 'f'])
Response
0

​
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 
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
  • source_arr — The source array to process. Array(T)
  • [, cond1_arr, ... , condN_arr] — Optional. N condition arrays providing additional arguments to the lambda function. Array(T)
Returned value Returns the first element of the source array for which func is true, otherwise returns NULL. Examples Usage example
Query
SELECT arrayFirstOrNull(x, y -> x=y, ['a', 'b', 'c'], ['c', 'b', 'a'])
Response
b
No match
Query
SELECT arrayFirstOrNull(x, y -> x=y, [0, 1, 2], [3, 3, 3]) AS res, toTypeName(res)
Response
NULL Nullable(UInt8)

​
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 
arrayFlatten(arr)
Aliases: flatten Arguments Returned value Returns a flattened array from the multidimensional array Array(T) Examples Usage example
Query
SELECT arrayFlatten([[[1]], [[2], [3]]]);
Response
[1, 2, 3]

​
arrayFold

Introduced in: v23.10.0 Applies a lambda function to one or more equally-sized arrays and collects the result in an accumulator. Syntax 
arrayFold(λ(acc, x1 [, x2, x3, ... xN]), arr1 [, arr2, arr3, ... arrN], acc)
Arguments
  • λ(x, x1 [, x2, x3, ... xN]) — A lambda function λ(acc, x1 [, x2, x3, ... xN]) → F(acc, x1 [, x2, x3, ... xN]) where F is an operation applied to acc and array values from x with the result of acc re-used. Lambda function
  • arr1 [, arr2, arr3, ... arrN] — N arrays over which to operate. Array(T)
  • 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
Query
SELECT arrayFold(acc,x -> acc + x*2, [1, 2, 3, 4], 3::Int64) AS res;
Response
23
Fibonacci sequence
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
┌─fibonacci─┐
│         0 │
│         1 │
│         1 │
│         2 │
│         3 │
│         5 │
│         8 │
│        13 │
│        21 │
│        34 │
└───────────┘
Example using multiple arrays
Query
SELECT arrayFold(
(acc, x, y) -> acc + (x * y),
[1, 2, 3, 4],
[10, 20, 30, 40],
0::Int64
) AS res;
Response
300

​
arrayIntersect

Introduced in: v1.1.0 Takes multiple arrays and returns an array with elements which are present in all source arrays. The result contains only unique values. Syntax 
arrayIntersect(arr, arr1, ..., arrN)
Arguments
  • arrN — N arrays from which to make the new array. Array(T).
Returned value Returns an array with distinct elements that are present in all N arrays Array(T) Examples Usage example
Query
SELECT
arrayIntersect([1, 2], [1, 3], [2, 3]) AS empty_intersection,
arrayIntersect([1, 2], [1, 3], [1, 4]) AS non_empty_intersection
Response
┌─empty_intersection─┬─non_empty_intersection─┐
│ []                 │ [1]                    │
└────────────────────┴────────────────────────┘

​
arrayJaccardIndex

Introduced in: v23.7.0 Returns the Jaccard index of two arrays. Syntax 
arrayJaccardIndex(arr_x, arr_y)
Arguments Returned value Returns the Jaccard index of arr_x and arr_y Float64 Examples Usage example
Query
SELECT arrayJaccardIndex([1, 2], [2, 3]) AS res
Response
0.3333333333333333

​
arrayJoin

Introduced in: v1.1.0 The arrayJoin function takes a row that contains an array and unfolds it, generating multiple rows – one for each element in the array. This is in contrast to Regular Functions in ClickHouse which map input values to output values within the same row, and Aggregate Functions which take a group of rows and “compress” or “reduce” them into a single summary row (or a single value within a summary row if used with GROUP BY). All the values in the columns are simply copied, except the values in the column where this function is applied; these are replaced with the corresponding array value. Syntax 
arrayJoin(arr)
Arguments Returned value Returns a set of rows unfolded from arr. Examples Basic usage
Query
SELECT arrayJoin([1, 2, 3] AS src) AS dst, 'Hello', src
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
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
┌─impressions─┐
│           2 │
└─────────────┘
Using multiple arrayJoin functions
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
Response
┌─impressions─┬─city─────┬─browser─┐
│           2 │ Istanbul │ Chrome  │
│           1 │ Istanbul │ Firefox │
│           2 │ Berlin   │ Chrome  │
│           1 │ Berlin   │ Firefox │
│           2 │ Bobruisk │ Chrome  │
│           1 │ Bobruisk │ Firefox │
└─────────────┴──────────┴─────────┘
Unexpected results due to optimizations
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
┌─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
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
Response
┌─impressions─┬─city─────┬─browser─┐
│           1 │ Istanbul │ Firefox │
│           1 │ Berlin   │ Chrome  │
│           1 │ Bobruisk │ Chrome  │
└─────────────┴──────────┴─────────┘
Using Tuple
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
Response
┌─impressions─┬─city─────┬─browser─┐
│           1 │ Istanbul │ Firefox │
│           1 │ Berlin   │ Chrome  │
│           1 │ Bobruisk │ Chrome  │
└─────────────┴──────────┴─────────┘

​
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 
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. - source — The source array to process. Array(T). - [, cond1, ... , condN] — Optional. N condition arrays providing additional arguments to the lambda function. Array(T).
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
Query
SELECT arrayLast(x, y -> x=y, ['a', 'b', 'c'], ['a', 'b', 'c'])
Response
c
No match
Query
SELECT arrayFirst(x, y -> x=y, [0, 1, 2], [3, 3, 3]) AS res, toTypeName(res)
Response
0 UInt8

​
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 
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
  • source_arr — The source array to process. Array(T)
  • [, cond1_arr, ... , condN_arr] — Optional. N condition arrays providing additional arguments to the lambda function. Array(T)
Returned value Returns the index of the last element of the source array for which func is true, otherwise returns 0 UInt32 Examples Usage example
Query
SELECT arrayLastIndex(x, y -> x=y, ['a', 'b', 'c'], ['a', 'b', 'c']);
Response
3
No match
Query
SELECT arrayLastIndex(x, y -> x=y, ['a', 'b', 'c'], ['d', 'e', 'f']);
Response
0

​
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 
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. - source_arr — The source array to process. Array(T). - [, cond1_arr, ... , condN_arr] — Optional. N condition arrays providing additional arguments to the lambda function. Array(T).
Returned value Returns the last element of the source array for which λ is not true, otherwise returns NULL. Examples Usage example
Query
SELECT arrayLastOrNull(x, y -> x=y, ['a', 'b', 'c'], ['a', 'b', 'c'])
Response
c
No match
Query
SELECT arrayLastOrNull(x, y -> x=y, [0, 1, 2], [3, 3, 3]) AS res, toTypeName(res)
Response
NULL Nullable(UInt8)

​
arrayLevenshteinDistance

Introduced in: v25.4.0 Calculates the Levenshtein distance for two arrays. Syntax 
arrayLevenshteinDistance(from, to)
Arguments Returned value Levenshtein distance between the first and the second arrays. Float64 Examples Usage example
Query
SELECT arrayLevenshteinDistance([1, 2, 4], [1, 2, 3])
Response
1

​
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 
arrayLevenshteinDistanceWeighted(from, to, from_weights, to_weights)
Arguments Returned value Levenshtein distance between the first and the second arrays with custom weights for each element Float64 Examples Usage example
Query
SELECT arrayLevenshteinDistanceWeighted(['A', 'B', 'C'], ['A', 'K', 'L'], [1.0, 2, 3], [3.0, 4, 5])
Response
14

​
arrayMap

Introduced in: v1.1.0 Returns an array obtained from the original arrays by applying a lambda function to each element. Syntax 
arrayMap(func, arr)
Arguments
  • func — A lambda function which operates on elements of the source array (x) and condition arrays (y). Lambda function
  • arr — N arrays to process. Array(T)
Returned value Returns an array from the lambda results Array(T) Examples Usage example
Query
SELECT arrayMap(x -> (x + 2), [1, 2, 3]) as res;
Response
[3, 4, 5]
Creating a tuple of elements from different arrays
Query
SELECT arrayMap((x, y) -> (x, y), [1, 2, 3], [4, 5, 6]) AS res
Response
[(1, 4),(2, 5),(3, 6)]

​
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 
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
  • source_arr — The source array to process. Array(T)
  • [, cond1_arr, ... , condN_arr] — Optional. N condition arrays providing additional arguments to the lambda function. Array(T)
Returned value Returns the maximum element in the source array, or the maximum element of the lambda results if provided. Examples Basic example
Query
SELECT arrayMax([5, 3, 2, 7]);
Response
7
Usage with lambda function
Query
SELECT arrayMax(x, y -> x/y, [4, 8, 12, 16], [1, 2, 1, 2]);
Response
12

​
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 
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
  • source_arr — The source array to process. Array(T)
  • cond1_arr, ... — Optional. N condition arrays providing additional arguments to the lambda function. Array(T)
Returned value Returns the minimum element in the source array, or the minimum element of the lambda results if provided. Examples Basic example
Query
SELECT arrayMin([5, 3, 2, 7]);
Response
2
Usage with lambda function
Query
SELECT arrayMin(x, y -> x/y, [4, 8, 12, 16], [1, 2, 1, 2]);
Response
4

​
arrayNormalizedGini

Introduced in: v25.1.0 Calculates the normalized Gini coefficient. Syntax 
arrayNormalizedGini(predicted, label)
Arguments
  • predicted — The predicted value. Array(T)
  • label — The actual value. Array(T)
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) Examples Usage example
Query
SELECT arrayNormalizedGini([0.9, 0.3, 0.8, 0.7],[6, 1, 0, 2]);
Response
(0.18055555555555558, 0.2638888888888889, 0.6842105263157896)

​
arrayPartialReverseSort

Introduced in: v23.2.0 This function is the same as arrayReverseSort but with an additional limit argument allowing partial sorting.
To retain only the sorted elements use arrayResize.
Syntax 
arrayPartialReverseSort([f,] arr [, arr1, ... ,arrN], limit)
Arguments
  • f(arr[, arr1, ... ,arrN]) — The lambda function to apply to elements of array x. Lambda function
  • arr — Array to be sorted. Array(T)
  • arr1, ... ,arrN — N additional arrays, in the case when f accepts multiple arguments. Array(T)
  • limit — Index value up until which sorting will occur. (U)Int*
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
Query
SELECT arrayPartialReverseSort(2, [5, 9, 1, 3])
Response
[9, 5, 1, 3]
simple_string
Query
SELECT arrayPartialReverseSort(2, ['expenses','lasso','embolism','gladly'])
Response
['lasso','gladly','expenses','embolism']
retain_sorted
Query
SELECT arrayResize(arrayPartialReverseSort(2, [5, 9, 1, 3]), 2)
Response
[9, 5]
lambda_simple
Query
SELECT arrayPartialReverseSort((x) -> -x, 2, [5, 9, 1, 3])
Response
[1, 3, 5, 9]
lambda_complex
Query
SELECT arrayPartialReverseSort((x, y) -> -y, 1, [0, 1, 2], [1, 2, 3]) as res
Response
[0, 1, 2]

​
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:
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.
Syntax 
arrayPartialShuffle(arr [, limit[, seed]])
Arguments
  • arr — The array to shuffle. Array(T)
  • seed — Optional. The seed to be used with random number generation. If not provided, a random one is used. (U)Int*
  • limit — Optional. The number to limit element swaps to, in the range [1..N]. (U)Int*
Returned value Array with elements partially shuffled. Array(T) Examples no_limit1
Query
SELECT arrayPartialShuffle([1, 2, 3, 4], 0)
Response
[2, 4, 3, 1]
no_limit2
Query
SELECT arrayPartialShuffle([1, 2, 3, 4])
Response
[4, 1, 3, 2]
random_seed
Query
SELECT arrayPartialShuffle([1, 2, 3, 4], 2)
Response
[3, 4, 1, 2]
explicit_seed
Query
SELECT arrayPartialShuffle([1, 2, 3, 4], 2, 41)
Response
[3, 2, 1, 4]
materialize
Query
SELECT arrayPartialShuffle(materialize([1, 2, 3, 4]), 2, 42), arrayPartialShuffle([1, 2, 3], 2, 42) FROM numbers(10)
Response
┌─arrayPartial⋯4]), 2, 42)─┬─arrayPartial⋯ 3], 2, 42)─┐
│ [3,2,1,4]                │ [3,2,1]                  │
│ [3,2,1,4]                │ [3,2,1]                  │
│ [4,3,2,1]                │ [3,2,1]                  │
│ [1,4,3,2]                │ [3,2,1]                  │
│ [3,4,1,2]                │ [3,2,1]                  │
│ [1,2,3,4]                │ [3,2,1]                  │
│ [1,4,3,2]                │ [3,2,1]                  │
│ [1,4,3,2]                │ [3,2,1]                  │
│ [3,1,2,4]                │ [3,2,1]                  │
│ [1,3,2,4]                │ [3,2,1]                  │
└──────────────────────────┴──────────────────────────┘

​
arrayPartialSort

Introduced in: v23.2.0 This function is the same as arraySort but with an additional limit argument allowing partial sorting.
To retain only the sorted elements use arrayResize.
Syntax 
arrayPartialSort([f,] arr [, arr1, ... ,arrN], limit)
Arguments
  • f(arr[, arr1, ... ,arrN]) — The lambda function to apply to elements of array x. Lambda function
  • arr — Array to be sorted. Array(T)
  • arr1, ... ,arrN — N additional arrays, in the case when f accepts multiple arguments. Array(T)
  • limit — Index value up until which sorting will occur. (U)Int*
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
Query
SELECT arrayPartialSort(2, [5, 9, 1, 3])
Response
[1, 3, 5, 9]
simple_string
Query
SELECT arrayPartialSort(2, ['expenses', 'lasso', 'embolism', 'gladly'])
Response
['embolism', 'expenses', 'gladly', 'lasso']
retain_sorted
Query
SELECT arrayResize(arrayPartialSort(2, [5, 9, 1, 3]), 2)
Response
[1, 3]
lambda_simple
Query
SELECT arrayPartialSort((x) -> -x, 2, [5, 9, 1, 3])
Response
[9, 5, 1, 3]
lambda_complex
Query
SELECT arrayPartialSort((x, y) -> -y, 1, [0, 1, 2], [1, 2, 3]) as res
Response
[2, 1, 0]

​
arrayPopBack

Introduced in: v1.1.0 Removes the last element from the array. Syntax 
arrayPopBack(arr)
Arguments
  • arr — The array for which to remove the last element from. Array(T)
Returned value Returns an array identical to arr but without the last element of arr Array(T) Examples Usage example
Query
SELECT arrayPopBack([1, 2, 3]) AS res;
Response
[1, 2]

​
arrayPopFront

Introduced in: v1.1.0 Removes the first item from the array. Syntax 
arrayPopFront(arr)
Arguments
  • arr — The array for which to remove the first element from. Array(T)
Returned value Returns an array identical to arr but without the first element of arr Array(T) Examples Usage example
Query
SELECT arrayPopFront([1, 2, 3]) AS res;
Response
[2, 3]

​
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 
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
  • source_arr — The source array to process. Array(T)
  • [, cond1_arr, ... , condN_arr] — Optional. N condition arrays providing additional arguments to the lambda function. Array(T)
Returned value Returns the product of elements in the source array, or the product of elements of the lambda results if provided. Float64 Examples Basic example
Query
SELECT arrayProduct([1, 2, 3, 4]);
Response
24
Usage with lambda function
Query
SELECT arrayProduct(x, y -> x+y, [2, 2], [2, 2]) AS res;
Response
16

​
arrayPushBack

Introduced in: v1.1.0 Adds one item to the end of the array. Syntax 
arrayPushBack(arr, x)
Arguments
  • arr — The array for which to add value x to the end of. Array(T)
  • x —
  • Single value to add to the end of the array. Array(T).
  • 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.
Returned value Returns an array identical to arr but with an additional value x at the end of the array Array(T) Examples Usage example
Query
SELECT arrayPushBack(['a'], 'b') AS res;
Response
['a','b']

​
arrayPushFront

Introduced in: v1.1.0 Adds one element to the beginning of the array. Syntax 
arrayPushFront(arr, x)
Arguments
  • arr — The array for which to add value x to the end of. Array(T). - x —
  • Single value to add to the start of the array. Array(T).
  • 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.
Returned value Returns an array identical to arr but with an additional value x at the beginning of the array Array(T) Examples Usage example
Query
SELECT arrayPushFront(['b'], 'a') AS res;
Response
['a','b']

​
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. The resulting value ranges from zero to one, with a higher value indicating better model performance. The ROC AUC (also known as simply AUC) is a concept in machine learning. For more details, please see here, here and here. Syntax 
arrayROCAUC(scores, labels[, scale[, partial_offsets]])
Aliases: arrayAUC Arguments
  • scores — Scores prediction model gives. Array((U)Int*) or Array(Float*)
  • labels — Labels of samples, usually 1 for positive sample and 0 for negative sample. Array((U)Int*) or 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
  • 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 of non-negative Integers. 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.
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].
Returned value Returns area under the receiver operating characteristic (ROC) curve. Float64 Examples Usage example
Query
SELECT arrayROCAUC([0.1, 0.4, 0.35, 0.8], [0, 0, 1, 1]);
Response
0.75

​
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. Syntax 
arrayRandomSample(arr, samples)
Arguments
  • arr — The input array or multidimensional array from which to sample elements. Array(T)
  • samples — The number of elements to include in the random sample. (U)Int*
Returned value An array containing a random sample of elements from the input array Array(T) Examples Usage example
Query
SELECT arrayRandomSample(['apple', 'banana', 'cherry', 'date'], 2) as res;
Response
['cherry','apple']
Using a multidimensional array
Query
SELECT arrayRandomSample([[1, 2], [3, 4], [5, 6]], 2) as res;
Response
[[3,4],[5,6]]

​
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 
arrayReduce(agg_f, arr1[, arr2, ... , arrN])
Arguments
  • agg_f — The name of an aggregate function which should be a constant. String
  • arr1[, arr2, ... , arrN] — N arrays corresponding to the arguments of agg_f. Array(T)
Returned value Returns the result of the aggregate function Examples Usage example
Query
SELECT arrayReduce('max', [1, 2, 3]);
Response
┌─arrayReduce('max', [1, 2, 3])─┐
│                             3 │
└───────────────────────────────┘
Example with aggregate function using multiple arguments
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
┌─arrayReduce('maxIf', [3, 5], [1, 0])─┐
│                                    3 │
└──────────────────────────────────────┘
Example with a parametric aggregate function
Query
SELECT arrayReduce('uniqUpTo(3)', [1, 2, 3, 4, 5, 6, 7, 8, 9, 10]);
Response
┌─arrayReduce('uniqUpTo(3)', [1, 2, 3, 4, 5, 6, 7, 8, 9, 10])─┐
│                                                           4 │
└─────────────────────────────────────────────────────────────┘

​
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), ...). Syntax 
arrayReduceInRanges(agg_f, ranges, arr1[, arr2, ... ,arrN])
Arguments
  • agg_f — The name of the aggregate function to use. 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) or Tuple(T)
  • arr1[, arr2, ... ,arrN] — N arrays as arguments to the aggregate function. Array(T)
Returned value Returns an array containing results of the aggregate function over the specified ranges Array(T) Examples Usage example
Query
SELECT arrayReduceInRanges(
    'sum',
    [(1, 5), (2, 3), (3, 4), (4, 4)],
    [1000000, 200000, 30000, 4000, 500, 60, 7]
) AS res
Response
┌─res─────────────────────────┐
│ [1234500,234000,34560,4567] │
└─────────────────────────────┘

​
arrayRemove

Introduced in: v25.11.0 Removes all elements equal to a given value from an array. NULLs are treated as equal. Syntax 
arrayRemove(arr, elem)
Aliases: array_remove Arguments
  • arr — Array(T) - elem — T
Returned value Returns a subset of the source array Array(T) Examples Example 1
Query
SELECT arrayRemove([1, 2, 2, 3], 2)
Response
[1, 3]
Example 2
Query
SELECT arrayRemove(['a', NULL, 'b', NULL], NULL)
Response
['a', 'b']

​
arrayResize

Introduced in: v1.1.0 Changes the length of the array. Syntax 
arrayResize(arr, size[, extender])
Arguments
  • arr — Array to resize. Array(T)
  • 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) Examples Example 1
Query
SELECT arrayResize([1], 3);
Response
[1,0,0]
Example 2
Query
SELECT arrayResize([1], 3, NULL);
Response
[1,NULL,NULL]

​
arrayReverse

Introduced in: v1.1.0 Reverses the order of elements of a given array.
Function reverse(arr) performs the same functionality but works on other data-types in addition to Arrays.
Syntax 
arrayReverse(arr)
Arguments
  • arr — The array to reverse. Array(T)
Returned value Returns an array of the same size as the original array containing the elements in reverse order Array(T) Examples Usage example
Query
SELECT arrayReverse([1, 2, 3])
Response
[3,2,1]

​
arrayReverseFill

Introduced in: v20.1.0 The arrayReverseFill function sequentially processes a source array from the last element to the first, evaluating a lambda condition at each position using elements from the source and condition arrays. When the condition evaluates to false at position i, the function replaces that element with the element at position i+1 from the current state of the array. The last element is always preserved regardless of any condition. Syntax 
arrayReverseFill(func(x[, y1, ..., yN]), source_arr[, cond1_arr, ... , condN_arr])
Arguments
  • func(x[, y1, ..., yN]) — A lambda function which operates on elements of the source array (x) and condition arrays (y). Lambda function
  • source_arr — The source array to process. Array(T)
  • [, cond1_arr, ... , condN_arr] — Optional. N condition arrays providing additional arguments to the lambda function. Array(T)
Returned value Returns an array with elements of the source array replaced by the results of the lambda. Array(T) Examples Example with a single array
Query
SELECT arrayReverseFill(x -> not isNull(x), [1, null, 2, null]) AS res
Response
[1, 2, 2, NULL]
Example with two arrays
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
[5, 6, 6, 2]

​
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. Syntax 
arrayReverseSort([f,] arr [, arr1, ... ,arrN)
Arguments
  • f(y1[, y2 ... yN]) — The lambda function to apply to elements of array x. - arr — An array to be sorted. Array(T) - arr1, ..., yN — Optional. N additional arrays, in the case when f accepts multiple arguments.
Returned value Returns the array x sorted in descending order if no lambda function is provided, otherwise it returns an array sorted according to the logic of the provided lambda function, and then reversed. Array(T). Examples Example 1
Query
SELECT arrayReverseSort((x, y) -> y, [4, 3, 5], ['a', 'b', 'c']) AS res;
Response
[5,3,4]
Example 2
Query
SELECT arrayReverseSort((x, y) -> -y, [4, 3, 5], [1, 2, 3]) AS res;
Response
[4,3,5]

​
arrayReverseSplit

Introduced in: v20.1.0 Split a source array into multiple arrays. When func(x[, y1, ..., yN]) returns something other than zero, the array will be split to the right of the element. The array will not be split after the last element. Syntax 
arrayReverseSplit(func(x[, y1, ..., yN]), source_arr[, cond1_arr, ... , condN_arr])
Arguments
  • func(x[, y1, ..., yN]) — A lambda function which operates on elements of the source array (x) and condition arrays (y). Lambda function
  • source_arr — The source array to process. Lambda function
  • [, cond1_arr, ... , condN_arr] — Optional. N condition arrays providing additional arguments to the lambda function. Array(T)
Returned value Returns an array of arrays. Array(Array(T)) Examples Usage example
Query
SELECT arrayReverseSplit((x, y) -> y, [1, 2, 3, 4, 5], [1, 0, 0, 1, 0]) AS res
Response
[[1], [2, 3, 4], [5]]

​
arrayRotateLeft

Introduced in: v23.8.0 Rotates an array to the left by the specified number of elements. Negative values of n are treated as rotating to the right by the absolute value of the rotation. Syntax 
arrayRotateLeft(arr, n)
Arguments Returned value An array rotated to the left by the specified number of elements Array(T) Examples Usage example
Query
SELECT arrayRotateLeft([1,2,3,4,5,6], 2) as res;
Response
[3,4,5,6,1,2]
Negative value of n
Query
SELECT arrayRotateLeft([1,2,3,4,5,6], -2) as res;
Response
[5,6,1,2,3,4]

​
arrayRotateRight

Introduced in: v23.8.0 Rotates an array to the right by the specified number of elements. Negative values of n are treated as rotating to the left by the absolute value of the rotation. Syntax 
arrayRotateRight(arr, n)
Arguments Returned value An array rotated to the right by the specified number of elements Array(T) Examples Usage example
Query
SELECT arrayRotateRight([1,2,3,4,5,6], 2) as res;
Response
[5,6,1,2,3,4]
Negative value of n
Query
SELECT arrayRotateRight([1,2,3,4,5,6], -2) as res;
Response
[3,4,5,6,1,2]

​
arrayShiftLeft

Introduced in: v23.8.0 Shifts an array to the left by the specified number of elements. New elements are filled with the provided argument or the default value of the array element type. If the number of elements is negative, the array is shifted to the right. Syntax 
arrayShiftLeft(arr, n[, default])
Arguments
  • arr — The array for which to shift the elements.Array(T). - n — Number of elements to shift.(U)Int8/16/32/64. - default — Optional. Default value for new elements.
Returned value An array shifted to the left by the specified number of elements Array(T) Examples Usage example
Query
SELECT arrayShiftLeft([1,2,3,4,5,6], 2) as res;
Response
[3,4,5,6,0,0]
Negative value of n
Query
SELECT arrayShiftLeft([1,2,3,4,5,6], -2) as res;
Response
[0,0,1,2,3,4]
Using a default value
Query
SELECT arrayShiftLeft([1,2,3,4,5,6], 2, 42) as res;
Response
[3,4,5,6,42,42]

​
arrayShiftRight

Introduced in: v23.8.0 Shifts an array to the right by the specified number of elements. New elements are filled with the provided argument or the default value of the array element type. If the number of elements is negative, the array is shifted to the left. Syntax 
arrayShiftRight(arr, n[, default])
Arguments
  • arr — The array for which to shift the elements. Array(T)
  • n — Number of elements to shift. (U)Int8/16/32/64
  • default — Optional. Default value for new elements.
Returned value An array shifted to the right by the specified number of elements Array(T) Examples Usage example
Query
SELECT arrayShiftRight([1, 2, 3, 4, 5, 6], 2) as res;
Response
[0, 0, 1, 2, 3, 4]
Negative value of n
Query
SELECT arrayShiftRight([1, 2, 3, 4, 5, 6], -2) as res;
Response
[3, 4, 5, 6, 0, 0]
Using a default value
Query
SELECT arrayShiftRight([1, 2, 3, 4, 5, 6], 2, 42) as res;
Response
[42, 42, 1, 2, 3, 4]

​
arrayShingles

Introduced in: v24.1.0 Generates an array of shingles (similar to ngrams for strings), i.e. consecutive sub-arrays with a specified length of the input array. Syntax 
arrayShingles(arr, l)
Arguments
  • arr — Array for which to generate an array of shingles. Array(T)
  • l — The length of each shingle. (U)Int*
Returned value An array of generated shingles Array(T) Examples Usage example
Query
SELECT arrayShingles([1, 2, 3, 4], 3) as res;
Response
[[1, 2, 3], [2, 3, 4]]

​
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.
This function will not materialize constants.
Syntax 
arrayShuffle(arr [, seed])
Arguments
  • arr — The array to shuffle. Array(T)
  • seed (optional) — Optional. The seed to be used with random number generation. If not provided a random one is used. (U)Int*
Returned value Array with elements shuffled Array(T) Examples Example without seed (unstable results)
Query
SELECT arrayShuffle([1, 2, 3, 4]);
Response
[1,4,2,3]
Example without seed (stable results)
Query
SELECT arrayShuffle([1, 2, 3, 4], 41);
Response
[3,2,1,4]

​
arraySimilarity

Introduced in: v25.4.0 Calculates the similarity of two arrays from 0 to 1 based on weighted Levenshtein distance. Syntax 
arraySimilarity(from, to, from_weights, to_weights)
Arguments Returned value Returns the similarity between 0 and 1 of the two arrays based on the weighted Levenshtein distance Float64 Examples Usage example
Query
SELECT arraySimilarity(['A', 'B', 'C'], ['A', 'K', 'L'], [1.0, 2, 3], [3.0, 4, 5]);
Response
0.2222222222222222

​
arraySlice

Introduced in: v1.1.0 Returns a slice of the array, with NULL elements included. Syntax 
arraySlice(arr, offset [, length])
Arguments
  • arr — Array to slice. Array(T)
  • 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*
  • 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*
Returned value Returns a slice of the array with length elements from the specified offset Array(T) Examples Usage example
Query
SELECT arraySlice([1, 2, NULL, 4, 5], 2, 3) AS res;
Response
[2, NULL, 4]

​
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. Syntax 
arraySort([f,] arr [, arr1, ... ,arrN])
Arguments
  • f(y1[, y2 ... yN]) — The lambda function to apply to elements of array x. - arr — An array to be sorted. Array(T) - arr1, ..., yN — Optional. N additional arrays, in the case when f accepts multiple arguments.
Returned value Returns the array arr sorted in ascending order if no lambda function is provided, otherwise it returns an array sorted according to the logic of the provided lambda function. Array(T). Examples Example 1
Query
SELECT arraySort([1, 3, 3, 0]);
Response
[0,1,3,3]
Example 2
Query
SELECT arraySort(['hello', 'world', '!']);
Response
['!','hello','world']
Example 3
Query
SELECT arraySort([1, nan, 2, NULL, 3, nan, -4, NULL, inf, -inf]);
Response
[-inf,-4,1,2,3,inf,nan,nan,NULL,NULL]

​
arraySplit

Introduced in: v20.1.0 Split a source array into multiple arrays. When func(x [, y1, ..., yN]) returns something other than zero, the array will be split to the left of the element. The array will not be split before the first element. Syntax 
arraySplit(func(x[, y1, ..., yN]), source_arr[, cond1_arr, ... , condN_arr])
Arguments
  • func(x[, y1, ..., yN]) — A lambda function which operates on elements of the source array (x) and condition arrays (y).Lambda function. - source_arr — The source array to split Array(T). - [, cond1_arr, ... , condN_arr] — Optional. N condition arrays providing additional arguments to the lambda function. Array(T).
Returned value Returns an array of arrays Array(Array(T)) Examples Usage example
Query
SELECT arraySplit((x, y) -> y, [1, 2, 3, 4, 5], [1, 0, 0, 1, 0]) AS res
Response
[[1, 2, 3], [4, 5]]

​
arraySum

Introduced in: v21.1.0 Returns the sum of elements in the source array. If a lambda function func is specified, returns the sum of elements of the lambda results. Syntax 
arraySum([func(x[, y1, ..., yN])], source_arr[, cond1_arr, ... , condN_arr])
Arguments
  • func(x[, y1, ..., yN]) — Optional. A lambda function which operates on elements of the source array (x) and condition arrays (y). Lambda function
  • source_arr — The source array to process. Array(T)
  • , cond1_arr, ... , condN_arr] — Optional. N condition arrays providing additional arguments to the lambda function. Array(T)
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
Query
SELECT arraySum([1, 2, 3, 4]);
Response
10
Usage with lambda function
Query
SELECT arraySum(x, y -> x+y, [1, 1, 1, 1], [1, 1, 1, 1]);
Response
8

​
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.
The symmetric difference of more than two sets is mathematically defined as the set of all input elements which occur in an odd number of input sets. In contrast, function arraySymmetricDifference simply returns the set of input elements which do not occur in all input sets.
Syntax 
arraySymmetricDifference(arr1, arr2, ... , arrN)
Arguments
  • arrN — N arrays from which to make the new array. Array(T).
Returned value Returns an array of distinct elements not present in all source arrays Array(T) Examples Usage example
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
┌─empty_symmetric_difference─┬─non_empty_symmetric_difference─┐
│ []                         │ [3,2]                          │
└────────────────────────────┴────────────────────────────────┘

​
arrayTranspose

Introduced in: v26.4.0 Transposes a two-dimensional array. All inner arrays must have the same length. Syntax 
arrayTranspose(arr)
Arguments
  • arr — A two-dimensional array to transpose. All inner arrays must have the same length. Array(Array(T))
Returned value A transposed two-dimensional array where element [i][j] of the result equals element [j][i] of the input. Array(Array(T)) Examples Square matrix
Query
SELECT arrayTranspose([[1, 2], [3, 4]])
Response
[[1, 3], [2, 4]]
Non-square matrix
Query
SELECT arrayTranspose([[1, 2, 3], [4, 5, 6]])
Response
[[1, 4], [2, 5], [3, 6]]
String elements
Query
SELECT arrayTranspose([['a', 'b'], ['c', 'd']])
Response
[['a', 'c'], ['b', 'd']]

​
arrayUnion

Introduced in: v24.10.0 Takes multiple arrays and returns an array which contains all elements that are present in one of the source arrays.The result contains only unique values. Syntax 
arrayUnion(arr1, arr2, ..., arrN)
Arguments
  • arrN — N arrays from which to make the new array. Array(T)
Returned value Returns an array with distinct elements from the source arrays Array(T) Examples Usage example
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
┌─num_example─┬─str_example────┬─null_example─┐
│ [10,-2,1]   │ ['hello','hi'] │ [3,2,1,NULL] │
└─────────────┴────────────────┴──────────────┘

​
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.
If you want to get a list of unique items in an array, you can use arrayReduce('groupUniqArray', arr).
Syntax 
arrayUniq(arr1[, arr2, ..., arrN])
Arguments
  • arr1 — Array for which to count the number of unique elements. Array(T)
  • [, arr2, ..., arrN] — Optional. Additional arrays used to count the number of unique tuples of elements at corresponding positions in multiple arrays. Array(T)
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 Examples Single argument
Query
SELECT arrayUniq([1, 1, 2, 2])
Response
2
Multiple argument
Query
SELECT arrayUniq([1, 2, 3, 1], [4, 5, 6, 4])
Response
3

​
arrayWithConstant

Introduced in: v20.1.0 Creates an array of length length filled with the constant x. Syntax 
arrayWithConstant(N, x)
Arguments
  • length — Number of elements in the array. (U)Int*
  • 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) Examples Usage example
Query
SELECT arrayWithConstant(3, 1)
Response
[1, 1, 1]

​
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 
arrayZip(arr1, arr2, ... , arrN)
Arguments
  • arr1, arr2, ... , arrN — N arrays to combine into a single array. Array(T)
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) Examples Usage example
Query
SELECT arrayZip(['a', 'b', 'c'], [5, 2, 1]);
Response
[('a', 5), ('b', 2), ('c', 1)]

​
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 
arrayZipUnaligned(arr1, arr2, ..., arrN)
Arguments
  • arr1, arr2, ..., arrN — N arrays to combine into a single array. Array(T)
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) or Tuple(T1, T2, ...) Examples Usage example
Query
SELECT arrayZipUnaligned(['a'], [1, 2, 3]);
Response
[('a', 1),(NULL, 2),(NULL, 3)]

​
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 
countEqual(arr, x)
Arguments
  • arr — Array to search. Array(T)
  • x — Value in the array to count. Any type.
Returned value Returns the number of elements in the array equal to x UInt64 Examples Usage example
Query
SELECT countEqual([1, 2, NULL, NULL], NULL)
Response
2

​
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.
Can be optimized by enabling the optimize_functions_to_subcolumns setting. With optimize_functions_to_subcolumns = 1 the function reads only size0 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;.
The function also works for Strings or UUIDs. Syntax 
empty(arr)
Arguments Returned value Returns 1 for an empty array or 0 for a non-empty array UInt8 Examples Usage example
Query
SELECT empty([]);
Response
1

​
emptyArrayDate

Introduced in: v1.1.0 Returns an empty Date array Syntax 
emptyArrayDate()
Arguments
  • None.
Returned value An empty Date array. Array(T) Examples Usage example
Query
SELECT emptyArrayDate
Response
[]

​
emptyArrayDateTime

Introduced in: v1.1.0 Returns an empty DateTime array Syntax 
emptyArrayDateTime()
Arguments
  • None.
Returned value An empty DateTime array. Array(T) Examples Usage example
Query
SELECT emptyArrayDateTime
Response
[]

​
emptyArrayFloat32

Introduced in: v1.1.0 Returns an empty Float32 array Syntax 
emptyArrayFloat32()
Arguments
  • None.
Returned value An empty Float32 array. Array(T) Examples Usage example
Query
SELECT emptyArrayFloat32
Response
[]

​
emptyArrayFloat64

Introduced in: v1.1.0 Returns an empty Float64 array Syntax 
emptyArrayFloat64()
Arguments
  • None.
Returned value An empty Float64 array. Array(T) Examples Usage example
Query
SELECT emptyArrayFloat64
Response
[]

​
emptyArrayInt16

Introduced in: v1.1.0 Returns an empty Int16 array Syntax 
emptyArrayInt16()
Arguments
  • None.
Returned value An empty Int16 array. Array(T) Examples Usage example
Query
SELECT emptyArrayInt16
Response
[]

​
emptyArrayInt32

Introduced in: v1.1.0 Returns an empty Int32 array Syntax 
emptyArrayInt32()
Arguments
  • None.
Returned value An empty Int32 array. Array(T) Examples Usage example
Query
SELECT emptyArrayInt32
Response
[]

​
emptyArrayInt64

Introduced in: v1.1.0 Returns an empty Int64 array Syntax 
emptyArrayInt64()
Arguments
  • None.
Returned value An empty Int64 array. Array(T) Examples Usage example
Query
SELECT emptyArrayInt64
Response
[]

​
emptyArrayInt8

Introduced in: v1.1.0 Returns an empty Int8 array Syntax 
emptyArrayInt8()
Arguments
  • None.
Returned value An empty Int8 array. Array(T) Examples Usage example
Query
SELECT emptyArrayInt8
Response
[]

​
emptyArrayString

Introduced in: v1.1.0 Returns an empty String array Syntax 
emptyArrayString()
Arguments
  • None.
Returned value An empty String array. Array(T) Examples Usage example
Query
SELECT emptyArrayString
Response
[]

​
emptyArrayToSingle

Introduced in: v1.1.0 Accepts an empty array and returns a one-element array that is equal to the default value. Syntax 
emptyArrayToSingle(arr)
Arguments Returned value An array with a single value of the Array’s default type. Array(T) Examples Basic example
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
┌─emptyArrayToSingle(a)─┬─emptyArrayToSingle(b)─┬─emptyArrayToSingle(c)───┐
│ [0]                   │ ['']                  │ ['1970-01-01 01:00:00'] │
└───────────────────────┴───────────────────────┴─────────────────────────┘

​
emptyArrayUInt16

Introduced in: v1.1.0 Returns an empty UInt16 array Syntax 
emptyArrayUInt16()
Arguments
  • None.
Returned value An empty UInt16 array. Array(T) Examples Usage example
Query
SELECT emptyArrayUInt16
Response
[]

​
emptyArrayUInt32

Introduced in: v1.1.0 Returns an empty UInt32 array Syntax 
emptyArrayUInt32()
Arguments
  • None.
Returned value An empty UInt32 array. Array(T) Examples Usage example
Query
SELECT emptyArrayUInt32
Response
[]

​
emptyArrayUInt64

Introduced in: v1.1.0 Returns an empty UInt64 array Syntax 
emptyArrayUInt64()
Arguments
  • None.
Returned value An empty UInt64 array. Array(T) Examples Usage example
Query
SELECT emptyArrayUInt64
Response
[]

​
emptyArrayUInt8

Introduced in: v1.1.0 Returns an empty UInt8 array Syntax 
emptyArrayUInt8()
Arguments
  • None.
Returned value An empty UInt8 array. Array(T) Examples Usage example
Query
SELECT emptyArrayUInt8
Response
[]

​
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 
has(haystack, needle)
Arguments
  • haystack — The source array, map, or JSON. Array or Map or JSON
  • 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 Examples Array basic usage
Query
SELECT has([1, 2, 3], 2)
Response
1
Array not found
Query
SELECT has([1, 2, 3], 4)
Response
0
Map basic usage
Query
SELECT has(map('a', 1, 'b', 2), 'b')
Response
1
JSON path
Query
SELECT has('{"a": {"b": 1}}'::JSON, 'a.b')
Response
1

​
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 
hasAll(set, subset)
Arguments
  • set — Array of any type with a set of elements. Array(T)
  • 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)
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
Query
SELECT hasAll([], [])
Response
1
Arrays containing NULL values
Query
SELECT hasAll([1, Null], [Null])
Response
1
Arrays containing values of a different type
Query
SELECT hasAll([1.0, 2, 3, 4], [1, 3])
Response
1
Arrays containing String values
Query
SELECT hasAll(['a', 'b'], ['a'])
Response
1
Arrays without a common type
Query
SELECT hasAll([1], ['a'])
Response
Raises a NO_COMMON_TYPE exception
Array of arrays
Query
SELECT hasAll([[1, 2], [3, 4]], [[1, 2], [3, 5]])
Response
0

​
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 
hasAny(arr_x, arr_y)
Arguments
  • arr_x — Array of any type with a set of elements. Array(T)
  • arr_y — Array of any type that shares a common supertype with array arr_x. Array(T)
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
Query
SELECT hasAny([1], [])
Response
0
Arrays containing NULL values
Query
SELECT hasAny([Null], [Null, 1])
Response
1
Arrays containing values of a different type
Query
SELECT hasAny([-128, 1., 512], [1])
Response
1
Arrays without a common type
Query
SELECT hasAny([[1, 2], [3, 4]], ['a', 'c'])
Response
Raises a `NO_COMMON_TYPE` exception
Array of arrays
Query
SELECT hasAll([[1, 2], [3, 4]], [[1, 2], [1, 2]])
Response
1

​
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 
hasSubstr(arr1, arr2)
Arguments
  • arr1 — Array of any type with a set of elements. Array(T)
  • arr2 — Array of any type with a set of elements. Array(T)
Returned value Returns 1 if array arr1 contains array arr2. Otherwise, returns 0. UInt8 Examples Both arrays are empty
Query
SELECT hasSubstr([], [])
Response
1
Arrays containing NULL values
Query
SELECT hasSubstr([1, Null], [Null])
Response
1
Arrays containing values of a different type
Query
SELECT hasSubstr([1.0, 2, 3, 4], [1, 3])
Response
0
Arrays containing strings
Query
SELECT hasSubstr(['a', 'b'], ['a'])
Response
1
Arrays with valid ordering
Query
SELECT hasSubstr(['a', 'b' , 'c'], ['a', 'b'])
Response
1
Arrays with invalid ordering
Query
SELECT hasSubstr(['a', 'b' , 'c'], ['a', 'c'])
Response
0
Array of arrays
Query
SELECT hasSubstr([[1, 2], [3, 4], [5, 6]], [[1, 2], [3, 4]])
Response
1
Arrays without a common type
Query
SELECT hasSubstr([1, 2, NULL, 3, 4], ['a'])
Response
Raises a `NO_COMMON_TYPE` exception

​
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. Syntax 
indexOf(arr, x)
Arguments
  • arr — An array to search in for x. Array(T)
  • x — Value of the first matching element in arr for which to return an index. UInt64
Returned value Returns the index (numbered from one) of the first x in arr if it exists. Otherwise, returns 0. UInt64 Examples Basic example
Query
SELECT indexOf([5, 4, 1, 3], 3)
Response
4
Array with nulls
Query
SELECT indexOf([1, 3, NULL, NULL], NULL)
Response
3

​
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.
Unlike the indexOf function, this function assumes that the array is sorted in ascending order. If the array is not sorted, results are undefined.
Syntax 
indexOfAssumeSorted(arr, x)
Arguments
  • arr — A sorted array to search. Array(T)
  • x — Value of the first matching element in sorted arr for which to return an index. UInt64
Returned value Returns the index (numbered from one) of the first x in arr if it exists. Otherwise, returns 0. UInt64 Examples Basic example
Query
SELECT indexOfAssumeSorted([1, 3, 3, 3, 4, 4, 5], 4)
Response
5

​
kql_array_sort_asc

Introduced in: v23.10.0 Sorts one or more arrays in ascending order. The first array is sorted, and subsequent arrays are reordered to match the first array’s sorted order. Null values are placed at the end. This is a KQL (Kusto Query Language) compatibility function. Syntax 
kql_array_sort_asc(array1[, array2, ..., nulls_last])
Arguments
  • array1 — The array to sort. Array(T)
  • array2 — Optional. Additional arrays to reorder according to array1’s sort order. Array(T)
  • nulls_last — Optional. A boolean indicating whether nulls should appear last. Default is true. UInt8
Returned value Returns a tuple of arrays sorted in ascending order. Tuple(Array, ...) Examples Basic usage
Query
SELECT kql_array_sort_asc([3, 1, 2])
Response
([1, 2, 3])

​
kql_array_sort_desc

Introduced in: v23.10.0 Sorts one or more arrays in descending order. The first array is sorted, and subsequent arrays are reordered to match the first array’s sorted order. Null values are placed at the end. This is a KQL (Kusto Query Language) compatibility function. Syntax 
kql_array_sort_desc(array1[, array2, ..., nulls_last])
Arguments
  • array1 — The array to sort. Array(T)
  • array2 — Optional additional arrays to reorder according to array1’s sort order. Array(T)
  • nulls_last — Optional boolean indicating whether nulls should appear last. Default is true. UInt8
Returned value Returns a tuple of arrays sorted in descending order. Tuple(Array, ...) Examples Basic usage
Query
SELECT kql_array_sort_desc([3, 1, 2])
Response
([3, 2, 1])

​
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.
  • If applied to a FixedString 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 
length(x)
Aliases: OCTET_LENGTH Arguments
  • x — Value for which to calculate the number of bytes (for String/FixedString) or elements (for Array). String or FixedString or Array(T)
Returned value Returns the number of number of bytes in the String/FixedString x / the number of elements in array x UInt64 Examples String example
Query
SELECT length('Hello, world!')
Response
13
Array example
Query
SELECT length(['Hello', 'world'])
Response
2
constexpr example
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
┌─str────┬─str_length_is_constant─┬─fixed_str_length_is_constant─┐
│ hello0 │                      0 │                            1 │
│ hello1 │                      0 │                            1 │
│ hello2 │                      0 │                            1 │
└────────┴────────────────────────┴──────────────────────────────┘
unicode example
Query
SELECT 'ёлка' AS str1, length(str1), lengthUTF8(str1), normalizeUTF8NFKD(str1) AS str2, length(str2), lengthUTF8(str2)
Response
┌─str1─┬─length(str1)─┬─lengthUTF8(str1)─┬─str2─┬─length(str2)─┬─lengthUTF8(str2)─┐
│ ёлка │            8 │                4 │ ёлка │           10 │                5 │
└──────┴──────────────┴──────────────────┴──────┴──────────────┴──────────────────┘
ascii_vs_utf8 example
Query
SELECT 'ábc' AS str, length(str), lengthUTF8(str)
Response
┌─str─┬─length(str)──┬─lengthUTF8(str)─┐
│ ábc │            4 │               3 │
└─────┴──────────────┴─────────────────┘

​
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.
Can be optimized by enabling the optimize_functions_to_subcolumns setting. With optimize_functions_to_subcolumns = 1 the function reads only size0 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.
The function also works for Strings or UUIDs. Syntax 
notEmpty(arr)
Arguments Returned value Returns 1 for a non-empty array or 0 for an empty array UInt8 Examples Usage example
Query
SELECT notEmpty([1,2]);
Response
1

​
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.
  • Returns NULL if any argument has Nullable(nothing) type. An exception is thrown if any argument has NULL value (Nullable(T) type).
Syntax 
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) Examples Usage example
Query
SELECT range(5), range(1, 5), range(1, 5, 2), range(-1, 5, 2);
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

Introduced in: v1.1.0 Creates an array with a single value. Syntax 
replicate(x, arr)
Arguments
  • x — The value to fill the result array with. Any
  • arr — An array. Array(T)
Returned value Returns an array of the same length as arr filled with value x. Array(T) Examples Usage example
Query
SELECT replicate(1, ['a', 'b', 'c']);
Response
┌─replicate(1, ['a', 'b', 'c'])───┐
│ [1, 1, 1]                       │
└─────────────────────────────────┘

​
reverse

Introduced in: v1.1.0 Reverses the order of the elements in the input array or the characters in the input string. Syntax 
reverse(arr | str)
Arguments Returned value Returns an array or string with the order of elements or characters reversed. Examples Reverse array
Query
SELECT reverse([1, 2, 3, 4]);
Response
[4, 3, 2, 1]
Reverse string
Query
SELECT reverse('abcd');
Response
'dcba'

​
Distance functions

All supported functions are described in distance functions documentation.