Working with arrays in ClickHouse

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")

There are many columns in this dataset, but we will focus on a subset of the columns.
Run the query below to see what our data looks like:

```
-- SELECT
-- *
-- FROM ontime.ontime LIMIT 100

SELECT
    FlightDate,
    Origin,
    OriginCityName,
    Dest,
    DestCityName,
    DepTime,
    DepDelayMinutes,
    ArrTime,
    ArrDelayMinutes
FROM ontime.ontime LIMIT 5
```

Let's take a look at the top 10 busiest airports in the US on a particular day chosen at random, say '2024-01-01'.
We're interested in understanding how many flights depart from each airport.
Our data contains one row per flight, but it would be convenient if we could group the data by the origin airport and roll the destinations into an array.

To achieve this we can use the [`groupArray`](/docs/sql-reference/aggregate-functions/reference/grouparray) aggregate function, which takes values of the specified column from each row and groups them in an array.

Run the query below to see how it works:

```
SELECT
    FlightDate,
    Origin,
    groupArray(toStringCutToZero(Dest)) AS Destinations
FROM ontime.ontime
WHERE Origin IN ('ATL', 'ORD', 'DFW', 'DEN', 'LAX', 'JFK', 'LAS', 'CLT', 'SFO', 'SEA') AND FlightDate='2024-01-01'
GROUP BY FlightDate, Origin
ORDER BY length(Destinations)
```

The [`toStringCutToZero`](/docs/sql-reference/functions/type-conversion-functions#toStringCutToZero) in the query above is used to remove null characters which appear after some of the airport's 3 letter designation.

With the data in this format, we can easily find the order of the busiest airports by finding the length of the rolled up "Destinations" arrays:

```
WITH
    '2024-01-01' AS date,
    busy_airports AS (
    SELECT
    FlightDate,
    Origin,
--highlight-next-line
    groupArray(toStringCutToZero(Dest)) AS Destinations
    FROM ontime.ontime
    WHERE Origin IN ('ATL', 'ORD', 'DFW', 'DEN', 'LAX', 'JFK', 'LAS', 'CLT', 'SFO', 'SEA')
    AND FlightDate = date
    GROUP BY FlightDate, Origin
    ORDER BY length(Destinations)
    )
SELECT
    Origin,
    length(Destinations) AS outward_flights
FROM busy_airports
ORDER BY outward_flights DESC
```

### arrayMap and arrayZip[​](#arraymap "Direct link to arrayMap and arrayZip")

We saw in the previous query that Denver International Airport was the airport with the most outward flights for our particular chosen day.
Let's take a look at how many of those flights were on-time, delayed by 15-30 minutes or delayed by more than 30 minutes.

Many of the array functions in ClickHouse are so-called ["higher-order functions"](/docs/sql-reference/functions/overview#higher-order-functions) and accept a lambda function as the first parameter.
The [`arrayMap`](/docs/sql-reference/functions/array-functions#arrayMap) function is an example of one such higher-order function and returns a new array from the provided array by applying a lambda function to each element of the original array.

Run the query below which uses the `arrayMap` function to see which flights were delayed or on-time.
For pairs of origin/destinations, it shows the tail number and status for every flight:

```
WITH arrayMap(
              d -> if(d >= 30, 'DELAYED', if(d >= 15, 'WARNING', 'ON-TIME')),
              groupArray(DepDelayMinutes)
    ) AS statuses

SELECT
    Origin,
    toStringCutToZero(Dest) AS Destination,
    arrayZip(groupArray(Tail_Number), statuses) as tailNumberStatuses
FROM ontime.ontime
WHERE Origin = 'DEN'
  AND FlightDate = '2024-01-01'
  AND DepTime IS NOT NULL
  AND DepDelayMinutes IS NOT NULL
GROUP BY ALL
```

In the above query, the `arrayMap` function takes a single-element array `[DepDelayMinutes]` and applies the lambda function `d -> if(d >= 30, 'DELAYED', if(d >= 15, 'WARNING', 'ON-TIME'` to categorize it.
Then the first element of the resulting array is extracted with `[DepDelayMinutes][1]`.
The [`arrayZip`](/docs/sql-reference/functions/array-functions#arrayZip) function combines the `Tail_Number` array and the `statuses` array into a single array.

### arrayFilter[​](#arrayfilter "Direct link to arrayFilter")

Next we'll look only at the number of flights that were delayed by 30 minutes or more, for airports `DEN`, `ATL` and `DFW`:

```
SELECT
    Origin,
    OriginCityName,
--highlight-next-line
    length(arrayFilter(d -> d >= 30, groupArray(ArrDelayMinutes))) AS num_delays_30_min_or_more
FROM ontime.ontime
WHERE Origin IN ('DEN', 'ATL', 'DFW')
    AND FlightDate = '2024-01-01'
GROUP BY Origin, OriginCityName
ORDER BY num_delays_30_min_or_more DESC
```

In the query above we pass a lambda function as the first argument to the [`arrayFilter`](/docs/sql-reference/functions/array-functions#arrayFilter) function.
This lambda function itself takes the delay in minutes (d) and returns `1` if the condition is met, else `0`.

```
d -> d >= 30
```

### arraySort and arrayIntersect[​](#arraysort-and-arrayintersect "Direct link to arraySort and arrayIntersect")

Next, we'll figure out which pairs of major US airports serve the most common destinations with the help of the [`arraySort`](/docs/sql-reference/functions/array-functions#arraySort) and [`arrayIntersect`](/docs/sql-reference/functions/array-functions#arrayIntersect) functions.
`arraySort` takes an array and sorts the elements in ascending order by default, although you can also pass a lambda function to it to define the sorting order.
`arrayIntersect` takes multiple arrays and returns an array which contains elements present in all the arrays.

Run the query below to see these two array functions in action:

```
WITH airport_routes AS (
    SELECT 
        Origin,
--highlight-next-line
        arraySort(groupArray(DISTINCT toStringCutToZero(Dest))) AS destinations
    FROM ontime.ontime
    WHERE FlightDate = '2024-01-01'
    GROUP BY Origin
)
SELECT 
    a1.Origin AS airport1,
    a2.Origin AS airport2,
--highlight-next-line
    length(arrayIntersect(a1.destinations, a2.destinations)) AS common_destinations
FROM airport_routes a1
CROSS JOIN airport_routes a2
WHERE a1.Origin < a2.Origin
    AND a1.Origin IN ('DEN', 'ATL', 'DFW', 'ORD', 'LAS')
    AND a2.Origin IN ('DEN', 'ATL', 'DFW', 'ORD', 'LAS')
ORDER BY common_destinations DESC
LIMIT 10
```

The query works in two main stages.
First, it creates a temporary dataset called `airport_routes` using a Common Table Expression (CTE) that looks at all flights on January 1, 2024, and for each origin airport, builds a sorted list of every unique destination which that airport serves.
In the `airport_routes` result set, for example, DEN might have an array containing all the cities it flies to, like `['ATL', 'BOS', 'LAX', 'MIA', ...]` and so on.

In the second stage, the query takes five major US hub airports (`DEN`, `ATL`, `DFW`, `ORD`, and `LAS`) and compares every possible pair of them.
It does this using a cross join, which creates all combinations of these airports.
Then, for each pair, it uses the `arrayIntersect` function to find which destinations appear in both airports' lists.
The length function counts how many destinations they have in common.