Skip to content
Tutorials Center
My learning

Topics

Lesson 16 of 35 ยท Querying and Functions

Arrays and Lambda Functions

Work with ClickHouse arrays: create and index them, use arrayMap, arrayFilter and arrayExists lambdas, ARRAY JOIN, groupArray and arrayReduce on real data.

Arrays are a first-class column type in ClickHouse, and the language around them is one of its most distinctive features. You can store a list of tags or timestamps in one cell, run higher-order functions with lambdas (arrayMap, arrayFilter), unnest with ARRAY JOIN, and build arrays back with groupArray. This replaces many awkward joins and self-joins with a few readable expressions.

ClickHouse cannot run on this site. Array and lambda code is read-only. The runnable blocks use SQLite's JSON functions (json_each, json_group_array) to imitate unnesting and collecting, since SQLite has no array type.

Creating and indexing arrays

SQL
SELECT
    [1, 2, 3]              AS nums,        -- Array(UInt8)
    ['a', 'b']             AS names,       -- Array(String)
    array(1, 2, 3)         AS same,
    range(5)               AS r,           -- [0,1,2,3,4]
    [10, 20, 30][1]        AS first,       -- 10  (indexes start at 1!)
    [10, 20, 30][-1]       AS last,        -- 30
    length([1, 2, 3])      AS len;

Indexing starts at 1, and index 0 returns the default value rather than an error. Negative indexes count from the end. As a column:

SQL
CREATE TABLE posts
(
    post_id UInt32,
    title   String,
    tags    Array(LowCardinality(String)),
    scores  Array(UInt16)
)
ENGINE = MergeTree ORDER BY post_id;

INSERT INTO posts VALUES
    (1, 'Intro',   ['sql', 'basics'],        [5, 4, 5]),
    (2, 'Joins',   ['sql', 'joins'],         [3, 5]),
    (3, 'Engines', ['clickhouse', 'storage'], []);

Everyday array functions

Function Meaning
has(arr, x) Is x in the array?
hasAll(a, b) / hasAny(a, b) Contains all / any of
indexOf(arr, x) 1-based position, 0 if absent
arrayConcat(a, b) Join arrays
arraySlice(arr, offset, length) Sub-array
arraySort(arr), arrayReverse(arr) Order
arrayDistinct(arr) Unique elements
arrayEnumerate(arr) [1, 2, ...] positions
arraySum(arr), arrayMax(arr), arrayAvg(arr) Aggregate one array
arrayJoin(arr) Unnest into rows (see below)
empty(arr) / notEmpty(arr) Emptiness
SQL
SELECT title FROM posts WHERE has(tags, 'sql');
SELECT title, arraySum(scores) / length(scores) AS avg_score FROM posts WHERE notEmpty(scores);

Lambdas: arrayMap, arrayFilter, arrayExists

A lambda is written x -> expression (or (x, y) -> ... for several arrays). It is passed as the first argument to a higher-order function:

SQL
SELECT
    arrayMap(x -> x * x, [1, 2, 3, 4])            AS squares,    -- [1,4,9,16]
    arrayFilter(x -> x % 2 = 0, [1, 2, 3, 4])      AS evens,      -- [2,4]
    arrayExists(x -> x > 3, [1, 2, 3, 4])          AS any_big,    -- 1
    arrayAll(x -> x > 0, [1, 2, 3])                AS all_pos,    -- 1
    arrayFirst(x -> x > 1, [1, 2, 3])              AS first_gt1,  -- 2
    arrayCount(x -> x > 1, [1, 2, 3])              AS how_many,   -- 2
    arrayMap((a, b) -> a + b, [1, 2], [10, 20])    AS pairwise,   -- [11,22]
    arrayFold((acc, x) -> acc + x, [1, 2, 3], 0::UInt32) AS total; -- 6

Think of them as map, filter and any from a functional language, operating on a whole column at once. Practical use: score weighting, cleaning tag lists, checking whether any event in a session matches.

SQL
SELECT post_id,
       arrayMap(s -> s * 2, scores)               AS doubled,
       arrayFilter(t -> t != 'basics', tags)       AS tags_without_basics
FROM posts;

ARRAY JOIN: arrays to rows

ARRAY JOIN repeats the row once per array element; arrayJoin() does the same as a function. An empty array removes the row (use LEFT ARRAY JOIN to keep it).

SQL
SELECT post_id, tag
FROM posts
ARRAY JOIN tags AS tag;

With a classic SQL engine you would unnest with json_each or a lateral join. The stand-in below stores tags as JSON text and unnests them, then counts how many posts use each tag.

SQL Stand-in: unnest tags and count them (SQLite)

Press Run (or Ctrl + Enter) to see the result here.

groupArray: rows to arrays

The opposite direction is an aggregate: groupArray(x) collects the values of a group into an array (groupUniqArray removes duplicates, groupArray(5)(x) keeps at most five).

SQL
SELECT user_id,
       groupArray(page)                  AS pages_in_order_of_arrival,
       groupUniqArray(page)              AS distinct_pages,
       arraySort(groupArray(event_time)) AS times
FROM page_views
GROUP BY user_id;

A very common funnel pattern is to collect each user's ordered events and then test that array with arrayExists or a sequence function. SQLite's json_group_array plays the same role:

SQL Stand-in: collect each user's pages (SQLite)

Press Run (or Ctrl + Enter) to see the result here.

arrayReduce, arrayZip and friends

  • arrayReduce('max', arr) applies any aggregate function to an array, so arrayReduce('quantile(0.5)', arr) is a median of one cell.
  • arrayZip(a, b) pairs two arrays into an array of tuples.
  • arrayCumSum(arr) is a running total: arrayCumSum([1, 2, 3]) gives [1, 3, 6].
  • arrayDifference(arr) returns successive differences.
  • arrayIntersect(a, b) returns elements present in both.

Because many of these work on a column of arrays in bulk, they are fast. A running total across a cell is a one-liner without a window function.

SQL Stand-in: running total with a window function (SQLite)

Press Run (or Ctrl + Enter) to see the result here.

Maps and tuples at a glance

Map(K, V) behaves like an array of pairs with key lookup: headers['host'], mapKeys(m), mapValues(m). Tuple holds a fixed set of typed values: (1, 'a').1 gives 1. Both let you keep a small, related set of fields in one column, though flat columns are faster to filter and compress.

Performance notes

  • Arrays are stored in two streams (sizes and flattened values), so they compress well.
  • ARRAY JOIN multiplies rows: filter first (WHERE) before unnesting when you can.
  • A huge array per row (thousands of elements) hurts; prefer separate rows.
  • has(tags, 'x') on an array in a bloom_filter skipping index can skip granules.

Common beginner mistakes

  • Assuming zero-based indexing.
  • Using a lambda without a higher-order function around it.
  • Forgetting ARRAY JOIN removes rows with empty arrays.
  • Unnesting a large array column before applying filters.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is the first element of [10, 20, 30]?

    ClickHouse arrays are 1-indexed; [-1] is the last element and index 0 gives the default value.
  2. 2. What does arrayFilter(x -> x % 2 = 0, [1, 2, 3, 4]) return?

    It keeps the elements for which the lambda is true: the even ones.
  3. 3. How do you turn an array column into one row per element?

    ARRAY JOIN (or the arrayJoin function) unnests arrays.
  4. 4. Which aggregate collects the values of a group into an array?

    groupArray builds an array from the rows of each group.

Summary

  • Arrays are 1-indexed typed columns with a rich function set (has, arraySort, arraySum, arraySlice ...).
  • Lambdas x -> expr power arrayMap, arrayFilter, arrayExists, arrayFold and more.
  • ARRAY JOIN unnests; groupArray collects; arrayReduce applies any aggregate to a single array.
  • Filter before unnesting, and keep arrays reasonably small.

Next: Aggregate functions and combinators