Array functions

**Returned value**

The dot product of the two vectors.

Note

The return type is determined by the type of the arguments. If Arrays or Tuples contain mixed element types then the result type is the supertype.

[`(U)Int*`](/docs/sql-reference/data-types/int-uint) or [`Float*`](/docs/sql-reference/data-types/float) or [`Decimal`](/docs/sql-reference/data-types/decimal)

**Examples**

**Array example**

```
SELECT arrayDotProduct([1, 2, 3], [4, 5, 6]) AS res, toTypeName(res);
```

```
32    UInt16
```

**Tuple example**

```
SELECT dotProduct((1::UInt16, 2::UInt8, 3::Float32),(4::Int16, 5::Float32, 6::UInt8)) AS res, toTypeName(res);
```

```
32    Float64
```

## arrayElement[​](#arrayElement "Direct link to 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`.

Note

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)`](/docs/sql-reference/data-types/array). - `n` — Position of the element to get. [`(U)Int*`](/docs/sql-reference/data-types/int-uint).

**Returned value**

Returns a single combined array from the provided array arguments [`Array(T)`](/docs/sql-reference/data-types/array)

**Examples**

**Usage example**

```
SELECT arrayElement(arr, 2) FROM (SELECT [1, 2, 3] AS arr)
```

```
2
```

**Negative indexing**

```
SELECT arrayElement(arr, -1) FROM (SELECT [1, 2, 3] AS arr)
```

```
3
```

**Using [n] notation**

```
SELECT arr[2] FROM (SELECT [1, 2, 3] AS arr)
```

```
2
```

**Index out of array bounds**

```
SELECT arrayElement(arr, 4) FROM (SELECT [1, 2, 3] AS arr)
```

```
0
```

## arrayElementOrNull[​](#arrayElementOrNull "Direct link to 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.

Note

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`](/docs/sql-reference/data-types/array)

**Returned value**

Returns a single combined array from the provided array arguments. [`Array(T)`](/docs/sql-reference/data-types/array)

**Examples**

**Usage example**

```
SELECT arrayElementOrNull(arr, 2) FROM (SELECT [1, 2, 3] AS arr)
```

```
2
```

**Negative indexing**

```
SELECT arrayElementOrNull(arr, -1) FROM (SELECT [1, 2, 3] AS arr)
```

```
3
```

**Index out of array bounds**

```
SELECT arrayElementOrNull(arr, 4) FROM (SELECT [1, 2, 3] AS arr)
```

```
NULL
```

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

Introduced in: v1.1.0

Returns the array `[1, 2, 3, ..., length (arr)]`

This function is normally used with the [`ARRAY JOIN`](/docs/sql-reference/statements/select/array-join) clause. It allows counting something just
once for each array after applying `ARRAY JOIN`.
This function can also be used in higher-order functions. For example, you can use it to get array indexes for elements that match a condition.

**Syntax**

```
arrayEnumerate(arr)
```

**Arguments**

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

**Returned value**

Returns the array `[1, 2, 3, ..., length (arr)]`. [`Array(UInt32)`](/docs/sql-reference/data-types/array)

**Examples**

**Basic example with ARRAY JOIN**

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

```
┌─id─┬─tag────────────┬─version─────┬─seq─┐
│  1 │ release-stable │ 2.4.0       │   1 │
│  1 │ dev            │ 2.6.0-alpha │   2 │
│  1 │ security       │ 2.4.0-sec1  │   3 │
└────┴────────────────┴─────────────┴─────┘
```

## arrayEnumerateDense[​](#arrayEnumerateDense "Direct link to 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)`](/docs/sql-reference/data-types/array)

**Returned value**

Returns an array of the same size as `arr`, indicating where each element first appears in the source array [`Array(T)`](/docs/sql-reference/data-types/array)

**Examples**

**Usage example**

```
SELECT arrayEnumerateDense([10, 20, 10, 30])
```

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

## arrayEnumerateDenseRanked[​](#arrayEnumerateDenseRanked "Direct link to 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*`](/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. Must be less than or equal to the depth of `arr`. [`UInt*`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns an array denoting where each element first appears in the source array [`Array`](/docs/sql-reference/data-types/array)

**Examples**

**Basic usage**

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

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

**Usage with a multidimensional array**

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

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