Scalar Functions
ARRAY_APPEND(array, value)
ARRAY_APPEND(array, value)
Returns an array consisting of
array with value appended to the end.ARRAY_CONCAT(arr1, arr2)
ARRAY_CONCAT(arr1, arr2)
Returns an array consisting of all the elements of array
arr1 followed by all of
the elements of array arr2.ARRAY_CONTAINS(array, value)
ARRAY_CONTAINS(array, value)
Returns true if
value is present in array.ARRAY_CONTAINS_ALL(arr1, arr2)
ARRAY_CONTAINS_ALL(arr1, arr2)
Returns true if every value in array
arr2 is present in array arr1; otherwise
returns false.ARRAY_CONTAINS_ANY(arr1, arr2)
ARRAY_CONTAINS_ANY(arr1, arr2)
Returns true if any value in array
arr2 is present in array arr; otherwise
returns false.ARRAY_DISTINCT(array)
ARRAY_DISTINCT(array)
Returns an array consisting of
array values with duplicates removed.ARRAY_EMPTY(array)
ARRAY_EMPTY(array)
Returns true if
array is empty; otherwise returns false.ARRAY_EXCEPT(arr1, arr2)
ARRAY_EXCEPT(arr1, arr2)
Returns an array consisting of all of the elements of array
arr1 with the ones that
also appear in array arr2 removed.ARRAY_INTERSECT(arr1, arr2)
ARRAY_INTERSECT(arr1, arr2)
Returns an array consisting of all of the elements of array
arr1 that also appear
in array arr2.ARRAY_ITEM(array, pos)
ARRAY_ITEM(array, pos)
Returns the item in
array at the given 1-based pos position.See examples.ARRAY_LENGTH(array)
ARRAY_LENGTH(array)
Returns the number of items in the given
array at the 1st dimension.See examples.ARRAY_LOWER(array, dim)
ARRAY_LOWER(array, dim)
Returns the lowest index of
array in the given dim dimension; since all arrays
are 1-dimensional, only specifying a dimension of 1 will return a value, and since
array indices are 1-based, that value will always be 1.ARRAY_NDIMS(array)
ARRAY_NDIMS(array)
Returns the number of dimensions in the given
array; since all arrays are
1-dimensional, that number will always be 1.ARRAY_NOT_EMPTY(array)
ARRAY_NOT_EMPTY(array)
Returns true if
array is not empty; otherwise returns false.ARRAY_SLICE(array, from, to)
ARRAY_SLICE(array, from, to)
Returns an array consisting of the values from
array between the from index
(inclusive) up to the to index (exclusive) using 0-based indexing. Negative indexes
are treated as offsets from the end of the array.ARRAY_TO_STRING(array, delim)
ARRAY_TO_STRING(array, delim)
Converts the items of
array into a string delimited by delim.See examples.ARRAY_UPPER(array, dim)
ARRAY_UPPER(array, dim)
Returns the highest index of
array in the given dim dimension; since all arrays
are 1-dimensional, only specifying a dimension of 1 will return a value, and since
array indices are 1-based, that value will always be equivalent to ARRAY_LENGTH.MAKE_ARRAY(value)
MAKE_ARRAY(value)
Returns an array consisting of the single
value element.STRING_TO_ARRAY(str, delim)
STRING_TO_ARRAY(str, delim)
Converts the values in
str, delimited by delim, into an array.See examples.Aggregation/Transposition Functions
The following functions can be used on array columns within aggregations. These functions can be used to convert primitive column values into arrays of those values (aggregation) or to convert array columns into columns of primitive values (unnest).ARRAY_AGG(expr)
ARRAY_AGG(expr)
Combines column values within a group into a single array of those values.See examples.
ARRAY_AGG_DISTINCT(expr)
ARRAY_AGG_DISTINCT(expr)
Combines column values within a group into a single array of those values, removing
any duplicates.See examples.
UNNEST_JSON_ARRAY
UNNEST_JSON_ARRAY
Transposes array values into columnar data.See UNNEST_JSON_ARRAY.