> ## Documentation Index
> Fetch the complete documentation index at: https://docs.risingwave.com/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_flatten`

<Note>
  Added in v2.5.0.
</Note>

Flattens an array of arrays into a single array. If the input array contains multiple levels of nested arrays, the function will only flatten the first level.

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

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

#### Best practices

When using the `unnest` function, consider the following performance-optimized patterns:

**For aggregation:** If you want to unnest an array and then aggregate the values back to a scalar, consider using functions like `array_sum` directly against the array. This approach is preferred over using `unnest` and then `sum` to aggregate. Pure functions like `array_sum` have much better performance than relational level aggregation, which introduces additional states.

**For unnesting:** If you want to unnest the array only, use `unnest` in the SELECT clause directly instead of in a correlated subquery. Correlated subqueries introduce additional states, which impacts performance. When you need to add ordinality to each unnested row, you can use `unnest` together with `generate_series`.

Given this example table:

```sql theme={null}
CREATE TABLE example (v1 int, v2 int[]);
INSERT INTO example VALUES (1, array[5,6,7]);
```

**For aggregation:**

<Tabs>
  <Tab title="✅ Recommended">
    Use array aggregate functions directly:

    ```sql theme={null}
    SELECT v1, array_sum(v2) AS sum FROM example;
    ```
  </Tab>

  <Tab title="❌ Not recommended">
    Avoid using unnest in correlated subqueries for aggregation:

    ```sql theme={null}
    SELECT v1, (SELECT sum(item) FROM unnest(example.v2) AS item) AS sum FROM example;
    ```
  </Tab>
</Tabs>

**For array elements with ordinality:**

<Tabs>
  <Tab title="✅ Recommended">
    Use `generate_series` with `unnest`:

    ```sql theme={null}
    SELECT v1, unnest(v2) AS item, generate_series(1, array_length(v2), 1) AS ord FROM example;
    ```
  </Tab>

  <Tab title="❌ Not recommended">
    Avoid using `WITH ORDINALITY`:

    ```sql theme={null}
    SELECT v1, item, ord FROM example, unnest(v2) WITH ORDINALITY AS foo(item, ord);
    ```
  </Tab>
</Tabs>

Note: If you're using `unnest` for row-level processing, followed by an `array_agg` aggregation, it can incur additional overhead due to subplan execution. Use `EXPLAIN` to examine your query plan, and see if there are unexpected JOINs in your query. If so, please consider rewriting with `array_transform` to perform your mapping logic to avoid the subplan execution.

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