Array Functions

This section provides reference information for array functions in Databend. Array functions enable creation, manipulation, searching, and transformation of array data structures.

Array Creation & Construction

Function	Description	Example
ARRAY_CONSTRUCT	Creates an array from individual values	ARRAY_CONSTRUCT(1, 2, 3) → [1,2,3]
RANGE	Generates an array of sequential numbers	RANGE(1, 5) → [1,2,3,4]

Array Access & Information

Function	Description	Example
GET	Gets an element from an array by index	GET([1,2,3], 1) → 1
ARRAY_GET	Alias for GET function	ARRAY_GET([1,2,3], 1) → 1
CONTAINS	Checks if an array contains a specific value	CONTAINS([1,2,3], 2) → true
ARRAY_CONTAINS	Checks if an array contains a specific value	ARRAY_CONTAINS([1,2,3], 2) → true

Array Modification

Function	Description	Example
ARRAY_APPEND	Appends an element to the end of an array	ARRAY_APPEND([1,2], 3) → [1,2,3]
ARRAY_PREPEND	Prepends an element to the beginning of an array	ARRAY_PREPEND(0, [1,2]) → [0,1,2]
ARRAY_INSERT	Inserts an element at a specific position	ARRAY_INSERT([1,3], 1, 2) → [1,2,3]
ARRAY_REMOVE	Removes all occurrences of a specified element	ARRAY_REMOVE([1,2,2,3], 2) → [1,3]
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 Combination & Manipulation

Function	Description	Example
ARRAY_CONCAT	Concatenates multiple arrays	ARRAY_CONCAT([1,2], [3,4]) → [1,2,3,4]
ARRAY_SLICE	Extracts a portion of an array	ARRAY_SLICE([1,2,3,4], 1, 2) → [1,2]
SLICE	Alias for ARRAY_SLICE function	SLICE([1,2,3,4], 1, 2) → [1,2]
ARRAYS_ZIP	Combines multiple arrays element-wise	ARRAYS_ZIP([1,2], ['a','b']) → [(1,'a'),(2,'b')]

Array Set Operations

Function	Description	Example
ARRAY_DISTINCT	Returns unique elements from an array	ARRAY_DISTINCT([1,2,2,3]) → [1,2,3]
ARRAY_UNIQUE	Alias for ARRAY_DISTINCT function	ARRAY_UNIQUE([1,2,2,3]) → [1,2,3]
ARRAY_INTERSECTION	Returns common elements between arrays	ARRAY_INTERSECTION([1,2,3], [2,3,4]) → [2,3]
ARRAY_EXCEPT	Returns elements in first array but not in second	ARRAY_EXCEPT([1,2,3], [2,4]) → [1,3]
ARRAY_OVERLAP	Checks if arrays have common elements	ARRAY_OVERLAP([1,2,3], [3,4,5]) → true

Array Processing & Transformation

Function	Description	Example
ARRAY_TRANSFORM	Applies a function to each array element	ARRAY_TRANSFORM([1,2,3], x -> x * 2) → [2,4,6]
ARRAY_FILTER	Filters array elements based on a condition	ARRAY_FILTER([1,2,3,4], x -> x > 2) → [3,4]
ARRAY_REDUCE	Reduces array to a single value using aggregation	ARRAY_REDUCE([1,2,3], 0, (acc,x) -> acc + x) → 6
ARRAY_AGGREGATE	Aggregates array elements using a function	ARRAY_AGGREGATE([1,2,3], 'sum') → 6

Array Utility Functions

Function	Description	Example
ARRAY_COMPACT	Removes null values from an array	ARRAY_COMPACT([1,null,2,null,3]) → [1,2,3]
ARRAY_FLATTEN	Flattens nested arrays into a single array	ARRAY_FLATTEN([[1,2],[3,4]]) → [1,2,3,4]
ARRAY_REVERSE	Reverses the order of array elements	ARRAY_REVERSE([1,2,3]) → [3,2,1]
ARRAY_INDEXOF	Returns the index of first occurrence of an element	ARRAY_INDEXOF([1,2,3,2], 2) → 1
UNNEST	Expands an array into individual rows	UNNEST([1,2,3]) → 1, 2, 3 (as separate rows)