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
arrayvalueappended_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
arraysconcatenated_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
array1array2array_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
arraystringarray_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
arrayintarray_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_arrayarrayarray_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;
arrayas_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;
arrayas_set
['Go', 'Tamr', 'Go', 'Tamr']['Go', 'Tamr']
['', '', null, '2', null, null]['', null, '2']
SELECT array.distinct(arrayOfArrays) as as_set;
arrayOfArraysas_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
inputoutput
[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
inputoutput
[0, 1, 2, 3][]
SELECT array.most_frequent(-1, input) AS output
inputoutput
[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;
arrayfirst_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;
arraylast_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;
arraythe_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;
arrayreversed
[null, 'one', 'two', null, 'three', '']['', 'three', null, 'two', 'one', null]
['1', '2', null][null, '2', '1']
[][]
nullnull
SELECT array.reverse(array) as reversed;
arrayreversed
[['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;
arraysorted
[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;
arraysorted
[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_arrayarray_of_nulls
[null, 'am', 'i', null, 'null', ''][null, null]
['no', 'nulls', 'here'][]
[][]
nullnull
SELECT array.nulls(int_array) AS array_of_nulls
int_arrayarray_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_stringarray_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_arraynon_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_arraynon_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_stringnon_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_arraynon_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_arraynon_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_stringnon_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_indexsubarray
['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_indexsubarray
['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_arraysum
[null, 3, 8, null, -2]9
[null, null]null
[]null
nullnull
SELECT array.sum(long_array) AS sum
long_arraysum
[null, 3, 8, null, -2]9
[null, null]null
[]null
nullnull
SELECT array.sum(double_array) AS sum
double_arraysum
[null, 3.0, 8.0, null, -2.5]8.5
[null, null]null
[]null
nullnull
SELECT array.sum(float_array) AS sum
float_arraysum
[null, 3.5, 8.5, null, -2.0]10.0
[null, null]null
[]null
nullnull