How to Use Array Functions in ClickHouse
Last updated July 25, 2026 · By the SaturnSQL team
arrayJoin expands an array into one row per element, arrayMap applies a lambda to each element, and has() checks membership. Arrays are first-class in ClickHouse, so these often replace joins against child tables.
Expand arrays into rows
arrayJoin multiplies rows: an event with three tags becomes three rows, one per tag. This is the ClickHouse equivalent of unnesting a child table, and it is usually faster because the array is stored inline with the row. Rows with empty arrays disappear entirely, which is why the notEmpty filter is written out here rather than left implicit.
SELECT user_id, arrayJoin(tags) AS tag
FROM events
WHERE notEmpty(tags);Transform and filter
arrayMap applies a lambda element by element and returns an array of the same length; arrayFilter is its filtering counterpart. has() tests membership and can use a bloom filter index if you have one on the column, so filtering with has(tags, 'checkout') stays fast on large tables. arrayExists and arrayAll cover the any and every cases.
SELECT arrayMap(x -> x * 2, [1, 2, 3]) AS doubled;
SELECT count()
FROM events
WHERE has(tags, 'checkout');Run this in SaturnSQL
SaturnSQL is a browser-based SQL editor for teams: shared query library, schema-aware autocomplete, and scheduled exports to Google Sheets and Slack.
Try it free