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

After ARRAY JOIN, the tags column changes from string[] (the array) to string (a single element). Because ARRAY JOIN is not currently wrapped as a dedicated builder helper here, you should annotate the rawQuery result shape explicitly.

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). In a hypequery project today, this is another case where raw SQL is the honest approach rather than a dedicated builder helper.

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 TypeScript behaviour to remember: after ARRAY JOIN, the exploded column's type changes from the array type to the element type. If your schema has tags: string[], the SQL result rows after ARRAY JOIN tags have tags: string. In a hypequery app today, model that explicitly in the rawQuery<T>() type parameter.