---
title: Array Functions
description: You can use array functions to evaluate arrays, perform
  computations on elements in an array, and to return a new array based on a
  transformation.
pubDate: 2026-08-17T09:53:44.266Z
antora:
  editUrl: https://github.com/couchbaselabs/docs-devex/edit/release/8.0/modules/n1ql/pages/n1ql-language-reference/arrayfun.adoc
  xref: xref:server:n1ql:n1ql-language-reference/arrayfun.adoc[]
---

[Consult the llms.txt file for a full list of contents](/llms.txt)
[View original HTML](/server/current/n1ql/n1ql-language-reference/arrayfun.html)

# Array Functions

> You can use array functions to evaluate arrays, perform computations on elements in an array, and to return a new array based on a transformation. 

## [](#fn-array-agg)ARRAY\_AGG(`expr`)

### [](#description)Description

This function returns an array of the non-`MISSING` group values in the input `expr`, including `NULL` values.

### [](#arguments)Arguments

expr

\[Required\] The group of elements you wish to output in an array.

### [](#return-values)Return Values

An array of non-MISSING values.

If the input `expression` is `MISSING` or if one of the elements in the array is `MISSING`, then it returns `MISSING`.

### [](#example)Example

Group a list of three items into an array.

Query

```sqlpp
SELECT ARRAY_AGG( [ "abc", 1, NULL ] ) AS array_aggregate;
```

Results

```json
[
  {
    "array_aggregate": [
      [
        "abc",
        1,
        null
      ]
    ]
  }
]
```

## [](#fn-array-append)ARRAY\_APPEND(`expr`, `val1`, `val2`, …)

### [](#description-2)Description

This function takes an array `expr` and one or more `val` arguments to return a new array with the specified `val` argument(s) appended.

It requires a minimum of two arguments and returns an error if there are fewer.

### [](#arguments-2)Arguments

expr

\[Required\] The array to be appended to.

val1, val2, …

\[At least 1 is required\] The values to be appended.

### [](#return-values-2)Return Values

A new array with the specified `val` argument(s) appended.

If either of the input argument types are `MISSING`, then it returns `MISSING`.

If either of the input argument types are `NULL`, then it returns `NULL`.

If the `expr` argument is not an array, then it returns `NULL`.

If the `expr` is in the `WHERE` clause of a partial index, this function lists the expressions that are implicitly covered.

### [](#example-2)Example

Append two numbers to the end of a given array.

Query

```sqlpp
SELECT ARRAY_APPEND( [ 1 , 2 ] , 3 , 4) AS numbers;
```

Results

```json
[
  {
    "numbers": [
      1,
      2,
      3,
      4
    ]
  }
]
```

## [](#fn-array-avg)ARRAY\_AVG(`expr`)

### [](#description-3)Description

This function takes an array `expr` as an argument and returns the arithmetic mean (average) of all the non-`NULL` number values in the array, or `NULL` if there are no such values.

### [](#arguments-3)Arguments

expr

\[Required\] The array of numbers to be evaluated.

### [](#return-values-3)Return Values

A number representing the arithmetic mean (average) of all the non-`NULL` number values in the array `expression`.

If there are no number values in array `expr`, then it returns `NULL`.

If the input `expr` is `MISSING`, then it returns `MISSING`.

If the array size of `expr` is 0 (no elements), then it returns `NULL`.

Any non-number elements in the array `expr` are ignored.

### [](#example-3)Example

Find the average from an array of numbers.

Query

```sqlpp
SELECT ARRAY_AVG( [ 0 , 1 , 1 , 2 , 3 , 5 ] ) AS array_average;
```

Results

```json
[
  {
    "array_average": 2
  }
]
```

## [](#fn-array-binary-search)ARRAY\_BINARY\_SEARCH(`expr`, `val`, …)

### [](#description-4)Description

This function returns the first position of the specified value `val` within the sorted array `expr`.

The array position is zero-based, that is, the first position is 0.

> [!NOTE]
> This function uses a binary search algorithm. If the array is unsorted, the function may not be able to find the value.

See also [ARRAY\_POSITION()](#fn-array-position), [ARRAY\_SORT()](#fn-array-sort).

### [](#arguments-4)Arguments

expr

\[Required\] The array you want to search, sorted in SQL++ collation order.

val

\[Required\] The value whose position you want to find.

### [](#return-values-4)Return Values

An integer representing the first position of the input `val`, where the first position is 0\. If the value `val` occurs more than once within the array `expr`, only the first position is returned.

It returns -1 if the input `val` is not found in the array.

If one of the arguments is `MISSING`, it returns `MISSING`.

If the input `expr` is not an array, it returns `NULL`.

### [](#example-4)Example

Find the position of a number in a sorted array.

Query

```sqlpp
SELECT ARRAY_BINARY_SEARCH( [ 1 , 3 , 5 , 7 , 9 ] , 5)
AS position;
```

Results

```json
[
  {
    "position": 2
  }
]
```

## [](#fn-array-concat)ARRAY\_CONCAT(`expr1`, `expr2`, …)

### [](#description-5)Description

This function takes two or more `expr` arrays and returns a new array after concatenating the input arrays.

If there are fewer than two arguments, then it returns an error.

### [](#arguments-5)Arguments

expression1, expression2, …

\[At least 2 are required\] The arrays to be concatenated together.

### [](#return-values-5)Return Values

A new array, concatenated from the input arrays.

If any of the input `expr` arguments or one of the array elements are `MISSING`, then it returns `MISSING`.

If any of the input `expr` arguments is `NULL`, then it returns `NULL`.

If any of the input `expr` arguments is not an array, then it returns `NULL`.

### [](#example-5)Example

Combine two arrays into a single array.

Query

```sqlpp
SELECT ARRAY_CONCAT( [ 1 , 2 , 3 ], [ 4 , 5 , 6 ] )
AS combined_array;
```

Results

```json
[
  {
    "combined_array": [
      1,
      2,
      3,
      4,
      5,
      6
    ]
  }
]
```

## [](#fn-array-contains)ARRAY\_CONTAINS(`expr`, `val`)

### [](#description-6)Description

This functions checks if the array `expression` contains the specified `value`.

### [](#arguments-6)Arguments

expr

\[Required\] The array to be searched.

val

\[Required\] The value that is being searched for.

### [](#return-values-6)Return Values

If either of the input argument types are `MISSING`, then it returns `MISSING`.

If either of the input argument types are `NULL`, then it returns `NULL`.

If the `expr` argument is not an array, then it returns `NULL`.

If the array `expr` contains `val`, then it returns `TRUE`; otherwise, it returns `FALSE`.

### [](#example-6)Example

Check if the value `3` exists in a given array of numbers.

Query

```sqlpp
SELECT ARRAY_CONTAINS( [ 1 , 2 , 3 , 4 , 5 ] , 3 )
AS contains_value;
```

Results

```json
[
  {
    "contains_value": true
  }
]
```

## [](#fn-array-count)ARRAY\_COUNT(`expr`)

### [](#description-7)Description

This function counts all the non-NULL values in the input `expr` array.

### [](#arguments-7)Arguments

expr

\[Required\] The array to be searched and evaluate its values.

### [](#return-values-7)Return Values

A count of all the non-`NULL` values in the array, or zero if there are no such values.

If the `expr` argument is `MISSING`, then it returns `MISSING`.

If the `expr` argument is `NULL`, then it returns `NULL`.

If the `expr` argument is not an array, then it returns `NULL`.

### [](#example-7)Example

Count the number of elements in a given array.

Query

```sqlpp
SELECT ARRAY_COUNT( [ 1 , 2 , 3 , 4 , 5 ] )
AS total_count;
```

Results

```json
[
  {
    "total_count": 5
  }
]
```

## [](#fn-array-distinct)ARRAY\_DISTINCT(`expr`)

### [](#description-8)Description

This function returns a new array with distinct elements of the input array `expr`.

### [](#arguments-8)Arguments

expr

\[Required\] The array of items to be evaluated.

### [](#return-values-8)Return Values

An array with distinct elements of the input array `expr`.

If the input `expr` is `MISSING`, it returns `MISSING`.

If the input `expr` is a non-array value, it returns `NULL`.

### [](#example-8)Example

Remove duplicate elements from a given array.

Query

```sqlpp
SELECT ARRAY_DISTINCT( [ 1 , 2 , 2 , 3 , 4 , 4 , 5 ] )
AS distinct_numbers;
```

Results

```json
[
  {
    "distinct_numbers": [
      1,
      2,
      3,
      4,
      5
    ]
  }
]
```

## [](#fn-array-except)ARRAY\_EXCEPT(`expr1`, `expr2`)

### [](#description-9)Description

This function returns all the elements of the first array, except for those which are also included in the second array.

### [](#arguments-9)Arguments

expr1

\[Required\] The input array, from which specified elements may be excluded.

expr2

\[Required\] The array of elements to be excluded.

### [](#return-value)Return Value

An array of all the elements in `expr1`, except for those which also occur in `expr2`.

If any of the arguments is `MISSING`, it returns `MISSING`.

If any of the arguments is a non-array, it returns `NULL`.

### [](#examples)Examples

Exclude elements of one array from another.

Query

```sqlpp
SELECT ARRAY_EXCEPT( [ 1 , 2 , 3 , 4 , 5 ] , [ 2 , 4 ] )
AS result_array;
```

Results

```json
[
  {
    "result_array": [
      1,
      3,
      5
    ]
  }
]
```

## [](#fn-array-flatten)ARRAY\_FLATTEN(`expr`, `depth`)

### [](#description-10)Description

This function flattens nested array elements into the top-level array, up to the specified depth.

### [](#arguments-10)Arguments

expr

\[Required\] The multilevel array to be flattened.

depth

\[Required\] The Integer representing the number of depths to flatten.

### [](#return-value-2)Return Value

An array with `depth` fewer levels than the input array `expr`.

If one of the arguments is `MISSING`, it returns `MISSING`.

If the input `expr` is a non-array, or if the input `depth` argument is not an integer, it returns `NULL`.

### [](#examples-2)Examples

Flatten a nested array by 1 and 2 levels.

Query

```sqlpp
SELECT
  ARRAY_FLATTEN(
    [ [ 1, 2 ], [ 3, [ 4, 5 ] ] ], 1
  ) AS flatten_by_1,
  ARRAY_FLATTEN(
    [ [ 1, 2 ], [ 3, [ 4, 5 ] ] ], 2
  ) AS flatten_by_2;
```

Results

```json
[
  {
    "flatten_by_1": [
      1,
      2,
      3,
      [
        4,
        5
      ]
    ],
    "flatten_by_2": [
      1,
      2,
      3,
      4,
      5
    ]
  }
]
```

## [](#fn-array-ifnull)ARRAY\_IFNULL(`expr`)

### [](#description-11)Description

This function parses the input array `expr` and returns the first non-`NULL` value in the array.

### [](#arguments-11)Arguments

expr

\[Required\] The array of values to be evaluated.

### [](#return-values-9)Return Values

The first non-NULL value in the input array.

If the input `expr` is MISSING, then it returns `MISSING`.

If the input `expr` is a non-array, then it returns `NULL`.

### [](#examples-3)Examples

Find the first non-NULL value in a given array.

Query

```sqlpp
SELECT ARRAY_IFNULL( [ NULL , NULL , 1 , 2 , 3 ] )
AS first_non_null;
```

Results

```json
[
  {
    "first_non_null": 1
  }
]
```

## [](#fn-array-insert)ARRAY\_INSERT(`expr`, `pos`, `val1`, `val2`, …)

### [](#description-12)Description

This function inserts the specified `value` or multiple `value` items into the specified `position` in the input array `expression`, and returns the new array.

### [](#arguments-12)Arguments

expr

\[Required\] The array to insert items into.

pos

\[Required\] The integer specifying the array position from the left of the input array `expr`, where the 1st position is 0 (zero).

val1, val2, …

\[At least one is required\] The value or multiple value items to insert into the input array expression.

### [](#return-values-10)Return Values

An array with the input value or multiple value items inserted into the input array expression at position `pos`.

If any of the three arguments are `MISSING`, then it returns `MISSING`.

If the `expr` argument is a non-array or if the `position` argument is not an integer, then it returns `NULL`.

### [](#example-9)Example

Insert `55` into the 2nd position of an array.

Query

```sqlpp
SELECT ARRAY_INSERT( [ 1 , 2 , 3 , 4 ] , 2 , 55 )
AS updated_array;
```

Results

```json
[
  {
    "updated_array": [
      1,
      2,
      55,
      3,
      4
    ]
  }
]
```

## [](#fn-array-intersect)ARRAY\_INTERSECT(`expr1`, `expr2`, ...)

### [](#description-13)Description

This function takes two or more arrays and returns the intersection of the input arrays as the result; that is, the array containing values that are present in all of the input arrays.

### [](#arguments-13)Arguments

expr1, expr2, …

\[At least 2 are required\] The two or more arrays to compare the values of.

### [](#return-values-11)Return Values

An array containing the values that are present in all of the input arrays.

If there are no common elements, then it returns an empty array.

If any of the input arguments are `MISSING`, then it returns `MISSING`.

If any of the input arguments are non-array values, then it returns `NULL`.

### [](#examples-4)Examples

Compare three arrays to find common elements.

Query

```sqlpp
SELECT ARRAY_INTERSECT(
  [ 1 , 2 , 3 , 4 ] ,
  [ 2 , 4 , 6 ] ,
  [ 4 , 8 ] )
AS array_intersection;
```

Results

```json
[
  {
    "array_intersection": [
      4
    ]
  }
]
```

Compare three arrays with no common elements.

Query

```sqlpp
SELECT ARRAY_INTERSECT(
  [ 1 , 2 , 3 ] ,
  [ 3 , 4 , 5 , 6 ] ,
  [ 6 , 7 , 8 , 9 ] )
AS array_intersection;
```

Results

```json
[
  {
    "array_intersection": []
  }
]
```

## [](#fn-array-length)ARRAY\_LENGTH(`expr`)

_Equivalent_: [LEN()](metafun.md#len)

### [](#description-14)Description

This function returns the number of elements in the input array.

### [](#arguments-14)Arguments

expr

\[Required\] The array whose elements you want to know the number of.

### [](#return-values-12)Return Values

An integer representing the number of elements in the input array.

If the input argument is MISSING, then it returns `MISSING`.

If the input argument is a non-array value, then it returns `NULL`.

### [](#example-10)Example

Find the number of elements in a given array.

Query

```sqlpp
SELECT ARRAY_LENGTH( [ 1 , 2 , 3 , 4 , 5 ] )
AS total_elements;
```

Results

```json
[
  {
    "total_elements": 5
  }
]
```

## [](#fn-array-max)ARRAY\_MAX(`expr`)

### [](#description-15)Description

This function returns the largest non-`NULL`, non-`MISSING` array element, in SQL++ collation order.

### [](#arguments-15)Arguments

expr

\[Required\] The array whose elements you want to know the highest value of.

### [](#return-values-13)Return Values

The largest non-`NULL`, non-`MISSING` array element, in SQL++ collation order.

If the input `expr` is `MISSING`, then it returns `MISSING`.

If the input `expr` is a non-array value, then it returns `NULL`.

### [](#example-11)Example

Find the largest value in a given array.

Query

```sqlpp
SELECT
  ARRAY_MAX( [ 1 , 3 , 9 , 5 ] ) AS max_num,
  ARRAY_MAX( [ "delta" , "united" , "southwest" ] ) max_string;
```

Results

```json
[
  {
    "max_num": 9,
    "max_string": "united"
  }
]
```

## [](#fn-array-min)ARRAY\_MIN(`expr`)

### [](#description-16)Description

This function returns the smallest non-`NULL`, non-`MISSING` array element, in SQL++ collation order.

### [](#arguments-16)Arguments

expr

\[Required\] The array whose elements you want to know the lowest value of.

### [](#return-values-14)Return Values

The smallest non-`NULL`, non-`MISSING` array element, in SQL++ collation order.

If the input `expr` is `MISSING`, then it returns `MISSING`.

If the input `expr` is a non-array value, then it returns `NULL`.

### [](#example-12)Example

Find the smallest value in a given array.

Query

```sqlpp
SELECT
  ARRAY_MIN( [ 1 , 3 , 9 , 5 ] ) AS min_num,
  ARRAY_MIN( [ "delta" , "united" , "southwest" ] ) min_string;
```

Results

```json
[
  {
    "min_num": 1,
    "min_string": "delta"
  }
]
```

## [](#fn-array-move)ARRAY\_MOVE(`expr`, `val1`, `val2`)

### [](#description-17)Description

This function returns a new array containing all the elements of `expr`, with one element moved to a new position.

### [](#arguments-17)Arguments

expr

\[Required\] The input array containing an element that you want to move.

val1

\[Required\] An integer specifying the old location of the element to move.

val2

\[Required\] An integer specifying the new location of the element to move.

If `val1` or `val2` are 0 or greater, the position is counted from the left of the input array, where the leftmost position in the array is 0 (zero). If `val1` or `val2` are less than 0, the position is counted from the right of the input array, where the rightmost position in the array is -1.

### [](#return-values-15)Return Values

An array with the element at the position specified by `val1` moved to a new position specified by `val2`.

If either of the `val` arguments is outside the array, the function returns `NULL`.

If any of the arguments is MISSING, then it returns `MISSING`.

If the `expr` argument is a non-array, or if either of the `val` arguments is not an integer, then it returns `NULL`.

### [](#examples-5)Examples

Move the 1st element in a given array to the 3rd position in the array.

Query

```sqlpp
SELECT ARRAY_MOVE(["a", "b", "c", "d", "e"], 0, 3)
AS updated_array;
```

Results

```json
[
  {
    "updated_array": [
      "b",
      "c",
      "d",
      "a",
      "e"
    ]
  }
]
```

Move the 1st element in a given array to the penultimate position in the array.

Query

```sqlpp
SELECT ARRAY_MOVE( [ "a" , "b" , "c" , "d" , "e" ] , 0 , -2)
AS penultimate;
```

Results

```json
[
  {
    "penultimate": [
      "b",
      "c",
      "d",
      "a",
      "e"
    ]
  }
]
```

## [](#fn-array-position)ARRAY\_POSITION(`expr`, `val`)

### [](#description-18)Description

This function returns the first position of the specified `value` within the array `expression`.

The array position is zero-based, that is, the first position is 0.

See also [ARRAY\_BINARY\_SEARCH()](#fn-array-binary-search).

### [](#arguments-18)Arguments

expr

\[Required\] The array you want to search.

val

\[Required\] The value whose position you want to know.

### [](#return-values-16)Return Values

An integer representing the first position of the input `val`, where the first position is 0\. If the value `val` occurs more than once within the array `expr`, only the first position is returned.

It returns -1 if the input `val` does not exist in the array.

If one of the arguments is `MISSING`, it returns `MISSING`.

If either of the arguments are non-array values, it returns `NULL`.

### [](#example-13)Example

Find the position of the value `30` in a given array.

Query

```sqlpp
SELECT ARRAY_POSITION( [ 10 , 20 , 30 , 40 , 50 ] , 30 )
AS position;
```

Results

```json
[
  {
    "position": 2
  }
]
```

## [](#fn-array-prepend)ARRAY\_PREPEND(`val1`, `val2`, … , `expr`)

### [](#description-19)Description

This function returns the new array after prepending the array `expr` with the specified `val` or multiple `val` arguments.

It requires a minimum of two arguments.

### [](#arguments-19)Arguments

val1, val2, …

\[At least 1 is required\] The value or multiple value arguments to prepend to the input `expr`.

expression

\[Required\] The array you want to have the input `value` argument(s) prepended to.

### [](#return-values-17)Return Values

A new array with the input `val` argument(s) prepended to the input array `expr`.

If one of the arguments is `MISSING`, it returns `MISSING`.

If the last argument is a non-array, it returns `NULL`.

### [](#example-14)Example

Add the value `0` to the beginning of a given array.

Query

```sqlpp
SELECT ARRAY_PREPEND( 0 , [ 1 , 2 , 3 , 4 , 5 ] )
AS updated_array;
```

Results

```json
[
  {
    "updated_array": [
      0,
      1,
      2,
      3,
      4,
      5
    ]
  }
]
```

## [](#fn-array-put)ARRAY\_PUT(`expr`, `val1`, `val2`, …)

### [](#description-20)Description

This function returns a new array with `val` or multiple `val` arguments appended if the `val` is not already present. Otherwise, it returns the unmodified input array `expr`.

It requires a minimum of two arguments.

### [](#arguments-20)Arguments

expr

\[Required\] The array you want to append the input `value` or `value` arguments.

val1, val2, …

\[At least 1 is required\] The value or multiple value arguments that you want appended to the end of the input array `expression`.

### [](#return-values-18)Return Values

A new array with `val` or multiple `val` arguments appended if the `val` is not already present. Otherwise, it returns the unmodified input array `expr`.

If one of the arguments is `MISSING`, then it returns `MISSING`.

If the first argument is a non-array, then it returns `NULL`.

### [](#example-15)Example

Append the value `5` to a given array of numbers.

Query

```sqlpp
SELECT ARRAY_PUT([1, 2, 3, 4], 5) AS updated_array;
```

Results

```json
[
  {
    "updated_array": [
      1,
      2,
      3,
      4,
      5
    ]
  }
]
```

## [](#fn-array-range)ARRAY\_RANGE(`start_num`, `end_num` `step_num` \])

### [](#description-21)Description

This function returns a new array of numbers, from `start_num` until the largest number less than `end_num`. Successive numbers are incremented by `step_int`.

If `step_int` is not specified, then the default value is 1\. If `step_num` is negative, then he function decrements until the smallest number greater than `end_num`.

### [](#arguments-21)Arguments

start\_num

\[Required\] The integer to start a new array with.

end\_num

\[Required\] The integer that is one number larger than the final integer in the output array.

step\_num

\[Optional; default is 1\] The number between each array element.

If `step_num` is negative, then the function decrements until the smallest number greater than `end_num`.

Output Values

A new array of numbers, from `start_num` until the largest number less than `end_num`.

If any of the arguments are `MISSING`, then it returns `MISSING`.

If any of the arguments do not start with a digit, then it returns an error.

### [](#examples-6)Examples

Generate an array of numbers from `0` to `20` with a step of `5`.

Query

```sqlpp
SELECT ARRAY_RANGE( 0 , 25 , 5 ) AS generated_array;
```

Results

```json
[
  {
    "generated_array": [
      0,
      5,
      10,
      15,
      20
    ]
  }
]
```

Generate an array of numbers from `0.1` to `1.1` with the default step `1`.

Query

```sqlpp
SELECT ARRAY_RANGE( 0.1 , 2 ) AS generated_array;
```

Results

```json
[
  {
    "generated_array": [
      0.1,
      1.1
    ]
  }
]
```

Generate an array from `10` to `3` with a step of `-3`.

Query

```sqlpp
SELECT ARRAY_RANGE( 10 , 3 , -3 ) AS generated_array;
```

Results

```json
[
  {
    "generated_array": [
      10,
      7,
      4
    ]
  }
]
```

## [](#fn-array-remove)ARRAY\_REMOVE(`expr`, `val1`, `val2`, …)

### [](#description-22)Description

This function returns a new array with all occurrences of the specified `value` or multiple `value` fields removed from the array `expression`. It requires a minimum of two arguments.

### [](#arguments-22)Arguments

expr

\[Required\] The input array to have the specified `val` or multiple `val` fields removed.

val1, val2, …

\[At least 1 is required\] The input value or multiple values to remove from the input array `expr`.

Output Values

A new array with all occurrences of the specified `val` or multiple `val` fields removed from the array `expr`.

If any of the arguments are `MISSING`, then it returns `MISSING`.

If the first argument is not an array, then it returns `NULL`.

> [!NOTE]
> This function cannot be used to remove NULL values from an array as it uses an equality predicate when evaluating the array elements to remove. Since NULL does not equal NULL, such values are not matched and remain in the array.
> 
> To remove NULL values, use the [Array Collection Operator](collectionops.md#array) instead. For example:
> 
> ```sqlpp
> SELECT ARRAY name FOR name IN ["Ryan", NULL, "Corrine"]
> WHEN name IS NOT NULL
> END AS filtered_names;
> ```

### [](#example-16)Example

Remove `3` and `5` from an array of numbers.

Query

```sqlpp
SELECT ARRAY_REMOVE( [ 1 , 2 , 3 , 4 , 5 ] , 3, 5 )
AS updated_array;
```

Results

```json
[
  {
    "updated_array": [
      1,
      2,
      4
    ]
  }
]
```

## [](#fn-array-repeat)ARRAY\_REPEAT(`val`, `rep_int`)

### [](#description-23)Description

This function returns a new array with the specified `val` repeated `rep_int` times.

### [](#arguments-23)Arguments

val

\[Required\] The input value you want repeated.

rep\_int

\[Required\] The integer number of times you want the input `val` repeated.

Output Values

A new array with the specified `val` repeated `rep_int` times.

If any of the arguments are `MISSING`, then it returns `MISSING`.

If the `rep_int` argument is not an integer, then it returns `NULL`.

### [](#example-17)Example

Create an array with the value `"hello"` repeated 3 times.

Query

```sqlpp
SELECT ARRAY_REPEAT("hello", 3) AS repeated_array;
```

Results

```json
[
  {
    "repeated_array": [
      "hello",
      "hello",
      "hello"
    ]
  }
]
```

## [](#fn-array-replace)ARRAY\_REPLACE(`expr`, `val1`, `val2` \[, `max_int`\])

### [](#description-24)Description

This function returns a new array with all occurrences of `value1` replaced with `value2`.

If `max_int` is specified, the function performs no more than `max_int` replacements.

### [](#arguments-24)Arguments

expr

\[Required\] The input array you want to replace `val1` with `val2`.

val1

\[Required\] The existing value in the input `expr` you want to replace.

val2

\[Required\] The new value you want to take the place of `val1` in the input `expr`.

max\_int

\[Optional. Default is no maximum\] The number of maximum replacements to perform.

### [](#return-values-19)Return Values

A new array with all or `max_int` occurrences of `val1` replaced with `val2`.

If any of the arguments are `MISSING`, then it returns `MISSING`.

If the first argument is not an array or if the second argument is `NULL`, then it returns `NULL`.

### [](#example-18)Example

Replace all occurrences of `2` with `99` in a given array.

Query

```sqlpp
SELECT ARRAY_REPLACE( [ 1 , 2 , 3 , 2 , 4 ] , 2 , 99) AS updated_array;
```

Results

```json
[
  {
    "updated_array": [
      1,
      99,
      3,
      99,
      4
    ]
  }
]
```

## [](#fn-array-replace-equivalent)ARRAY\_REPLACE\_EQUIVALENT(`expr`, `val1`, `val2` \[, `max_int`\])

### [](#description-25)Description

This function is similar to the [ARRAY\_REPLACE()](#fn-array-replace) function and returns a new array with all occurrences of `val1` replaced with `val2`. However, it determines matches using the equivalence operator instead of the equality operator. This enables you to replace `NULL` values and composite object values with `NULL` attributes, which is not possible with [ARRAY\_REPLACE()](#fn-array-replace).

If `max_int` is specified, the function performs no more than `max_int` replacements.

### [](#arguments-25)Arguments

expr

\[Required\] The input array you want to replace `val1` with `val2`.

val1

\[Required\] The existing value in the input `expr` you want to replace.

val2

\[Required\] The new value you want to take the place of `val1` in the input `expr`.

max\_int

\[Optional\] The maximum number of replacements to perform. By default, you can perform any number of replacements.

### [](#return-values-20)Return Values

A new array with all or `max_int` occurrences of `val1` replaced with `val2`.

If any of the arguments are `MISSING`, then it returns `MISSING`.

If the first argument is not an array, then it returns `NULL`.

### [](#example-19)Example

The following example shows how the ARRAY\_REPLACE\_EQUIVALENT function can replace `NULL` values, while the ARRAY\_REPLACE function cannot.

Query

```sqlpp
SELECT
ARRAY_REPLACE( [ 1 , 2 , null , 4 ] , null , 99 )
  AS replace_result,
ARRAY_REPLACE_EQUIVALENT( [ 1 , 2 , null , 4 ] , null , 99 )
  AS replace_equiv_result;
```

Results

```json
[
  {
    "replace_result": null,
    "replace_equiv_result": [
      1,
      2,
      99,
      4
    ]
  }
]
```

## [](#fn-array-reverse)ARRAY\_REVERSE(`expr`)

### [](#description-26)Description

This function returns a new array with all the elements of `expr` in reverse order.

### [](#arguments-26)Arguments

expr

\[Required\] The input array whose elements you want to reverse.

### [](#return-values-21)Return Values

A new array with all the elements of `expr` in reverse order.

If the argument is `MISSING`, then it returns `MISSING`.

If the argument is a non-array value, then it returns `NULL`.

### [](#example-20)Example

Reverse the order of elements in a given array.

Query

```sqlpp
SELECT ARRAY_REVERSE( [ 1 , 2 , 3 , 4 , 5 ] )
AS reversed_array;
```

Results

```json
[
  {
    "reversed_array": [
      5,
      4,
      3,
      2,
      1
    ]
  }
]
```

## [](#fn-array-sort)ARRAY\_SORT(`expr`)

### [](#description-27)Description

This function returns a new array with the elements of `expr` sorted in SQL++ collation order.

### [](#arguments-27)Arguments

expr

\[Required\] The input array you want sorted.

### [](#return-values-22)Return Values

A new array with the elements of `expr` sorted in SQL++ collation order.

If the argument is `MISSING`, then it returns `MISSING`.

If the argument is a non-array value, then it returns `NULL`.

### [](#example-21)Example

Sort the elements of a given array in ascending order.

Query

```sqlpp
SELECT ARRAY_SORT( [ 5 , 3 , 1 , 4 , 2 ] )
AS sorted_array;
```

Results

```json
[
  {
    "sorted_array": [
      1,
      2,
      3,
      4,
      5
    ]
  }
]
```

## [](#fn-array-star)ARRAY\_STAR(`expr`)

### [](#description-28)Description

This function converts an array of `expr` objects into an object of arrays.

### [](#arguments-28)Arguments

expr

\[Required\] The input array you want to convert into an object of arrays.

### [](#output-values)Output Values

An object of arrays.

If the argument is `MISSING`, then it returns `MISSING`.

If the argument is a non-array value, then it returns `NULL`.

### [](#example-22)Example

Convert a given array of two objects, each with three fields, into an object of three arrays.

```sqlpp
SELECT ARRAY_STAR([
  { "id": 10226, "airline": "Atifly", "code": "A1F" },
  { "id": 10123, "airline": "Texas Wings", "code": "TXW" }
]) AS object_of_arrays;
```

Results

```json
[
  {
    "object_of_arrays": {
      "airline": [ "Atifly", "Texas Wings"],
      "code": [ "A1F", "TXW" ],
      "id": [ 10226, 10123]
    }
  }
]
```

### [](#array-references)Array References

You can use an asterisk (\*) as an array subscript which converts the array to an object of arrays.

The following example returns an array of airline names from an array of flight objects:

Query

```sqlpp
WITH sample AS (
  SELECT [
    { "airline": "Atifly", "code": "AF" },
    { "airline": "Texas Wings", "code": "TXW" }
  ] AS flights
)
SELECT flights[*].airline
FROM sample;
```

Results

```json
[
  {
    "airline": [
      "Atifly",
      "Texas Wings"
    ]
  }
]
```

You can write an equivalent query using the `array_star()` function:

Query

```sqlpp
WITH sample AS (
  SELECT [
    { "airline": "Atifly", "code": "AF" },
    { "airline": "Texas Wings", "code": "TXW" }
  ] AS flights
)
SELECT array_star(flights).airline
FROM sample;
```

Results

```json
[
  {
    "airline": [
      "Atifly",
      "Texas Wings"
    ]
  }
]
```

### [](#empty-array-subscripts)Empty Array Subscripts

You can use an empty array subscript (`[ ]`) to return an array that includes only defined elements.

The rules governing its use are as follows:

| Expression                  | Description                                                                                                                                                                                                                                                                                                                                                                  |
| --------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| field\[\]                   | If field is not an array, it returns NULL. If field is an array, it returns the array as is.                                                                                                                                                                                                                                                                                 |
| field\[\]\[\]               | If field is not an array, it returns NULL. If field contains only unnamed arrays, it returns field as is. Otherwise, it returns NULL.                                                                                                                                                                                                                                        |
| field\[\].field2            | If field is not an array, it returns MISSING. If field is an array, it extracts field2 from each element in field and returns a new array with those values. If field contains unnamed arrays, they are flattened by one level. Then from the resulting array, it extracts field2 from each object where it is present.                                                      |
| field\[\]\[\].field2        | If field is not an array, it returns MISSING. If every element in field is not an unnamed array, it returns MISSING. If field contains unnamed arrays, they are flattened by one level. Then from the resulting array, it extracts field2 from each object where it is present.                                                                                              |
| field\[\].field2\[\].field3 | Returns an array of field3 values by traversing two levels of arrays. First it extracts field2 from each element of field, then extracts field3 from each element of the resulting field2 array. If field and field2 contain unnamed arrays, they are flattened by one level. Then from the resulting field2 array, it extracts field3 from each object where it is present. |

> [!NOTE]
> If you use more that two empty array subscripts (for example, `field[][][]`), the function considers only the first two subscripts and ignores the rest.

#### [](#example-23)Example

Given the following sample document:

```json
{
  "a": {
    "airline": "AF",
    "airlineid": "airline_003",
    "destinationairport": "SFO",
    "distance": 2481.617376098415,
    "equipment": "320",
    "id": 3,
    "schedule": [
      {
        "day": 0,
        "flight": "AF198",
        "utc": "10:13:00"
      },
      {
        "flight": "AF250",
        "utc": "12:59:00"
      },
      {
        "day": 2,
        "flight": "AF223",
        "special_flights": [
          {
            "a": "SA"
          },
          {
            "b": [
              [
                {
                  "c": "SC1"
                },
                {
                  "c": "SC2"
                }
              ],
              [
                {
                  "c": "SC3"
                }
              ]
            ]
          }
        ],
        "utc": "19:41:00"
      }
    ],
    "sourceairport": "DFW"
  }
}
```

Here's how different array subscripts evaluate:

* `a.airline[]`: Returns `NULL` (not an array).
* `a.schedule[]`: Equivalent to `a.schedule`, returns the array.
* `a.schedule[].day`: Returns `[0, 2]`. This is in contrast to `a.schedule[*].day`, which returns `[0, null, 2]`.
* `a.schedule[].special_flights[].a`: Returns `["SA"]`.
* `a.schedule[][].utc`: Returns `MISSING`.
* `a.schedule[].special_flights[].b[][].c`: Returns `["SC1", "SC2", "SC3"]`.
* `a.schedule[].special_flights[].b[].c`: Also returns `["SC1", "SC2", "SC3"]`, as the unnamed arrays are flattened.

## [](#fn-array-sum)ARRAY\_SUM(`expr`)

### [](#description-29)Description

This function returns the sum of all the non-`NULL` number values in the `expr` array.

### [](#arguments-29)Arguments

expr

\[Required\] The input array of numbers you want to know the total value of.

### [](#return-values-23)Return Values

The sum of all the non-`NULL` number values in the `expr` array.

If there are no number values, then it returns 0 (zero).

If the argument is `MISSING`, then it returns `MISSING`.

If the argument is a non-array value, then it returns `NULL`.

### [](#example-24)Example

Find the total of a given array of numbers.

Query

```sqlpp
SELECT ARRAY_SUM( [ 0 , 1 , 1 , 2 , 3 , 5 ] ) as sum;
```

Results

```json
[
  {
    "sum": 12
  }
]
```

## [](#fn-array-symdiff)ARRAY\_SYMDIFF(`expr1`, `expr2`, …)

This function has a synonym [ARRAY\_SYMDIFF1()](#fn-array-symdiff1).

### [](#description-30)Description

This function returns a new array based on the set symmetric difference, or disjunctive union, of the input `expression` arrays. The new array contains only those elements that appear in exactly one of the input arrays, and it requires a minimum of two arguments.

### [](#arguments-30)Arguments

expr1, expr2, …

\[At least 2 are required\] The input arrays to compare.

### [](#return-values-24)Return Values

A new array containing only those elements that appear in exactly one of the input arrays.

If any of the arguments is `MISSING`, then it returns `MISSING`.

If any of the arguments is a non-array value, then it returns `NULL`.

> [!NOTE]
> The difference between [ARRAY\_SYMDIFF()](#fn-array-symdiff) and [ARRAY\_SYMDIFFN()](#fn-array-symdiffn) is that the former function includes the value when it appears only once, while the latter function includes the value when it appears an odd number of times in the input arrays.
> 
> Refer to the following article for more information on the difference between a normal and n-ary symdiff: <https://en.wikipedia.org/wiki/Symmetric%5Fdifference>.

### [](#example-25)Example

Find the elements that appear in exactly one of these three input arrays.

Query

```sqlpp
SELECT ARRAY_SYMDIFF( [ 1 , 2 ] , [ 1 , 2 , 4 ] , [ 1 , 3 ] )
AS symm_diff1;
```

Results

```json
[
  {
    "symm_diff1": [
      3,
      4
    ]
  }
]
```

## [](#fn-array-symdiff1)ARRAY\_SYMDIFF1(`expr1`, `expr2`, …)

Synonym of [ARRAY\_SYMDIFF()](#fn-array-symdiff).

## [](#fn-array-symdiffn)ARRAY\_SYMDIFFN(`expr1`, `expr2`, …)

### [](#description-31)Description

This function returns a new array based on the set symmetric difference, or disjunctive union, of the input arrays. The new array contains only those elements that appear in an odd number of input arrays, and it requires a minimum of two arguments.

### [](#arguments-31)Arguments

expr1, expr2, …

\[At least 2 are required\] The input arrays to compare.

### [](#return-values-25)Return Values

A new array containing only those elements that appear in an odd number of the input arrays.

If any of the arguments is `MISSING`, then it returns `MISSING`.

If any of the arguments is a non-array value, then it returns `NULL`.

> [!NOTE]
> The difference between [ARRAY\_SYMDIFF()](#fn-array-symdiff) and [ARRAY\_SYMDIFFN()](#fn-array-symdiffn) is that the former function includes the value when it appears only once, while the latter function includes the value when it appears an odd number of times in the input arrays.
> 
> Refer to the following article for more information on the difference between a normal and n-ary symdiff: <https://en.wikipedia.org/wiki/Symmetric%5Fdifference>.

### [](#example-26)Example

Find the elements that appear in an odd number of these three input arrays.

Query

```sqlpp
SELECT ARRAY_SYMDIFFN( [ 1 , 2 ] , [ 1 , 2 , 4 ] , [ 1 , 3 ] )
AS symm_diffn;
```

Results

```json
[
  {
    "symm_diffn": [
      1,
      3,
      4
    ]
  }
]
```

## [](#fn-array-union)ARRAY\_UNION(`expr1`, `expr2`, …)

### [](#description-32)Description

This function returns a new array with the set union of the input arrays, and it requires a minimum of two arguments.

### [](#arguments-32)Arguments

expr1, expr2, …

\[At least 2 are required\] The input arrays to compare.

### [](#return-values-26)Return Values

A new array with the set union of the input arrays.

If any of the arguments is `MISSING`, then it returns `MISSING`.

If any of the arguments is a non-array value, then it returns `NULL`.

### [](#examples-7)Examples

List the union of three given arrays.

Query

```sqlpp
SELECT ARRAY_UNION( [ 1 , 2 ] , [ 1 , 2 , 4 ] , [ 1 , 3 ] )
AS array_union;
```

Results

```json
[
  {
    "array_union": [
      3,
      2,
      1,
      4
    ]
  }
]
```

List the union of two given arrays with a string.

Query

```sqlpp
SELECT ARRAY_UNION( [ 1 , 2 ] , [ 1 , 2 , 4 ] , "abc")
AS array_union;
```

Results

```json
[
  {
    "array_union": null
  }
]
```