SQL array functions

Use array functions to create and operate on Arrow arrays or lists in SQL queries.

• array_any_value
• array_append
• array_cat
• array_concat
• array_contains
• array_dims
• array_distance
• array_distinct
• array_element
• array_empty
• array_except
• array_extract
• array_has
• array_has_all
• array_has_any
• array_indexof
• array_intersect
• array_join
• array_length
• array_max
• array_min
• array_ndims
• array_pop_back
• array_pop_front
• array_position
• array_positions
• array_prepend
• array_push_back
• array_push_front
• array_remove
• array_remove_all
• array_remove_n
• array_repeat
• array_replace
• array_replace_all
• array_replace_n
• array_resize
• array_reverse
• array_slice
• array_sort
• array_to_string
• array_union
• arrays_overlap
• cardinality
• empty
• flatten
• generate_series
• list_any_value
• list_append
• list_cat
• list_concat
• list_contains
• list_dims
• list_distance
• list_distinct
• list_element
• list_empty
• list_except
• list_extract
• list_has
• list_has_all
• list_has_any
• list_indexof
• list_intersect
• list_join
• list_length
• list_max
• list_ndims
• list_pop_back
• list_pop_front
• list_position
• list_positions
• list_prepend
• list_push_back
• list_push_front
• list_remove
• list_remove_all
• list_remove_n
• list_repeat
• list_replace
• list_replace_all
• list_replace_n
• list_resize
• list_reverse
• list_slice
• list_sort
• list_to_string
• list_union
• make_array
• make_list
• range
• string_to_array
• string_to_list

array_any_value

Returns the first non-null element in the array.

array_any_value(array)

Arguments

• array: Array expression. Can be a constant, column, or function, and any combination of array operators.

Aliases

• list_any_value

SELECT array_any_value([NULL, 1, 2, 3]) AS array_any_value

array_append

Appends an element to the end of an array.

array_append(array, element)

Arguments

• array: Array expression. Can be a constant, column, or function, and any combination of array operators.
• element: Element to append to the array.

Aliases

• list_append
• array_push_back
• list_push_back

SELECT array_append([1, 2, 3], 4) AS array_append

array_cat

Alias of array_concat.

array_concat

Concatenates multiple arrays into a single array.

array_concat(array[, ..., array_n])

Arguments

• array: Array expression. Can be a constant, column, or function, and any combination of array operators.
• array_n: Subsequent array column or literal array to concatenate.

Aliases

• array_cat
• list_concat
• list_cat

SELECT array_concat([1, 2], [3, 4], [5, 6]) AS array_concat

array_contains

Alias of array_has.

array_dims

Returns an array of the array’s dimensions.

array_dims(array)

Arguments

• array: Array expression. Can be a constant, column, or function, and any combination of array operators.

Aliases

• list_dims

SELECT array_dims([[1, 2, 3], [4, 5, 6]]) AS array_dims

array_distance

Returns the Euclidean distance between two input arrays of equal length.

array_distance(array1, array2)

Arguments

• array1: Array expression. Can be a constant, column, or function, and any combination of array operators.
• array2: Array expression. Can be a constant, column, or function, and any combination of array operators.

Aliases

• list_distance

SELECT array_distance([1, 2], [1, 4]) AS array_distance

array_distinct

Returns distinct values from the array after removing duplicates.

array_distinct(array)

Arguments

• array: Array expression. Can be a constant, column, or function, and any combination of array operators.

Aliases

• list_distinct

SELECT array_distinct([1, 3, 2, 3, 1, 2, 4]) AS array_distinct

array_element

Extracts the element with the index n from the array.

array_element(array, index)

Arguments

• array: Array expression. Can be a constant, column, or function, and any combination of array operators.
• index: Index to use to extract the element from the array.

Aliases

• array_extract
• list_element
• list_extract

SELECT array_element([1, 2, 3, 4], 3) AS array_element

array_empty

Alias of empty.

array_except

Returns an array containing elements from the first array that are not present in the second array.

array_except(array1, array2)

Arguments

• array1: Array expression. Can be a constant, column, or function, and any combination of array operators.
• array2: Array expression. Can be a constant, column, or function, and any combination of array operators.

Aliases

• list_except

SELECT array_except([1, 2, 3, 4], [5, 6, 3, 4]) AS array_except

array_extract

Alias of array_element.

array_has

Returns true if the array contains the element.

array_has(array, element)

Arguments

• array: Array expression. Can be a constant, column, or function, and any combination of array operators.
• element: Scalar or Array expression. Can be a constant, column, or function, and any combination of array operators.

Aliases

• list_has
• array_contains
• list_contains

SELECT array_has([1, 2, 3], 2) AS array_has

array_has_all

Returns true if all elements of sub-array exist in array.

array_has_all(array, sub-array)

Arguments

• array: Array expression. Can be a constant, column, or function, and any combination of array operators.
• sub-array: Array expression. Can be a constant, column, or function, and any combination of array operators.

Aliases

• list_has_all

SELECT array_has_all([1, 2, 3, 4], [2, 3]) AS array_has_all

array_has_any

Returns true if at least one element appears in both arrays.

array_has_any(array, sub-array)

Arguments

• array: Array expression. Can be a constant, column, or function, and any combination of array operators.
• sub-array: Array expression. Can be a constant, column, or function, and any combination of array operators.

Aliases

• list_has_any
• arrays_overlap

SELECT array_has_any([1, 2, 3], [3, 4]) AS array_has_any

array_indexof

Alias of array_position.

array_intersect

Returns an array containing only the elements that appear in both array1 and array2.

array_intersect(array1, array2)

Arguments

• array1: Array expression. Can be a constant, column, or function, and any combination of array operators.
• array2: Array expression. Can be a constant, column, or function, and any combination of array operators.

Aliases

• list_intersect

SELECT array_intersect([1, 2, 3, 4], [5, 6, 3, 4]) AS array_intersect

SELECT array_intersect([1, 2, 3, 4], [5, 6, 7, 8]) AS array_intersect

array_join

Alias of array_to_string.

array_length

Returns the length of the array dimension.

array_length(array, dimension)

Arguments

• array: Array expression. Can be a constant, column, or function, and any combination of array operators.
• dimension: Array dimension. Default is 1.

Aliases

• list_length

SELECT array_length([1, 2, 3, 4, 5]) AS array_length

WITH vars AS (
  SELECT [
    [1, 2, 3, 4, 5],
    [5, 6, 7, 8, 9]
  ] AS example_array
)

SELECT
  array_length(example_array, 1) AS 'dim1_length',
  array_length(example_array, 2) AS 'dim2_length'
FROM vars

array_max

Returns the maximum value in the array.

array_max(array)

Arguments

• array: Array expression. Can be a constant, column, or function, and any combination of array operators.

Aliases

• list_max

SELECT array_max([3,1,4,2]) AS array_max

array_min

Returns the minimum value in the array.

array_min(array)

Arguments

• array: Array expression. Can be a constant, column, or function, and any combination of array operators.

SELECT array_min([3,1,4,2]) AS array_min

array_ndims

Returns the number of dimensions of the array.

array_ndims(array)

Arguments

• array: Array expression. Can be a constant, column, or function, and any combination of array operators.

Aliases

• list_ndims

SELECT array_ndims([[1, 2, 3], [4, 5, 6]]) AS array_ndims

array_pop_back

Returns the array without the last element.

array_pop_back(array)

Arguments

• array: Array expression. Can be a constant, column, or function, and any combination of array operators.

Aliases

• list_pop_back

SELECT array_pop_back([1, 2, 3]) AS array_pop_back

array_pop_front

Returns the array without the first element.

array_pop_front(array)

Arguments

• array: Array expression. Can be a constant, column, or function, and any combination of array operators.

Aliases

• list_pop_front

SELECT array_pop_front([1, 2, 3]) AS array_pop_front

array_position

Returns the position of the first occurrence of the specified element in the array, or NULL if not found.

array_position(array, element, index)

Arguments

• array: Array expression. Can be a constant, column, or function, and any combination of array operators.
• element: Element to search for position in the array.
• index: Index at which to start searching (1-indexed). Default is 1.

Aliases

• list_position
• array_indexof
• list_indexof

SELECT array_position([1, 2, 2, 3, 1, 4], 2) AS array_position

SELECT array_position([1, 2, 2, 3, 1, 4], 2, 3) AS array_position

array_positions

Searches for an element in the array and returns the position or index of each occurrence.

array_positions(array, element)

Arguments

• array: Array expression. Can be a constant, column, or function, and any combination of array operators.
• element: Element to search for position in the array.

Aliases

• list_positions

SELECT array_positions(['John', 'Jane', 'James', 'John'], 'John') AS array_positions

array_prepend

Prepends an element to the beginning of an array.

array_prepend(element, array)

Arguments

• array: Array expression. Can be a constant, column, or function, and any combination of array operators.
• element: Element to prepend to the array.

Aliases

• list_prepend
• array_push_front
• list_push_front

SELECT array_prepend(1, [2, 3, 4]) AS array_prepend

array_push_back

Alias of array_append.

array_push_front

Alias of array_prepend.

array_remove

Removes the first element from the array equal to the given value.

array_remove(array, element)

Arguments

• array: Array expression. Can be a constant, column, or function, and any combination of array operators.
• element: Element to remove from the array.

Aliases

• list_remove

SELECT array_remove([1, 2, 2, 3, 2, 1, 4], 2) AS array_remove

array_remove_all

Removes all elements from the array equal to the specified value.

array_remove_all(array, element)

Arguments

• array: Array expression. Can be a constant, column, or function, and any combination of array operators.
• element: Element to be removed from the array.

Aliases

• list_remove_all

SELECT array_remove_all([1, 2, 2, 3, 2, 1, 4], 2) AS array_remove_all

array_remove_n

Removes the first max elements from the array equal to the specified value.

array_remove_n(array, element, max)

Arguments

• array: Array expression. Can be a constant, column, or function, and any combination of array operators.
• element: Element to remove from the array.
• max: Maximum number of occurrences to remove.

Aliases

• list_remove_n

SELECT array_remove_n([1, 2, 2, 3, 2, 1, 4], 2, 2) AS array_remove_n

array_repeat

Returns an array containing element count times.

array_repeat(element, count)

Arguments

• element: Element expression. Can be a constant, column, or function, and any combination of array operators.
• count: Number of times to repeat the element.

Aliases

• list_repeat

SELECT array_repeat(1, 3) AS array_repeat

SELECT array_repeat('John', 3) AS array_repeat

SELECT array_repeat([1, 2], 2) AS array_repeat

array_replace

Replaces the first occurrence of the specified element with another specified element.

array_replace(array, from, to)

Arguments

• array: Array expression. Can be a constant, column, or function, and any combination of array operators.
• from: Element to replace.
• to: Replacement element.

Aliases

• list_replace

SELECT array_replace(['John', 'Jane', 'James', 'John'], 'John', 'Joe') AS array_replace

array_replace_all

Replaces all occurrences of the specified element with another specified element.

array_replace_all(array, from, to)

Arguments

• array: Array expression. Can be a constant, column, or function, and any combination of array operators.
• from: Element to replace.
• to: Replacement element.

Aliases

• list_replace_all

SELECT array_replace_all(['John', 'Jane', 'James', 'John'], 'John', 'Joe') AS array_replace_all

array_replace_n

Replaces the first max occurrences of the specified element with another specified element.

array_replace_n(array, from, to, max)

Arguments

• array: Array expression. Can be a constant, column, or function, and any combination of array operators.
• from: Element to replace.
• to: Replacement element.
• max: Maximum number of occurrences to replace.

Aliases

• list_replace_n

SELECT array_replace_n(['John', 'Jane', 'James', 'John', 'John'], 'John', 'Joe', 2) AS array_replace_n

array_resize

Resizes the list to contain size elements. Initializes new elements with value Resizes the array to the specified size. If expanding, fills new elements with the specified value (or NULL if not provided). If shrinking, truncates excess elements.

array_resize(array, size, value)

Arguments

• array: Array expression. Can be a constant, column, or function, and any combination of array operators.
• size: New size of the array.
• value: Value to use for new elements. Default is NULL.

Aliases

• list_resize

SELECT array_resize([1, 2, 3], 5, 0) AS array_resize

array_reverse

Returns the array with the order of the elements reversed.

array_reverse(array)

Arguments

• array: Array expression. Can be a constant, column, or function, and any combination of array operators.

Aliases

• list_reverse

SELECT array_reverse([1, 2, 3, 4]) AS array_reverse

array_slice

Returns a slice of the array based on 1-indexed start and end positions.

array_slice(array, begin, end)

Arguments

• array: Array expression. Can be a constant, column, or function, and any combination of array operators.
• begin: Index of the first element. If negative, it counts backward from the end of the array.
• end: Index of the last element. If negative, it counts backward from the end of the array.
• stride: Stride of the array slice. The default is 1.

Aliases

• list_slice

SELECT array_slice([1, 2, 3, 4, 5, 6, 7, 8], 3, 6) AS array_slice

array_sort

Sorts elements in an array. If elements are numeric, it sorts elements in numerical order. If elements are strings, it sorts elements in lexicographical order.

array_sort(array, sort_order, sort_nulls)

Arguments

• array: Array expression. Can be a constant, column, or function, and any combination of array operators.
• sort_order: Sort order ('ASC' (default) or 'DESC').
• sort_nulls: Sort nulls first or last ('NULLS FIRST' (default) or 'NULLS LAST').

Aliases

• list_sort

SELECT array_sort([3, 1, 2]) AS array_sort

SELECT array_sort(['banana', 'apple', 'cherry'], 'DESC') AS array_sort

SELECT
  array_sort(
    ['banana', 'apple', NULL, 'cherry', NULL],
    'ASC',
    'NULLS LAST'
  ) AS array_sort

array_to_string

Converts an array to a string by joining all elements with the specified delimiter.

array_to_string(array, delimiter[, null_string])

Arguments

• array: Array expression. Can be a constant, column, or function, and any combination of array operators.
• delimiter: Array element separator.
• null_string: Optional. String to replace NULL values in the array. If not provided, NULL elements are ignored.

Aliases

• list_to_string
• array_join
• list_join

SELECT array_to_string([1,2,3,4,5,6,7,8], ',') AS array_to_string

SELECT array_to_string([[1,2,3,4,5,NULL,7,8,NULL]], '-', '?') AS array_to_string

array_union

Returns an array of elements that are present in both arrays (all elements from Returns an array containing all unique elements from both input arrays, with duplicates removed.

array_union(array1, array2)

Arguments

• array1: Array expression. Can be a constant, column, or function, and any combination of array operators.
• array2: Array expression. Can be a constant, column, or function, and any combination of array operators.

Aliases

• list_union

SELECT array_union([1, 2, 3, 4], [5, 6, 3, 4]) AS array_union

arrays_overlap

Alias of array_has_any.

cardinality

Returns the total number of elements in the array.

cardinality(array)

Arguments

• array: Array expression. Can be a constant, column, or function, and any combination of array operators.

SELECT cardinality([[1, 2, 3, 4], [5, 6, 7, 8]]) AS cardinality

empty

Returns true for an empty array or false for a non-empty array.

empty(array)

Arguments

• array: Array expression. Can be a constant, column, or function, and any combination of array operators.

Aliases

• array_empty
• list_empty

SELECT empty(['apple']) AS empty

flatten

Flattens nested arrays into a single-level array.

• Recursively flattens arrays at any depth of nesting
• Returns unchanged if the array is already flat

The result contains all elements from all nested arrays in a single flat array.

flatten(array)

Arguments

• array: Array expression. Can be a constant, column, or function, and any combination of array operators.

SELECT flatten([[1, 2], [3, 4]]) AS flattened

generate_series

Returns an array with values between the specified start and stop values generated at the specified step.

The range start..stop contains all values greater than or equal to start and less than or equal to stop (start <= x <= stop). If start is greater than or equal to stop (start >= stop), the function returns an empty array.

generate_series is similar to range, but includes the upper bound (stop) in the output array.

generate_series(start, stop, step)

Arguments

• start: Start of the series. Supports integers, timestamps, dates, or string types that can be coerced to Date32.
• stop: Upper bound of the series. Supports integers, timestamps, dates, or string types that can be coerced to Date32. The type must be the same as start.
• step: Increase by step (cannot be 0). Steps less than a day are only supported for ranges with the TIMESTAMP type.

Related functions

range

SELECT generate_series(1,5) AS generate_series

SELECT
  generate_series(
    DATE '2025-03-01',
    DATE '2025-08-01',
    INTERVAL '1 month'
  ) AS generate_series

SELECT
  generate_series(
    '2025-01-01T00:00:00Z'::timestamp,
    '2025-01-01T06:00:00Z'::timestamp,
    INTERVAL '2 hours'
  ) AS generate_series

list_any_value

Alias of array_any_value.

list_append

Alias of array_append.

list_cat

Alias of array_concat.

list_concat

Alias of array_concat.

list_contains

Alias of array_has.

list_dims

Alias of array_dims.

list_distance

Alias of array_distance.

list_distinct

Alias of array_distinct.

list_element

Alias of array_element.

list_empty

Alias of empty.

list_except

Alias of array_except.

list_extract

Alias of array_element.

list_has

Alias of array_has.

list_has_all

Alias of array_has_all.

list_has_any

Alias of array_has_any.

list_indexof

Alias of array_position.

list_intersect

Alias of array_intersect.

list_join

Alias of array_to_string.

list_length

Alias of array_length.

list_max

Alias of array_max.

list_ndims

Alias of array_ndims.

list_pop_back

Alias of array_pop_back.

list_pop_front

Alias of array_pop_front.

list_position

Alias of array_position.

list_positions

Alias of array_positions.

list_prepend

Alias of array_prepend.

list_push_back

Alias of array_append.

list_push_front

Alias of array_prepend.

list_remove

Alias of array_remove.

list_remove_all

Alias of array_remove_all.

list_remove_n

Alias of array_remove_n.

list_repeat

Alias of array_repeat.

list_replace

Alias of array_replace.

list_replace_all

Alias of array_replace_all.

list_replace_n

Alias of array_replace_n.

list_resize

Alias of array_resize.

list_reverse

Alias of array_reverse.

list_slice

Alias of array_slice.

list_sort

Alias of array_sort.

list_to_string

Alias of array_to_string.

list_union

Alias of array_union.

make_array

Returns an array using the specified input expressions.

make_array(expression1[, ..., expression_n])

Arguments

• expression_n: Expression to include in the output array. Can be a constant, column, or function, and any combination of arithmetic or string operators.

Aliases

• make_list

SELECT make_array(1, 2, 3, 4, 5) AS make_array

make_list

Alias of make_array.

range

Returns an array with values between the specified start and stop values generated at the specified step.

The range start..stop contains all values greater than or equal to start and less than stop (start <= x < stop). If start is greater than or equal to stop (start >= stop), the function returns an empty array.

range is similar to generate_series, but does not include the upper bound (stop) in the output array.

range(start, stop, step)

Arguments

• start: Start of the series. Supports integers, timestamps, dates, or string types that can be coerced to Date32.
• stop: Upper bound of the series. Supports integers, timestamps, dates, or string types that can be coerced to Date32. The type must be the same as start.
• step: Increase by step (cannot be 0). Steps less than a day are only supported for ranges with the TIMESTAMP type.

Related functions

generate_series

SELECT range(1, 5, 1) AS range

SELECT
  range(
    DATE '2025-03-01',
    DATE '2025-08-01',
    INTERVAL '1 month'
  ) AS range

SELECT
  range(
    '2025-01-01T00:00:00Z'::timestamp,
    '2025-01-01T06:00:00Z'::timestamp,
    INTERVAL '2 hours'
  ) AS range

string_to_array

Splits a string into an array of substrings based on a delimiter. Any substrings matching the optional null_str argument are replaced with NULL.

string_to_array(str, delimiter[, null_str])

Arguments

• str: String expression to split.
• delimiter: Delimiter string to split on.
• null_str: (Optional) Substring values to replace with NULL.

Aliases

• string_to_list

SELECT string_to_array('abc, def, ghi', ', ') AS string_to_array

SELECT string_to_array('abc##def', '##') AS string_to_array

SELECT string_to_array('abc def', ' ', 'def') AS string_to_array

string_to_list

Alias of string_to_array.