In ClickHouse, how do arrayMap and arrayFilter with lambdas transform an Array column?

## Arrays are a first-class column type In ClickHouse, `Array(T)` is a normal column type you declare in a `MergeTree` table, not an escape hatch. Physically it is stored as a flat column of element values plus an offsets column marking where each row's array ends, so arrays read and compress well. Arrays are **1-indexed**: `arr[1]` is the first element and `length(arr)` is the size. Because an array is a per-row value, the idiomatic way to work with it is the family of **higher-order array functions**, which take a lambda and apply it element-wise inside each row. ## The lambda A lambda is written with an arrow: `x -> x * 2`, or with several parameters, `(x, y) -> x + y`. Three rules matter: 1. A lambda is only valid **in the argument position of a higher-order function**. There is no lambda value type, so you cannot alias one, store it in a column, or pass it around. 2. A lambda closes over the surrounding row: it can reference other columns and constants, e.g. `arrayFilter(x -> x >= min_value, prices)` where `min_value` is a column. 3. With several array arguments, the lambda takes one parameter per array and the arrays are traversed **in parallel**, so they must all have the same length in that row; otherwise ClickHouse throws. ## The common functions ```sql SELECT arrayMap(x -> x * 2, [1, 2, 3]) AS doubled, -- [2,4,6] arrayFilter(x -> x > 1, [1, 2, 3]) AS kept, -- [2,3] arrayCount(x -> x > 1, [1, 2, 3]) AS n_big, -- 2 arraySum(x -> x * x, [1, 2, 3]) AS sum_squares, -- 14 arrayExists(x -> x = 2, [1, 2, 3]) AS any_two, -- 1 arrayAll(x -> x > 0, [1, 2, 3]) AS all_positive -- 1 ``` Without a lambda, several of these degrade to the obvious aggregate over the array: `arraySum(arr)`, `arrayMin(arr)`, `arrayMax(arr)`. `arraySort(x -> -x, arr)` sorts by the lambda's result rather than by the element itself, and `arraySort((x, y) -> y, arr, keys)` sorts `arr` by a parallel key array. For membership you usually want `has(arr, value)` or `indexOf(arr, value)` rather than a lambda. ## The mental model that matters There are three different "loops" in ClickHouse SQL and candidates confuse them: - **Higher-order array functions** loop over elements *inside one row* and return a value for that row. Row count is unchanged. - **Aggregate functions** loop over *rows* and collapse them into one value per group. - **`ARRAY JOIN`** (and the `arrayJoin` function) *expands* one row into many, one per element. If your report needs one output row per tag, that is `ARRAY JOIN`. If it needs one output row per event with a computed property of its tags, that is a higher-order function. Choosing the array-function path keeps the query on one row per event, which is usually far cheaper than expanding and re-grouping. ## Performance and pitfalls Higher-order functions execute vectorized over the flat element storage, so they are fast, but each one that returns an array (`arrayMap`, `arrayFilter`) allocates a new array. Prefer a single pass: `arrayCount(x -> x > 10, arr)` rather than `length(arrayFilter(x -> x > 10, arr))`, and `arrayExists(...)` rather than `arrayCount(...) > 0`. Other things that bite: - Empty arrays are legal and common. `arraySum([])` is 0, `arrayFirst` returns the element type's default, and index access past the end returns a default rather than raising. - Mismatched lengths in a multi-array lambda raise an exception at runtime, not at parse time, so it can appear only for a subset of rows. - `Array(Nullable(T))` propagates NULLs through the lambda; if you want NULLs skipped you must filter them explicitly. - Arrays are not a substitute for a real column when the set of attributes is fixed and known — a real column prunes and compresses better. ## What interviewers are checking They want to see that you write ClickHouse rather than portable SQL: that your first instinct for "how many of this row's items cost more than 100" is `arrayCount(x -> x > 100, prices)` and not an unnest-and-regroup. Being able to say plainly that lambdas run per row, are only legal as function arguments, and can close over other columns is the whole answer at this level.