Working with arrays in ClickHouse

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/guides/starter_guides/working_with_arrays.md)

> In this guide, you'll learn how to use arrays in ClickHouse along with some of the most commonly used [array functions](/docs/sql-reference/functions/array-functions).

## Introduction to arrays[​](#array-basics "Direct link to Introduction to arrays")

An array is an in-memory data structure which groups together values.
We call these *elements* of the array, and each element can be referred to by an index, which indicates the position of the element in this grouping.

Arrays in ClickHouse can be formed using the [`array`](/docs/sql-reference/data-types/array) function:

```
array(T)
```

Or alternatively, using `[]`:

```
[]
```

For example, you can create an array of numbers:

```
SELECT array(1, 2, 3) AS numeric_array

┌─numeric_array─┐
│ [1,2,3]       │
└───────────────┘
```

Or an array of strings:

```
SELECT array('hello', 'world') AS string_array

┌─string_array──────┐
│ ['hello','world'] │
└───────────────────┘
```

Or an array of nested types such as [tuples](/docs/sql-reference/data-types/tuple):

```
SELECT array(tuple(1, 2), tuple(3, 4))

┌─[(1, 2), (3, 4)]─┐
│ [(1,2),(3,4)]    │
└──────────────────┘
```

You might be tempted to make an array of different types like this:

```
SELECT array('Hello', 'world', 1, 2, 3)
```

However, array elements should always have a common super-type, which is the smallest data type that can represent values from two or more different types without loss, allowing them to be used together.
If there is no common super-type, you'll get an exception when you try to form the array:

```
Received exception:
Code: 386. DB::Exception: There is no supertype for types String, String, UInt8, UInt8, UInt8 because some of them are String/FixedString/Enum and some of them are not: In scope SELECT ['Hello', 'world', 1, 2, 3]. (NO_COMMON_TYPE)
```

when creating arrays on the fly, ClickHouse picks the narrowest type that fits all elements.
For example, if you create an array of integers and floats, a super-type of float is chosen:

```
SELECT [1::UInt8, 2.5::Float32, 3::UInt8] AS mixed_array, toTypeName([1, 2.5, 3]) AS array_type;

┌─mixed_array─┬─array_type─────┐
│ [1,2.5,3]   │ Array(Float64) │
└─────────────┴────────────────┘
```

Creating arrays of different types

You can use the `use_variant_as_common_type` setting to change the default behavior described above.
This allows you to use the [Variant](/docs/sql-reference/data-types/variant) type as a result type for `if`/`multiIf`/`array`/`map` functions when there is no common type for argument types.

For example:

```
SELECT
    [1, 'ClickHouse', ['Another', 'Array']] AS array,
    toTypeName(array)
SETTINGS use_variant_as_common_type = 1;
```

```
┌─array────────────────────────────────┬─toTypeName(array)────────────────────────────┐
│ [1,'ClickHouse',['Another','Array']] │ Array(Variant(Array(String), String, UInt8)) │
└──────────────────────────────────────┴──────────────────────────────────────────────┘
```

You can then also read the types from the array by type name:

```
SELECT
    [1, 'ClickHouse', ['Another', 'Array']] AS array,
    array.UInt8,
    array.String,
    array.`Array(String)`
SETTINGS use_variant_as_common_type = 1;
```

```
┌─array────────────────────────────────┬─array.UInt8───┬─array.String─────────────┬─array.Array(String)─────────┐
│ [1,'ClickHouse',['Another','Array']] │ [1,NULL,NULL] │ [NULL,'ClickHouse',NULL] │ [[],[],['Another','Array']] │
└──────────────────────────────────────┴───────────────┴──────────────────────────┴─────────────────────────────┘
```

Use of the index with `[]` provides a convenient way to access array elements.
In ClickHouse, it's important to know that the array index always starts from **1**.
This may be different from other programming languages you're used to where arrays are zero-indexed.

For example, given an array, you can select the first element of an array by writing:

```
WITH array('hello', 'world') AS string_array
SELECT string_array[1];

┌─arrayElement⋯g_array, 1)─┐
│ hello                    │
└──────────────────────────┘
```

It is also possible to use negative indexes.
In this way, you can select elements relative to the last element:

```
WITH array('hello', 'world') AS string_array
SELECT string_array[-1];

┌─arrayElement⋯g_array, -1)─┐
│ world                     │
└───────────────────────────┘
```

Despite arrays being 1 based-indexed, you can still access elements at position 0.
The returned value will be the *default value* of the array type.
In the example below, an empty string is returned as this is the default value for the string data type:

```
WITH ['hello', 'world', 'arrays are great aren\'t they?'] AS string_array
SELECT string_array[0]

┌─arrayElement⋯g_array, 0)─┐
│                          │
└──────────────────────────┘
```

## Array functions[​](#array-functions "Direct link to Array functions")

ClickHouse offers a host of useful functions which operate on arrays.
In this section, we'll look at some of the most useful ones, starting from the simplest and working up in complexity.

### length, arrayEnumerate, indexOf, has\* functions[​](#length-arrayEnumerate-indexOf-has-functions "Direct link to length, arrayEnumerate, indexOf, has* functions")

The `length` function is used to return the number of elements in the array:

```
WITH array('learning', 'ClickHouse', 'arrays') AS string_array
SELECT length(string_array);

┌─length(string_array)─┐
│                    3 │
└──────────────────────┘
```

You can also use the [`arrayEnumerate`](/docs/sql-reference/functions/array-functions#arrayEnumerate) function to return an array of indexes of the elements:

```
WITH array('learning', 'ClickHouse', 'arrays') AS string_array
SELECT arrayEnumerate(string_array);

┌─arrayEnumerate(string_array)─┐
│ [1,2,3]                      │
└──────────────────────────────┘
```

If you want to find the index of a particular value, you can use the `indexOf` function:

```
SELECT indexOf([4, 2, 8, 8, 9], 8);

┌─indexOf([4, 2, 8, 8, 9], 8)─┐
│                           3 │
└─────────────────────────────┘
```

Notice that this function will return the first index it encounters if there are multiple identical values in the array.
If your array elements are sorted in ascending order then you can use the [`indexOfAssumeSorted`](/docs/sql-reference/functions/array-functions#indexOfAssumeSorted) function.

The functions `has`, `hasAll` and `hasAny` are useful for determining whether an array contains a given value.
Consider the following example:

```
WITH ['Airbus A380', 'Airbus A350', 'Airbus A220', 'Boeing 737', 'Boeing 747-400'] AS airplanes
SELECT
    has(airplanes, 'Airbus A350') AS has_true,
    has(airplanes, 'Lockheed Martin F-22 Raptor') AS has_false,
    hasAny(airplanes, ['Boeing 737', 'Eurofighter Typhoon']) AS hasAny_true,
    hasAny(airplanes, ['Lockheed Martin F-22 Raptor', 'Eurofighter Typhoon']) AS hasAny_false,
    hasAll(airplanes, ['Boeing 737', 'Boeing 747-400']) AS hasAll_true,
    hasAll(airplanes, ['Boeing 737', 'Eurofighter Typhoon']) AS hasAll_false
FORMAT Vertical;
```

```
has_true:     1
has_false:    0
hasAny_true:  1
hasAny_false: 0
hasAll_true:  1
hasAll_false: 0
```

## Exploring flight data with array functions[​](#exploring-flight-data-with-array-functions "Direct link to Exploring flight data with array functions")

So far, the examples have been pretty simple.
The utility of arrays really shows itself when used on a real-world dataset.

We will be using the [ontime dataset](/docs/getting-started/example-datasets/ontime), which contains flight data from the Bureau of Transportation Statistics.
You can find this dataset on the [SQL playground](https://sql.clickhouse.com/?query_id=M4FSVBVMSHY98NKCQP8N4K).

We've selected this dataset as arrays are often well suited to working with time-series data and can assist in simplifying
otherwise complex queries.

Tip

Click the "play" button below to run the queries directly in the docs and see the result live.

### groupArray[​](#grouparray "Direct link to groupArray")