Array functions

Build, inspect, transform and flatten arrays.

Function	Type	What it does
`aggregate`	Scalar	Combines array elements into a running value, with an optional final transformation.
`array`	Scalar	Constructs an array from expressions.
`array_append`	Scalar	Adds an element to the end of an array.
`array_compact`	Scalar	Removes NULL elements from an array.
`array_concat`	Scalar	Concatenates arrays in argument order.
`array_contains`	Scalar	Returns TRUE when the array contains the search value.
`array_distinct`	Scalar	Removes duplicate elements, keeping their first occurrence.
`array_intersect`	Scalar	Returns distinct elements that occur in both arrays.
`array_intersection`	Scalar	Returns distinct elements that occur in both arrays.
`array_join`	Scalar	Joins array elements as text.
`array_max`	Scalar	Returns the largest non-NULL element of an array.
`array_min`	Scalar	Returns the smallest non-NULL element of an array.
`array_position`	Scalar	Returns the first 1-based position of an element in an array.
`array_prepend`	Scalar	Adds an element to the start of an array.
`array_size`	Scalar	Counts the elements in an array.
`array_slice`	Scalar	Returns an inclusive slice using 1-based bounds.
`array_sort`	Scalar	Sorts an array naturally or with a comparator lambda.
`array_to_string`	Scalar	Joins array elements as text.
`arrays_overlap`	Scalar	Tests whether arrays share an element.
`cardinality`	Scalar	Counts the elements in an array or entries in a map.
`element_at`	Scalar	Returns an array element by position or a map value by key.
`filter`	Scalar	Keeps array elements whose lambda returns TRUE.
`filter_array`	Scalar	Keeps array elements whose lambda returns TRUE.
`flatten`	Scalar · Table	Flattens one array nesting level in Databricks scalar context, or expands a collection as a table in Snowflake-style context.
`get`	Scalar	Returns the element at a zero-based array index.
`reduce`	Scalar	Combines array elements into a running value, with an optional final transformation.
`sequence`	Scalar	Builds an inclusive integer, date or timestamp sequence.
`size`	Scalar	Counts the elements in an array or entries in a map.
`slice`	Scalar	Returns an inclusive slice using 1-based bounds.
`sort_array`	Scalar	Sorts array elements.
`subscript`	Being confirmed	Previous docs; being confirmed.
`transform`	Scalar	Applies a lambda to array elements.
`try_element_at`	Scalar	Returns an array element by position or a map value by key.
`unnest`	Being confirmed	Previous docs; being confirmed.

Last modified on November 3, 2026

Query Engine

Get started

Connect data

Run clusters

Query

Connect your tools

Secure and govern

Troubleshoot

Reference

Concepts