How to Use ARRAY JOIN in ClickHouse with TypeScript

ClickHouse has native support for Array(T) columns — you can store an array of strings, integers, or other values in a single column. ARRAY JOIN is the mechanism for turning those arrays into rows, which is what you need when you want to aggregate or filter on individual array elements.

What ARRAY JOIN Does

Given a table with an array column:

| event_id | user_id | tags                          |
|----------|---------|-------------------------------|
| 1        | u1      | ['react', 'typescript', 'web']|
| 2        | u2      | ['python', 'data']            |
| 3        | u3      | []                            |

ARRAY JOIN tags explodes each row into one row per array element:

| event_id | user_id | tags       |
|----------|---------|------------|
| 1        | u1      | react      |
| 1        | u1      | typescript |
| 1        | u1      | web        |
| 2        | u2      | python     |
| 2        | u2      | data       |

Row 3 (empty array) disappears — regular ARRAY JOIN excludes rows with empty arrays. LEFT ARRAY JOIN keeps them, producing one row with tags = '' (the default empty value for the element type).

Schema Example

Generate the TypeScript schema:

The generated type will represent tags as string[] in TypeScript — an array of strings, matching the Array(String) column type.

Using ARRAY JOIN from a hypequery-based TypeScript app

arrayJoin() only accepts array-typed columns from the generated schema, so a scalar column is rejected at compile time. At runtime, ClickHouse returns one row per array element.

LEFT ARRAY JOIN — Keep Rows With Empty Arrays

Regular ARRAY JOIN drops rows where the array is empty. Use LEFT ARRAY JOIN when you need to preserve those rows:

LEFT ARRAY JOIN is analogous to a LEFT JOIN in standard SQL — you get at least one row per source row, even when the array is empty.

Practical Use Case: Tag Analytics

Here's a complete example — find the top tags used in events this month, with the count of unique users who used each tag:

Joining with Index

ClickHouse also supports array index access when you ARRAY JOIN — this is useful when you have parallel arrays where position matters:

This joins tags and tag_weights in parallel (both arrays must have the same length). For several arrays in the same clause, use a focused raw expression; the fluent arrayJoin() helper covers the common one-column case.

Event Properties Pattern

A common pattern in event tracking is to store event property keys and values as parallel arrays rather than nested objects (ClickHouse handles parallel arrays more efficiently than JSON parsing at scale):

Type Implications

The key runtime behaviour to remember: after ARRAY JOIN, ClickHouse returns the exploded column's element value on each row. The builder validates that the source column is an array; use an aliased selectExpr() when you also want to state a transformed result type explicitly.