Array functions

Arroyo’s Scalar function implementations are based on Apache DataFusion 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                               |
+---------------------------------+