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.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

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

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

SELECT array.contains(array, int) AS array_contains_item

SELECT array.contains(nested_array, array) AS array_contains_item

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;

SELECT array.distinct(array) as as_set;

SELECT array.distinct(arrayOfArrays) as as_set;

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

SELECT array.most_frequent(0, input) AS output

SELECT array.most_frequent(-1, input) AS output

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.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.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.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;

SELECT array.reverse(array) as reversed;

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;

SELECT array.sort(array) as sorted;

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

SELECT array.nulls(int_array) AS array_of_nulls

SELECT array.nulls(array_of_array_of_string) AS array_of_nulls

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

SELECT array.non_nulls(int_array) AS non_nulls

SELECT array.non_nulls(array_of_array_of_string) AS non_nulls

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

SELECT array.non_empties(int_array) AS non_empties

SELECT array.non_empties(array_of_array_of_string) AS non_empties

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

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

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

SELECT array.sum(long_array) AS sum

SELECT array.sum(double_array) AS sum

SELECT array.sum(float_array) AS sum