User GuidesAPI ReferenceRelease Notes
Doc HomeHelp CenterLog In

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
arrayvalue→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
array1array2→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']
nullnullnull
['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
arraystring→array_contains_item
['how', 'are', 'you']'you'True
['how', 'are', 'you']'me'False
['1', '2', null, '3']nullnull
['1', '2', '3']nullnull
null'you'null
[]nullFalse
SELECT array.contains(array, int) AS array_contains_item
arrayint→array_contains_item
[1, 2, 3, -1, 0, 42]1True
[1, 2, 3, -1, 0, 42]2True
[1, 2, 3, -1, 0, 42]-1True
[1, 2, 3, -1, 0, 42]42True
[1, 2, 3, -1, 0, 42]17False
[1, 2, 3, -1, 0, 42]nullnull
[1, 2, 3, null, 0, 42]nullnull
null1null
SELECT array.contains(nested_array, array) AS array_contains_item
nested_arrayarray→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']]nullnull
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]
[][]
nullnull
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][]
[][]
nullnull
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
nullnull

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
nullnull

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
nullnull

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']
[][]
nullnull
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']
[][]
nullnull
SELECT array.sort(array) as sorted;
array→sorted
[null, 4, 7, null, 4, 0][null, null, 0, 4, 4, 7]
[12][12]
[][]
nullnull

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'][]
[][]
nullnull
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']
[][]
nullnull
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']
[][]
nullnull
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_arraystart_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']5null
['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']nullnull
null0null
[]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_arraystart_indexend_index→subarray
['0', '1', '2', '3']03['0', '1', '2']
['0', '1', '2', '3']12['1']
['0', '1', '2', '3']13['1', '2']
['0', '1', '2', '3']11[]
['0', '1', '2', '3']04['0', '1', '2', '3']
['0', '1', '2', '3']05['0', '1', '2', '3']
['0', '1', '2', '3']32null
['0', '1', '2', '3']-14['3']
['0', '1', '2', '3']-13[]
['0', '1', '2', '3']1-1['1', '2']
['0', '1', '2', '3']-3-1['1', '2']
null01null
['0', '1', '2', '3']null1null
['0', '1', '2', '3']0nullnull
[]01[]

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
nullnull
SELECT array.sum(long_array) AS sum
long_array→sum
[null, 3, 8, null, -2]9
[null, null]null
[]null
nullnull
SELECT array.sum(double_array) AS sum
double_array→sum
[null, 3.0, 8.0, null, -2.5]8.5
[null, null]null
[]null
nullnull
SELECT array.sum(float_array) AS sum
float_array→sum
[null, 3.5, 8.5, null, -2.0]10.0
[null, null]null
[]null
nullnull