---
title: SQL array functions
description: Use array functions to create and operate on Arrow arrays or lists in SQL queries.
url: https://docs.influxdata.com/influxdb3/cloud-serverless/reference/sql/functions/array/
estimated_tokens: 30407
product: InfluxDB Cloud Serverless
version: cloud-serverless
---

# SQL array functions

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

-   [array\_any\_value](#array_any_value)
-   [array\_append](#array_append)
-   [array\_cat](#array_cat)
-   [array\_concat](#array_concat)
-   [array\_contains](#array_contains)
-   [array\_dims](#array_dims)
-   [array\_distance](#array_distance)
-   [array\_distinct](#array_distinct)
-   [array\_element](#array_element)
-   [array\_empty](#array_empty)
-   [array\_except](#array_except)
-   [array\_extract](#array_extract)
-   [array\_has](#array_has)
-   [array\_has\_all](#array_has_all)
-   [array\_has\_any](#array_has_any)
-   [array\_indexof](#array_indexof)
-   [array\_intersect](#array_intersect)
-   [array\_join](#array_join)
-   [array\_length](#array_length)
-   [array\_max](#array_max)
-   [array\_min](#array_min)
-   [array\_ndims](#array_ndims)
-   [array\_pop\_back](#array_pop_back)
-   [array\_pop\_front](#array_pop_front)
-   [array\_position](#array_position)
-   [array\_positions](#array_positions)
-   [array\_prepend](#array_prepend)
-   [array\_push\_back](#array_push_back)
-   [array\_push\_front](#array_push_front)
-   [array\_remove](#array_remove)
-   [array\_remove\_all](#array_remove_all)
-   [array\_remove\_n](#array_remove_n)
-   [array\_repeat](#array_repeat)
-   [array\_replace](#array_replace)
-   [array\_replace\_all](#array_replace_all)
-   [array\_replace\_n](#array_replace_n)
-   [array\_resize](#array_resize)
-   [array\_reverse](#array_reverse)
-   [array\_slice](#array_slice)
-   [array\_sort](#array_sort)
-   [array\_to\_string](#array_to_string)
-   [array\_union](#array_union)
-   [arrays\_overlap](#arrays_overlap)
-   [cardinality](#cardinality)
-   [empty](#empty)
-   [flatten](#flatten)
-   [generate\_series](#generate_series)
-   [list\_any\_value](#list_any_value)
-   [list\_append](#list_append)
-   [list\_cat](#list_cat)
-   [list\_concat](#list_concat)
-   [list\_contains](#list_contains)
-   [list\_dims](#list_dims)
-   [list\_distance](#list_distance)
-   [list\_distinct](#list_distinct)
-   [list\_element](#list_element)
-   [list\_empty](#list_empty)
-   [list\_except](#list_except)
-   [list\_extract](#list_extract)
-   [list\_has](#list_has)
-   [list\_has\_all](#list_has_all)
-   [list\_has\_any](#list_has_any)
-   [list\_indexof](#list_indexof)
-   [list\_intersect](#list_intersect)
-   [list\_join](#list_join)
-   [list\_length](#list_length)
-   [list\_max](#list_max)
-   [list\_ndims](#list_ndims)
-   [list\_pop\_back](#list_pop_back)
-   [list\_pop\_front](#list_pop_front)
-   [list\_position](#list_position)
-   [list\_positions](#list_positions)
-   [list\_prepend](#list_prepend)
-   [list\_push\_back](#list_push_back)
-   [list\_push\_front](#list_push_front)
-   [list\_remove](#list_remove)
-   [list\_remove\_all](#list_remove_all)
-   [list\_remove\_n](#list_remove_n)
-   [list\_repeat](#list_repeat)
-   [list\_replace](#list_replace)
-   [list\_replace\_all](#list_replace_all)
-   [list\_replace\_n](#list_replace_n)
-   [list\_resize](#list_resize)
-   [list\_reverse](#list_reverse)
-   [list\_slice](#list_slice)
-   [list\_sort](#list_sort)
-   [list\_to\_string](#list_to_string)
-   [list\_union](#list_union)
-   [make\_array](#make_array)
-   [make\_list](#make_list)
-   [range](#range)
-   [string\_to\_array](#string_to_array)
-   [string\_to\_list](#string_to_list)

## array\_any\_value

Returns the first non-null element in the array.

```sql
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`

[](#view-array_any_value-example)

View `array_any_value` example

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

| array_any_value |
| --- |
| 1 |

## array\_append

Appends an element to the end of an array.

```sql
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`

[](#view-array_append-example)

View `array_append` example

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

| array_append |
| --- |
| [1, 2, 3, 4] |

## array\_cat

*Alias of [array\_concat](#array_concat).*

## array\_concat

Concatenates multiple arrays into a single array.

```sql
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`

[](#view-array_concat-example)

View `array_concat` example

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

| array_concat |
| --- |
| [1, 2, 3, 4, 5, 6] |

## array\_contains

*Alias of [array\_has](#array_has).*

## array\_dims

Returns an array of the array’s dimensions.

```sql
array_dims(array)
```

### Arguments

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

#### Aliases

-   `list_dims`

[](#view-array_dims-example)

View `array_dims` example

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

| array_dims(List([1,2,3,4,5,6])) |
| --- |
| [2, 3] |

## array\_distance

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

```sql
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`

[](#view-array_distance-example)

View `array_distance` example

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

| array_distance |
| --- |
| 2.0 |

## array\_distinct

Returns distinct values from the array after removing duplicates.

```sql
array_distinct(array)
```

### Arguments

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

#### Aliases

-   `list_distinct`

[](#view-array_distinct-example)

View `array_distinct` example

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

| array_distinct(List([1,2,3,4])) |
| --- |
| [1, 2, 3, 4] |

## array\_element

Extracts the element with the index n from the array.

```sql
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`

[](#view-array_element-example)

View `array_element` example

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

| array_element |
| --- |
| 3 |

## array\_empty

*Alias of [empty](#empty).*

## array\_except

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

```sql
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`

[](#view-array_except-example)

View `array_except` example

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

| array_except |
| --- |
| [1, 2] |

## array\_extract

*Alias of [array\_element](#array_element).*

## array\_has

Returns `true` if the array contains the element.

```sql
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`

[](#view-array_has-example)

View `array_has` example

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

| array_has |
| --- |
| true |

## array\_has\_all

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

```sql
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`

[](#view-array_has_all-example)

View `array_has_all` example

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

| array_has_all |
| --- |
| true |

## array\_has\_any

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

```sql
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`

[](#view-array_has_any-example)

View `array_has_any` example

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

| array_has_any |
| --- |
| true |

## array\_indexof

*Alias of [array\_position](#array_position).*

## array\_intersect

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

```sql
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`

[](#view-array_intersect-example-with-intersecting-arrays)

View `array_intersect` example with intersecting arrays

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

| array_intersect |
| --- |
| [3, 4] |

[](#view-array_intersect-example-with-non-intersecting-arrays)

View `array_intersect` example with non-intersecting arrays

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

| array_intersect |
| --- |
| [] |

## array\_join

*Alias of [array\_to\_string](#array_to_string).*

## array\_length

Returns the length of the array dimension.

```sql
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`

[](#view-array_length-example-with-single-dimension-array)

View `array_length` example with single-dimension array

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

| array_length |
| --- |
| 5 |

[](#view-array_length-example-with-multi-dimension-array)

View `array_length` example with multi-dimension array

```sql
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
```

| dim1_length | dim2_length |
| --- | --- |
| 2 | 5 |

## array\_max

Returns the maximum value in the array.

```sql
array_max(array)
```

### Arguments

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

#### Aliases

-   `list_max`

[](#view-array_max-example)

View `array_max` example

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

| array_max |
| --- |
| 4 |

## array\_min

Returns the minimum value in the array.

```sql
array_min(array)
```

### Arguments

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

[](#view-array_min-example)

View `array_min` example

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

| array_min |
| --- |
| 1 |

## array\_ndims

Returns the number of dimensions of the array.

```sql
array_ndims(array)
```

### Arguments

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

#### Aliases

-   `list_ndims`

[](#view-array_ndims-example)

View `array_ndims` example

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

| array_ndims |
| --- |
| 2 |

## array\_pop\_back

Returns the array without the last element.

```sql
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`

[](#view-array_pop_back-example)

View `array_pop_back` example

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

| array_pop_back |
| --- |
| [1, 2] |

## array\_pop\_front

Returns the array without the first element.

```sql
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`

[](#view-array_pop_front-example)

View `array_pop_front` example

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

| array_pop_front |
| --- |
| [2, 3] |

## array\_position

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

```sql
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`

[](#view-array_position-example)

View `array_position` example

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

| array_position |
| --- |
| 2 |

[](#view-array_position-example-with-index-offset)

View `array_position` example with index offset

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

| array_position |
| --- |
| 3 |

## array\_positions

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

```sql
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`

[](#view-array_positions-example)

View `array_positions` example

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

| array_positions |
| --- |
| [1, 4] |

## array\_prepend

Prepends an element to the beginning of an array.

```sql
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`

[](#view-array_prepend-example)

View `array_prepend` example

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

| array_prepend |
| --- |
| [1, 2, 3, 4] |

## array\_push\_back

*Alias of [array\_append](#array_append).*

## array\_push\_front

*Alias of [array\_prepend](#array_prepend).*

## array\_remove

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

```sql
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`

[](#view-array_remove-example)

View `array_remove` example

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

| array_remove |
| --- |
| [1, 2, 3, 2, 1, 4] |

## array\_remove\_all

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

```sql
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`

[](#view-array_remove_all-example)

View `array_remove_all` example

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

| array_remove_all |
| --- |
| [1, 3, 1, 4] |

## array\_remove\_n

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

```sql
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`

[](#view-array_remove_n-example)

View `array_remove_n` example

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

| array_remove_n |
| --- |
| [1, 3, 2, 1, 4] |

## array\_repeat

Returns an array containing element `count` times.

```sql
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`

[](#view-array_repeat-example-with-numeric-values)

View `array_repeat` example with numeric values

```sql
SELECT array_repeat(1, 3) AS array_repeat
```

| array_repeat |
| --- |
| [1, 1, 1] |

[](#view-array_repeat-example-with-string-values)

View `array_repeat` example with string values

```sql
SELECT array_repeat('John', 3) AS array_repeat
```

| array_repeat |
| --- |
| [John, John, John] |

[](#view-array_repeat-example-with-array-values)

View `array_repeat` example with array values

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

| array_repeat |
| --- |
| [[1, 2], [1, 2]] |

## array\_replace

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

```sql
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`

[](#view-array_replace-example)

View `array_replace` example

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

| array_replace |
| --- |
| [Joe, Jane, James, John] |

## array\_replace\_all

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

```sql
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`

[](#view-array_replace_all-example)

View `array_replace_all` example

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

| array_replace_all |
| --- |
| [Joe, Jane, James, Joe] |

## array\_replace\_n

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

```sql
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`

[](#view-array_replace_n-example)

View `array_replace_n` example

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

| array_replace_n |
| --- |
| [Joe, Jane, James, Joe, John] |

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

```sql
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`

[](#view-array_resize-example)

View `array_resize` example

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

| array_resize(List([1,2,3],5,0)) |
| --- |
| [1, 2, 3, 0, 0] |

## array\_reverse

Returns the array with the order of the elements reversed.

```sql
array_reverse(array)
```

### Arguments

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

#### Aliases

-   `list_reverse`

[](#view-array_reverse-example)

View `array_reverse` example

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

| array_reverse |
| --- |
| [4, 3, 2, 1] |

## array\_slice

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

```sql
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`

[](#view-array_slice-example)

View `array_slice` example

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

| array_slice |
| --- |
| [3, 4, 5, 6] |

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

```sql
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`

[](#view-array_sort-example-with-numeric-elements)

View `array_sort` example with numeric elements

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

| array_sort |
| --- |
| [1, 2, 3] |

[](#view-array_sort-example-with-string-elements)

View `array_sort` example with string elements

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

| array_sort |
| --- |
| [cherry, banana, apple] |

[](#view-array_sort-example-with-_null_-elements)

View `array_sort` example with *NULL* elements

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

| array_sort |
| --- |
| [apple, banana, cherry, , ] |

## array\_to\_string

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

```sql
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`

[](#view-array_to_string-example)

View `array_to_string` example

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

| array_to_string |
| --- |
| 1,2,3,4,5,6,7,8 |

[](#view-array_to_string-example-with-_null_-replacements)

View `array_to_string` example with *NULL* replacements

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

| array_to_string |
| --- |
| 1-2-3-4-5-?-7-8-? |

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

```sql
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`

[](#view-array_union-example)

View `array_union` example

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

| array_union |
| --- |
| [1, 2, 3, 4, 5, 6] |

## arrays\_overlap

*Alias of [array\_has\_any](#array_has_any).*

## cardinality

Returns the total number of elements in the array.

```sql
cardinality(array)
```

### Arguments

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

[](#view-cardinality-example)

View `cardinality` example

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

| cardinality |
| --- |
| 8 |

## empty

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

```sql
empty(array)
```

### Arguments

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

#### Aliases

-   `array_empty`
-   `list_empty`

[](#view-empty-example)

View `empty` example

```sql
SELECT empty(['apple']) AS empty
```

| empty |
| --- |
| false |

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

```sql
flatten(array)
```

### Arguments

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

[](#view-flatten-example)

View `flatten` example

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

| flattened |
| --- |
| [1, 2, 3, 4] |

## 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](#range), but includes the upper bound (**stop**) in the output array.*

```sql
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](#range)

[](#view-generate_series-example)

View `generate_series` example

```sql
SELECT generate_series(1,5) AS generate_series
```

| generate_series |
| --- |
| [1, 2, 3, 4, 5] |

[](#view-range-example-with-dates)

View `range` example with dates

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

| generate_series |
| --- |
| [2025-03-01, 2025-04-01, 2025-05-01, 2025-06-01, 2025-07-01, 2025-08-01] |

[](#view-generate_series-example-using-timestamps)

View `generate_series` example using timestamps

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

| generate_series |
| --- |
| [2025-01-01T00:00:00, 2025-01-01T02:00:00, 2025-01-01T04:00:00, 2025-01-01T06:00:00] |

## list\_any\_value

*Alias of [array\_any\_value](#array_any_value).*

## list\_append

*Alias of [array\_append](#array_append).*

## list\_cat

*Alias of [array\_concat](#array_concat).*

## list\_concat

*Alias of [array\_concat](#array_concat).*

## list\_contains

*Alias of [array\_has](#array_has).*

## list\_dims

*Alias of [array\_dims](#array_dims).*

## list\_distance

*Alias of [array\_distance](#array_distance).*

## list\_distinct

*Alias of [array\_distinct](#array_distinct).*

## list\_element

*Alias of [array\_element](#array_element).*

## list\_empty

*Alias of [empty](#empty).*

## list\_except

*Alias of [array\_except](#array_except).*

## list\_extract

*Alias of [array\_element](#array_element).*

## list\_has

*Alias of [array\_has](#array_has).*

## list\_has\_all

*Alias of [array\_has\_all](#array_has_all).*

## list\_has\_any

*Alias of [array\_has\_any](#array_has_any).*

## list\_indexof

*Alias of [array\_position](#array_position).*

## list\_intersect

*Alias of [array\_intersect](#array_intersect).*

## list\_join

*Alias of [array\_to\_string](#array_to_string).*

## list\_length

*Alias of [array\_length](#array_length).*

## list\_max

*Alias of [array\_max](#array_max).*

## list\_ndims

*Alias of [array\_ndims](#array_ndims).*

## list\_pop\_back

*Alias of [array\_pop\_back](#array_pop_back).*

## list\_pop\_front

*Alias of [array\_pop\_front](#array_pop_front).*

## list\_position

*Alias of [array\_position](#array_position).*

## list\_positions

*Alias of [array\_positions](#array_positions).*

## list\_prepend

*Alias of [array\_prepend](#array_prepend).*

## list\_push\_back

*Alias of [array\_append](#array_append).*

## list\_push\_front

*Alias of [array\_prepend](#array_prepend).*

## list\_remove

*Alias of [array\_remove](#array_remove).*

## list\_remove\_all

*Alias of [array\_remove\_all](#array_remove_all).*

## list\_remove\_n

*Alias of [array\_remove\_n](#array_remove_n).*

## list\_repeat

*Alias of [array\_repeat](#array_repeat).*

## list\_replace

*Alias of [array\_replace](#array_replace).*

## list\_replace\_all

*Alias of [array\_replace\_all](#array_replace_all).*

## list\_replace\_n

*Alias of [array\_replace\_n](#array_replace_n).*

## list\_resize

*Alias of [array\_resize](#array_resize).*

## list\_reverse

*Alias of [array\_reverse](#array_reverse).*

## list\_slice

*Alias of [array\_slice](#array_slice).*

## list\_sort

*Alias of [array\_sort](#array_sort).*

## list\_to\_string

*Alias of [array\_to\_string](#array_to_string).*

## list\_union

*Alias of [array\_union](#array_union).*

## make\_array

Returns an array using the specified input expressions.

```sql
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`

[](#view-make_array-example)

View `make_array` example

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

| make_array |
| --- |
| [1, 2, 3, 4, 5] |

## make\_list

*Alias of [make\_array](#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](#generate_series), but does not include the upper bound (**stop**) in the output array.*

```sql
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](#generate_series)

[](#view-range-example)

View `range` example

```sql
SELECT range(1, 5, 1) AS range
```

| range |
| --- |
| [1, 2, 3, 4] |

[](#view-range-example-with-dates)

View `range` example with dates

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

| range |
| --- |
| [2025-03-01, 2025-04-01, 2025-05-01, 2025-06-01, 2025-07-01] |

[](#view-range-example-with-timestamps)

View range example with timestamps

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

| range |
| --- |
| [2025-01-01T00:00:00, 2025-01-01T02:00:00, 2025-01-01T04:00:00] |

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

```sql
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`

[](#view-string_to_array-example-with-comma-delimited-list)

View `string_to_array` example with comma-delimited list

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

| string_to_array |
| --- |
| [abc, def, ghi] |

[](#view-string_to_array-example-with-a-non-standard-delimiter)

View `string_to_array` example with a non-standard delimiter

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

| string_to_array |
| --- |
| [‘abc’, ‘def’] |

[](#view-string_to_array-example-with-_null_-replacements)

View `string_to_array` example with *NULL* replacements

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

| string_to_array |
| --- |
| [‘abc’, NULL] |

## string\_to\_list

*Alias of [string\_to\_array](#string_to_array).*