> ## Documentation Index > Fetch the complete documentation index at: https://clickhouse.com/docs/llms.txt > Use this file to discover all available pages before exploring further. # Working with arrays in ClickHouse > Starter guide on how to use arrays in ClickHouse export const RunnableCode = ({children, run = false, showStats = true}) => { const [results, setResults] = useState(null); const [error, setError] = useState(null); const [loading, setLoading] = useState(false); const [showResults, setShowResults] = useState(false); const [stats, setStats] = useState(null); const [isDark, setIsDark] = useState(false); const [hoveredRow, setHoveredRow] = useState(-1); const codeRef = useRef(null); useEffect(() => { if (typeof window !== 'undefined') { const check = () => setIsDark(document.documentElement.classList.contains('dark')); check(); const observer = new MutationObserver(check); observer.observe(document.documentElement, { attributes: true, attributeFilter: ['class'] }); return () => observer.disconnect(); } }, []); useEffect(() => { if (codeRef.current) { const block = codeRef.current.querySelector('.code-block'); if (block) { block.style.marginBottom = '0'; block.style.marginTop = '0'; block.style.borderBottomLeftRadius = '0'; block.style.borderBottomRightRadius = '0'; } } }); const getSqlText = () => { if (!codeRef.current) return ''; const code = codeRef.current.querySelector('code'); return (code || codeRef.current).textContent.trim(); }; const executeQuery = async () => { const sql = getSqlText(); if (!sql) return; setLoading(true); setError(null); setResults(null); setShowResults(true); try { const cleanQuery = sql.replace(/;$/, '').trim(); const params = new URLSearchParams({ query: cleanQuery, default_format: 'JSONCompact', result_overflow_mode: 'break', read_overflow_mode: 'break', allow_experimental_analyzer: '1' }); const res = await fetch(`https://sql-clickhouse.clickhouse.com/?${params.toString()}`, { method: 'POST', headers: { 'Authorization': `Basic ${btoa(`demo:`)}` } }); const text = await res.text(); if (!res.ok) { setError(text || `HTTP ${res.status}`); setLoading(false); return; } const json = JSON.parse(text); setResults(json); setStats(json.statistics || null); } catch (err) { setError(err.message || 'Query execution failed'); } setLoading(false); }; useEffect(() => { if (run) executeQuery(); }, []); const formatRows = n => { if (n >= 1e9) return `${(n / 1e9).toFixed(1)}B`; if (n >= 1e6) return `${(n / 1e6).toFixed(1)}M`; if (n >= 1e3) return `${(n / 1e3).toFixed(1)}K`; return String(n); }; const formatBytes = b => { if (b >= 1e9) return `${(b / 1e9).toFixed(2)} GB`; if (b >= 1e6) return `${(b / 1e6).toFixed(2)} MB`; if (b >= 1e3) return `${(b / 1e3).toFixed(2)} KB`; return `${b} B`; }; const isNumericType = type => { return (/^(UInt|Int|Float|Decimal)/).test(type); }; const isHyperlink = value => { return typeof value === 'string' && (/^https?:\/\//).test(value); }; const computeColumnExtremes = (meta, data) => { const extremes = {}; for (let i = 0; i < meta.length; i++) { if (isNumericType(meta[i].type)) { let min = Infinity, max = -Infinity; for (const row of data) { const v = Number(row[i]); if (!isNaN(v)) { if (v < min) min = v; if (v > max) max = v; } } if (max > -Infinity) { extremes[i] = { min, max }; } } } return extremes; }; const computeColumnWidths = (meta, data) => { const lengths = meta.map((col, i) => { const headerLen = col.name.length + col.type.length + 1; let maxData = 0; for (const row of data) { const v = row[i]; const len = v === null ? 4 : String(v).length; if (len > maxData) maxData = len; } return Math.max(headerLen, maxData); }); const total = lengths.reduce((s, l) => s + l, 0); return lengths.map(l => `${(l / total * 100).toFixed(1)}%`); }; const copyResultsAsTSV = () => { if (!results || !results.meta || !results.data) return; const header = results.meta.map(col => col.name).join('\t'); const rows = results.data.map(row => row.map(cell => cell === null ? 'NULL' : String(cell)).join('\t')); const tsv = [header, ...rows].join('\n'); navigator.clipboard.writeText(tsv); }; const borderColor = isDark ? 'rgba(255,255,255,0.15)' : '#e5e7eb'; const bgColor = isDark ? 'rgba(255,255,255,0.05)' : '#f9fafb'; const headerBg = isDark ? '#2a2a2a' : '#f3f4f6'; const textColor = isDark ? '#e5e7eb' : '#1f2937'; const mutedColor = isDark ? '#d1d5db' : '#6b7280'; const accentColor = isDark ? '#FAFF69' : '#323232'; const accentTextColor = isDark ? '#000' : '#fff'; const barColor = isDark ? '#35372f' : '#d2d2d2'; const cellBg = isDark ? '#1f201b' : '#ffffff'; const cellBgHover = isDark ? 'lch(15.8 0 0)' : '#f0f0f0'; const extremes = results && results.meta && results.data ? computeColumnExtremes(results.meta, results.data) : {}; const colWidths = results && results.meta && results.data ? computeColumnWidths(results.meta, results.data) : []; const getCellBarStyle = (cell, ci, ri) => { if (cell === null) return null; const colMeta = results.meta[ci]; if (!isNumericType(colMeta.type) || !extremes[ci] || results.data.length <= 1 || extremes[ci].max <= 0) return null; const ratio = 100 * Number(cell) / extremes[ci].max; const bg = ri === hoveredRow ? cellBgHover : cellBg; return { background: `linear-gradient(to right, ${barColor} 0%, ${barColor} ${ratio}%, ${bg} ${ratio}%, ${bg} 100%)` }; }; const renderCell = (cell, ci) => { if (cell === null) { return NULL; } const value = String(cell); if (isHyperlink(value)) { return {value} ; } return value; }; return
{}
{children}
{}
{results && } {showStats && stats && Read {formatRows(stats.rows_read)} rows, {formatBytes(stats.bytes_read)} in {stats.elapsed.toFixed(3)}s }
{} {showResults &&
{loading &&
Executing query...
} {error &&
{error}
} {results && results.meta && results.data &&
{results.meta.map((col, i) =>
1 ? 'right' : 'left', backgroundColor: headerBg, borderBottom: `1px solid ${borderColor}`, color: textColor, fontWeight: 600, fontSize: '12px', whiteSpace: 'nowrap', overflow: 'hidden', textOverflow: 'ellipsis' }}> {col.name} {col.type}
)} {results.data.map((row, ri) => row.map((cell, ci) =>
setHoveredRow(ri)} onMouseLeave={() => setHoveredRow(-1)} style={{ padding: '4px 12px', color: textColor, whiteSpace: 'nowrap', overflow: 'hidden', textOverflow: 'ellipsis', textAlign: isNumericType(results.meta[ci].type) && results.meta.length > 1 ? 'right' : 'left', borderBottom: `1px solid ${borderColor}`, backgroundColor: ri === hoveredRow ? cellBgHover : ri % 2 === 0 ? 'transparent' : bgColor, transition: 'background-color 0.1s', ...getCellBarStyle(cell, ci, ri) }}> {renderCell(cell, ci)}
))}
} {results && results.data &&
{results.rows} row{results.rows !== 1 ? 's' : ''}
}
}
; }; > In this guide, you'll learn how to use arrays in ClickHouse along with some of the most commonly used [array functions](/docs/reference/functions/regular-functions/array-functions).

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/reference/data-types/array) function: ```sql theme={null} array(T) ``` Or alternatively, using `[]`: ```sql theme={null} [] ``` For example, you can create an array of numbers: ```sql theme={null} SELECT array(1, 2, 3) AS numeric_array ``` ```response theme={null} ┌─numeric_array─┐ │ [1,2,3] │ └───────────────┘ ``` Or an array of strings: ```sql theme={null} SELECT array('hello', 'world') AS string_array ``` ```response theme={null} ┌─string_array──────┐ │ ['hello','world'] │ └───────────────────┘ ``` Or an array of nested types such as [tuples](/docs/reference/data-types/tuple): ```sql theme={null} SELECT array(tuple(1, 2), tuple(3, 4)) ``` ```response theme={null} ┌─[(1, 2), (3, 4)]─┐ │ [(1,2),(3,4)] │ └──────────────────┘ ``` You might be tempted to make an array of different types like this: ```sql theme={null} 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: ```sql theme={null} 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: ```sql theme={null} SELECT [1::UInt8, 2.5::Float32, 3::UInt8] AS mixed_array, toTypeName([1, 2.5, 3]) AS array_type; ``` ```response theme={null} ┌─mixed_array─┬─array_type─────┐ │ [1,2.5,3] │ Array(Float64) │ └─────────────┴────────────────┘ ``` 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/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: ```sql theme={null} SELECT [1, 'ClickHouse', ['Another', 'Array']] AS array, toTypeName(array) SETTINGS use_variant_as_common_type = 1; ``` ```response theme={null} ┌─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: ```sql theme={null} SELECT [1, 'ClickHouse', ['Another', 'Array']] AS array, array.UInt8, array.String, array.`Array(String)` SETTINGS use_variant_as_common_type = 1; ``` ```response theme={null} ┌─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: ```sql theme={null} WITH array('hello', 'world') AS string_array SELECT string_array[1]; ``` ```response theme={null} ┌─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: ```sql theme={null} WITH array('hello', 'world') AS string_array SELECT string_array[-1]; ``` ```response theme={null} ┌─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: ```sql theme={null} WITH ['hello', 'world', 'arrays are great aren\'t they?'] AS string_array SELECT string_array[0] ``` ```response theme={null} ┌─arrayElement⋯g_array, 0)─┐ │ │ └──────────────────────────┘ ```

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

The `length` function is used to return the number of elements in the array: ```sql theme={null} WITH array('learning', 'ClickHouse', 'arrays') AS string_array SELECT length(string_array); ``` ```response theme={null} ┌─length(string_array)─┐ │ 3 │ └──────────────────────┘ ``` You can also use the [`arrayEnumerate`](/docs/reference/functions/regular-functions/array-functions#arrayEnumerate) function to return an array of indexes of the elements: ```sql theme={null} WITH array('learning', 'ClickHouse', 'arrays') AS string_array SELECT arrayEnumerate(string_array); ``` ```response theme={null} ┌─arrayEnumerate(string_array)─┐ │ [1,2,3] │ └──────────────────────────────┘ ``` If you want to find the index of a particular value, you can use the `indexOf` function: ```sql theme={null} SELECT indexOf([4, 2, 8, 8, 9], 8); ``` ```response theme={null} ┌─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/reference/functions/regular-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: ```sql theme={null} 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; ``` ```response theme={null} has_true: 1 has_false: 0 hasAny_true: 1 hasAny_false: 0 hasAll_true: 1 hasAll_false: 0 ```

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/get-started/sample-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. Click the "play" button below to run the queries directly in the docs and see the result live.

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: ```sql theme={null} -- 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/reference/functions/aggregate-functions/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: ```sql theme={null} 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/reference/functions/regular-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: ```sql highlight={7} theme={null} WITH '2024-01-01' AS date, busy_airports AS ( 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 = 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

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/reference/functions/regular-functions/overview#higher-order-functions) and accept a lambda function as the first parameter. The [`arrayMap`](/docs/reference/functions/regular-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: ```sql theme={null} 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/reference/functions/regular-functions/array-functions#arrayZip) function combines the `Tail_Number` array and the `statuses` array into a single array.

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`: ```sql highlight={4} theme={null} SELECT Origin, OriginCityName, 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/reference/functions/regular-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`. ```sql theme={null} d -> d >= 30 ```

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/reference/functions/regular-functions/array-functions#arraySort) and [`arrayIntersect`](/docs/reference/functions/regular-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: ```sql highlight={4,12} theme={null} WITH airport_routes AS ( SELECT Origin, 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, 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. 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

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: ```sql highlight={5-6} theme={null} SELECT Origin, toStringCutToZero(Dest) AS Destination, groupArray(DepDelayMinutes) AS delays, round(arrayReduce('avg', groupArray(DepDelayMinutes)), 2) AS avg_delay, round(arrayReduce('max', groupArray(DepDelayMinutes)), 2) AS worst_delay 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

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. ```sql theme={null} 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: ```sql theme={null} 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: ```sql theme={null} 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

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/reference/data-types/tuple#creating-tuples), [JSON](/docs/reference/data-types/newjson), and [Map](/docs/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: