ClickHouse Array Functions in TypeScript — A Practical Guide

ClickHouse treats arrays as a first-class data type. Array(String) is a normal column, and the standard library around it (groupArray, arrayMap, arrayFilter, ARRAY JOIN, arraySum, has) is one of the main reasons analytics queries that would take three CTEs in Postgres collapse into one statement in ClickHouse.

Short answer: the array toolkit has three moves, and almost every array query is some combination of them:

1. Collect rows into an array with groupArray(col) — the inverse of a join, one array per group.
2. Transform the array in place with arrayMap(x -> expr, arr) and arrayFilter(x -> cond, arr) — higher-order functions with lambda syntax.
3. Flatten an array back into rows with ARRAY JOIN when you want to aggregate over individual elements.

From TypeScript, hypequery gives you ARRAY JOIN as native builder methods (.arrayJoin() / .leftArrayJoin()), and the array functions as raw SQL expressions inside .select([...]) via selectExpr. They're expressions, not clauses, so that's the honest place for them:

If you want to run these examples against your own schema, the quick start gets you from npm install to typed queries in a few minutes, and the ClickHouse query builder guide covers the rest of the builder surface. The rest of this post walks through each function with a realistic product-analytics schema, then builds the full collect → filter → transform pipeline.

The Schema

One events table, used consistently below:

Run hypequery generate and the generated schema types tags as string[]. ClickHouse arrays come back over HTTP as real JSON arrays, so the TypeScript type matches the runtime shape exactly. Every array function below produces a column whose JS representation is a plain array you can .map() over in application code.

groupArray: Collect Rows Into Arrays

groupArray(col) is an aggregate function like sum() or count(), except instead of reducing a group to a number it collects the group's values into an array. It's how you turn "one row per event" into "one row per user, with their events inside it":

In hypequery, groupArray isn't a dedicated builder method the way .sum() or .count() are — it goes through selectExpr:

One caveat worth knowing before you build session-replay features on this: groupArray collects values in whatever order rows arrive from storage, which is not guaranteed to be chronological. If the sequence order matters, sort the rows first (an inner query ordered by created_at) rather than trusting the default read order.

Also note the type of what comes back. groupArray(page_path) on a String column gives string[], but groupArray(some_uint64_col) gives string[] too, because ClickHouse serializes UInt64 as strings in JSON to avoid JS precision loss. hypequery's generated types encode this, so the compiler tells you before production does.

arrayMap: Transform Every Element

arrayMap(x -> expr, arr) applies an expression to each element and returns a new array of the same length. The lambda syntax is x -> expr, ClickHouse's arrow, not JavaScript's:

The lambda can reference other columns from the same row, not just x: arrayMap(x -> concat(event_type, ':', x), tags) is valid. That's a genuine difference from mapping in application code, where you'd have to carry the extra column along manually.

arrayFilter: Prune Elements

arrayFilter(x -> cond, arr) keeps only the elements where the lambda returns non-zero:

Filtering inside the array is the key distinction from WHERE. A WHERE page_path LIKE '/app/%' filter would remove rows before collection — users who never touched /app/ disappear from the result entirely. arrayFilter after groupArray keeps every user and prunes their array, so users with zero app pages show up with an empty array. Which one you want depends on the question, and it's a real off-by-a-cohort trap: "users and their app pages" versus "users who used the app, and their pages" are different queries.

Note that arrayFilter here operates on the groupArray output, so it belongs inside the aggregate expression, not in a .where() call.

ARRAY JOIN: Flatten Arrays Into Rows

ARRAY JOIN goes the other direction — it explodes each array element into its own row, which is what you need when you want to GROUP BY individual elements. Unlike the functions above, this is a clause, and hypequery supports it as a native, typed builder method:

That's the classic tag-frequency query: explode tags, count rows per element. .arrayJoin() drops rows whose array is empty; .leftArrayJoin() keeps them, with the element column set to the type's default value (empty string for Array(String)). It's the same regular-vs-LEFT distinction as ordinary joins.

ARRAY JOIN also gives you the practical answer to array membership filters. ClickHouse's has(tags, 'checkout') works in SQL WHERE clauses, but hypequery's typed .where() operators don't include array membership, so the builder-native pattern is to flatten and filter on the element:

The .distinct() matters: after flattening, a user appears once per matching element, and dedup brings it back to one row per user. There's more depth here (multiple arrays joined in lockstep, LEFT ARRAY JOIN semantics, when flattening beats array functions) that the dedicated ARRAY JOIN guide covers.

The Pipeline: Collect, Prune, Transform in One Query

The three moves compose, and the composition is the payoff. Say you want each user's journey through the app over the last week: the pages they visited, without internal admin routes, normalized to lowercase for consistent downstream grouping.

The SQL reads inside-out — collect, then filter, then map:

And the hypequery version, with the row-level filters staying in typed .where() calls and only the array expression itself in raw SQL:

The equivalent without array functions is a subquery to filter events, an aggregation to collect them, and a post-processing pass in JavaScript to normalize: three network-visible stages instead of one scan. The array pipeline does all of it inside the aggregation, on the server, in a single pass over the data.

Numeric arrays compose the same way. "Engaged time per user, counting only views longer than a second":

One honest note on engaged_time_ms: summing UInt32 values widens to UInt64, which arrives in JS as a string. Convert with Number() if the values fit within a safe integer, or keep it as a string for display.

The Rest of the Toolkit, Briefly

You'll reach for these less often, but they round out the surface:

• arraySum(arr) — sum of elements; also takes a lambda form arraySum(x -> expr, arr).
• arrayCount(x -> cond, arr) — how many elements match; arrayCount(arr) alone counts non-zero elements.
• has(arr, elem) — 1 if the array contains the element. The go-to membership check in raw SQL WHERE clauses.
• hasAny(arr1, arr2) — 1 if the arrays share any element; hasAny(tags, ['promo', 'discount']) is "tagged with either".
• length(arr) — element count; selectExpr('length(tags)', 'tag_count') is often all the "array analytics" a dashboard needs.

All of them are expressions, so from hypequery they travel through selectExpr (or rawAs) inside .select([...]).

What's Typed and What Isn't

Worth being precise about the tradeoff in these examples. Everything the builder handles natively — .arrayJoin('tags'), .where(), .groupBy(), .orderBy() — is checked against your generated schema; a typo in a column name fails at compile time. Inside a selectExpr string, you're writing raw SQL: groupArray(page_paht) won't be caught until ClickHouse rejects it at runtime.

That's a deliberate design position rather than a gap. Array function calls are arbitrary nested expressions with lambdas; a builder API that tried to model arrayMap(x -> lower(x), arrayFilter(...)) as chained method calls would be longer than the SQL and harder to read. The practical pattern that keeps most of the safety: keep row selection, filtering, grouping, and ordering in typed builder methods, and confine raw SQL to the one expression that genuinely is an expression. The result column still lands in your typed result set under the alias you gave it in selectExpr.

Where to Go From Here

• How to use ARRAY JOIN in ClickHouse with TypeScript — the flattening side in full: multiple arrays, LEFT ARRAY JOIN, tag explosion patterns.
• Filter operators in the hypequery builder — the typed .where() surface used alongside every array expression above.
• ClickHouse for product analytics — the broader event-analytics patterns that journeys, tags, and engagement metrics feed into.