---
title: "Master ClickHouse® array functions for analytics and manipulation"
excerpt: "ClickHouse array functions unlock powerful analytical patterns. These examples solve problems that frustrate most SQL developers."
authors: "Cameron Archer"
categories: "AI Resources"
createdOn: "2025-10-09 00:00:00"
publishedOn: "2025-10-10 00:00:00"
updatedOn: "2025-10-10 00:00:00"
status: "published"
---

Arrays in ClickHouse{% sup %}®{% /sup %} store multiple values in a single column, letting you keep related data together without joins or separate tables. This approach works particularly well for event properties, time series measurements, and any data where the number of elements varies from row to row.

This guide covers how to create and manipulate arrays in ClickHouse{% sup %}®{% /sup %}, from basic operations like filtering and transforming elements to advanced patterns for analytics queries and real-time APIs.

## Why use arrays in ClickHouse{% sup %}®{% /sup %} analytics

ClickHouse{% sup %}®{% /sup %} treats arrays as first-class data types, which means you can store multiple values in a single column without creating separate tables or complex joins. This approach works well when you have related data that naturally belongs together, like a list of product tags, a series of timestamps, or a collection of user actions.

Arrays can improve query performance because related data stays in the same row. Instead of joining a million-row events table to get a user's action history, you can store those actions as an array and retrieve everything in one read. This matters when you're running analytics queries that need to scan large datasets quickly.

You'll see arrays used for storing event properties, time series measurements, nested JSON attributes, and any data where the number of elements varies from row to row. For example, one user might have three tags while another has ten, and arrays handle this naturally without wasting storage space.

## Declaring and inserting array data

### Create table with array columns

When you create a table with array columns, you specify the type of data the array holds using `Array(Type)` syntax. The Type can be any ClickHouse{% sup %}®{% /sup %} data type like `String`, `Int32`, `Float64`, or even another array for nested structures.

```sql
CREATE TABLE user_events (
    user_id String,
    event_names Array(String),
    event_timestamps Array(DateTime),
    event_values Array(Float64)
) ENGINE = MergeTree()
ORDER BY user_id;
```

All elements in an array have to be the same type. ClickHouse{% sup %}®{% /sup %} enforces this when you insert data, so you can't mix strings and numbers in the same array. Nested arrays like `Array(Array(String))` are possible but less common because they add complexity.

### Insert rows with literals and JSONEachRow

You can insert array data using bracket notation `[1, 2, 3]` or the `array()` function, which do the same thing. Both work in `INSERT` statements and `SELECT` queries.

```sql
INSERT INTO user_events VALUES 
    ('user_123', ['login', 'view_page', 'logout'], 
     [toDateTime('2024-01-15 10:00:00'), toDateTime('2024-01-15 10:05:00'), toDateTime('2024-01-15 10:15:00')],
     [1.0, 2.5, 0.0]);
```

When you're ingesting JSON data with nested arrays, the `JSONEachRow` format automatically parses JSON arrays into ClickHouse{% sup %}®{% /sup %} array columns as long as your schema matches:

```sql
INSERT INTO user_events FORMAT JSONEachRow
{"user_id": "user_456", "event_names": ["signup", "purchase"], "event_timestamps": ["2024-01-16 14:30:00", "2024-01-16 14:35:00"], "event_values": [0.0, 99.99]}
```

## Essential array functions every engineer needs to know

### ArrayElement and indexOf

The `arrayElement(arr, index)` function accesses elements by their position using 1-based indexing, where the first element is at position 1. Most people use the bracket notation `arr[index]` instead because it's shorter and more familiar.

```sql
SELECT arrayElement(['apple', 'banana', 'orange'], 2); -- Returns 'banana'
SELECT ['apple', 'banana', 'orange'][1]; -- Returns 'apple'
```

Negative indices count backward from the end, so `-1` gives you the last element and `-2` gives you the second-to-last. The `indexOf(arr, value)` function returns the 1-based position of the first match, or 0 if the value isn't found.

```sql
SELECT ['red', 'green', 'blue'][-1]; -- Returns 'blue'
SELECT indexOf(['red', 'green', 'blue'], 'green'); -- Returns 2
```

### length and arraySlice

The `length(arr)` function tells you how many elements are in an array. You can use this to filter rows by array size or calculate statistics about array dimensions.

```sql
SELECT length([10, 20, 30, 40]); -- Returns 4
SELECT user_id FROM user_events WHERE length(event_names) > 5;
```

The `arraySlice(arr, offset, length)` function extracts a portion of an array starting at a given position (1-based) and continuing for a specified number of elements. If you ask for more elements than exist, you get everything from the offset to the end.

### arrayConcat and arrayDistinct

The `arrayConcat(arr1, arr2, ...)` function combines multiple arrays into one, keeping elements in order. You can pass as many arrays as you want, and they'll be joined sequentially.

```sql
SELECT arrayConcat([1, 2], [3, 4], [5]); -- Returns [1, 2, 3, 4, 5]
```

The `arrayDistinct(arr)` function removes duplicate elements. The order of elements in the result isn't guaranteed, so if order matters, combine this with `arraySort()`.

## Filtering data with ClickHouse{% sup %}®{% /sup %} arrayFilter

The `arrayFilter()` function applies a boolean condition to each element and returns a new array with only the elements where the condition is true. The syntax uses lambda expressions: `arrayFilter(x -> condition, arr)` where `x` represents each element.

```sql
SELECT arrayFilter(x -> x > 10, [5, 15, 8, 20, 3]); -- Returns [15, 20]
SELECT arrayFilter(x -> x LIKE '%error%', ['info', 'error_log', 'warning', 'error_fatal']); 
-- Returns ['error_log', 'error_fatal']
```

This comes in handy when you're filtering event arrays based on conditions, like extracting only high-value purchases or specific event types. The lambda variable can have any name, though `x` is what most people use.

### Boolean predicates on nested events

When you're working with parallel arrays (like separate arrays for event names and values), you can filter based on conditions that reference multiple arrays at once. ClickHouse{% sup %}®{% /sup %} supports lambda functions with multiple parameters: `arrayFilter((x, y) -> condition, arr1, arr2)`.

```sql
SELECT arrayFilter((event, value) -> value > 50, 
                   ['purchase', 'view', 'purchase', 'click'], 
                   [25.0, 10.0, 75.0, 5.0]); 
-- Returns ['purchase'] (the third event where value > 50)
```

### Combining arrayFilter with arrayMap

You can chain array functions to filter and then transform elements in one query. The `arrayMap()` function applies a transformation to each element, and when you combine it with `arrayFilter()`, you get a complete data pipeline.

```sql
SELECT arrayMap(x -> x * 2, arrayFilter(x -> x > 10, [5, 15, 8, 20, 3])); 
-- Returns [30, 40] (filters to [15, 20], then doubles each)
```

## Unnesting arrays with ClickHouse{% sup %}®{% /sup %} array join

The `ARRAY JOIN` clause expands arrays into separate rows, creating one output row for each array element.

```sql
SELECT user_id, event_name
FROM user_events
ARRAY JOIN event_names AS event_name;
```

If a row has an array with three elements, `ARRAY JOIN` produces three output rows, each with one element. The `LEFT ARRAY JOIN` variant keeps rows with empty arrays, producing a single row with NULL values for the array columns.

### Lateral view versus array join performance

The `ARRAY JOIN` operation in ClickHouse{% sup %}®{% /sup %} is optimized for expanding arrays and typically performs better than using subqueries with the `arrayJoin()` function, with properly configured indexes reducing query time from [0.720 to 0.012 seconds](https://clickhouse.com/docs/best-practices/use-data-skipping-indices-where-appropriate?utm_source=openai) on large datasets.

```sql
SELECT user_id, event_name, event_timestamp
FROM user_events
ARRAY JOIN event_names AS event_name, event_timestamps AS event_timestamp;
```

For queries that aggregate data after expansion, think about whether you actually need to unnest the arrays or if array functions like `arraySum()` or `arrayFilter()` can produce the same result more efficiently.

## Transforming arrays with map reduce and other higher-order helpers

### arrayMap for element-wise transformation

The `arrayMap()` function applies a transformation to every element in an array, returning a new array with the transformed values. The lambda syntax is `arrayMap(x -> expression, arr)` where the expression defines how each element changes.

```sql
SELECT arrayMap(x -> x * 2, [1, 2, 3, 4]); -- Returns [2, 4, 6, 8]
SELECT arrayMap(x -> upper(x), ['hello', 'world']); -- Returns ['HELLO', 'WORLD']
```

This works with any data type and any transformation, including type conversions. You can convert an array of integers to strings or extract specific fields from complex types.

### arrayReduce for aggregation in place

The `arrayReduce()` function applies any ClickHouse{% sup %}®{% /sup %} aggregate function to array elements without a `GROUP BY` clause. The syntax is `arrayReduce('aggregate_function', arr)` where the aggregate function name is a string.

```sql
SELECT arrayReduce('sum', [10, 20, 30, 40]); -- Returns 100
SELECT arrayReduce('avg', [5.5, 10.5, 15.5]); -- Returns 10.5
```

This is useful when you want to aggregate array values within a single row rather than across multiple rows. Any aggregate function that ClickHouse{% sup %}®{% /sup %} supports, including custom ones, works with `arrayReduce()`.

### arrayZip and arrayEnumerate

The `arrayZip()` function combines multiple arrays element-by-element, creating an array of tuples. Each tuple contains one element from each input array at the same position, and the result length matches the shortest input array.

```sql
SELECT arrayZip(['a', 'b', 'c'], [1, 2, 3]); 
-- Returns [('a', 1), ('b', 2), ('c', 3)]
```

The `arrayEnumerate()` function adds sequential index numbers to array elements, returning an array of integers from 1 to the array length. This helps when you need to track element positions during transformations, including scenarios like [forward and backfilling values using array functions](https://www.tinybird.co/blog-posts/tips-7-filling-null-values-on-clickhouse).

## Aggregating arrays for metrics and dashboards

### groupArray and groupUniqArray

The `groupArray()` aggregate function collects values from multiple rows into a single array during aggregation. This is useful for creating lists of related items grouped by a common dimension.

```sql
SELECT user_id, groupArray(event_name) AS all_events
FROM user_events
ARRAY JOIN event_names AS event_name
GROUP BY user_id;
```

You can limit the array size by passing a maximum length parameter: `groupArray(100)(column)` collects up to 100 elements. The `groupUniqArray()` function works the same way but only includes distinct values, automatically removing duplicates.

### arraySum arrayAvg arrayMin arrayMax

ClickHouse{% sup %}®{% /sup %} provides built-in aggregate functions that work directly on array columns without `arrayReduce()`. These functions are optimized for common operations and easier to read, contributing to [faster dashboard performance](https://www.tinybird.co/blog-posts/7-tips-to-make-your-dashboards-faster).

```sql
SELECT user_id, 
       arraySum(event_values) AS total_value,
       arrayAvg(event_values) AS avg_value,
       arrayMin(event_values) AS min_value,
       arrayMax(event_values) AS max_value
FROM user_events;
```

These functions only work with numeric arrays and return a single numeric value per row. For non-numeric aggregations or custom logic, use `arrayReduce()` instead.

## Performance tips for large arrays at scale

### Skip indexes and sparse columns

When you filter on array contents using functions like `has(arr, value)`, ClickHouse{% sup %}®{% /sup %} can use [bloom filter skip indexes](https://www.tinybird.co/blog-posts/using-bloom-filter-text-indexes-in-clickhouse) to avoid reading unnecessary data blocks. Create a bloom filter index on array columns that appear frequently in WHERE clauses.

```sql
CREATE TABLE events (
    user_id String,
    tags Array(String),
    INDEX tags_idx tags TYPE bloom_filter GRANULARITY 1
) ENGINE = MergeTree()
ORDER BY user_id;
```

Arrays with many NULL or empty values can be stored efficiently using the `Sparse` column codec, which compresses runs of default values. This reduces storage and improves query performance when most rows have empty arrays.

### Avoiding arrayJoin on hot paths

The `arrayJoin()` function and `ARRAY JOIN` clause can multiply the number of rows processed, which impacts query performance on large datasets. When possible, use array functions like `arrayFilter()`, `arrayMap()`, or aggregate functions to work with arrays without expansion.

For frequently-executed queries, consider pre-computing unnested data in a [materialized view](https://www.tinybird.co/blog-posts/what-are-materialized-views-and-why-do-they-matter-for-realtime) rather than performing `ARRAY JOIN` operations on every query. This trades storage space for query speed.

## Create real-time array-processing anaytics APIs with Tinybird

[Tinybird](https://tinybird.co) provides a [managed ClickHouse{% sup %}®{% /sup %} service](https://www.tinybird.co/clickhouse) with infrastructure and tooling for local development, streaming ingestion, and API hosting. You can use Tinybird to deploy ClickHouse{% sup %}®{% /sup %} queries as hosted and scalable REST APIs that integrate with your app.

Install the Tinybird CLI and start a local ClickHouse{% sup %}®{% /sup %} container for development:

### 1. Set up local workspace

```bash
curl -L tinybird.co | sh
tb login
tb local start
```

The [local container](https://www.tinybird.co/docs/forward/install-tinybird/local) runs Tinybird's managed ClickHouse{% sup %}®{% /sup %} on your machine, letting you develop and test queries before deploying to Tinybird Cloud.

### 2. Create data source with NDJSON events

Create a sample data file called `events.ndjson` with array columns:

```json
{"user_id": "user_001", "event_names": ["login", "view_dashboard", "logout"], "event_values": [0.0, 1.5, 0.0]}
{"user_id": "user_002", "event_names": ["signup", "add_payment", "purchase"], "event_values": [0.0, 0.0, 99.99]}
{"user_id": "user_003", "event_names": ["login", "search", "view_product"], "event_values": [0.0, 0.0, 0.5]}
```

Create the data source and load the data:

```bash
tb datasource create events --file events.ndjson
tb build
tb datasource append events --file events.ndjson
```

### 3. Build pipe with arrayFilter and arrayJoin

Create a file called `high_value_events.pipe` that filters and expands arrays:

```sql
TOKEN high_value_events_read READ

NODE filter_events
SQL >
    %
    SELECT 
        user_id,
        arrayFilter((name, value) -> value > {{Float64(min_value, 1.0)}}, 
                    event_names, 
                    event_values) AS filtered_names,
        arrayFilter(value -> value > {{Float64(min_value, 1.0)}}, 
                    event_values) AS filtered_values
    FROM events
    WHERE length(filtered_names) > 0

NODE expand_events
SQL >
    %
    SELECT 
        user_id,
        event_name,
        event_value
    FROM filter_events
    ARRAY JOIN filtered_names AS event_name, filtered_values AS event_value
    ORDER BY event_value DESC
    LIMIT {{Int32(limit, 10)}}

TYPE endpoint
```

This pipe filters events based on a minimum value parameter, then expands the results into individual rows. With this approach, you can even build a [Google Analytics alternative in 3 minutes](https://www.tinybird.co/blog-posts/google-analytics-alternative-in-3-minutes) using array functions to process event streams.

### 4. Deploy and test API endpoint

Deploy your data source and pipe to Tinybird Cloud:

```bash
tb --cloud deploy
```

Get the API endpoint URL with an example curl command:

```bash
curl "https://api.us-east.tinybird.co/v0/pipes/high_value_events.json?min_value=50&limit=5&token=p.ey..."
```

Test the API with different parameters:

```bash
curl "https://api.us-east.tinybird.co/v0/pipes/high_value_events.json?min_value=50&limit=5&token=p.ey..."
```

The response includes filtered and expanded events as JSON with metadata about the query execution.

## Ship analytics faster with Tinybird managed ClickHouse{% sup %}®{% /sup %}

If you're looking for the [best database for real-time analytics](https://www.tinybird.co/blog/best-database-for-real-time-analytics), ClickHouse{% sup %}®{% /sup %} is a strong choice, and Tinybird handles ClickHouse{% sup %}®{% /sup %} infrastructure so developers can focus on building features rather than managing databases. The platform automatically scales compute and storage, optimizes query performance, and provides built-in monitoring without dedicated DevOps resources.

For workloads with heavy array processing, Tinybird's managed service handles memory management and optimization that would otherwise require manual tuning. The platform also provides streaming ingestion from sources like Kafka and a [simple HTTP streaming endpoint](https://www.tinybird.co/docs/forward/get-data-in/events-api), making it easy to get array data into ClickHouse{% sup %}®{% /sup %} in real time.

To get started, [sign up for a free Tinybird account](https://cloud.tinybird.co/signup) and follow the [documentation](https://www.tinybird.co/docs) to create your first data source with array columns. The CLI supports local development with instant feedback, and deployment to production takes seconds.

## FAQs about ClickHouse{% sup %}®{% /sup %} array functions

### What is the maximum array length in ClickHouse{% sup %}®{% /sup %}?

ClickHouse{% sup %}®{% /sup %} arrays are limited by available memory rather than a hard element count. Large arrays may impact query performance and storage efficiency, so consider alternative approaches like separate tables for arrays with thousands of elements.

### How do higher-order functions perform compared to normal loops?

Higher-order functions like `arrayMap()` and `arrayFilter()` are vectorized and typically faster than equivalent subqueries or joins for array processing. ClickHouse{% sup %}®{% /sup %} optimizes these operations at the query execution level.

### Can I index elements inside an array column?

ClickHouse{% sup %}®{% /sup %} supports bloom filter skip indexes on array contents using the `has()` function with a default [false positive rate of 2.5%](https://clickhouse.com/docs/en/optimize/skipping-indexes), but individual element indexing requires array unnesting. Bloom filters help skip data blocks that don't contain specific array values.

### Does array join always process elements sequentially?

`ARRAY JOIN` operations can be parallelized across multiple threads when processing large datasets, though individual array expansion happens sequentially. Query performance depends on array sizes and the number of rows being processed.

### How do I stream nested arrays from Kafka into ClickHouse{% sup %}®{% /sup %}?

Use `JSONEachRow` format with properly typed array columns in your table schema, ensuring JSON structure matches expected array types. Tinybird provides managed Kafka connectors that handle schema validation and streaming ingestion automatically.