Array, struct & map functions
Reference for array, struct & map functions in HotSQL. Names, signatures, and examples match the engine exactly.
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
> select any_match([1, 2, 3], x -> x > 2);
+----------------------------------+
| any_match([1, 2, 3], x -> x > 2) |
+----------------------------------+
| true |
+----------------------------------+
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.
> select array_any_value([NULL, 1, 2, 3]);
+-------------------------------+
| array_any_value(List([NULL,1,2,3])) |
+-------------------------------------+
| 1 |
+-------------------------------------+
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.
> select array_append([1, 2, 3], 4);
+--------------------------------------+
| array_append(List([1,2,3]),Int64(4)) |
+--------------------------------------+
| [1, 2, 3, 4] |
+--------------------------------------+
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.
> select array_compact([1, NULL, 2, NULL, 3]) arr;
+-----------+
| arr |
+-----------+
| [1, 2, 3] |
+-----------+
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.
> 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] |
+---------------------------------------------------+
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.
> select array_dims([[1, 2, 3], [4, 5, 6]]);
+---------------------------------+
| array_dims(List([1,2,3,4,5,6])) |
+---------------------------------+
| [2, 3] |
+---------------------------------+
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.
> select array_distance([1, 2], [1, 4]);
+------------------------------------+
| array_distance(List([1,2], [1,4])) |
+------------------------------------+
| 2.0 |
+------------------------------------+
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.
> select array_distinct([1, 3, 2, 3, 1, 2, 4]);
+---------------------------------+
| array_distinct(List([1,2,3,4])) |
+---------------------------------+
| [1, 2, 3, 4] |
+---------------------------------+
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.
> select array_element([1, 2, 3, 4], 3);
+-----------------------------------------+
| array_element(List([1,2,3,4]),Int64(3)) |
+-----------------------------------------+
| 3 |
+-----------------------------------------+
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.
> 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] |
+----------------------------------------------------+
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.
> select array_filter([1, 2, 3, 4, 5], x -> x > 2);
+--------------------------------------------+
| array_filter([1, 2, 3, 4, 5], x -> x > 2) |
+--------------------------------------------+
| [3, 4, 5] |
+--------------------------------------------+
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.
> select array_has([1, 2, 3], 2);
+-----------------------------+
| array_has(List([1,2,3]), 2) |
+-----------------------------+
| true |
+-----------------------------+
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.
> select array_has_all([1, 2, 3, 4], [2, 3]);
+--------------------------------------------+
| array_has_all(List([1,2,3,4]), List([2,3])) |
+--------------------------------------------+
| true |
+--------------------------------------------+
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.
> select array_has_any([1, 2, 3], [3, 4]);
+------------------------------------------+
| array_has_any(List([1,2,3]), List([3,4])) |
+------------------------------------------+
| true |
+------------------------------------------+
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.
> 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]); |
+----------------------------------------------------+
| [] |
+----------------------------------------------------+
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.
> select array_length([1, 2, 3, 4, 5], 1);
+-------------------------------------------+
| array_length(List([1,2,3,4,5]), 1) |
+-------------------------------------------+
| 5 |
+-------------------------------------------+
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.
> select array_max([3,1,4,2]);
+-----------------------------------------+
| array_max(List([3,1,4,2])) |
+-----------------------------------------+
| 4 |
+-----------------------------------------+
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.
> 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.
> select array_ndims([[1, 2, 3], [4, 5, 6]]);
+----------------------------------+
| array_ndims(List([1,2,3,4,5,6])) |
+----------------------------------+
| 2 |
+----------------------------------+
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.
> select array_normalize([3.0, 4.0]);
+-----------------------------+
| array_normalize(List([3.0,4.0])) |
+-----------------------------+
| [0.6, 0.8] |
+-----------------------------+
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.
> select array_pop_back([1, 2, 3]);
+-------------------------------+
| array_pop_back(List([1,2,3])) |
+-------------------------------+
| [1, 2] |
+-------------------------------+
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.
> select array_pop_front([1, 2, 3]);
+-------------------------------+
| array_pop_front(List([1,2,3])) |
+-------------------------------+
| [2, 3] |
+-------------------------------+
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).
> 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 |
+----------------------------------------------------+
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.
> select array_positions([1, 2, 2, 3, 1, 4], 2);
+-----------------------------------------------+
| array_positions(List([1,2,2,3,1,4]),Int64(2)) |
+-----------------------------------------------+
| [2, 3] |
+-----------------------------------------------+
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.
> select array_prepend(1, [2, 3, 4]);
+---------------------------------------+
| array_prepend(Int64(1),List([2,3,4])) |
+---------------------------------------+
| [1, 2, 3, 4] |
+---------------------------------------+
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.
> 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] |
+---------------------------------------------------+
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.
> 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] |
+-----------------------------------------------------+
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.
> 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] |
+----------------------------------------------------------+
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.
> 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]] |
+------------------------------------+
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.
> 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] |
+--------------------------------------------------------+
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.
> 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] |
+------------------------------------------------------------+
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.
> 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] |
+-------------------------------------------------------------------+
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.
> select array_resize([1, 2, 3], 5, 0);
+-------------------------------------+
| array_resize(List([1,2,3],5,0)) |
+-------------------------------------+
| [1, 2, 3, 0, 0] |
+-------------------------------------+
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.
> select array_reverse([1, 2, 3, 4]);
+------------------------------------------------------------+
| array_reverse(List([1, 2, 3, 4])) |
+------------------------------------------------------------+
| [4, 3, 2, 1] |
+------------------------------------------------------------+
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.
> 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] |
+--------------------------------------------------------+
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.
> select array_sort([3, 1, 2]);
+-----------------------------+
| array_sort(List([3,1,2])) |
+-----------------------------+
| [1, 2, 3] |
+-----------------------------+
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.
> 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 |
+----------------------------------------------------+
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
> 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] |
+-------------------------------------------+
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.
> 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] |
+----------------------------------------------------+
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.
> 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}] |
+---------------------------------------------------+
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.
> 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.
> 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 |
+-----------------------------------------------+
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.
> select empty([1]);
+------------------+
| empty(List([1])) |
+------------------+
| 0 |
+------------------+
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.
> 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.
> 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.
> 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 |
+-------------------------------------------------------+
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.
> 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] |
+----------------------------------------------------------+
map
Returns an Arrow map with the specified key-value pairs.
The make_map function creates a map from two lists: one for keys and one for values. Each key must be unique and non-null.
map(key, value)
map(key: value)
make_map(['key1', 'key2'], ['value1', 'value2'])
Arguments
-
key: Formap: Expression to be used for key. Can be a constant, column, function, or any combination of arithmetic or string operators.For
make_map: The list of keys to be used in the map. Each key must be unique and non-null. -
value: Formap: Expression to be used for value. Can be a constant, column, function, or any combination of arithmetic or string operators.For
make_map: The list of values to be mapped to the corresponding keys.
-- Using map function
SELECT MAP('type', 'test');
----
{type: test}
SELECT MAP(['POST', 'HEAD', 'PATCH'], [41, 33, null]);
----
{POST: 41, HEAD: 33, PATCH: NULL}
SELECT MAP([[1,2], [3,4]], ['a', 'b']);
----
{[1, 2]: a, [3, 4]: b}
SELECT MAP { 'a': 1, 'b': 2 };
----
{a: 1, b: 2}
-- Using make_map function
SELECT MAKE_MAP(['POST', 'HEAD'], [41, 33]);
----
{POST: 41, HEAD: 33}
SELECT MAKE_MAP(['key1', 'key2'], ['value1', null]);
----
{key1: value1, key2: }
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.
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.
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');
----
[]
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.
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.
SELECT map_values(MAP {'a': 1, 'b': NULL, 'c': 3});
----
[1, , 3]
SELECT map_values(map([100, 5], [42, 43]));
----
[42, 43]
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.
> 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 withNULL.
> 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] |
+---------------------------------------------+