Array

This section describes the following array functions:

FunctionDescription

Return all the distinct elements in the array.

This function concatenates the elements of the given array to a string using the separator.

Return the maximum value in an array.

Return the minimum value in an array.

Returns the values in the array in ascending order

Returns the values in the array in descending order

This function calculates the sum of all non-null elements of the input. If there are no non-null elements, returns 0. The behavior is similar to aggregate function SUM, but operates on an array.

Returns the amount of non-null items in a given array.

Returns the amount of true values in a given array. This can be used with conditional operators to count the number of array elements that fulfil a condition. For example, COUNT_VALUES_IF(my_values[] > 3) will return the number of elements that are greater than 3.

This function returns an element of an array at a given index. If index >= 0, this function provides the same functionality as the SQL-standard subscript operator ([]). If index < 0, ELEMENT_AT accesses elements from the last to the first.

Returns the first element in the array.

Returns the last element in the array.

Calculates a 1-based positional index for each non-null value within a sub-array, resetting the index with each new sub-array.

Calculates a continuous, 1-based positional index for each value in an array, treating the entire row as a single context. It assigns a unique index to each non-null element across the arrays within the row.