Array functions

Last updated Oct 1, 2026 Basin Pipelines scalar function implementations are based on Apache DataFusion ↗︎ (via Arroyo ↗︎) and these docs are derived from the DataFusion function reference.

array_append

Appends an element to the end of an array.

array_append(array, element)

Arguments

  • array: Array expression. Can be a constant, column, or function, and any combination of array operators.
  • element: Element to append to the array.

Example

> select array_append([1, 2, 3], 4);
+--------------------------------------+
| array_append(List([1,2,3]),Int64(4)) |
+--------------------------------------+
| [1, 2, 3, 4]                         |
+--------------------------------------+

Aliases

  • array_push_back
  • list_append
  • list_push_back

array_sort

Sort array.

array_sort(array, desc, nulls_first)

Arguments

  • array: Array expression. Can be a constant, column, or function, and any combination of array operators.
  • desc: Whether to sort in descending order( ASC or DESC).
  • nulls_first: Whether to sort nulls first( NULLS FIRST or NULLS LAST).

Example

> select array_sort([3, 1, 2]);
+-----------------------------+
| array_sort(List([3,1,2]))   |
+-----------------------------+
| [1, 2, 3]                   |
+-----------------------------+

Aliases

  • list_sort

array_resize

Resizes the list to contain size elements. Initializes new elements with value or empty if value is not set.

array_resize(array, size, value)

Arguments

  • array: Array expression. Can be a constant, column, or function, and any combination of array operators.
  • size: New size of given array.
  • value: Defines new elements' value or empty if value is not set.

Example

> select array_resize([1, 2, 3], 5, 0);
+-------------------------------------+
| array_resize(List([1,2,3],5,0))     |
+-------------------------------------+
| [1, 2, 3, 0, 0]                     |
+-------------------------------------+

Aliases

  • list_resize

array_cat

Alias of array_concat.

array_concat

Concatenates arrays.

array_concat(array[, ..., array_n])

Arguments

  • array: Array expression to concatenate. Can be a constant, column, or function, and any combination of array operators.
  • array_n: Subsequent array column or literal array to concatenate.

Example

> select array_concat([1, 2], [3, 4], [5, 6]);
+---------------------------------------------------+
| array_concat(List([1,2]),List([3,4]),List([5,6])) |
+---------------------------------------------------+
| [1, 2, 3, 4, 5, 6]                                |
+---------------------------------------------------+

Aliases

  • array_cat
  • list_cat
  • list_concat

array_contains

Alias of array_has.

array_has

Returns true if the array contains the element

array_has(array, element)

Arguments

  • array: Array expression. Can be a constant, column, or function, and any combination of array operators.
  • element: Scalar or Array expression. Can be a constant, column, or function, and any combination of array operators.

Aliases

  • list_has

array_has_all

Returns true if all elements of sub-array exist in array

array_has_all(array, sub-array)

Arguments

  • array: Array expression. Can be a constant, column, or function, and any combination of array operators.
  • sub-array: Array expression. Can be a constant, column, or function, and any combination of array operators.

Aliases

  • list_has_all

array_has_any

Returns true if any elements exist in both arrays

array_has_any(array, sub-array)

Arguments

  • array: Array expression. Can be a constant, column, or function, and any combination of array operators.
  • sub-array: Array expression. Can be a constant, column, or function, and any combination of array operators.

Aliases

  • list_has_any

array_dims

Returns an array of the array's dimensions.

array_dims(array)

Arguments

  • array: Array expression. Can be a constant, column, or function, and any combination of array operators.

Example

> select array_dims([[1, 2, 3], [4, 5, 6]]);
+---------------------------------+
| array_dims(List([1,2,3,4,5,6])) |
+---------------------------------+
| [2, 3]                          |
+---------------------------------+

Aliases

  • list_dims

array_distinct

Returns distinct values from the array after removing duplicates.

array_distinct(array)

Arguments

  • array: Array expression. Can be a constant, column, or function, and any combination of array operators.

Example

> select array_distinct([1, 3, 2, 3, 1, 2, 4]);
+---------------------------------+
| array_distinct(List([1,2,3,4])) |
+---------------------------------+
| [1, 2, 3, 4]                    |
+---------------------------------+

Aliases

  • list_distinct

array_element

Extracts the element with the index n from the array.

array_element(array, index)

Arguments

  • array: Array expression. Can be a constant, column, or function, and any combination of array operators.
  • index: Index to extract the element from the array.

Example

> select array_element([1, 2, 3, 4], 3);
+-----------------------------------------+
| array_element(List([1,2,3,4]),Int64(3)) |
+-----------------------------------------+
| 3                                       |
+-----------------------------------------+

Aliases

  • array_extract
  • list_element
  • list_extract

array_extract

Alias of array_element.

array_fill

Returns an array filled with copies of the given value.

DEPRECATED: use array_repeat instead!

array_fill(element, array)

Arguments

  • array: Array expression. Can be a constant, column, or function, and any combination of array operators.
  • element: Element to copy to the array.

flatten

Converts an array of arrays to a flat array

  • Applies to any depth of nested arrays
  • Does not change arrays that are already flat

The flattened array contains all the elements from all source arrays.

Arguments

  • array: Array expression Can be a constant, column, or function, and any combination of array operators.
flatten(array)

array_indexof

Alias of array_position.

array_intersect

Returns an array of elements in the intersection of array1 and array2.

array_intersect(array1, array2)

Arguments

  • array1: Array expression. Can be a constant, column, or function, and any combination of array operators.
  • array2: Array expression. Can be a constant, column, or function, and any combination of array operators.

Example

> select array_intersect([1, 2, 3, 4], [5, 6, 3, 4]);
+----------------------------------------------------+
| array_intersect([1, 2, 3, 4], [5, 6, 3, 4]);       |
+----------------------------------------------------+
| [3, 4]                                             |
+----------------------------------------------------+
> select array_intersect([1, 2, 3, 4], [5, 6, 7, 8]);
+----------------------------------------------------+
| array_intersect([1, 2, 3, 4], [5, 6, 7, 8]);       |
+----------------------------------------------------+
| []                                                 |
+----------------------------------------------------+

Aliases

  • list_intersect

array_join

Alias of array_to_string.

array_length

Returns the length of the array dimension.

array_length(array, dimension)

Arguments

  • array: Array expression. Can be a constant, column, or function, and any combination of array operators.
  • dimension: Array dimension.

Example

> select array_length([1, 2, 3, 4, 5]);
+---------------------------------+
| array_length(List([1,2,3,4,5])) |
+---------------------------------+
| 5                               |
+---------------------------------+

Aliases

  • list_length

array_ndims

Returns the number of dimensions of the array.

array_ndims(array, element)

Arguments

  • array: Array expression. Can be a constant, column, or function, and any combination of array operators.

Example

> select array_ndims([[1, 2, 3], [4, 5, 6]]);
+----------------------------------+
| array_ndims(List([1,2,3,4,5,6])) |
+----------------------------------+
| 2                                |
+----------------------------------+

Aliases

  • list_ndims

array_prepend

Prepends an element to the beginning of an array.

array_prepend(element, array)

Arguments

  • element: Element to prepend to the array.
  • array: Array expression. Can be a constant, column, or function, and any combination of array operators.

Example

> select array_prepend(1, [2, 3, 4]);
+---------------------------------------+
| array_prepend(Int64(1),List([2,3,4])) |
+---------------------------------------+
| [1, 2, 3, 4]                          |
+---------------------------------------+

Aliases

  • array_push_front
  • list_prepend
  • list_push_front

array_pop_front

Returns the array without the first element.

array_pop_front(array)

Arguments

  • array: Array expression. Can be a constant, column, or function, and any combination of array operators.

Example

> select array_pop_front([1, 2, 3]);
+-------------------------------+
| array_pop_front(List([1,2,3])) |
+-------------------------------+
| [2, 3]                        |
+-------------------------------+

Aliases

  • list_pop_front

array_pop_back

Returns the array without the last element.

array_pop_back(array)

Arguments

  • array: Array expression. Can be a constant, column, or function, and any combination of array operators.

Example

> select array_pop_back([1, 2, 3]);
+-------------------------------+
| array_pop_back(List([1,2,3])) |
+-------------------------------+
| [1, 2]                        |
+-------------------------------+

Aliases

  • list_pop_back

array_position

Returns the position of the first occurrence of the specified element in the array.

array_position(array, element)
array_position(array, element, index)

Arguments

  • array: Array expression. Can be a constant, column, or function, and any combination of array operators.
  • element: Element to search for position in the array.
  • index: Index at which to start searching.

Example

> select array_position([1, 2, 2, 3, 1, 4], 2);
+----------------------------------------------+
| array_position(List([1,2,2,3,1,4]),Int64(2)) |
+----------------------------------------------+
| 2                                            |
+----------------------------------------------+

Aliases

  • array_indexof
  • list_indexof
  • list_position

array_positions

Searches for an element in the array, returns all occurrences.

array_positions(array, element)

Arguments

  • array: Array expression. Can be a constant, column, or function, and any combination of array operators.
  • element: Element to search for positions in the array.

Example

> select array_positions([1, 2, 2, 3, 1, 4], 2);
+-----------------------------------------------+
| array_positions(List([1,2,2,3,1,4]),Int64(2)) |
+-----------------------------------------------+
| [2, 3]                                        |
+-----------------------------------------------+

Aliases

  • list_positions

array_push_back

Alias of array_append.

array_push_front

Alias of array_prepend.

array_repeat

Returns an array containing element count times.

array_repeat(element, count)

Arguments

  • element: Element expression. Can be a constant, column, or function, and any combination of array operators.
  • count: Value of how many times to repeat the element.

Example

> select array_repeat(1, 3);
+---------------------------------+
| array_repeat(Int64(1),Int64(3)) |
+---------------------------------+
| [1, 1, 1]                       |
+---------------------------------+

> select array_repeat([1, 2], 2);
+------------------------------------+
| array_repeat(List([1,2]),Int64(2)) |
+------------------------------------+
| [[1, 2], [1, 2]]                   |
+------------------------------------+

Aliases

  • list_repeat

array_remove

Removes the first element from the array equal to the given value.

array_remove(array, element)

Arguments

  • array: Array expression. Can be a constant, column, or function, and any combination of array operators.
  • element: Element to be removed from the array.

Example

> select array_remove([1, 2, 2, 3, 2, 1, 4], 2);
+----------------------------------------------+
| array_remove(List([1,2,2,3,2,1,4]),Int64(2)) |
+----------------------------------------------+
| [1, 2, 3, 2, 1, 4]                           |
+----------------------------------------------+

Aliases

  • list_remove

array_remove_n

Removes the first max elements from the array equal to the given value.

array_remove_n(array, element, max)

Arguments

  • array: Array expression. Can be a constant, column, or function, and any combination of array operators.
  • element: Element to be removed from the array.
  • max: Number of first occurrences to remove.

Example

> select array_remove_n([1, 2, 2, 3, 2, 1, 4], 2, 2);
+---------------------------------------------------------+
| array_remove_n(List([1,2,2,3,2,1,4]),Int64(2),Int64(2)) |
+---------------------------------------------------------+
| [1, 3, 2, 1, 4]                                         |
+---------------------------------------------------------+

Aliases

  • list_remove_n

array_remove_all

Removes all elements from the array equal to the given value.

array_remove_all(array, element)

Arguments

  • array: Array expression. Can be a constant, column, or function, and any combination of array operators.
  • element: Element to be removed from the array.

Example

> select array_remove_all([1, 2, 2, 3, 2, 1, 4], 2);
+--------------------------------------------------+
| array_remove_all(List([1,2,2,3,2,1,4]),Int64(2)) |
+--------------------------------------------------+
| [1, 3, 1, 4]                                     |
+--------------------------------------------------+

Aliases

  • list_remove_all

array_replace

Replaces the first occurrence of the specified element with another specified element.

array_replace(array, from, to)

Arguments

  • array: Array expression. Can be a constant, column, or function, and any combination of array operators.
  • from: Initial element.
  • to: Final element.