Array Functions
Array functions operate exclusively on arrays, allowing creation of, and interaction with, arrays. See also ARRAY.OF.
ARRAY.APPEND
Adds a value to the end of an array. Tip: To find the function that best meets your needs, review these similar functions: ARRAY_CONCAT, STR_JOIN, ARRAY.CONCAT.
SYNTAX
array.append(array, value)
array
- Type: {'type': 'array', 'elementType': 'any'}
- Description: The base array
value
- Type: any
- Description: The value to append to the base array. The value must be the same type as the elements in the array.
OUTPUT
{'type': 'array', 'elementType': 'any'}
EXAMPLE
SELECT array.append(array, value) AS appended_array
array | value | → | appended_array |
---|---|---|---|
['1', '2', '3'] | '4' | ['1', '2', '3', '4'] | |
['', '', '', ''] | '' | ['', '', '', '', ''] | |
[] | '1' | ['1'] | |
null | '1' | null | |
['1'] | null | ['1', null] |
ARRAY.CONCAT
Given some number of input arrays, it returns a single array containing the elements of all the arrays in sequence. Given a null as any argument, it will return null.Tip: To find the function that best meets your needs, review these similar functions: ARRAY_CONCAT, STR_JOIN, ARRAY.APPEND.
SYNTAX
array.concat(arrays)
arrays
- Type: {'type': 'array', 'elementType': 'any'}
- Description: The arrays to concatenate
OUTPUT
{'type': 'array', 'elementType': 'any'}
EXAMPLE
SELECT array.concat(...arrays) AS concatenated_arrays
arrays | → | concatenated_arrays |
---|---|---|
[['one', 'two'], ['three'], ['four', 'five']] | ['one', 'two', 'three', 'four', 'five'] | |
[['some', 'duplicate'], ['duplicate', 'values']] | ['some', 'duplicate', 'duplicate', 'values'] | |
[['some', '', 'empty'], ['values', '']] | ['some', '', 'empty', 'values', ''] | |
[['some', null, 'null'], ['values', null]] | ['some', null, 'null', 'values', null] | |
[['no', 'null', 'arrays!'], null, null] | null | |
[[], [], []] | [] | |
[[], ['1'], []] | ['1'] | |
[] | [] |
ARRAY.DIFF
Given two arrays, it returns the first array with all elements in the second array removed.Duplicate elements in the first array are not deduplicated.
SYNTAX
array.diff(array1, array2)
array1
- Type: {'type': 'array', 'elementType': 'any'}
- Description: The first array
array2
- Type: {'type': 'array', 'elementType': 'any'}
- Description: The second array
OUTPUT
{'type': 'array', 'elementType': 'any'}
EXAMPLE
SELECT array.diff(array1, array2) AS array_diff
array1 | array2 | → | array_diff |
---|---|---|---|
['one', 'two', 'three', 'two', 'one'] | ['one', 'one', 'four'] | ['two', 'three', 'two'] | |
null | ['one', 'one', 'four'] | null | |
['one', 'two', 'three', 'two', 'one'] | null | ['one', 'two', 'three', 'two', 'one'] | |
null | null | null | |
['one', 'two', 'three', 'two', 'one'] | [] | ['one', 'two', 'three', 'two', 'one'] | |
[] | ['one', 'one', 'four'] | [] |
ARRAY.CONTAINS
Returns true if any element in the array is equal to the given item. If it is not found in the array, it returns false. If the given array is null, this function returns null.
SYNTAX
array.contains(array, item)
array
- Type: {'type': 'array', 'elementType': 'any'}
- Description: The array to look in
item
- Type: any
- Description: The item to look for
OUTPUT
bool
EXAMPLES
SELECT array.contains(array, string) AS array_contains_item
array | string | → | array_contains_item |
---|---|---|---|
['how', 'are', 'you'] | 'you' | True | |
['how', 'are', 'you'] | 'me' | False | |
['1', '2', null, '3'] | null | null | |
['1', '2', '3'] | null | null | |
null | 'you' | null | |
[] | null | False |
SELECT array.contains(array, int) AS array_contains_item
array | int | → | array_contains_item |
---|---|---|---|
[1, 2, 3, -1, 0, 42] | 1 | True | |
[1, 2, 3, -1, 0, 42] | 2 | True | |
[1, 2, 3, -1, 0, 42] | -1 | True | |
[1, 2, 3, -1, 0, 42] | 42 | True | |
[1, 2, 3, -1, 0, 42] | 17 | False | |
[1, 2, 3, -1, 0, 42] | null | null | |
[1, 2, 3, null, 0, 42] | null | null | |
null | 1 | null |
SELECT array.contains(nested_array, array) AS array_contains_item
nested_array | array | → | array_contains_item |
---|---|---|---|
[['how'], ['are', 'you']] | ['how'] | True | |
[['how'], ['are', 'you']] | ['are', 'you'] | True | |
[['how'], ['are', 'you']] | ['are'] | False | |
[[null], ['are', 'you']] | [null] | null | |
[[null], ['are', 'you']] | ['???'] | null | |
[null, ['are', 'you']] | null | null | |
null | ['???'] | null |
ARRAY.DISTINCT
Removes duplicate values from the given array. This uses binary comparison to determine whether two values are duplicates of each other, which means that null is considered a duplicate of another null.
SYNTAX
array.distinct(array)
array
- Type: {'type': 'array', 'elementType': 'any'}
- Description: The input array
OUTPUT
{'type': 'array', 'elementType': 'any'}
EXAMPLES
SELECT array.distinct(array) as as_set;
array | → | as_set |
---|---|---|
[0, 1, null, 2] | [0, 1, null, 2] | |
[0, 1, null, 2, 1, 2] | [0, 1, null, 2] | |
[null, null, null] | [null] | |
[] | [] | |
null | null |
SELECT array.distinct(array) as as_set;
array | → | as_set |
---|---|---|
['Go', 'Tamr', 'Go', 'Tamr'] | ['Go', 'Tamr'] | |
['', '', null, '2', null, null] | ['', null, '2'] |
SELECT array.distinct(arrayOfArrays) as as_set;
arrayOfArrays | → | as_set |
---|---|---|
[[1, 2], [1], [2], [2, 1], [1, 2], [1], [2], [2, 1]] | [[1, 2], [1], [2], [2, 1]] | |
[[null], [], [null], []] | [[null], []] |
ARRAY.MOST_FREQUENT
Returns the N most frequently occurring values in an array, skipping null values. It would return less than N elements if there are less than N non-null distinct elements. It would not return more than N elements. If there are multiple values with the same frequency, it would return the smaller ones first. String characters are ranked from least to greatest in the order specified under ARRAY.SORT.Tip: To find the function that best meets your needs, review these similar functions: TOP, MODE.
SYNTAX
array.most_frequent(N, array)
N
- Type: int
- Description: The maximum number of top-occurring elements to return
array
- Type: {'type': 'array', 'elementType': 'any'}
- Description: An array in which to find the N most frequent elements
OUTPUT
{'type': 'array', 'elementType': 'any'}
EXAMPLES
SELECT array.most_frequent(2, input) AS output
input | → | output |
---|---|---|
[0, 0, 1, 1, 2, 3] | [0, 1] | |
[0, 0, 0, 2, 2, 1, 1] | [0, 1] | |
[0, 1, 2, 3] | [0, 1] | |
[0, 0, 0, 0] | [0] | |
[0] | [0] | |
[0, 1, null, null, null] | [0, 1] | |
[0, null, null, null] | [0] | |
[null, null, null] | [] | |
[null] | [] | |
[] | [] | |
null | null |
SELECT array.most_frequent(0, input) AS output
input | → | output |
---|---|---|
[0, 1, 2, 3] | [] |
SELECT array.most_frequent(-1, input) AS output
input | → | output |
---|---|---|
[0, 1, 2, 3] | null |
ARRAY.FIRST
Returns the first element in the given array.Tip: To find the function that best meets your needs, review similar function FIRST.
SYNTAX
array.first(array)
array
- Type: {'type': 'array', 'elementType': 'any'}
- Description: The input array
OUTPUT
any
EXAMPLE
SELECT array.first(array) as first_element;
array | → | first_element |
---|---|---|
['1', '2', '3'] | '1' | |
['', '2', '3'] | '' | |
[null, '2', '3'] | null | |
[] | null | |
null | null |
ARRAY.LAST
Returns the last element in the given array.Tip: To find the function that best meets your needs, review similar function LAST.
SYNTAX
array.last(array)
array
- Type: {'type': 'array', 'elementType': 'any'}
- Description: The input array
OUTPUT
any
EXAMPLE
SELECT array.last(array) as last_element;
array | → | last_element |
---|---|---|
['1', '2', '3'] | '3' | |
['1', '2', ''] | '' | |
['1', '2', null] | null | |
[] | null | |
null | null |
ARRAY.REST
Removes the first element from an array.
SYNTAX
array.rest(array)
array
- Type: {'type': 'array', 'elementType': 'any'}
- Description: The input array
OUTPUT
{'type': 'array', 'elementType': 'any'}
EXAMPLE
SELECT array.rest(array) as the_rest;
array | → | the_rest |
---|---|---|
['1', '2', '3'] | ['2', '3'] | |
['', '', '', ''] | ['', '', ''] | |
[null, null, '1'] | [null, '1'] | |
['1'] | [] | |
[] | null | |
null | null |
ARRAY.REVERSE
Returns the given array with elements in reverse order.
SYNTAX
array.reverse(array)
array
- Type: {'type': 'array', 'elementType': 'any'}
- Description: The array to reverse
OUTPUT
{'type': 'array', 'elementType': 'any'}
EXAMPLES
SELECT array.reverse(array) as reversed;
array | → | reversed |
---|---|---|
[null, 'one', 'two', null, 'three', ''] | ['', 'three', null, 'two', 'one', null] | |
['1', '2', null] | [null, '2', '1'] | |
[] | [] | |
null | null |
SELECT array.reverse(array) as reversed;
array | → | reversed |
---|---|---|
[['one', 'two'], ['three'], ['four', 'five']] | [['four', 'five'], ['three'], ['one', 'two']] |
ARRAY.SORT
Sorts the input array.
SYNTAX
array.sort(array)
array
- Type: {'type': 'array', 'elementType': 'any'}
- Description: The input array
OUTPUT
{'type': 'array', 'elementType': 'any'}
EXAMPLES
SELECT array.sort(array) as sorted;
array | → | sorted |
---|---|---|
[null, 'b', 'a', null, 'c', ''] | [null, null, '', 'a', 'b', 'c'] | |
['2', '1', null] | [null, '1', '2'] | |
['v'] | ['v'] | |
[] | [] | |
null | null |
SELECT array.sort(array) as sorted;
array | → | sorted |
---|---|---|
[null, 4, 7, null, 4, 0] | [null, null, 0, 4, 4, 7] | |
[12] | [12] | |
[] | [] | |
null | null |
ARRAY.NULLS
Returns the given array filtered to only include the null values.
SYNTAX
array.nulls(array)
array
- Type: {'type': 'array', 'elementType': 'any'}
- Description: The input array
OUTPUT
{'type': 'array', 'elementType': 'any'}
EXAMPLES
SELECT array.nulls(string_array) AS array_of_nulls
string_array | → | array_of_nulls |
---|---|---|
[null, 'am', 'i', null, 'null', ''] | [null, null] | |
['no', 'nulls', 'here'] | [] | |
[] | [] | |
null | null |
SELECT array.nulls(int_array) AS array_of_nulls
int_array | → | array_of_nulls |
---|---|---|
[1, 2, null] | [null] | |
[null, null, null] | [null, null, null] |
SELECT array.nulls(array_of_array_of_string) AS array_of_nulls
array_of_array_of_string | → | array_of_nulls |
---|---|---|
[null, [null], ['hello', null, 'world']] | [null] | |
[[], ['i', 'have', 'strings']] | [] |
ARRAY.NON_NULLS
Returns the given array with all null values filtered out. Similar functions: NON_EMPTIES and COALESCE.
SYNTAX
array.non_nulls(array)
array
- Type: {'type': 'array', 'elementType': 'any'}
- Description: The input array
OUTPUT
{'type': 'array', 'elementType': 'any'}
EXAMPLES
SELECT array.non_nulls(string_array) AS non_nulls
string_array | → | non_nulls |
---|---|---|
[null, 'am', 'i', null, 'null', ''] | ['am', 'i', 'null', ''] | |
['no', 'nulls', 'here'] | ['no', 'nulls', 'here'] | |
[] | [] | |
null | null |
SELECT array.non_nulls(int_array) AS non_nulls
int_array | → | non_nulls |
---|---|---|
[1, 2, null] | [1, 2] | |
[null, null, null] | [] |
SELECT array.non_nulls(array_of_array_of_string) AS non_nulls
array_of_array_of_string | → | non_nulls |
---|---|---|
[null, [null], ['hello', null, 'world']] | [[null], ['hello', null, 'world']] | |
[[], ['i', 'have', 'strings']] | [[], ['i', 'have', 'strings']] |
ARRAY.NON_EMPTIES
Returns the given array with all empty values filtered out.Similar functions: NON_NULLS and COALESCE.
SYNTAX
array.non_empties(array)
array
- Type: {'type': 'array', 'elementType': 'any'}
- Description: The input array
OUTPUT
{'type': 'array', 'elementType': 'any'}
EXAMPLES
SELECT array.non_empties(string_array) AS non_empties
string_array | → | non_empties |
---|---|---|
[null, 'am', 'i', null, 'null', '', '\t \n'] | ['am', 'i', 'null'] | |
['no', 'nulls', 'here'] | ['no', 'nulls', 'here'] | |
[] | [] | |
null | null |
SELECT array.non_empties(int_array) AS non_empties
int_array | → | non_empties |
---|---|---|
[1, 2, null] | [1, 2] | |
[null, null, null] | [] |
SELECT array.non_empties(array_of_array_of_string) AS non_empties
array_of_array_of_string | → | non_empties |
---|---|---|
[null, [], [null], [null, '', '\t \n'], ['hello', null, 'world', '', '\t \n']] | [['hello', null, 'world', '', '\t \n']] | |
[[], ['i', 'have', 'strings']] | [['i', 'have', 'strings']] |
ARRAY.SLICE
Removes the elements of the array before the start_index
. If start_index
is positive or zero, it indexes the array starting from the beginning; if negative, it indexes the array starting from the end.Tip: To find the function that best meets your needs, review these similar functions: GET, ARRAY.SLICE2.
SYNTAX
array.slice(array, start_index)
array
- Type: {'type': 'array', 'elementType': 'any'}
- Description: The input array
start_index
- Type: int
- Description: The index denoting the start (inclusive) of the return array
OUTPUT
{'type': 'array', 'elementType': 'any'}
EXAMPLE
SELECT array.slice(string_array, start_index) AS subarray
string_array | start_index | → | subarray |
---|---|---|---|
['0', '1', '2', '3'] | 0 | ['0', '1', '2', '3'] | |
['0', '1', '2', '3'] | 1 | ['1', '2', '3'] | |
['0', '1', '2', '3'] | 2 | ['2', '3'] | |
['0', '1', '2', '3'] | 3 | ['3'] | |
['0', '1', '2', '3'] | 4 | [] | |
['0', '1', '2', '3'] | 5 | null | |
['0', '1', '2', '3'] | -1 | ['3'] | |
['0', '1', '2', '3'] | -2 | ['2', '3'] | |
['0', '1', '2', '3'] | -3 | ['1', '2', '3'] | |
['0', '1', '2', '3'] | -4 | ['0', '1', '2', '3'] | |
['0', '1', '2', '3'] | -5 | ['0', '1', '2', '3'] | |
['0', '1', '2', '3'] | null | null | |
null | 0 | null | |
[] | 0 | [] |
ARRAY.SLICE2
Returns the part of the given array between the start_index
and end_index
. The values start_index
and end_index
may be either zero or positive (indexing from the start of the array) or negative (indexing from the end). Together, start_index
(inclusive) and end_index
(exclusive) determine the range of the array. If the range is empty then null is returned. If end_index
is positive, any value greater than the index of the last element is equivalent to that index. Tip: To find the function that best meets your needs, review these similar functions: GET, ARRAY.SLICE.
SYNTAX
array.slice2(array, start_index, end_index)
array
- Type: {'type': 'array', 'elementType': 'any'}
- Description: The input array
start_index
- Type: int
- Description: The index denoting the start (inclusive) of the return array
end_index
- Type: int
- Description: The index denoting the end (exclusive) of the return array
OUTPUT
{'type': 'array', 'elementType': 'any'}
EXAMPLE
SELECT array.slice2(string_array, start_index, end_index) AS subarray
string_array | start_index | end_index | → | subarray |
---|---|---|---|---|
['0', '1', '2', '3'] | 0 | 3 | ['0', '1', '2'] | |
['0', '1', '2', '3'] | 1 | 2 | ['1'] | |
['0', '1', '2', '3'] | 1 | 3 | ['1', '2'] | |
['0', '1', '2', '3'] | 1 | 1 | [] | |
['0', '1', '2', '3'] | 0 | 4 | ['0', '1', '2', '3'] | |
['0', '1', '2', '3'] | 0 | 5 | ['0', '1', '2', '3'] | |
['0', '1', '2', '3'] | 3 | 2 | null | |
['0', '1', '2', '3'] | -1 | 4 | ['3'] | |
['0', '1', '2', '3'] | -1 | 3 | [] | |
['0', '1', '2', '3'] | 1 | -1 | ['1', '2'] | |
['0', '1', '2', '3'] | -3 | -1 | ['1', '2'] | |
null | 0 | 1 | null | |
['0', '1', '2', '3'] | null | 1 | null | |
['0', '1', '2', '3'] | 0 | null | null | |
[] | 0 | 1 | [] |
ARRAY.SUM
Returns the sum of the non-null elements of a numeric array. If no non-null elements exist in the array, null is returned.Tip: To find the function that best meets your needs, review similar function SUM.
SYNTAX
array.sum(array)
array
- Type: {'type': 'array', 'elementType': 'number'}
- Description: The input array
OUTPUT
number
EXAMPLES
SELECT array.sum(int_array) AS sum
int_array | → | sum |
---|---|---|
[null, 3, 8, null, -2] | 9 | |
[null, null] | null | |
[] | null | |
null | null |
SELECT array.sum(long_array) AS sum
long_array | → | sum |
---|---|---|
[null, 3, 8, null, -2] | 9 | |
[null, null] | null | |
[] | null | |
null | null |
SELECT array.sum(double_array) AS sum
double_array | → | sum |
---|---|---|
[null, 3.0, 8.0, null, -2.5] | 8.5 | |
[null, null] | null | |
[] | null | |
null | null |
SELECT array.sum(float_array) AS sum
float_array | → | sum |
---|---|---|
[null, 3.5, 8.5, null, -2.0] | 10.0 | |
[null, null] | null | |
[] | null | |
null | null |
Updated 6 months ago