Scalar functions for manipulating arrays
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.
Arguments
Example
Aliases
array_sort
Sort array.
Arguments
ASC
or DESC
).NULLS FIRST
or NULLS LAST
).Example
Aliases
array_resize
Resizes the list to contain size elements. Initializes new elements with value or empty if value is not set.
Arguments
Example
Aliases
array_cat
Alias of array_concat.
array_concat
Concatenates arrays.
Arguments
Example
Aliases
array_contains
Alias of array_has.
array_has
Returns true if the array contains the element
Arguments
Aliases
array_has_all
Returns true if all elements of sub-array exist in array
Arguments
Aliases
array_has_any
Returns true if any elements exist in both arrays
Arguments
Aliases
array_dims
Returns an array of the array’s dimensions.
Arguments
Example
Aliases
array_distinct
Returns distinct values from the array after removing duplicates.
Arguments
Example
Aliases
array_element
Extracts the element with the index n from the array.
Arguments
Example
Aliases
array_extract
Alias of array_element.
array_fill
Returns an array filled with copies of the given value.
DEPRECATED: use array_repeat
instead!
Arguments
flatten
Converts an array of arrays to a flat array
The flattened array contains all the elements from all source arrays.
Arguments
array_indexof
Alias of array_position.
array_intersect
Returns an array of elements in the intersection of array1 and array2.
Arguments
Example
Aliases
array_join
Alias of array_to_string.
array_length
Returns the length of the array dimension.
Arguments
Example
Aliases
array_ndims
Returns the number of dimensions of the array.
Arguments
Example
Aliases
array_prepend
Prepends an element to the beginning of an array.
Arguments
Example
Aliases
array_pop_front
Returns the array without the first element.
Arguments
Example
Aliases
array_pop_back
Returns the array without the last element.
Arguments
Example
Aliases
array_position
Returns the position of the first occurrence of the specified element in the array.
Arguments
Example
Aliases
array_positions
Searches for an element in the array, returns all occurrences.
Arguments
Example
Aliases
array_push_back
Alias of array_append.
array_push_front
Alias of array_prepend.
array_repeat
Returns an array containing element count
times.
Arguments
Example
Aliases
array_remove
Removes the first element from the array equal to the given value.
Arguments
Example
Aliases
array_remove_n
Removes the first max
elements from the array equal to the given value.
Arguments
Example
Aliases
array_remove_all
Removes all elements from the array equal to the given value.
Arguments
Example
Aliases
array_replace
Replaces the first occurrence of the specified element with another specified element.
Arguments
Example
Aliases
array_replace_n
Replaces the first max
occurrences of the specified element with another specified element.
Arguments
Example
Aliases
array_replace_all
Replaces all occurrences of the specified element with another specified element.
Arguments
Example
Aliases
array_reverse
Returns the array with the order of the elements reversed.
Arguments
Example
Aliases
array_slice
Returns a slice of the array based on 1-indexed start and end positions.
Arguments
Example
Aliases
array_to_string
Converts each element to its text representation.
Arguments
Example
Aliases
array_union
Returns an array of elements that are present in both arrays (all elements from both arrays) with out duplicates.
Arguments
Example
Aliases
array_except
Returns an array of the elements that appear in the first array but not in the second.
Arguments
Example
Aliases
cardinality
Returns the total number of elements in the array.
Arguments
Example
empty
Returns 1 for an empty array or 0 for a non-empty array.
Arguments
Example
Aliases
generate_series
Similar to the range function, but it includes the upper bound.
Arguments
Example
list_append
Alias of array_append.
list_cat
Alias of array_concat.
list_concat
Alias of array_concat.
list_dims
Alias of array_dims.
list_distinct
Alias of array_dims.
list_element
Alias of array_element.
list_empty
Alias of empty.
list_except
Alias of array_element.
list_extract
Alias of array_element.
list_has
Alias of array_has.
list_has_all
Alias of array_has_all.
list_has_any
Alias of array_has_any.
list_indexof
Alias of array_position.
list_intersect
Alias of array_position.
list_join
Alias of array_to_string.
list_length
Alias of array_length.
list_ndims
Alias of array_ndims.
list_prepend
Alias of array_prepend.
list_pop_back
Alias of array_pop_back.
list_pop_front
Alias of array_pop_front.
list_position
Alias of array_position.
list_positions
Alias of array_positions.
list_push_back
Alias of array_append.
list_push_front
Alias of array_prepend.
list_repeat
Alias of array_repeat.
list_resize
Alias of array_resize.
list_remove
Alias of array_remove.
list_remove_n
Alias of array_remove_n.
list_remove_all
Alias of array_remove_all.
list_replace
Alias of array_replace.
list_replace_n
Alias of array_replace_n.
list_replace_all
Alias of array_replace_all.
list_reverse
Alias of array_reverse.
list_slice
Alias of array_slice.
list_sort
Alias of array_sort.
list_to_string
Alias of array_to_string.
list_union
Alias of array_union.
make_array
Returns an Arrow array using the specified input expressions.
array_empty
Alias of empty.
Arguments
Example
Aliases
make_list
Alias of make_array.
string_to_array
Splits a string in to an array of substrings based on a delimiter. Any substrings matching the optional null_str
argument are replaced with NULL.
SELECT string_to_array('abc##def', '##')
or SELECT string_to_array('abc def', ' ', 'def')
Arguments
NULL
Aliases
string_to_list
Alias of string_to_array.
trim_array
Removes the last n elements from the array.
DEPRECATED: use array_slice
instead!
Arguments
range
Returns an Arrow array between start and stop with step. SELECT range(2, 10, 3) -> [2, 5, 8]
or SELECT range(DATE '1992-09-01', DATE '1993-03-01', INTERVAL '1' MONTH);
The range start..end contains all values with start <= x < end. It is empty if start >= end.
Step can not be 0 (then the range will be nonsense.).
Note that when the required range is a number, it accepts (stop), (start, stop), and (start, stop, step) as parameters, but when the required range is a date, it must be 3 non-NULL parameters. For example,
are allowed in number ranges
but in date ranges, only
is allowed, and
are not allowed
Arguments
Aliases
Scalar functions for manipulating arrays
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.
Arguments
Example
Aliases
array_sort
Sort array.
Arguments
ASC
or DESC
).NULLS FIRST
or NULLS LAST
).Example
Aliases
array_resize
Resizes the list to contain size elements. Initializes new elements with value or empty if value is not set.
Arguments
Example
Aliases
array_cat
Alias of array_concat.
array_concat
Concatenates arrays.
Arguments
Example
Aliases
array_contains
Alias of array_has.
array_has
Returns true if the array contains the element
Arguments
Aliases
array_has_all
Returns true if all elements of sub-array exist in array
Arguments
Aliases
array_has_any
Returns true if any elements exist in both arrays
Arguments
Aliases
array_dims
Returns an array of the array’s dimensions.
Arguments
Example
Aliases
array_distinct
Returns distinct values from the array after removing duplicates.
Arguments
Example
Aliases
array_element
Extracts the element with the index n from the array.
Arguments
Example
Aliases
array_extract
Alias of array_element.
array_fill
Returns an array filled with copies of the given value.
DEPRECATED: use array_repeat
instead!
Arguments
flatten
Converts an array of arrays to a flat array
The flattened array contains all the elements from all source arrays.
Arguments
array_indexof
Alias of array_position.
array_intersect
Returns an array of elements in the intersection of array1 and array2.
Arguments
Example
Aliases
array_join
Alias of array_to_string.
array_length
Returns the length of the array dimension.
Arguments
Example
Aliases
array_ndims
Returns the number of dimensions of the array.
Arguments
Example
Aliases
array_prepend
Prepends an element to the beginning of an array.
Arguments
Example
Aliases
array_pop_front
Returns the array without the first element.
Arguments
Example
Aliases
array_pop_back
Returns the array without the last element.
Arguments
Example
Aliases
array_position
Returns the position of the first occurrence of the specified element in the array.
Arguments
Example
Aliases
array_positions
Searches for an element in the array, returns all occurrences.
Arguments
Example
Aliases
array_push_back
Alias of array_append.
array_push_front
Alias of array_prepend.
array_repeat
Returns an array containing element count
times.
Arguments
Example
Aliases
array_remove
Removes the first element from the array equal to the given value.
Arguments
Example
Aliases
array_remove_n
Removes the first max
elements from the array equal to the given value.
Arguments
Example
Aliases
array_remove_all
Removes all elements from the array equal to the given value.
Arguments
Example
Aliases
array_replace
Replaces the first occurrence of the specified element with another specified element.
Arguments
Example
Aliases
array_replace_n
Replaces the first max
occurrences of the specified element with another specified element.
Arguments
Example
Aliases
array_replace_all
Replaces all occurrences of the specified element with another specified element.
Arguments
Example
Aliases
array_reverse
Returns the array with the order of the elements reversed.
Arguments
Example
Aliases
array_slice
Returns a slice of the array based on 1-indexed start and end positions.
Arguments
Example
Aliases
array_to_string
Converts each element to its text representation.
Arguments
Example
Aliases
array_union
Returns an array of elements that are present in both arrays (all elements from both arrays) with out duplicates.
Arguments
Example
Aliases
array_except
Returns an array of the elements that appear in the first array but not in the second.
Arguments
Example
Aliases
cardinality
Returns the total number of elements in the array.
Arguments
Example
empty
Returns 1 for an empty array or 0 for a non-empty array.
Arguments
Example
Aliases
generate_series
Similar to the range function, but it includes the upper bound.
Arguments
Example
list_append
Alias of array_append.
list_cat
Alias of array_concat.
list_concat
Alias of array_concat.
list_dims
Alias of array_dims.
list_distinct
Alias of array_dims.
list_element
Alias of array_element.
list_empty
Alias of empty.
list_except
Alias of array_element.
list_extract
Alias of array_element.
list_has
Alias of array_has.
list_has_all
Alias of array_has_all.
list_has_any
Alias of array_has_any.
list_indexof
Alias of array_position.
list_intersect
Alias of array_position.
list_join
Alias of array_to_string.
list_length
Alias of array_length.
list_ndims
Alias of array_ndims.
list_prepend
Alias of array_prepend.
list_pop_back
Alias of array_pop_back.
list_pop_front
Alias of array_pop_front.
list_position
Alias of array_position.
list_positions
Alias of array_positions.
list_push_back
Alias of array_append.
list_push_front
Alias of array_prepend.
list_repeat
Alias of array_repeat.
list_resize
Alias of array_resize.
list_remove
Alias of array_remove.
list_remove_n
Alias of array_remove_n.
list_remove_all
Alias of array_remove_all.
list_replace
Alias of array_replace.
list_replace_n
Alias of array_replace_n.
list_replace_all
Alias of array_replace_all.
list_reverse
Alias of array_reverse.
list_slice
Alias of array_slice.
list_sort
Alias of array_sort.
list_to_string
Alias of array_to_string.
list_union
Alias of array_union.
make_array
Returns an Arrow array using the specified input expressions.
array_empty
Alias of empty.
Arguments
Example
Aliases
make_list
Alias of make_array.
string_to_array
Splits a string in to an array of substrings based on a delimiter. Any substrings matching the optional null_str
argument are replaced with NULL.
SELECT string_to_array('abc##def', '##')
or SELECT string_to_array('abc def', ' ', 'def')
Arguments
NULL
Aliases
string_to_list
Alias of string_to_array.
trim_array
Removes the last n elements from the array.
DEPRECATED: use array_slice
instead!
Arguments
range
Returns an Arrow array between start and stop with step. SELECT range(2, 10, 3) -> [2, 5, 8]
or SELECT range(DATE '1992-09-01', DATE '1993-03-01', INTERVAL '1' MONTH);
The range start..end contains all values with start <= x < end. It is empty if start >= end.
Step can not be 0 (then the range will be nonsense.).
Note that when the required range is a number, it accepts (stop), (start, stop), and (start, stop, step) as parameters, but when the required range is a date, it must be 3 non-NULL parameters. For example,
are allowed in number ranges
but in date ranges, only
is allowed, and
are not allowed
Arguments
Aliases