Arrays, ARRAY JOIN and lambda functions

Arrays are a first-class type in ClickHouse, and a large part of the function library is built around them. Two ideas cover most use: turn many rows into one array (groupArray) and turn one array back into many rows (ARRAY JOIN). In between, higher-order functions with lambdas (arrayMap, arrayFilter, …) do the work without leaving SQL.

The examples use a handful of rows in the demo events table:

CREATE TABLE events (event_time DateTime, user_id UInt64, event_type LowCardinality(String),
                     url String, duration_ms UInt32, country LowCardinality(String))
ENGINE = MergeTree ORDER BY (event_type, event_time);

INSERT INTO events VALUES
('2026-03-01 10:00:00', 1, 'view',     '/shop/shoes/red',  120, 'DE'),
('2026-03-01 10:01:00', 1, 'click',    '/shop/shoes/red',   80, 'DE'),
('2026-03-01 10:02:00', 1, 'purchase', '/checkout',        300, 'DE'),
('2026-03-01 11:00:00', 2, 'view',     '/shop/hats',        40, 'TR'),
('2026-03-01 11:05:00', 2, 'view',     '/blog',            500, 'TR'),
('2026-03-01 12:00:00', 3, 'view',     '/shop/shoes/blue',  90, 'US');

Rows to arrays: groupArray

groupArray(x) collects the values of a group into an array. The order of elements follows the order in which rows reach the aggregation, which is not guaranteed unless you sort in a subquery first:

SELECT user_id, groupArray(event_type) AS path, groupArray(url) AS urls
FROM (SELECT * FROM events ORDER BY event_time)
GROUP BY user_id ORDER BY user_id;

┌─user_id─┬─path────────────────────────┬─urls──────────────────────────────────────────────┐
│       1 │ ['view','click','purchase'] │ ['/shop/shoes/red','/shop/shoes/red','/checkout'] │
│       2 │ ['view','view']             │ ['/shop/hats','/blog']                            │
│       3 │ ['view']                    │ ['/shop/shoes/blue']                              │
└─────────┴─────────────────────────────┴───────────────────────────────────────────────────┘

Each user’s journey is now one value you can inspect with array functions (has(path, 'purchase'), indexOf, arrayCumSum). groupUniqArray does the same with duplicates removed, and groupArray(n)(x) caps the length.

Strings can be turned into arrays, too:

SELECT url, splitByChar('/', url) AS parts, length(parts) AS depth
FROM events GROUP BY url ORDER BY url;

┌─url──────────────┬─parts──────────────────────┬─depth─┐
│ /blog            │ ['','blog']                │     2 │
│ /checkout        │ ['','checkout']            │     2 │
│ /shop/hats       │ ['','shop','hats']         │     3 │
│ /shop/shoes/blue │ ['','shop','shoes','blue'] │     4 │
│ /shop/shoes/red  │ ['','shop','shoes','red']  │     4 │
└──────────────────┴────────────────────────────┴───────┘

Note the empty first element from the leading /. Arrays are 1-based, and indexing out of range returns the type’s default, not an error: [1,2,3][1] is 1, [1,2,3][-1] is 3, and [1,2,3][10] is 0.

Arrays to rows: ARRAY JOIN

ARRAY JOIN repeats the row once for each element of an array, with the element in a new column. Other columns are copied:

SELECT url, part
FROM (SELECT DISTINCT url FROM events)
ARRAY JOIN splitByChar('/', url) AS part
WHERE part != ''
ORDER BY url, part;

 1. /blog            blog
 2. /checkout        checkout
 3. /shop/hats       hats
 4. /shop/hats       shop
 5. /shop/shoes/blue blue
 6. /shop/shoes/blue shoes
 7. /shop/shoes/blue shop
 8. /shop/shoes/red  red
 9. /shop/shoes/red  shoes
10. /shop/shoes/red  shop

Counting the most visited path segments over all events:

SELECT part, count()
FROM events
ARRAY JOIN arrayFilter(x -> x != '', splitByChar('/', url)) AS part
GROUP BY part ORDER BY count() DESC, part;

shop      4
shoes     3
red       2
blog      1
blue      1
checkout  1
hats      1

Several arrays and positions

ARRAY JOIN accepts several arrays at once, and walks them in parallel (they must have equal length). Pair every element with its position through arrayEnumerate:

SELECT url, part, i
FROM (SELECT DISTINCT url FROM events WHERE url = '/shop/shoes/red')
ARRAY JOIN splitByChar('/', url) AS part,
           arrayEnumerate(splitByChar('/', url)) AS i;

/shop/shoes/red  ''     1
/shop/shoes/red  shop   2
/shop/shoes/red  shoes  3
/shop/shoes/red  red    4

Two arrays in one ARRAY JOIN is a zip. Two separate ARRAY JOIN clauses is a cross product. That difference is a classic source of row explosions.

Empty arrays: LEFT ARRAY JOIN

Plain ARRAY JOIN drops rows whose array is empty. LEFT ARRAY JOIN keeps them, with the type’s default in the element column:

SELECT user_id, tag
FROM (SELECT 1 AS user_id, ['a','b'] AS tags UNION ALL SELECT 2, [])
LEFT ARRAY JOIN tags AS tag ORDER BY user_id, tag;

1  a
1  b
2  ''

With the plain form, user 2 would be missing from the result.

arrayJoin() the function

arrayJoin(arr) does the same unrolling inside the SELECT list: SELECT arrayJoin([1,2,3]) returns three rows. It is convenient but easy to misuse:

It multiplies rows wherever it appears, including inside expressions and WHERE, so a query with two arrayJoin calls produces a cross product.
It runs before aggregation, so count() counts the unrolled rows.

Prefer the ARRAY JOIN clause when the intent is “one row per element”: it is explicit about row multiplication and lets you name the element.

Higher-order functions

The functions taking a lambda x -> expression as their first argument process arrays without unrolling them.

Function	Meaning	Example	Result
`arrayMap(f, arr)`	transform each element	`arrayMap(x -> x * 2, [1,2,3])`	`[2,4,6]`
`arrayFilter(f, arr)`	keep elements where `f` is true	`arrayFilter(x -> x % 2 = 1, [1,2,3,4,5])`	`[1,3,5]`
`arrayExists(f, arr)`	is there any element with `f` true?	`arrayExists(x -> x > 2, [1,2,3])`	`1`
`arrayAll(f, arr)`	do all elements satisfy `f`?	`arrayAll(x -> x > 0, [1,2,3])`	`1`
`arrayCount(f, arr)`	count elements with `f` true	`arrayCount(x -> x > 1, [1,2,3])`	`2`
`arraySum(f, arr)`	sum after applying `f`	`arraySum(x -> x * x, [1,2,3])`	`14`
`arrayFold(f, arr, init)`	left fold	`arrayFold((acc, x) -> acc + x, [1,2,3], 0::UInt64)`	`6`

A lambda over several arrays takes several parameters and walks them in step: arrayMap((x, y) -> x + y, [1,2,3], [10,20,30]) is [11,22,33].

Everyday array functions

SELECT has(['a','b'], 'a')                 AS h,        -- 1
       hasAny([1,2,3], [3,9])              AS any_of,   -- 1
       hasAll([1,2,3], [1,2])              AS all_of,   -- 1
       arrayZip(['a','b'], [1,2])          AS zipped,   -- [('a',1),('b',2)]
       arrayEnumerate(['x','y','z'])       AS idx,      -- [1,2,3]
       arraySort([3,1,2])                  AS sorted,   -- [1,2,3]
       arrayReverse([1,2,3])               AS rev,      -- [3,2,1]
       arrayDistinct([1,1,2])              AS dist,     -- [1,2]
       arraySlice([1,2,3,4,5], 2, 3)       AS sl,       -- [2,3,4]
       arrayConcat([1], [2,3])             AS cc,       -- [1,2,3]
       indexOf(['a','b','c'], 'b')         AS pos,      -- 2
       arrayStringConcat(['a','b'], '-')   AS joined;   -- 'a-b'

For running totals inside an array, arrayCumSum; for extremes, arrayMin/arrayMax; for per-position aggregation across rows, the -ForEach combinator, e.g. sumForEach(array_column).

The “any of these columns” idiom

Arrays also solve a plain SQL annoyance: applying the same test to several columns. Wrap the columns in an array literal and use a lambda over it:

SELECT url, country, event_type
FROM events
WHERE arrayExists(x -> position(x, 'shoes') > 0, [url, country, event_type]);

This returns the three rows where any of the three columns contains shoes, and avoids writing the condition three times with OR. Adding another column to the check is one more array element. The same shape works for “all columns” (arrayAll), “how many columns” (arrayCount) or “first non-empty” (arrayFirst).

Related: arraySum(arrayMap(x -> position(x, 'shoes'), [url, country, event_type])) > 0 is equivalent but less direct; arrayExists says what you mean.

Gotchas

Large arrays are memory-heavy. groupArray over a huge group builds the whole array in RAM; use groupArray(max_size)(x) or aggregate differently.
Arrays are stored column-wise with an offsets column. A query that touches arr reads all of it, even if it needs one element. To read only the lengths, use the arr.size0 subcolumn instead of length(arr).
Array(Nullable(T)) is allowed but slower and bulkier than non-nullable arrays.

Reference: array functions in the docs.