> ## Documentation Index
> Fetch the complete documentation index at: https://risingwavelabs-wyx-add-timestamp-dedicated-page.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# Array functions and operators

## Array functions

### `array_append`

Appends `any_compatible` to the end of the input array.

```sql theme={null}
array_append ( array, any_compatible ) → array
```

```sql theme={null}
array_append(array[66], 123) → {66, 123}
```

### `array_cat`

Concatenates two arrays with the same data type.

If the one of the input arrays is a 2-dimensional array, the other array will be appended within the first array as the last element if it is the second argument. The other array will be prepended within the first array as the first element if it is the first argument.

```sql theme={null}
array_cat ( array, array ) → array
```

```sql theme={null}
array_cat(array[66], array[123]) → {66, 123}

array_cat(array[array[66]], array[233]) → {{66}, {233}}

array_cat(array[233], array[array[66]]) → {{233}, {66}}
```

### `array_dims`

Returns the dimensions of an array as a string. The array must be one dimensional.

```sql theme={null}
array_dims ( array ) → string
```

```sql theme={null}
array_dims(array[2,3,4]) → [1:3]
```

### `array_distinct`[](#array%5Fdistinct "Direct link to array_distinct")

Returns an array of the same type as the input array with all duplicate values removed.

```sql theme={null}
array_distinct ( array ) → array
```

```sql theme={null}
array_distinct(array[1,2,1,1]) → {1,2}
```

### `array_length`[](#array%5Flength "Direct link to array_length")

This function has two variants.

#### `array_length ( array )`[](#array%5Flength--array- "Direct link to array_length--array-")

Returns the length of *array*.

```sql theme={null}
array_length ( array ) → int
```

```sql theme={null}
array_length(array[1,2,3,4,1]) → 5
```

#### `array_length ( array int )`[](#array%5Flength--array-int- "Direct link to array_length--array-int-")

Returns the length of the requested array dimension in `array`. `int` must be 1.

```sql theme={null}
array_length ( array, int ) → int
```

```sql theme={null}
array_length(array[2, 3, 4], 1) → 3
```

### `array_lower`[](#array%5Flower "Direct link to array_lower")

Returns the lower bound of the requested array dimension in `array`. (This is always 1 or null.)

```sql theme={null}
array_lower ( array, int ) → int
```

```sql theme={null}
array_lower(array[2, 3, 4], 1) → 1
```

### `array_max`[](#array%5Fmax "Direct link to array_max")

Returns the maximum value in an array.

Null elements are skipped, but if the array contains only null elements, NULL is returned.

```sql theme={null}
array_max ( array ) → type of the elements
```

```sql theme={null}
array_max(array[3.14, 1.14, 1.14514]) → 3.14

array_max(array[date'2002-10-30', date'2023-09-06', date'2017-06-18']) → 2023-09-06

array_max(array['','']) → empty

array_max(array['a', 'b', NULL, 'c']) → c

array_max(array[NULL]) → NULL
```

### `array_min`[](#array%5Fmin "Direct link to array_min")

Returns the minimum value in an array.

Null elements are skipped, but if the array contains only null elements, NULL is returned.

```sql theme={null}
array_min ( array ) → type of the elements
```

```sql theme={null}
array_min(array['a', 'b', 'c']) → a

array_min(array[date'2002-10-30', date'2023-09-06', date'2017-06-18']) → 2002-10-30

array_min(array['','']) → empty

array_min(array['a', 'b', NULL, 'c']) → a

array_min(array[NULL]) → NULL
```

### `array_ndims`[](#array%5Fndims "Direct link to array_ndims")

Returns the number of dimensions of `array`.

```sql theme={null}
array_ndims ( array ) → int
```

```sql theme={null}
array_ndims(array[array[2, 3], array[4, 5]]) → 2
```

### `array_position`[](#array%5Fposition "Direct link to array_position")

Returns the subscript of the first occurrence of `any_compatible` element in `array`.

```sql theme={null}
array_position ( array, any_compatible ) → int
```

```sql theme={null}
array_position(array[1,2,3,4,5,6,1,2,3,4,5,6], 4) → 4
```

### `array_positions`[](#array%5Fpositions "Direct link to array_positions")

Returns an array of the subscripts of all occurrences of `any_compatible` element in `array`.

```sql theme={null}
array_positions ( array, any_compatible ) → array
```

```sql theme={null}
array_positions(array[1,2,3,4,5,6,1,2,3,4,5,6], 4) → {4, 10}
```

### `array_prepend`[](#array%5Fprepend "Direct link to array_prepend")

Prepends `any_compatible` to the beginning of the input array.

```sql theme={null}
array_prepend ( any_compatible, array ) → array
```

```sql theme={null}
array_prepend(123, array[66]) → {123, 66}
```

***

### `array_remove`[](#array%5Fremove "Direct link to array_remove")

Returns an array with all occurrences of `any_compatible` element removed. Multidimensional arrays are also supported.

```sql theme={null}
array_remove ( array, any_compatible ) → array
```

```sql theme={null}
array_remove(array[array[1],array[2],array[3],array[2]], array[2]) → {{1},{3}}
```

### `array_replace`[](#array%5Freplace "Direct link to array_replace")

Returns an array with all occurrences of `current_element` replaced with `new_element`. Multidimensional arrays are also supported. When the array is multidimensional, the element must be an array of one less dimension. Recursively replacing the base element of a multidimensional array is not supported.

```sql theme={null}
array_replace ( array, current_element, new_element ) → array
```

```sql theme={null}
array_replace(array[7, null, 8, null], null, 0.5) → {7,0.5,8,0.5}
```

### `array_sort`[](#array%5Fsort "Direct link to array_sort")

Sorts the elements of an array in ascending order.

```sql theme={null}
array_sort ( array ) → array
```

```sql theme={null}
array_sort(array[-1000, 2000, 0]) → {-1000,0,2000}

array_sort(array['abcdef', 'aacedf', 'aaadef']) → {aaadef,aacedf,abcdef}

array_sort(array[3, 2, NULL, 1, NULL]) → {1,2,3,NULL,NULL}
```

### `array_sum`[](#array%5Fsum "Direct link to array_sum")

Returns the sum of the values in the array. Null elements are skipped.

```sql theme={null}
array_sum ( array ) → type of the elements
```

```sql theme={null}
array_sum(array[3, 2, NULL]) → 5

array_sum(array[-10, 20, -30]) → -20

array_sum(array[interval'4 hour', interval'3 day']) → '3 days 04:00:00'
```

### `array_to_string` and `array_join`[](#array%5Fto%5Fstring-and-array%5Fjoin "Direct link to array_to_string-and-array_join")

Converts an array to a string. The optional `delimiter_string` separates the array's elements in the resulting string, and the optional `null_string` represents `NULL` elements in the array. `array_join` can also be used instead of `array_to_string`.

```sql theme={null}
array_to_string ( array, delimiter_string, null_string ) → string

array_join(array, delimiter_string, null_string) → string
```

```sql theme={null}
array_to_string (array[1, 2, 3, NULL, 5], ',', '*') → 1,2,3,*,5

array_join(array[1, 2, 3, NULL, 5], ',', '*') → 1,2,3,*,5
```

### `array_transform`[](#array%5Ftransform "Direct link to array_transform")

This function takes an array, transforms the elements, and returns the results in a new array. The output array always has the same length as the input array.

```sql theme={null}
array_transform ( array_expression, lambda_expression )

lambda_expression:
| element_alias | transform_expression
```

Each element in `array_expression` is evaluated against the `transform_expression`. `element_alias` is an alias that represents an array element.

```sql Example A theme={null}
SELECT array_transform('{1,2,3}'::int[], |x| (x::double precision+0.5));
------RESULT
{1.5,2.5,3.5}
```

```sql Example B theme={null}
SELECT array_transform(
    ARRAY['Apple', 'Airbnb', 'Amazon', 'Facebook', 'Google', 'Microsoft', 'Netflix', 'Uber'],
    |x| case when x ilike 'A%' then 'A' else 'Other' end
);
------RESULT
{A,A,A,Other,Other,Other,Other,Other}
```

Note that the `transform_expression` does not support referencing columns. For example, if you have a table:

```sql theme={null}
CREATE TABLE t(v int, arr int[]);
```

The following query will fail.

```sql theme={null}
SELECT array_transform(arr, |x| x + v) FROM t;
```

***

### `array_upper`[](#array%5Fupper "Direct link to array_upper")

Returns the upper bound of the requested array dimension in `array`. `int` must be `1`. (This will return the same value as `array_length`.)

```sql theme={null}
array_upper ( array, int ) → int
```

```sql theme={null}
array_upper(array[array[2, 3, 4], array[3, 4, 5]], 1) → 2
```

### `cardinality`[](#cardinality "Direct link to cardinality")

Returns the total number of elements in `array` or 0 if the array is empty.

```sql theme={null}
cardinality ( array ) → int
```

```sql theme={null}
cardinality(array[array[array[3,4,5],array[2,2,2]],array[array[6,7,8],array[0,0,0]]]) → 12
```

### `string_to_array`

Converts a string to an array. The optional `delimiter_string` separates the `string`'s elements to create the resulting array, and the optional `null_string` represents `NULL` elements in the array.

```sql theme={null}
string_to_array ( string, delimiter_string, null_string ) → array
```

```sql theme={null}
string_to_array('a b c', ' ', 'a') → {NULL,b,c}
```

### `trim_array`[](#trim%5Farray "Direct link to trim_array")

Trims an array by removing the last n elements. If the array is multidimensional, only the first dimension is trimmed.

```sql theme={null}
trim_array ( array, num_of_elements_to_trim ) → array
```

```sql theme={null}
trim_array(array[1,2,3,4,5,NULL], 4) → {1,2}
```

### `unnest`[](#unnest "Direct link to unnest")

Expands an array, or combination of arrays, into a set of rows. The array's elements are output in the order they are stored.

```sql theme={null}
unnest ( array ) → set_of_any_element
```

```sql theme={null}
unnest(Array[Array[1,3,4,5],Array[2,3]]) →
1
3
4
5
2
3
```

## Array operators[](#array-operators "Direct link to Array operators")

### `array @> array -> boolean`[](#array--array---boolean "Direct link to array--array---boolean")

This operator checks if the left array contains all elements of the right array.

```sql theme={null}
array[1,2,3] @> array[2,3] → t
```

### `array <@ array -> boolean`[](#array--array---boolean-1 "Direct link to array--array---boolean-1")

This operator checks if the left array is contained by the right array.

```sql theme={null}
array[2,3] <@ array[1,2,3] → t
```

### `array || anycompatible → array`[](#array--anycompatible--array "Direct link to array--anycompatible--array")

Appends *any\_compatible* to the end of *array*. This operation achieves the same result as using `array_append`.

```sql theme={null}
array[66] || 123 → {66, 123}
```

### `array || array → array`

Concatenates two arrays with the same data type. This operation achieves the same result as using `array_cat`.

```sql theme={null}
array[66] || array[123] → {66, 123}
```

### `anycompatible || array → array`

Prepends *any\_compatible* to the beginning of *array*. This operation achieves the same result as using `array_prepend`.

```sql theme={null}
123 || array[66] → {123, 66}
```