The []
operator is used to access an element of an array and is indexed starting from one:
The ||
operator is used to concatenate an array with an array or an element of the same type:
Returns whether all elements of an array match the given predicate. Returns true
if all the elements
match the predicate (a special case is when the array is empty); false
if one or more elements don’t
match; NULL
if the predicate function returns NULL
for one or more elements and true
for all
other elements.
Returns whether any elements of an array match the given predicate. Returns true
if one or more
elements match the predicate; false
if none of the elements matches (a special case is when the
array is empty); NULL
if the predicate function returns NULL
for one or more elements and false
for all other elements.
Remove duplicate values from the array x
.
Returns an array of the elements in the intersection of x
and y
, without duplicates.
Returns an array of the elements in the union of x
and y
, without duplicates.
Returns an array of elements in x
but not in y
, without duplicates.
Returns a map where the keys are the unique elements in the input array
x
and the values are the number of times that each element appears in
x
. Null values are ignored.
Returns an empty map if the input array has no non-null elements.
Concatenates the elements of the given array using the delimiter. Null elements are omitted in the result.
Concatenates the elements of the given array using the delimiter and an optional string to replace nulls.
Returns the maximum value of input array.
Returns the minimum value of input array.
Returns the position of the first occurrence of the element
in array x
(or 0 if not found).
Remove all elements that equal element
from array x
.
Sorts and returns the array x
. The elements of x
must be orderable.
Null elements will be placed at the end of the returned array.
Sorts and returns the array
based on the given comparator function
.
The comparator will take two nullable arguments representing two nullable
elements of the array
. It returns -1, 0, or 1 as the first nullable
element is less than, equal to, or greater than the second nullable element.
If the comparator function returns other values (including NULL
), the
query will fail and raise an error.
Tests if arrays x
and y
have any non-null elements in common.
Returns null if there are no non-null elements in common but either array contains null.
Returns the cardinality (size) of the array x
.
Concatenates the arrays array1
, array2
, ...
, arrayN
.
This function provides the same functionality as the SQL-standard concatenation operator (||
).
Returns n-element sub-groups of input array. If the input array has no duplicates,
combinations
returns n-element subsets.
Order of sub-groups is deterministic but unspecified. Order of elements within
a sub-group deterministic but unspecified. n
must be not be greater than 5,
and the total size of sub-groups generated must be smaller than 100,000.
Returns true if the array x
contains the element
.
Return true if array x
contains all of array seq
as a subsequence (all values in the same consecutive order).
Returns element of array
at given index
.
If index
> 0, this function provides the same functionality as the SQL-standard subscript operator ([]
),
except that the function returns NULL
when accessing an index
larger than array length, whereas
the subscript operator would fail in such a case.
If index
< 0, element_at
accesses elements from the last to the first.
Constructs an array from those elements of array
for which function
returns true:
Flattens an array(array(T))
to an array(T)
by concatenating the contained arrays.
Returns n
-grams (sub-sequences of adjacent n
elements) for the array
.
The order of the n
-grams in the result is unspecified.
Returns whether no elements of an array match the given predicate. Returns true
if none of the elements
matches the predicate (a special case is when the array is empty); false
if one or more elements match;
NULL
if the predicate function returns NULL
for one or more elements and false
for all other elements.
Returns a single value reduced from array
. inputFunction
will
be invoked for each element in array
in order. In addition to taking
the element, inputFunction
takes the current state, initially
initialState
, and returns the new state. outputFunction
will be
invoked to turn the final state into the result value. It may be the
identity function (i -> i
).
Repeat element
for count
times.
Returns an array which has the reversed order of array x
.
Generate a sequence of integers from start
to stop
, incrementing
by 1
if start
is less than or equal to stop
, otherwise -1
.
Generate a sequence of integers from start
to stop
, incrementing by step
.
Generate a sequence of dates from start
date to stop
date, incrementing
by 1
day if start
date is less than or equal to stop
date, otherwise -1
day.
Generate a sequence of dates from start
to stop
, incrementing by step
.
The type of step
can be either INTERVAL DAY TO SECOND
or INTERVAL YEAR TO MONTH
.
Generate a sequence of timestamps from start
to stop
, incrementing by step
.
The type of step
can be either INTERVAL DAY TO SECOND
or INTERVAL YEAR TO MONTH
.
Generate a random permutation of the given array x
.
Subsets array x
starting from index start
(or starting from the end
if start
is negative) with a length of length
.
Remove n
elements from the end of array:
Returns an array that is the result of applying function
to each element of array
:
Merges the given arrays, element-wise, into a single array of rows. The M-th element of
the N-th argument will be the N-th field of the M-th output element.
If the arguments have an uneven length, missing values are filled with NULL
.
Merges the two given arrays, element-wise, into a single array using function
.
If one array is shorter, nulls are appended at the end to match the length of the
longer array, before applying function
.
The []
operator is used to access an element of an array and is indexed starting from one:
The ||
operator is used to concatenate an array with an array or an element of the same type:
Returns whether all elements of an array match the given predicate. Returns true
if all the elements
match the predicate (a special case is when the array is empty); false
if one or more elements don’t
match; NULL
if the predicate function returns NULL
for one or more elements and true
for all
other elements.
Returns whether any elements of an array match the given predicate. Returns true
if one or more
elements match the predicate; false
if none of the elements matches (a special case is when the
array is empty); NULL
if the predicate function returns NULL
for one or more elements and false
for all other elements.
Remove duplicate values from the array x
.
Returns an array of the elements in the intersection of x
and y
, without duplicates.
Returns an array of the elements in the union of x
and y
, without duplicates.
Returns an array of elements in x
but not in y
, without duplicates.
Returns a map where the keys are the unique elements in the input array
x
and the values are the number of times that each element appears in
x
. Null values are ignored.
Returns an empty map if the input array has no non-null elements.
Concatenates the elements of the given array using the delimiter. Null elements are omitted in the result.
Concatenates the elements of the given array using the delimiter and an optional string to replace nulls.
Returns the maximum value of input array.
Returns the minimum value of input array.
Returns the position of the first occurrence of the element
in array x
(or 0 if not found).
Remove all elements that equal element
from array x
.
Sorts and returns the array x
. The elements of x
must be orderable.
Null elements will be placed at the end of the returned array.
Sorts and returns the array
based on the given comparator function
.
The comparator will take two nullable arguments representing two nullable
elements of the array
. It returns -1, 0, or 1 as the first nullable
element is less than, equal to, or greater than the second nullable element.
If the comparator function returns other values (including NULL
), the
query will fail and raise an error.
Tests if arrays x
and y
have any non-null elements in common.
Returns null if there are no non-null elements in common but either array contains null.
Returns the cardinality (size) of the array x
.
Concatenates the arrays array1
, array2
, ...
, arrayN
.
This function provides the same functionality as the SQL-standard concatenation operator (||
).
Returns n-element sub-groups of input array. If the input array has no duplicates,
combinations
returns n-element subsets.
Order of sub-groups is deterministic but unspecified. Order of elements within
a sub-group deterministic but unspecified. n
must be not be greater than 5,
and the total size of sub-groups generated must be smaller than 100,000.
Returns true if the array x
contains the element
.
Return true if array x
contains all of array seq
as a subsequence (all values in the same consecutive order).
Returns element of array
at given index
.
If index
> 0, this function provides the same functionality as the SQL-standard subscript operator ([]
),
except that the function returns NULL
when accessing an index
larger than array length, whereas
the subscript operator would fail in such a case.
If index
< 0, element_at
accesses elements from the last to the first.
Constructs an array from those elements of array
for which function
returns true:
Flattens an array(array(T))
to an array(T)
by concatenating the contained arrays.
Returns n
-grams (sub-sequences of adjacent n
elements) for the array
.
The order of the n
-grams in the result is unspecified.
Returns whether no elements of an array match the given predicate. Returns true
if none of the elements
matches the predicate (a special case is when the array is empty); false
if one or more elements match;
NULL
if the predicate function returns NULL
for one or more elements and false
for all other elements.
Returns a single value reduced from array
. inputFunction
will
be invoked for each element in array
in order. In addition to taking
the element, inputFunction
takes the current state, initially
initialState
, and returns the new state. outputFunction
will be
invoked to turn the final state into the result value. It may be the
identity function (i -> i
).
Repeat element
for count
times.
Returns an array which has the reversed order of array x
.
Generate a sequence of integers from start
to stop
, incrementing
by 1
if start
is less than or equal to stop
, otherwise -1
.
Generate a sequence of integers from start
to stop
, incrementing by step
.
Generate a sequence of dates from start
date to stop
date, incrementing
by 1
day if start
date is less than or equal to stop
date, otherwise -1
day.
Generate a sequence of dates from start
to stop
, incrementing by step
.
The type of step
can be either INTERVAL DAY TO SECOND
or INTERVAL YEAR TO MONTH
.
Generate a sequence of timestamps from start
to stop
, incrementing by step
.
The type of step
can be either INTERVAL DAY TO SECOND
or INTERVAL YEAR TO MONTH
.
Generate a random permutation of the given array x
.
Subsets array x
starting from index start
(or starting from the end
if start
is negative) with a length of length
.
Remove n
elements from the end of array:
Returns an array that is the result of applying function
to each element of array
:
Merges the given arrays, element-wise, into a single array of rows. The M-th element of
the N-th argument will be the N-th field of the M-th output element.
If the arguments have an uneven length, missing values are filled with NULL
.
Merges the two given arrays, element-wise, into a single array using function
.
If one array is shorter, nulls are appended at the end to match the length of the
longer array, before applying function
.