ClickHouse Array & Conditional Functions: has, arrayMap, multiIf

ClickHouse has a full set of lambda functions for arrays, plus its own way of defaulting nulls and branching on conditions.

Common

• ifNull(x, default) — default value (there’s no nvl)
• has(arr, val) — array membership check (equivalent to array_contains)
• groupArray() / groupUniqArray() — roll multiple rows into an array
• if(c, t, f) / multiIf(...) — compact conditionals
• arrayMap() / arrayFilter() / arrayExists() / arrayAll()
  • arrayMap(x -> ..., array)
  • arrayFilter(x -> ..., array)
  • arrayExists(x -> ..., array): equivalent to Python’s any()
  • arrayAll(x -> ..., arr): equivalent to Python’s all()
• toDate() / formatDateTime() / toStartOfMonth() — date handling

if / multiIf

SELECT
    user_id,
    -- Simple binary classification using if()
    if(days_active <= 30, 'Active', 'Churned') AS user_status,

    -- Multi-tier segmentation using multiIf()
    multiIf(
        total_spent >= 5000 AND days_active <= 14, 'VIP_Active',
        total_spent >= 5000, 'VIP_At_Risk',
        total_spent >= 1000 AND days_active <= 30, 'Loyal_Regular',
        total_spent < 1000 AND days_active > 90, 'Dead_Account',
        'Standard'
    ) AS marketing_segment
FROM user_metrics_local;

arrayFilter / arrayExists

arrayFilter drops elements that don’t match the lambda; arrayExists returns 1 as soon as one element matches, like an inline EXISTS subquery.

SELECT
    request_id,
    -- Keep only the latency elements exceeding 500ms
    arrayFilter(
        latency -> latency > 500,
        microservice_latencies
    ) AS slow_dependencies
FROM api_gateway_logs
WHERE arrayExists(latency -> latency > 500, microservice_latencies);

---

References

• ClickHouse Array Functions — https://clickhouse.com/docs/en/sql-reference/functions/array-functions

Related: back to the Cross-Engine DB Query overview; see also ClickHouse data types and the Spark counterpart, Spark array functions and collect.