Array functions

This section describes the following array functions

  • ARRAY_DISTINCT: Get all the distinct elements in the array

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

  • ARRAY_MAX: Returns the maximum value in an array

  • ARRAY_MIN: Returns the minimum value in an array

  • ARRAY_SORT: Returns the values in the array in ascending order

  • ARRAY_SORT_DESC: Returns the values in the array in descending order

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

  • COUNT_VALUES: Returns the amount of non-null items in a given array

  • COUNT_VALUES_IF: 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.

  • ELEMENT_AT: 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.

  • FIRST_ELEMENT: Gets the first element in the array

  • LAST_ELEMENT: Gets the last element in the array

Last updated