ARRAY.APPEND

Adds a value to the end of an array.

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.

SYNTAX

array.concat()

OUTPUT

{'type': 'array', 'elementType': 'any'}

EXAMPLE

SELECT array.concat(...arrays) AS concatenated_arrays

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

Returns the first element in the given array.

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.

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

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

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.

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