Working with arrays in ClickHouse

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.

The condition `a1.Origin < a2.Origin`, ensures that each pair only appears once.
Without this, you'd get both JFK-LAX and LAX-JFK as separate results, which would be redundant since they represent the same comparison.
Finally, the query sorts the results to show which airport pairs have the highest number of shared destinations and returns just the top 10.
This reveals which major hubs have the most overlapping route networks, which could indicate competitive markets where multiple airlines are serving the same city pairs, or hubs that serve similar geographic regions and could potentially be used as alternative connection points for travelers.

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

While we're looking at delays, let's use yet another higher-order array function, `arrayReduce`, to find the average and maximum delay
for each route from Denver International Airport:

```
SELECT
    Origin,
    toStringCutToZero(Dest) AS Destination,
    groupArray(DepDelayMinutes) AS delays,
--highlight-start
    round(arrayReduce('avg', groupArray(DepDelayMinutes)), 2) AS avg_delay,
    round(arrayReduce('max', groupArray(DepDelayMinutes)), 2) AS worst_delay
--highlight-end
FROM ontime.ontime
WHERE Origin = 'DEN'
    AND FlightDate = '2024-01-01'
    AND DepDelayMinutes IS NOT NULL
GROUP BY Origin, Destination
ORDER BY avg_delay DESC
```

In the example above, we used `arrayReduce` to find the average and maximum delays for various outward flights from `DEN`.
`arrayReduce` applies an aggregate function, specified in the first parameter to the function, to the elements of the provided array, specified in the second parameter of the function.

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

Regular functions in ClickHouse have the property that they return the same number of rows than they receive.
There is however, one interesting and unique function that breaks this rule, which is worth learning about - the `arrayJoin` function.

`arrayJoin` "explodes" an array by taking it and creating a separate row for each element.
This is similar to the `UNNEST` or `EXPLODE` SQL functions in other databases.

Unlike most array functions that return arrays or scalar values, `arrayJoin` fundamentally changes the result set by multiplying the number of rows.

Consider the query below which returns an array of values from 0 to 100 in steps of 10.
We could consider the array to be different delay times: 0 minutes, 10 minutes, 20 minutes, and so on.

```
WITH range(0, 100, 10) AS delay
SELECT delay
```

We can write a query using `arrayJoin` to work out how many delays there were of up to that number of minutes between two airports.
The query below creates a histogram showing the distribution of flight delays from Denver (DEN) to Miami (MIA) on January 1, 2024, using cumulative delay buckets:

```
WITH range(0, 100, 10) AS delay,
    toStringCutToZero(Dest) AS Destination

SELECT
    'Up to ' || arrayJoin(delay) || ' minutes' AS delayTime,
    countIf(DepDelayMinutes >= arrayJoin(delay)) AS flightsDelayed
FROM ontime.ontime
WHERE Origin = 'DEN' AND Destination = 'MIA' AND FlightDate = '2024-01-01'
GROUP BY delayTime
ORDER BY flightsDelayed DESC
```

In the query above we return an array of delays using a CTE clause (`WITH` clause).
`Destination` converts the destination code to a string.

We use `arrayJoin` to explode the delay array into separate rows.
Each value from the `delay` array becomes its own row with alias `del`,
and we get 10 rows: one for `del=0`, one for `del=10`, one for `del=20`, etc.
For each delay threshold (`del`), the query counts how many flights had delays greater than or equal to that threshold
using `countIf(DepDelayMinutes >= del)`.

`arrayJoin` also has a SQL command equivalent `ARRAY JOIN`.
The query above is reproduced below with the SQL command equivalent for comparison:

```
WITH range(0, 100, 10) AS delay, 
     toStringCutToZero(Dest) AS Destination

SELECT    
    'Up to ' || del || ' minutes' AS delayTime,
    countIf(DepDelayMinutes >= del) flightsDelayed
FROM ontime.ontime
ARRAY JOIN delay AS del
WHERE Origin = 'DEN' AND Destination = 'MIA' AND FlightDate = '2024-01-01'
GROUP BY ALL
ORDER BY flightsDelayed DESC
```

## Next steps[​](#next-steps "Direct link to Next steps")

Congratulations! You've learned how to work with arrays in ClickHouse, from basic array creation and indexing to powerful functions like `groupArray`, `arrayFilter`, `arrayMap`, `arrayReduce`, and `arrayJoin`.
To continue your learning journey, explore the complete array functions reference to discover additional functions like `arrayFlatten`, `arrayReverse`, and `arrayDistinct`.
You might also want to learn about related data structures such as [`tuples`](/docs/sql-reference/data-types/tuple#creating-tuples), [JSON](/docs/sql-reference/data-types/newjson), and [Map](/docs/sql-reference/data-types/map) types which work well alongside arrays.
Practice applying these concepts to your own datasets, and experiment with different queries on the SQL playground or other example datasets.

Arrays are a fundamental feature in ClickHouse that enable, efficient analytical queries - as you become more comfortable with array functions, you'll find they can dramatically simplify complex aggregations and time-series analysis.
For more array fun, we recommend the YouTube video below from Mark, our resident data expert:

- [Introduction to arrays](#array-basics)- [Array functions](#array-functions)
    - [length, arrayEnumerate, indexOf, has\* functions](#length-arrayEnumerate-indexOf-has-functions)- [Exploring flight data with array functions](#exploring-flight-data-with-array-functions)
      - [groupArray](#grouparray)- [arrayMap and arrayZip](#arraymap)- [arrayFilter](#arrayfilter)- [arraySort and arrayIntersect](#arraysort-and-arrayintersect)- [arrayReduce](#arrayReduce)- [arrayJoin](#arrayJoin)- [Next steps](#next-steps)

Was this page helpful?