Array Functions

This page provides a comprehensive overview of Array functions in Databend, organized by functionality for easy reference.

Array Creation and Manipulation

Function	Description	Example
ARRAY_APPEND	Appends an element to the end of an array	`ARRAY_APPEND([1, 2], 3)` → `[1,2,3]`
ARRAY_CONCAT	Concatenates two arrays	`ARRAY_CONCAT([1, 2], [3, 4])` → `[1,2,3,4]`
ARRAY_PREPEND	Prepends an element to the beginning of an array	`ARRAY_PREPEND(0, [1, 2])` → `[0,1,2]`
ARRAY_DISTINCT	Removes duplicate elements from an array	`ARRAY_DISTINCT([1, 1, 2, 2])` → `[1,2]`
ARRAY_FLATTEN	Flattens nested arrays into a single array	`ARRAY_FLATTEN([[1, 2], [3, 4]])` → `[1,2,3,4]`
ARRAY_REMOVE_FIRST	Removes the first element from an array	`ARRAY_REMOVE_FIRST([1, 2, 3])` → `[2,3]`
ARRAY_REMOVE_LAST	Removes the last element from an array	`ARRAY_REMOVE_LAST([1, 2, 3])` → `[1,2]`
ARRAY_SORT	Sorts elements in an array	`ARRAY_SORT([3, 1, 2])` → `[1,2,3]`
ARRAY_UNIQUE	Removes duplicate elements from an array	`ARRAY_UNIQUE([1, 1, 2, 2])` → `[1,2]`
ARRAYS_ZIP	Combines multiple arrays into an array of tuples	`ARRAYS_ZIP([1, 2], ['a', 'b'])` → `[(1,'a'),(2,'b')]`
RANGE	Creates an array of integers in the specified range	`RANGE(1, 5)` → `[1,2,3,4]`

Function	Description	Example
ARRAY_GET / GET	Gets an element at the specified position	`ARRAY_GET([1, 2, 3], 1)` → `2`
ARRAY_LENGTH / ARRAY_SIZE	Returns the number of elements in an array	`ARRAY_LENGTH([1, 2, 3])` → `3`
ARRAY_INDEXOF	Returns the position of the first occurrence of an element	`ARRAY_INDEXOF([1, 2, 3], 2)` → `1`
ARRAY_CONTAINS / CONTAINS	Checks if an array contains a specific element	`CONTAINS([1, 2, 3], 2)` → `true`
SLICE / ARRAY_SLICE	Extracts a subarray	`SLICE([1, 2, 3, 4], 1, 2)` → `[2,3]`

Function	Description	Example
ARRAY_TRANSFORM	Applies a lambda function to each element	`ARRAY_TRANSFORM([1, 2, 3], x -> x * 2)` → `[2,4,6]`
ARRAY_FILTER	Filters elements based on a lambda condition	`ARRAY_FILTER([1, 2, 3], x -> x > 1)` → `[2,3]`
ARRAY_REDUCE	Reduces array to a single value using a lambda function	`ARRAY_REDUCE([1, 2, 3], 0, (s, x) -> s + x)` → `6`
ARRAY_APPLY	Applies a function to each element	`ARRAY_APPLY([1, 2, 3], x -> x * x)` → `[1,4,9]`
ARRAY_AGGREGATE	Applies an aggregate function to array elements	`ARRAY_AGGREGATE([1, 2, 3], 'sum')` → `6`
ARRAY_TO_STRING	Converts an array to a string with a delimiter	`ARRAY_TO_STRING([1, 2, 3], ',')` → `'1,2,3'`
UNNEST	Expands an array into a set of rows	`SELECT UNNEST([1, 2, 3])` → `1`, `2`, `3` (as rows)