SQL array functions
Use array functions to create and operate on Arrow arrays or lists in SQL queries.
- array_any_value
- array_append
- array_cat
- array_concat
- array_contains
- array_dims
- array_distance
- array_distinct
- array_element
- array_empty
- array_except
- array_extract
- array_has
- array_has_all
- array_has_any
- array_indexof
- array_intersect
- array_join
- array_length
- array_max
- array_min
- array_ndims
- array_pop_back
- array_pop_front
- array_position
- array_positions
- array_prepend
- array_push_back
- array_push_front
- array_remove
- array_remove_all
- array_remove_n
- array_repeat
- array_replace
- array_replace_all
- array_replace_n
- array_resize
- array_reverse
- array_slice
- array_sort
- array_to_string
- array_union
- arrays_overlap
- cardinality
- empty
- flatten
- generate_series
- list_any_value
- list_append
- list_cat
- list_concat
- list_contains
- list_dims
- list_distance
- list_distinct
- list_element
- list_empty
- list_except
- list_extract
- list_has
- list_has_all
- list_has_any
- list_indexof
- list_intersect
- list_join
- list_length
- list_max
- list_ndims
- list_pop_back
- list_pop_front
- list_position
- list_positions
- list_prepend
- list_push_back
- list_push_front
- list_remove
- list_remove_all
- list_remove_n
- list_repeat
- list_replace
- list_replace_all
- list_replace_n
- list_resize
- list_reverse
- list_slice
- list_sort
- list_to_string
- list_union
- make_array
- make_list
- range
- string_to_array
- string_to_list
array_any_value
Returns the first non-null element in the array.
array_any_value(array)
Arguments
- array: Array expression. Can be a constant, column, or function, and any combination of array operators.
Aliases
list_any_value
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.
Aliases
list_append
array_push_back
list_push_back
array_cat
Alias of array_concat.
array_concat
Concatenates multiple arrays into a single array.
array_concat(array[, ..., array_n])
Arguments
- array: Array expression. Can be a constant, column, or function, and any combination of array operators.
- array_n: Subsequent array column or literal array to concatenate.
Aliases
array_cat
list_concat
list_cat
array_contains
Alias of array_has.
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.
Aliases
list_dims
array_distance
Returns the Euclidean distance between two input arrays of equal length.
array_distance(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.
Aliases
list_distance
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.
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 use to extract the element from the array.
Aliases
array_extract
list_element
list_extract
array_empty
Alias of empty.
array_except
Returns an array containing elements from the first array that are not present in the second array.
array_except(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.
Aliases
list_except
array_extract
Alias of array_element.
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_contains
list_contains
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 at least one element appears 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
arrays_overlap
array_indexof
Alias of array_position.
array_intersect
Returns an array containing only the elements that appear in both 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.
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. Default is
1
.
Aliases
list_length
array_max
Returns the maximum value in the array.
array_max(array)
Arguments
- array: Array expression. Can be a constant, column, or function, and any combination of array operators.
Aliases
list_max
array_min
Returns the minimum value in the array.
array_min(array)
Arguments
- array: Array expression. Can be a constant, column, or function, and any combination of array operators.
array_ndims
Returns the number of dimensions of the array.
array_ndims(array)
Arguments
- array: Array expression. Can be a constant, column, or function, and any combination of array operators.
Aliases
list_ndims
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.
Aliases
list_pop_back
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.
Aliases
list_pop_front
array_position
Returns the position of the first occurrence of the specified element in the array, or NULL if not found.
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 (1-indexed). Default is
1
.
Aliases
list_position
array_indexof
list_indexof
array_positions
Searches for an element in the array and returns the position or index of each occurrence.
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 position in the array.
Aliases
list_positions
array_prepend
Prepends an element to the beginning of an array.
array_prepend(element, array)
Arguments
- array: Array expression. Can be a constant, column, or function, and any combination of array operators.
- element: Element to prepend to the array.
Aliases
list_prepend
array_push_front
list_push_front
array_push_back
Alias of array_append.
array_push_front
Alias of array_prepend.
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 remove from the array.
Aliases
list_remove
array_remove_all
Removes all elements from the array equal to the specified 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.
Aliases
list_remove_all
array_remove_n
Removes the first max
elements from the array equal to the specified 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 remove from the array.
- max: Maximum number of occurrences to remove.
Aliases
list_remove_n
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: Number of times to repeat the element.
Aliases
list_repeat
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: Element to replace.
- to: Replacement element.
Aliases
list_replace
array_replace_all
Replaces all occurrences of the specified element with another specified element.
array_replace_all(array, from, to)
Arguments
- array: Array expression. Can be a constant, column, or function, and any combination of array operators.
- from: Element to replace.
- to: Replacement element.
Aliases
list_replace_all
array_replace_n
Replaces the first max
occurrences of the specified element with another
specified element.
array_replace_n(array, from, to, max)
Arguments
- array: Array expression. Can be a constant, column, or function, and any combination of array operators.
- from: Element to replace.
- to: Replacement element.
- max: Maximum number of occurrences to replace.
Aliases
list_replace_n
array_resize
Resizes the list to contain size elements. Initializes new elements with value Resizes the array to the specified size. If expanding, fills new elements with the specified value (or NULL if not provided). If shrinking, truncates excess elements.
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 the array.
- value: Value to use for new elements. Default is NULL.
Aliases
list_resize
array_reverse
Returns the array with the order of the elements reversed.
array_reverse(array)
Arguments
- array: Array expression. Can be a constant, column, or function, and any combination of array operators.
Aliases
list_reverse
array_slice
Returns a slice of the array based on 1-indexed start and end positions.
array_slice(array, begin, end)
Arguments
- array: Array expression. Can be a constant, column, or function, and any combination of array operators.
- begin: Index of the first element. If negative, it counts backward from the end of the array.
- end: Index of the last element. If negative, it counts backward from the end of the array.
- stride: Stride of the array slice. The default is
1
.
Aliases
list_slice
array_sort
Sorts elements in an array. If elements are numeric, it sorts elements in numerical order. If elements are strings, it sorts elements in lexicographical order.
array_sort(array, sort_order, sort_nulls)
Arguments
- array: Array expression. Can be a constant, column, or function, and any combination of array operators.
- sort_order: Sort order (
'ASC'
(default) or'DESC'
). - sort_nulls: Sort nulls first or last (
'NULLS FIRST'
(default) or'NULLS LAST'
).
Aliases
list_sort
array_to_string
Converts an array to a string by joining all elements with the specified delimiter.
array_to_string(array, delimiter[, null_string])
Arguments
- array: Array expression. Can be a constant, column, or function, and any combination of array operators.
- delimiter: Array element separator.
- null_string: Optional. String to replace NULL values in the array. If not provided, NULL elements are ignored.
Aliases
list_to_string
array_join
list_join
array_union
Returns an array of elements that are present in both arrays (all elements from Returns an array containing all unique elements from both input arrays, with duplicates removed.
array_union(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.
Aliases
list_union
arrays_overlap
Alias of array_has_any.
cardinality
Returns the total number of elements in the array.
cardinality(array)
Arguments
- array: Array expression. Can be a constant, column, or function, and any combination of array operators.
empty
Returns true
for an empty array or false
for a non-empty array.
empty(array)
Arguments
- array: Array expression. Can be a constant, column, or function, and any combination of array operators.
Aliases
array_empty
list_empty
flatten
Flattens nested arrays into a single-level array.
- Recursively flattens arrays at any depth of nesting
- Returns unchanged if the array is already flat
The result contains all elements from all nested arrays in a single flat array.
flatten(array)
Arguments
- array: Array expression. Can be a constant, column, or function, and any combination of array operators.
generate_series
Returns an array with values between the specified start and stop values generated at the specified step.
The range start..stop
contains all values greater than or equal to start
and less than or equal to stop (start <= x <= stop
).
If start is greater than or equal to stop (start >= stop
), the
function returns an empty array.
generate_series
is similar to range, but includes the upper bound
(stop) in the output array.
generate_series(start, stop, step)
Arguments
- start: Start of the series. Supports integers, timestamps, dates, or
string types that can be coerced to
Date32
. - stop: Upper bound of the series. Supports integers, timestamps,
dates, or string types that can be coerced to
Date32
. The type must be the same as start. - step: Increase by step (cannot be
0
). Steps less than a day are only supported for ranges with theTIMESTAMP
type.
Related functions
list_any_value
Alias of array_any_value.
list_append
Alias of array_append.
list_cat
Alias of array_concat.
list_concat
Alias of array_concat.
list_contains
Alias of array_has.
list_dims
Alias of array_dims.
list_distance
Alias of array_distance.
list_distinct
Alias of array_distinct.
list_element
Alias of array_element.
list_empty
Alias of empty.
list_except
Alias of array_except.
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_intersect.
list_join
Alias of array_to_string.
list_length
Alias of array_length.
list_max
Alias of array_max.
list_ndims
Alias of array_ndims.
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_prepend
Alias of array_prepend.
list_push_back
Alias of array_append.
list_push_front
Alias of array_prepend.
list_remove
Alias of array_remove.
list_remove_all
Alias of array_remove_all.
list_remove_n
Alias of array_remove_n.
list_repeat
Alias of array_repeat.
list_replace
Alias of array_replace.
list_replace_all
Alias of array_replace_all.
list_replace_n
Alias of array_replace_n.
list_resize
Alias of array_resize.
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 array using the specified input expressions.
make_array(expression1[, ..., expression_n])
Arguments
- expression_n: Expression to include in the output array. Can be a constant, column, or function, and any combination of arithmetic or string operators.
Aliases
make_list
make_list
Alias of make_array.
range
Returns an array with values between the specified start and stop values generated at the specified step.
The range start..stop
contains all values greater than or equal to start
and less than stop (start <= x < stop
).
If start is greater than or equal to stop (start >= stop
), the
function returns an empty array.
range
is similar to generate_series, but does not include
the upper bound (stop) in the output array.
range(start, stop, step)
Arguments
- start: Start of the series. Supports integers, timestamps, dates, or
string types that can be coerced to
Date32
. - stop: Upper bound of the series. Supports integers, timestamps,
dates, or string types that can be coerced to
Date32
. The type must be the same as start. - step: Increase by step (cannot be
0
). Steps less than a day are only supported for ranges with theTIMESTAMP
type.
Related functions
string_to_array
Splits a string into an array of substrings based on a delimiter. Any substrings
matching the optional null_str
argument are replaced with NULL
.
string_to_array(str, delimiter[, null_str])
Arguments
- str: String expression to split.
- delimiter: Delimiter string to split on.
- null_str: (Optional) Substring values to replace with
NULL
.
Aliases
string_to_list
string_to_list
Alias of string_to_array.
Was this page helpful?
Thank you for your feedback!
Support and feedback
Thank you for being part of our community! We welcome and encourage your feedback and bug reports for InfluxDB Cloud Serverless and this documentation. To find support, use the following resources:
Customers with an annual or support contract can contact InfluxData Support.