Array functions

SELECT arrayEnumerateDenseRanked(1,[[10,10,30,20],[40,50,10,30]],2);
```

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

**Example with increased clear\_depth**

```
-- 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);
```

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

## arrayEnumerateUniq[​](#arrayEnumerateUniq "Direct link to 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)`](/docs/sql-reference/data-types/array)
- `arr2, ...` — Optional. Additional arrays of the same size for tuple uniqueness. [`Array(UInt32)`](/docs/sql-reference/data-types/array)

**Returned value**

Returns an array where each element is the position among elements with the same value or tuple. [`Array(T)`](/docs/sql-reference/data-types/array)

**Examples**

**Basic usage**

```
SELECT arrayEnumerateUniq([10, 20, 10, 30]);
```

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

**Multiple arrays**

```
SELECT arrayEnumerateUniq([1, 1, 1, 2, 2, 2], [1, 1, 2, 1, 1, 2]);
```

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

**ARRAY JOIN aggregation**

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

```
┌──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[​](#arrayEnumerateUniqRanked "Direct link to 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*`](/docs/sql-reference/data-types/int-uint)
- `arr` — N-dimensional array to enumerate. [`Array(T)`](/docs/sql-reference/data-types/array)
- `max_array_depth` — The maximum effective depth. Positive integer less than or equal to the depth of `arr`. [`UInt*`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns an N-dimensional array the same size as `arr` with each element showing the position of that element in relation to other elements of the same value. [`Array(T)`](/docs/sql-reference/data-types/array)

**Examples**

**Example 1**

```
-- 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);
```

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

**Example 2**

```
-- 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]]
```

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

**Example 3**

```
-- 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);
```

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

**Example 4**

```
-- Changing clear_depth=2, results in elements being enumerated separately for each row.
SELECT arrayEnumerateUniqRanked(2,[[1, 2, 3],[2, 2, 1],[3]], 2);
```

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

## arrayExcept[​](#arrayExcept "Direct link to 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)`](/docs/sql-reference/data-types/array)
- `except` — The array containing elements to exclude from the result. [`Array(T)`](/docs/sql-reference/data-types/array)

**Returned value**

Returns an array of the same type as the input array containing elements from `source` that weren't found in `except`. [`Array(T)`](/docs/sql-reference/data-types/array)

**Examples**

**basic**

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

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

**with\_nulls1**

```
SELECT arrayExcept([1, NULL, 2, NULL], [2])
```

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

**with\_nulls2**

```
SELECT arrayExcept([1, NULL, 2, NULL], [NULL, 2, NULL])
```

```
[1]
```

**strings**

```
SELECT arrayExcept(['apple', 'banana', 'cherry'], ['banana', 'date'])
```

```
['apple', 'cherry']
```

## arrayExists[​](#arrayExists "Direct link to 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`](/docs/sql-reference/functions/overview#arrow-operator-and-lambda)
- `source_arr` — The source array to process. [`Array(T)`](/docs/sql-reference/data-types/array)
- `[, cond1_arr, ... , condN_arr]` — Optional. N condition arrays providing additional arguments to the lambda function. [`Array(T)`](/docs/sql-reference/data-types/array)

**Returned value**

Returns `1` if the lambda function returns true for at least one element, `0` otherwise [`UInt8`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Usage example**

```
SELECT arrayExists(x, y -> x=y, [1, 2, 3], [0, 0, 0])
```

```
0
```