Array functions

**Examples**

**Usage example**

```
SELECT emptyArrayInt8
```

```
[]
```

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

Introduced in: v1.1.0

Returns an empty String array

**Syntax**

```
emptyArrayString()
```

**Arguments**

- None.

**Returned value**

An empty String array. [`Array(T)`](/docs/sql-reference/data-types/array)

**Examples**

**Usage example**

```
SELECT emptyArrayString
```

```
[]
```

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

- `arr` — An empty array. [`Array(T)`](/docs/sql-reference/data-types/array)

**Returned value**

An array with a single value of the Array's default type. [`Array(T)`](/docs/sql-reference/data-types/array)

**Examples**

**Basic example**

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

```
┌─emptyArrayToSingle(a)─┬─emptyArrayToSingle(b)─┬─emptyArrayToSingle(c)───┐
│ [0]                   │ ['']                  │ ['1970-01-01 01:00:00'] │
└───────────────────────┴───────────────────────┴─────────────────────────┘
```

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

Introduced in: v1.1.0

Returns an empty UInt16 array

**Syntax**

```
emptyArrayUInt16()
```

**Arguments**

- None.

**Returned value**

An empty UInt16 array. [`Array(T)`](/docs/sql-reference/data-types/array)

**Examples**

**Usage example**

```
SELECT emptyArrayUInt16
```

```
[]
```

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

Introduced in: v1.1.0

Returns an empty UInt32 array

**Syntax**

```
emptyArrayUInt32()
```

**Arguments**

- None.

**Returned value**

An empty UInt32 array. [`Array(T)`](/docs/sql-reference/data-types/array)

**Examples**

**Usage example**

```
SELECT emptyArrayUInt32
```

```
[]
```

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

Introduced in: v1.1.0

Returns an empty UInt64 array

**Syntax**

```
emptyArrayUInt64()
```

**Arguments**

- None.

**Returned value**

An empty UInt64 array. [`Array(T)`](/docs/sql-reference/data-types/array)

**Examples**

**Usage example**

```
SELECT emptyArrayUInt64
```

```
[]
```

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

Introduced in: v1.1.0

Returns an empty UInt8 array

**Syntax**

```
emptyArrayUInt8()
```

**Arguments**

- None.

**Returned value**

An empty UInt8 array. [`Array(T)`](/docs/sql-reference/data-types/array)

**Examples**

**Usage example**

```
SELECT emptyArrayUInt8
```

```
[]
```

## has[​](#has "Direct link to 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`](/docs/sql-reference/data-types/array) or [`Map`](/docs/sql-reference/data-types/map) or [`JSON`](/docs/sql-reference/data-types/newjson)
- `needle` — The value to search for (element in array, key in map, or path string in JSON).

**Returned value**

Returns `1` if the haystack contains the specified needle, otherwise `0`. [`UInt8`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Array basic usage**

```
SELECT has([1, 2, 3], 2)
```

```
1
```

**Array not found**

```
SELECT has([1, 2, 3], 4)
```

```
0
```

**Map basic usage**

```
SELECT has(map('a', 1, 'b', 2), 'b')
```

```
1
```

**JSON path**

```
SELECT has('{"a": {"b": 1}}'::JSON, 'a.b')
```

```
1
```

## hasAll[​](#hasAll "Direct link to 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)`](/docs/sql-reference/data-types/array)
- `subset` — Array of any type that shares a common supertype with `set` containing elements that should be tested to be a subset of `set`. [`Array(T)`](/docs/sql-reference/data-types/array)

**Returned value**

- `1`, if `set` contains all of the elements from `subset`.
- `0`, otherwise.

Raises a `NO_COMMON_TYPE` exception if the set and subset elements do not share a common supertype.

**Examples**

**Empty arrays**

```
SELECT hasAll([], [])
```

```
1
```

**Arrays containing NULL values**

```
SELECT hasAll([1, Null], [Null])
```

```
1
```

**Arrays containing values of a different type**

```
SELECT hasAll([1.0, 2, 3, 4], [1, 3])
```

```
1
```

**Arrays containing String values**

```
SELECT hasAll(['a', 'b'], ['a'])
```

```
1
```

**Arrays without a common type**

```
SELECT hasAll([1], ['a'])
```

```
Raises a NO_COMMON_TYPE exception
```

**Array of arrays**

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

```
0
```

## hasAny[​](#hasAny "Direct link to 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)`](/docs/sql-reference/data-types/array)
- `arr_y` — Array of any type that shares a common supertype with array `arr_x`. [`Array(T)`](/docs/sql-reference/data-types/array)

**Returned value**

- `1`, if `arr_x` and `arr_y` have one similar element at least.
- `0`, otherwise.

Raises a `NO_COMMON_TYPE` exception if any of the elements of the two arrays do not share a common supertype.

**Examples**

**One array is empty**

```
SELECT hasAny([1], [])
```

```
0
```

**Arrays containing NULL values**

```
SELECT hasAny([Null], [Null, 1])
```

```
1
```

**Arrays containing values of a different type**

```
SELECT hasAny([-128, 1., 512], [1])
```

```
1
```

**Arrays without a common type**

```
SELECT hasAny([[1, 2], [3, 4]], ['a', 'c'])
```

```
Raises a `NO_COMMON_TYPE` exception
```

**Array of arrays**

```
SELECT hasAll([[1, 2], [3, 4]], [[1, 2], [1, 2]])
```

```
1
```

## hasSubstr[​](#hasSubstr "Direct link to 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)`](/docs/sql-reference/data-types/array)
- `arr2` — Array of any type with a set of elements. [`Array(T)`](/docs/sql-reference/data-types/array)