Collection functions

SQL Function Description

SQL Function	Description
`array[int]`	Returns the element at position int in array. The index starts from 1. For example, assume that you have a field called `inventory` in your records with the array: `["shampoo", "conditioner", "body wash"]` To access the `conditioner` element in this list, use `ARRAY[2]`.
`ELEMENT(array)`	Returns the sole element of array (whose cardinality should be one); returns `NULL` if array is empty. Throws an exception if array has more than one element.
`CARDINALITY(array)` `CARDINALITY(map)`	Returns the number of entries in array or map.
`map[value]`	Returns the value specified by key value in map. For example, assume that you have a field called `zoo` in your records with the map: `{"mammals":"tiger", "fish":"goldfish"}` Then, `zoo['mammals']` would return `tiger`.
`ARRAY_APPEND(array, element)`	Appends an element to the end of the array and returns the result. If the array itself is null, the function will return null. If an element to add is null, the null element will be added to the end of the array.
`ARRAY_CONTAINS(haystack, needle)`	Returns whether the given element exists in an array. Checking for null elements in the array is supported. If the array itself is null, the function will return null. The given element is cast implicitly to the array’s element type if necessary.
`ARRAY_DISTINCT(haystack)`	Returns an array with unique elements. If the array itself is null, the function will return null. Keeps ordering of elements.
`ARRAY_POSITION(haystack, needle)`	Returns the position of the first occurrence of element in the given array as int. Returns 0 if the given value could not be found in the array. Returns null if either of the arguments are null. And this is not zero based, but 1-based index. The first element in the array has index 1.
`ARRAY_PREPEND(array, element)`	Appends an element to the beginning of the array and returns the result. If the array itself is null, the function will return null. If an element to add is null, the null element will be added to the beginning of the array.
`ARRAY_REMOVE(haystack, needle)`	Removes all elements that equal to element from array. If the array itself is null, the function will return null. Keeps ordering of elements.
`ARRAY_REVERSE(haystack)`	Returns an array in reverse order. If the array itself is null, the function will return null.
`ARRAY_SLICE(array, start_offset[, end_offset])`	Returns a subarray of the input array between start_offset and end_offset inclusive. The offsets are 1-based however 0 is also treated as the beginning of the array. Positive values are counted from the beginning of the array while negative from the end. If end_offset is omitted then this offset is treated as the length of the array. If start_offset is after end_offset or both are out of array bounds an empty array will be returned. Returns null if any input is null.
`ARRAY_SORT(array[, ascending_order[, null_first]])`	Returns the array in sorted order. The function sorts an array, defaulting to ascending order with `NULL` values at the start when only the array is input. Specifying ascending_order as `true` orders the array in ascending with `NULL` values first, and setting it to `false` orders it in descending with `NULL` values last. Independently, null_first as `true` moves `NULL` values to the beginning, and as `false` to the end, irrespective of the sorting order. The function returns null if any input is null.
`ARRAY_UNION(array1, array2)`	Returns an array of the elements in the union of array1 and array2, without duplicates. If any of the array is null, the function will return null.
`ARRAY_CONCAT(array1, …)`	Returns an array that is the result of concatenating at least one array. This array contains all the elements in the first array, followed by all the elements in the second array, and so forth, up to the Nth array. If any input array is NULL, the function returns NULL.
`ARRAY_EXCEPT(array1, array2)`	Returns an array that contains the elements from array1 that are not in array2, without duplicates. If no elements remain after excluding the elements in array2 from array1, the function returns an empty array. If one or both arguments are `NULL`, the function returns `NULL`. The order of the elements from array1 is kept.
`ARRAY_INTERSECT(array1, array2)`	Returns an array that contains the elements from array1 that are also in array2, without duplicates. If no elements that are both in array1 and array2, the function returns an empty array. If any of the array is null, the function will return null. The order of the elements from array1 is kept.
`ARRAY_MAX(array)`	Returns the maximum value from the array, if array itself is null, the function returns null.
`ARRAY_JOIN(array, delimiter[, nullReplacement])`	Returns a string that represents the concatenation of the elements in the given array and the elements' data type in the given array is string. The delimiter is a string that separates each pair of consecutive elements of the array. The optional nullReplacement is a string that replaces null elements in the array. If nullReplacement is not specified, null elements in the array will be omitted from the resulting string. Returns null if input array or delimiter or nullReplacement are null.
`ARRAY_MIN(array)`	Returns the minimum value from the array, if array itself is null, the function returns null.
`MAP_KEYS(map)`	Returns the keys of the map as array. No order guaranteed.
`MAP_UNION(map1, …)`	Returns a map created by merging at least one map. These maps should have a common map type. If there are overlapping keys, the value from map2 will overwrite the value from map1, the value from map3 will overwrite the value from map2, the value from mapN will overwrite the value from map(N-1). If any of maps is null, return null.
`MAP_VALUES(map)`	Returns the values of the map as array. No order guaranteed.
`ARRAY_ENTRIES(map)`	Returns an array of all entries in the given map. No order guaranteed.
`MAP_FROM_ARRAY(array_of_keys, array_of_values)`	Returns a map created from arrays of keys and values. Note that the lengths of two arrays should be the same.
`SPLIT(string, delimiter)`	Returns an array of substrings by splitting the input string based on the given delimiter. If the delimiter is not found in the string, the original string is returned as the only element in the array. If the delimiter is empty, every character in the string is split. If the string or delimiter is null, a null value is returned. If the delimiter is found at the beginning or end of the string, or there are contiguous delimiters, then an empty string is added to the array.
`ARRAY_AGG(expression)`	Accepts a set of values and returns an array in which each value in the set is assigned to an element of the array. This function can be used to aggregate the elements of the many-side of a 1:n join. It can be used with scalar values as well as `ROW` values. In contrast to `JSON_ARRAYAGG()`, this function emits a typed array. Unlike other aggregate functions, `ARRAY_AGG()` doesn’t remove null values. If required, the `FILTER` clause can be used for removing null values before the aggregation, including `NULL` rows, which are rows that contain only `NULL` attributes as these are considered to be `NULL` themselves. If all values are removed, ARRAY_AGG() will return `NULL`, rather than an empty array. Example 1: Aggregating scalar values: `SELECT po.id, po.customer, array_agg(ol.item) AS items FROM purchase_orders po LEFT JOIN order_lines ol on po.id = ol.purchase_order_id GROUP BY po.id, po.customer` Example result: `1, 'Bob', ['hammer', 'nails']` Example 2: Aggregating ROW values: `SELECT po.id, po.customer, array_agg(row(ol.id, ol.item)) AS items FROM purchase_orders po LEFT JOIN order_lines ol on po.id = ol.purchase_order_id GROUP BY po.id, po.customer` Example result: `1, 'Bob', [ { 11, 'hammer' }, { 12, 'nails'} ]` Example 3: Aggregating ROW values, omitting any NULL rows: SELECT po.id, po.customer, array_agg(row(ol.id, ol.item)) FILTER (WHERE ol.id IS NOT NULL) AS items FROM purchase_orders po LEFT JOIN order_lines ol on po.id = ol.purchase_order_id GROUP BY po.id, po.customer` Example result: `1, 'Bob', null`

array[int]

Returns the element at position int in array. The index starts from 1.

For example, assume that you have a field called inventory in your records with the array:

["shampoo", "conditioner", "body wash"]

To access the conditioner element in this list, use ARRAY[2].

ELEMENT(array)

Returns the sole element of array (whose cardinality should be one); returns NULL if array is empty. Throws an exception if array has more than one element.

CARDINALITY(array)

CARDINALITY(map)

Returns the number of entries in array or map.

map[value]

Returns the value specified by key value in map.

For example, assume that you have a field called zoo in your records with the map:

{"mammals":"tiger", "fish":"goldfish"}

Then, zoo['mammals'] would return tiger.

ARRAY_APPEND(array, element)

Appends an element to the end of the array and returns the result. If the array itself is null, the function will return null. If an element to add is null, the null element will be added to the end of the array.

ARRAY_CONTAINS(haystack, needle)

Returns whether the given element exists in an array. Checking for null elements in the array is supported. If the array itself is null, the function will return null. The given element is cast implicitly to the array’s element type if necessary.

ARRAY_DISTINCT(haystack)

Returns an array with unique elements. If the array itself is null, the function will return null. Keeps ordering of elements.

ARRAY_POSITION(haystack, needle)

Returns the position of the first occurrence of element in the given array as int. Returns 0 if the given value could not be found in the array. Returns null if either of the arguments are null. And this is not zero based, but 1-based index. The first element in the array has index 1.

ARRAY_PREPEND(array, element)

Appends an element to the beginning of the array and returns the result. If the array itself is null, the function will return null. If an element to add is null, the null element will be added to the beginning of the array.

ARRAY_REMOVE(haystack, needle)

Removes all elements that equal to element from array. If the array itself is null, the function will return null. Keeps ordering of elements.

ARRAY_REVERSE(haystack)

Returns an array in reverse order. If the array itself is null, the function will return null.

ARRAY_SLICE(array, start_offset[, end_offset])

Returns a subarray of the input array between start_offset and end_offset inclusive. The offsets are 1-based however 0 is also treated as the beginning of the array. Positive values are counted from the beginning of the array while negative from the end. If end_offset is omitted then this offset is treated as the length of the array. If start_offset is after end_offset or both are out of array bounds an empty array will be returned. Returns null if any input is null.

ARRAY_SORT(array[, ascending_order[, null_first]])

Returns the array in sorted order. The function sorts an array, defaulting to ascending order with NULL values at the start when only the array is input. Specifying ascending_order as true orders the array in ascending with NULL values first, and setting it to false orders it in descending with NULL values last. Independently, null_first as true moves NULL values to the beginning, and as false to the end, irrespective of the sorting order. The function returns null if any input is null.

ARRAY_UNION(array1, array2)

Returns an array of the elements in the union of array1 and array2, without duplicates. If any of the array is null, the function will return null.

ARRAY_CONCAT(array1, …)

Returns an array that is the result of concatenating at least one array. This array contains all the elements in the first array, followed by all the elements in the second array, and so forth, up to the Nth array. If any input array is NULL, the function returns NULL.

ARRAY_EXCEPT(array1, array2)

Returns an array that contains the elements from array1 that are not in array2, without duplicates. If no elements remain after excluding the elements in array2 from array1, the function returns an empty array. If one or both arguments are NULL, the function returns NULL. The order of the elements from array1 is kept.

ARRAY_INTERSECT(array1, array2)

Returns an array that contains the elements from array1 that are also in array2, without duplicates. If no elements that are both in array1 and array2, the function returns an empty array. If any of the array is null, the function will return null. The order of the elements from array1 is kept.

ARRAY_MAX(array)

Returns the maximum value from the array, if array itself is null, the function returns null.

ARRAY_JOIN(array, delimiter[, nullReplacement])

Returns a string that represents the concatenation of the elements in the given array and the elements' data type in the given array is string. The delimiter is a string that separates each pair of consecutive elements of the array. The optional nullReplacement is a string that replaces null elements in the array. If nullReplacement is not specified, null elements in the array will be omitted from the resulting string. Returns null if input array or delimiter or nullReplacement are null.

ARRAY_MIN(array)

Returns the minimum value from the array, if array itself is null, the function returns null.

MAP_KEYS(map)

Returns the keys of the map as array. No order guaranteed.

MAP_UNION(map1, …)

Returns a map created by merging at least one map. These maps should have a common map type. If there are overlapping keys, the value from map2 will overwrite the value from map1, the value from map3 will overwrite the value from map2, the value from mapN will overwrite the value from map(N-1). If any of maps is null, return null.

MAP_VALUES(map)

Returns the values of the map as array. No order guaranteed.

ARRAY_ENTRIES(map)

Returns an array of all entries in the given map. No order guaranteed.

MAP_FROM_ARRAY(array_of_keys, array_of_values)

Returns a map created from arrays of keys and values. Note that the lengths of two arrays should be the same.

SPLIT(string, delimiter)

Returns an array of substrings by splitting the input string based on the given delimiter. If the delimiter is not found in the string, the original string is returned as the only element in the array. If the delimiter is empty, every character in the string is split. If the string or delimiter is null, a null value is returned. If the delimiter is found at the beginning or end of the string, or there are contiguous delimiters, then an empty string is added to the array.

ARRAY_AGG(expression)

Accepts a set of values and returns an array in which each value in the set is assigned to an element of the array. This function can be used to aggregate the elements of the many-side of a 1:n join. It can be used with scalar values as well as ROW values. In contrast to JSON_ARRAYAGG(), this function emits a typed array.

Unlike other aggregate functions, ARRAY_AGG() doesn’t remove null values. If required, the FILTER clause can be used for removing null values before the aggregation, including NULL rows, which are rows that contain only NULL attributes as these are considered to be NULL themselves. If all values are removed, ARRAY_AGG() will return NULL, rather than an empty array.

Example 1: Aggregating scalar values:

SELECT
  po.id,
  po.customer,
  array_agg(ol.item) AS items
  FROM purchase_orders po LEFT JOIN order_lines ol on po.id = ol.purchase_order_id
  GROUP BY po.id, po.customer

Example result: 1, 'Bob', ['hammer', 'nails']

Example 2: Aggregating ROW values:

SELECT
  po.id,
  po.customer,
  array_agg(row(ol.id, ol.item)) AS items
  FROM purchase_orders po LEFT JOIN order_lines ol on po.id = ol.purchase_order_id
  GROUP BY po.id, po.customer

Example result: 1, 'Bob', [ { 11, 'hammer' }, { 12, 'nails'} ]

Example 3: Aggregating ROW values, omitting any NULL rows:

SELECT
  po.id,
  po.customer,
  array_agg(row(ol.id, ol.item)) FILTER (WHERE ol.id IS NOT NULL) AS items
  FROM purchase_orders po LEFT JOIN order_lines ol on po.id = ol.purchase_order_id
  GROUP BY po.id, po.customer`

Example result: 1, 'Bob', null