Array, struct, and map functions
These functions are available in Telemetry SQL. Signatures, aliases, argument descriptions, and examples below follow the vendored engine fork. Examples that name a table assume matching columns in your own table; result grids illustrate values and may differ from the dashboard or JSON display.
Structured event objects are typed structs, accessed as payload.user.id or payload['user']['id']. A literal dotted column name uses double quotes, such as "user.id". Array element indexes are one-based. These functions operate on typed values; a JSON string must not be treated as an already parsed struct.
SQL reference · Scalar functions · String and regular expression functions · Date and time functions · Aggregate functions · Window functions
Array Functions
- any_match
- array_any_match
- array_any_value
- array_append
- array_cat
- array_compact
- array_concat
- array_contains
- array_dims
- array_distance
- array_distinct
- array_element
- array_empty
- array_except
- array_extract
- array_filter
- array_has
- array_has_all
- array_has_any
- array_indexof
- array_intersect
- array_join
- array_length
- array_max
- array_min
- array_ndims
- array_normalize
- 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_transform
- array_union
- arrays_overlap
- arrays_zip
- cardinality
- cosine_distance
- dot_product
- empty
- flatten
- generate_series
- inner_product
- list_any_match
- list_any_value
- list_append
- list_cat
- list_compact
- list_concat
- list_contains
- list_dims
- list_distance
- list_distinct
- list_element
- list_empty
- list_except
- list_extract
- list_filter
- list_has
- list_has_all
- list_has_any
- list_indexof
- list_intersect
- list_join
- list_length
- list_max
- list_ndims
- list_normalize
- 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_transform
- list_union
- list_zip
- make_array
- make_list
- range
- string_to_array
- string_to_list
any_match
Alias of array_any_match.
array_any_match
Returns whether any elements of an array match the given predicate. Returns true if one or more elements match, false if none match (including empty arrays), and null if the predicate returns null for some elements and false for all others.
any_match(array, predicate)
Arguments
- array: Array expression. Can be a constant, column, or function, and any combination of array operators.
- predicate: Lambda predicate that returns a boolean
Example
select any_match([1, 2, 3], x -> x > 2);
+----------------------------------+
| any_match([1, 2, 3], x -> x > 2) |
+----------------------------------+
| true |
+----------------------------------+
Aliases
- any_match
- list_any_match
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.
Example
select array_any_value([NULL, 1, 2, 3]);
+-------------------------------+
| array_any_value(List([NULL,1,2,3])) |
+-------------------------------------+
| 1 |
+-------------------------------------+
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.
Example
select array_append([1, 2, 3], 4);
+--------------------------------------+
| array_append(List([1,2,3]),Int64(4)) |
+--------------------------------------+
| [1, 2, 3, 4] |
+--------------------------------------+
Aliases
- list_append
- array_push_back
- list_push_back
array_cat
Alias of array_concat.
array_compact
Removes null values from the array.
array_compact(array)
Arguments
- array: Array expression. Can be a constant, column, or function, and any combination of array operators.
Example
select array_compact([1, NULL, 2, NULL, 3]) arr;
+-----------+
| arr |
+-----------+
| [1, 2, 3] |
+-----------+
Aliases
- list_compact
array_concat
Concatenates arrays.
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.
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_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.
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_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.
Example
select array_distance([1, 2], [1, 4]);
+------------------------------------+
| array_distance(List([1,2], [1,4])) |
+------------------------------------+
| 2.0 |
+------------------------------------+
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.
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_empty
Alias of empty.
array_except
Returns an array of the elements that appear in the first array but not in the second.
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.
Example
select array_except([1, 2, 3, 4], [5, 6, 3, 4]);
+----------------------------------------------------+
| array_except([1, 2, 3, 4], [5, 6, 3, 4]); |
+----------------------------------------------------+
| [1, 2] |
+----------------------------------------------------+
select array_except([1, 2, 3, 4], [3, 4, 5, 6]);
+----------------------------------------------------+
| array_except([1, 2, 3, 4], [3, 4, 5, 6]); |
+----------------------------------------------------+
| [1, 2] |
+----------------------------------------------------+
Aliases
- list_except
array_extract
Alias of array_element.
array_filter
filters the values of an array using a boolean lambda
array_filter(array, x -> x > 2)
Arguments
- array: Array expression. Can be a constant, column, or function, and any combination of array operators.
- lambda: Lambda that returns a boolean. Elements for which the lambda returns true are kept.
Example
select array_filter([1, 2, 3, 4, 5], x -> x > 2);
+--------------------------------------------+
| array_filter([1, 2, 3, 4, 5], x -> x > 2) |
+--------------------------------------------+
| [3, 4, 5] |
+--------------------------------------------+
Aliases
- list_filter
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.
Example
select array_has([1, 2, 3], 2);
+-----------------------------+
| array_has(List([1,2,3]), 2) |
+-----------------------------+
| true |
+-----------------------------+
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.
Example
select array_has_all([1, 2, 3, 4], [2, 3]);
+--------------------------------------------+
| array_has_all(List([1,2,3,4]), List([2,3])) |
+--------------------------------------------+
| true |
+--------------------------------------------+
Aliases
- list_has_all
array_has_any
Returns true if the arrays have any elements in common.
array_has_any(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_has_any([1, 2, 3], [3, 4]);
+------------------------------------------+
| array_has_any(List([1,2,3]), List([3,4])) |
+------------------------------------------+
| true |
+------------------------------------------+
Aliases
- list_has_any
- arrays_overlap
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], 1);
+-------------------------------------------+
| array_length(List([1,2,3,4,5]), 1) |
+-------------------------------------------+
| 5 |
+-------------------------------------------+
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.
Example
select array_max([3,1,4,2]);
+-----------------------------------------+
| array_max(List([3,1,4,2])) |
+-----------------------------------------+
| 4 |
+-----------------------------------------+
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.
Example
select array_min([3,1,4,2]);
+-----------------------------------------+
| array_min(List([3,1,4,2])) |
+-----------------------------------------+
| 1 |
+-----------------------------------------+
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.
- element: Array element.
Example
select array_ndims([[1, 2, 3], [4, 5, 6]]);
+----------------------------------+
| array_ndims(List([1,2,3,4,5,6])) |
+----------------------------------+
| 2 |
+----------------------------------+
Aliases
- list_ndims
array_normalize
Returns the L2-normalized vector for the input numeric array, computed as array[i] / sqrt(sum(array[i]^2)) per element. Returns NULL if the input is NULL, contains NULL elements, or has zero magnitude (all elements are zero). Returns an empty array for an empty input array.
array_normalize(array)
Arguments
- array: Array expression. Can be a constant, column, or function, and any combination of array operators.
Example
select array_normalize([3.0, 4.0]);
+-----------------------------+
| array_normalize(List([3.0,4.0])) |
+-----------------------------+
| [0.6, 0.8] |
+-----------------------------+
Aliases
- list_normalize
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_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_position
Returns the position of the first occurrence of the specified element in the array, or NULL if not found. Comparisons are done using IS DISTINCT FROM semantics, so NULL is considered to match NULL.
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 in the array.
- index: Index at which to start searching (1-indexed).
Example
select array_position([1, 2, 2, 3, 1, 4], 2);
+----------------------------------------------+
| array_position(List([1,2,2,3,1,4]),Int64(2)) |
+----------------------------------------------+
| 2 |
+----------------------------------------------+
select array_position([1, 2, 2, 3, 1, 4], 2, 3);
+----------------------------------------------------+
| array_position(List([1,2,2,3,1,4]),Int64(2), Int64(3)) |
+----------------------------------------------------+
| 3 |
+----------------------------------------------------+
Aliases
- list_position
- array_indexof
- list_indexof
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 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_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.
Example
select array_prepend(1, [2, 3, 4]);
+---------------------------------------+
| array_prepend(Int64(1),List([2,3,4])) |
+---------------------------------------+
| [1, 2, 3, 4] |
+---------------------------------------+
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. NULL elements already in the array are preserved when removing a non-NULL value. If element evaluates to NULL, the result is NULL rather than removing NULL entries.
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] |
+----------------------------------------------+
select array_remove([1, 2, NULL, 2, 4], 2);
+---------------------------------------------------+
| array_remove(List([1,2,NULL,2,4]),Int64(2)) |
+---------------------------------------------------+
| [1, NULL, 2, 4] |
+---------------------------------------------------+
Aliases
- list_remove
array_remove_all
Removes all elements from the array equal to the given value. NULL elements already in the array are preserved when removing a non-NULL value. If element evaluates to NULL, the result is NULL rather than removing NULL entries.
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] |
+--------------------------------------------------+
select array_remove_all([1, 2, NULL, 2, 4], 2);
+-----------------------------------------------------+
| array_remove_all(List([1,2,NULL,2,4]),Int64(2)) |
+-----------------------------------------------------+
| [1, NULL, 4] |
+-----------------------------------------------------+
Aliases
- list_remove_all
array_remove_n
Removes the first max elements from the array equal to the given value. NULL elements already in the array are preserved when removing a non-NULL value. If element evaluates to NULL, the result is NULL rather than removing NULL entries.
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] |
+---------------------------------------------------------+
select array_remove_n([1, 2, NULL, 2, 4], 2, 2);
+----------------------------------------------------------+
| array_remove_n(List([1,2,NULL,2,4]),Int64(2),Int64(2)) |
+----------------------------------------------------------+
| [1, NULL, 4] |
+----------------------------------------------------------+
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: 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_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.
Example
select array_replace([1, 2, 2, 3, 2, 1, 4], 2, 5);
+--------------------------------------------------------+
| array_replace(List([1,2,2,3,2,1,4]),Int64(2),Int64(5)) |
+--------------------------------------------------------+
| [1, 5, 2, 3, 2, 1, 4] |
+--------------------------------------------------------+
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: Initial element.
- to: Final element.
Example
select array_replace_all([1, 2, 2, 3, 2, 1, 4], 2, 5);
+------------------------------------------------------------+
| array_replace_all(List([1,2,2,3,2,1,4]),Int64(2),Int64(5)) |
+------------------------------------------------------------+
| [1, 5, 5, 3, 5, 1, 4] |
+------------------------------------------------------------+
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: Initial element.
- to: Final element.
- max: Number of first occurrences to replace.
Example
select array_replace_n([1, 2, 2, 3, 2, 1, 4], 2, 5, 2);
+-------------------------------------------------------------------+
| array_replace_n(List([1,2,2,3,2,1,4]),Int64(2),Int64(5),Int64(2)) |
+-------------------------------------------------------------------+
| [1, 5, 5, 3, 2, 1, 4] |
+-------------------------------------------------------------------+
Aliases
- list_replace_n
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_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.
Example
select array_reverse([1, 2, 3, 4]);
+------------------------------------------------------------+
| array_reverse(List([1, 2, 3, 4])) |
+------------------------------------------------------------+
| [4, 3, 2, 1] |
+------------------------------------------------------------+
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.
Example
select array_slice([1, 2, 3, 4, 5, 6, 7, 8], 3, 6);
+--------------------------------------------------------+
| array_slice(List([1,2,3,4,5,6,7,8]),Int64(3),Int64(6)) |
+--------------------------------------------------------+
| [3, 4, 5, 6] |
+--------------------------------------------------------+
Aliases
- list_slice
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 ascending (
ASC) or descending (DESC) order. The default isASC. - nulls_first: Whether to sort nulls first (
NULLS FIRST) or last (NULLS LAST). The default isNULLS FIRST.
Example
select array_sort([3, 1, 2]);
+-----------------------------+
| array_sort(List([3,1,2])) |
+-----------------------------+
| [1, 2, 3] |
+-----------------------------+
Aliases
- list_sort
array_to_string
Converts each element to its text representation.
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 use for null values in the output. If not provided, nulls will be omitted.
Example
select array_to_string([[1, 2, 3, 4], [5, 6, 7, 8]], ',');
+----------------------------------------------------+
| array_to_string(List([1,2,3,4,5,6,7,8]),Utf8(",")) |
+----------------------------------------------------+
| 1,2,3,4,5,6,7,8 |
+----------------------------------------------------+
Aliases
- list_to_string
- array_join
- list_join
array_transform
transforms the values of an array
array_transform(array, x -> x*2)
Arguments
- array: Array expression. Can be a constant, column, or function, and any combination of array operators.
- lambda: Lambda
Example
select array_transform([1, 2, 3, 4, 5], x -> x*2);
+-------------------------------------------+
| array_transform([1, 2, 3, 4, 5], x -> x*2) |
+-------------------------------------------+
| [2, 4, 6, 8, 10] |
+-------------------------------------------+
Aliases
- list_transform
array_union
Returns an array of elements that are present in both arrays (all elements from both arrays) without duplicates.
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.
Example
select array_union([1, 2, 3, 4], [5, 6, 3, 4]);
+----------------------------------------------------+
| array_union([1, 2, 3, 4], [5, 6, 3, 4]); |
+----------------------------------------------------+
| [1, 2, 3, 4, 5, 6] |
+----------------------------------------------------+
select array_union([1, 2, 3, 4], [5, 6, 7, 8]);
+----------------------------------------------------+
| array_union([1, 2, 3, 4], [5, 6, 7, 8]); |
+----------------------------------------------------+
| [1, 2, 3, 4, 5, 6, 7, 8] |
+----------------------------------------------------+
Aliases
- list_union
arrays_overlap
Alias of array_has_any.
arrays_zip
Returns an array of structs created by combining the elements of each input array at the same index. If the arrays have different lengths, shorter arrays are padded with NULLs.
arrays_zip(array1[, ..., array_n])
Arguments
- array1: First array expression.
- array_n: Optional additional array expressions.
Example
select arrays_zip([1, 2, 3]);
+---------------------------------------------------+
| arrays_zip([1, 2, 3]) |
+---------------------------------------------------+
| [{1: 1}, {1: 2}, {1: 3}] |
+---------------------------------------------------+
select arrays_zip([1, 2], [3, 4, 5]);
+---------------------------------------------------+
| arrays_zip([1, 2], [3, 4, 5]) |
+---------------------------------------------------+
| [{1: 1, 2: 3}, {1: 2, 2: 4}, {1: NULL, 2: 5}] |
+---------------------------------------------------+
Aliases
- list_zip
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.
Example
select cardinality([[1, 2, 3, 4], [5, 6, 7, 8]]);
+--------------------------------------+
| cardinality(List([1,2,3,4,5,6,7,8])) |
+--------------------------------------+
| 8 |
+--------------------------------------+
cosine_distance
Returns the cosine distance between two input arrays of equal length. The cosine distance is defined as 1 - cosine_similarity, i.e. 1 - dot(a,b) / (||a|| * ||b||). Returns NULL if either array is NULL or contains only zeros.
cosine_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.
Example
select cosine_distance([1.0, 0.0], [0.0, 1.0]);
+-----------------------------------------------+
| cosine_distance(List([1.0,0.0]),List([0.0,1.0])) |
+-----------------------------------------------+
| 1.0 |
+-----------------------------------------------+
dot_product
Alias of inner_product.
empty
Returns 1 for an empty array or 0 for a non-empty array.
empty(array)
Arguments
- array: Array expression. Can be a constant, column, or function, and any combination of array operators.
Example
select empty([1]);
+------------------+
| empty(List([1])) |
+------------------+
| 0 |
+------------------+
Aliases
- array_empty
- list_empty
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.
flatten(array)
Arguments
- array: Array expression. Can be a constant, column, or function, and any combination of array operators.
Example
select flatten([[1, 2], [3, 4]]);
+------------------------------+
| flatten(List([1,2], [3,4])) |
+------------------------------+
| [1, 2, 3, 4] |
+------------------------------+
generate_series
Similar to the range function, but it includes the upper bound.
generate_series(stop)
generate_series(start, stop[, step])
Arguments
- start: Start of the series. Ints, timestamps, dates or string types that can be coerced to Date32 are supported.
- end: End of the series (included). Type must be the same as start.
- step: Increase by step (can not be 0). Steps less than a day are supported only for timestamp ranges.
Example
select generate_series(1,3);
+------------------------------------+
| generate_series(Int64(1),Int64(3)) |
+------------------------------------+
| [1, 2, 3] |
+------------------------------------+
inner_product
Returns the inner product (dot product) of two input arrays of equal length, computed as sum(array1[i] * array2[i]). Returns NULL if either array is NULL or contains NULL elements. Returns 0.0 for two empty arrays.
inner_product(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 inner_product([1.0, 2.0, 3.0], [4.0, 5.0, 6.0]);
+-------------------------------------------------------+
| inner_product(List([1.0,2.0,3.0]),List([4.0,5.0,6.0])) |
+-------------------------------------------------------+
| 32.0 |
+-------------------------------------------------------+
Aliases
- dot_product
list_any_match
Alias of array_any_match.
list_any_value
Alias of array_any_value.
list_append
Alias of array_append.
list_cat
Alias of array_concat.
list_compact
Alias of array_compact.
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_filter
Alias of array_filter.
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_normalize
Alias of array_normalize.
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_transform
Alias of array_transform.
list_union
Alias of array_union.
list_zip
Alias of arrays_zip.
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.
Example
select make_array(1, 2, 3, 4, 5);
+----------------------------------------------------------+
| make_array(Int64(1),Int64(2),Int64(3),Int64(4),Int64(5)) |
+----------------------------------------------------------+
| [1, 2, 3, 4, 5] |
+----------------------------------------------------------+
Aliases
- make_list
make_list
Alias of make_array.
range
Returns an Arrow array between start and stop with step. The range start..end contains all values with start <= x < end. It is empty if start >= end. Step cannot be 0.
range(stop)
range(start, stop[, step])
Arguments
- start: Start of the range. Ints, timestamps, dates or string types that can be coerced to Date32 are supported.
- end: End of the range (not included). Type must be the same as start.
- step: Increase by step (cannot be 0). Steps less than a day are supported only for timestamp ranges.
Example
select range(2, 10, 3);
+-----------------------------------+
| range(Int64(2),Int64(10),Int64(3))|
+-----------------------------------+
| [2, 5, 8] |
+-----------------------------------+
select range(DATE '1992-09-01', DATE '1993-03-01', INTERVAL '1' MONTH);
+--------------------------------------------------------------------------+
| range(DATE '1992-09-01', DATE '1993-03-01', INTERVAL '1' MONTH) |
+--------------------------------------------------------------------------+
| [1992-09-01, 1992-10-01, 1992-11-01, 1992-12-01, 1993-01-01, 1993-02-01] |
+--------------------------------------------------------------------------+
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: Substring values to be replaced with
NULL.
Example
select string_to_array('abc##def', '##');
+-----------------------------------+
| string_to_array(Utf8('abc##def')) |
+-----------------------------------+
| ['abc', 'def'] |
+-----------------------------------+
select string_to_array('abc def', ' ', 'def');
+---------------------------------------------+
| string_to_array(Utf8('abc def'), Utf8(' '), Utf8('def')) |
+---------------------------------------------+
| ['abc', NULL] |
+---------------------------------------------+
Aliases
- string_to_list
string_to_list
Alias of string_to_array.
Struct Functions
named_struct
Returns an Arrow struct using the specified name and input expressions pairs.
For information on comparing and ordering struct values (including NULL handling),
see data types.
named_struct(expression1_name, expression1_input[, ..., expression_n_name, expression_n_input])
Arguments
- expression_n_name: Name of the column field. Must be a constant string.
- expression_n_input: Expression to include in the output struct. Can be a constant, column, or function, and any combination of arithmetic or string operators.
Example
For example, this query converts two columns a and b to a single column with
a struct type of fields field_a and field_b:
select * from t;
+---+---+
| a | b |
+---+---+
| 1 | 2 |
| 3 | 4 |
+---+---+
select named_struct('field_a', a, 'field_b', b) from t;
+-------------------------------------------------------+
| named_struct(Utf8("field_a"),t.a,Utf8("field_b"),t.b) |
+-------------------------------------------------------+
| {field_a: 1, field_b: 2} |
| {field_a: 3, field_b: 4} |
+-------------------------------------------------------+
row
Alias of struct.
struct
Returns an Arrow struct using the specified input expressions optionally named.
Fields in the returned struct use the optional name or the cN naming convention.
For example: c0, c1, c2, etc.
For information on comparing and ordering struct values (including NULL handling),
see data types.
struct(expression1[, ..., expression_n])
Arguments
- expression1, expression_n: Expression to include in the output struct. Can be a constant, column, or function, any combination of arithmetic or string operators.
Example
For example, this query converts two columns a and b to a single column with
a struct type of fields field_a and c1:
select * from t;
+---+---+
| a | b |
+---+---+
| 1 | 2 |
| 3 | 4 |
+---+---+
-- use default names `c0`, `c1`
select struct(a, b) from t;
+-----------------+
| struct(t.a,t.b) |
+-----------------+
| {c0: 1, c1: 2} |
| {c0: 3, c1: 4} |
+-----------------+
-- name the first field `field_a`
select struct(a as field_a, b) from t;
+--------------------------------------------------+
| named_struct(Utf8("field_a"),t.a,Utf8("c1"),t.b) |
+--------------------------------------------------+
| {field_a: 1, c1: 2} |
| {field_a: 3, c1: 4} |
+--------------------------------------------------+
Aliases
- row
Map Functions
element_at
Alias of map_extract.
map
Constructs a map from keys and values. Keys must be unique and non-null; values can be null.
map(keys_array, values_array)
map(key1, value1[, key2, value2, ...])
MAP { key1: value1, key2: value2 }
With exactly two array arguments, map pairs corresponding elements from the arrays. Otherwise, arguments are alternating keys and values. Array lengths must match.
SELECT map(['POST', 'HEAD'], [41, 33]) AS counts;
SELECT map('POST', 41, 'HEAD', 33) AS counts;
SELECT MAP { 'POST': 41, 'HEAD': 33 } AS counts;
Each query returns a map with POST mapped to 41 and HEAD mapped to 33.
make_map
Constructs a map from alternating keys and values. This SQL planner function always uses key/value pairs; it does not have the two-array pairing behavior of map.
make_map(key1, value1[, key2, value2, ...])
SELECT make_map('POST', 41, 'HEAD', 33) AS counts;
Keys must be unique and non-null. Supply an even number of arguments. To combine an array of keys with an array of values, use map(keys_array, values_array).
map_entries
Returns a list of all entries in the map.
map_entries(map)
Arguments
- map: Map expression. Can be a constant, column, or function, and any combination of map operators.
Example
SELECT map_entries(MAP {'a': 1, 'b': NULL, 'c': 3});
----
[{'key': a, 'value': 1}, {'key': b, 'value': NULL}, {'key': c, 'value': 3}]
SELECT map_entries(map([100, 5], [42, 43]));
----
[{'key': 100, 'value': 42}, {'key': 5, 'value': 43}]
map_extract
Returns a list containing the value for the given key or an empty list if the key is not present in the map.
map_extract(map, key)
Arguments
- map: Map expression. Can be a constant, column, or function, and any combination of map operators.
- key: Key to extract from the map. Can be a constant, column, or function, any combination of arithmetic or string operators, or a named expression of the previously listed.
Example
SELECT map_extract(MAP {'a': 1, 'b': NULL, 'c': 3}, 'a');
----
[1]
SELECT map_extract(MAP {1: 'one', 2: 'two'}, 2);
----
['two']
SELECT map_extract(MAP {'x': 10, 'y': NULL, 'z': 30}, 'y');
----
[NULL]
-- non-existing key
SELECT map_extract(MAP {'x': 10, 'y': NULL, 'z': 30}, 'a');
----
[]
Aliases
- element_at
map_keys
Returns a list of all keys in the map.
map_keys(map)
Arguments
- map: Map expression. Can be a constant, column, or function, and any combination of map operators.
Example
SELECT map_keys(MAP {'a': 1, 'b': NULL, 'c': 3});
----
[a, b, c]
SELECT map_keys(map([100, 5], [42, 43]));
----
[100, 5]
map_values
Returns a list of all values in the map.
map_values(map)
Arguments
- map: Map expression. Can be a constant, column, or function, and any combination of map operators.
Example
SELECT map_values(MAP {'a': 1, 'b': NULL, 'c': 3});
----
[1, , 3]
SELECT map_values(map([100, 5], [42, 43]));
----
[42, 43]
Attribution
Adapted from the function documentation in Telemetry's vendored Apache DataFusion fork, with Telemetry-specific additions and corrections. Copyright 2019–2026 The Apache Software Foundation. Distributed under the Apache License 2.0; see the Apache notice.