> ## Documentation Index
> Fetch the complete documentation index at: https://docs.ocient.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Array Functions and Operators

> Reference for Ocient SQL array functions and operators, with examples for constructing, accessing, slicing, concatenating, and aggregating array values.

export const PostgreSQL = "PostgreSQL®";

export const Ocient = "Ocient®";

The {Ocient} System enables you to work with data as arrays. The system contains functions that work with arrays and operators that enable you to parse data within arrays.

## ARRAY\_CAT\_DISTINCT

Concatenates two or more arrays in the order of the input arguments. After concatenation, the function removes duplicates from the result array while preserving the first occurrence order. The function skips any NULL input arguments. If all input arrays are NULL, the function returns a NULL. If at least one NULL element is present in the array, the function returns a NULL in the result array with the position of the first NULL the function finds in the order of the input arguments.

**Syntax**

```sql SQL theme={null}
ARRAY_CAT_DISTINCT(input_array1, input_array2 [, ...])
```

| **Argument** | **Data** **Type** | **Description** |
| - | - | - |
| `input_array1, input_array2 [, ...]` | ARRAY | Two or more arrays for concatenation and deduplication. The elements within all input arrays must have the same data type. |

**Examples**

**Concatenate Two Arrays and Remove Duplicates**

Concatenate two arrays and remove duplicate elements within the arrays.

```sql SQL theme={null}
SELECT ARRAY_CAT_DISTINCT(ARRAY[1,2,2,3,1], ARRAY[2,3,4,4,5]);
```

Output: `[1,2,3,4,5]`

**Concatenate Two Arrays with NULLs and Remove Duplicates**

Deduplicate the contents of the array. In this case, the array contains NULLs.

```sql SQL theme={null}
SELECT ARRAY_CAT_DISTINCT(ARRAY[1,2,2,NULL,1], ARRAY[2,4,4,NULL]);
```

Output: `[1,2,NULL,4]`

## ARRAY\_DISTINCT

Removes duplicates from an array while preserving the first occurrence order. If at least one NULL is present in the array, the function returns a NULL with the position of the first NULL of the input array in the output array.

**Syntax**

```sql SQL theme={null}
ARRAY_DISTINCT(input_array)
```

| **Argument** | **Data** **Type** | **Description** |
| - | - | - |
| `input_array` | ARRAY | The array for deduplication. |

**Examples**

**Remove Duplicates from an Array**

Deduplicate the contents of the array.

```sql SQL theme={null}
SELECT ARRAY_DISTINCT(ARRAY[1,2,2,3,1]);
```

Output: `[1,2,3]`

**Remove Duplicates from an Array with NULLs**

Deduplicate the contents of the array. In this case, the array contains NULLs.

```sql SQL theme={null}
SELECT ARRAY_DISTINCT(ARRAY[1,2,NULL,2,3,NULL,1]);
```

Output: `[1,2,NULL,3]`

## ARRAY\_INTERSECT\_DISTINCT\_COUNT

Returns the count of distinct elements that are common to both input arrays as an `INT` value. The function applies set semantics, collapsing duplicate elements within each array before computing the intersection. Both arrays must have the same element type.

If both array arguments are `NULL`, the function returns `NULL`. If one argument is `NULL` and the other is a non-NULL array, the function returns `NULL`. If both arrays are empty, the function returns `0`. NULL elements within arrays participate in the intersection (a NULL in both arrays counts as one shared element).

**Syntax**

```sql SQL theme={null}
ARRAY_INTERSECT_DISTINCT_COUNT(array1, array2)
```

| **Argument** | **Data** **Type** | **Description** |
| - | - | - |
| `array1` | Array | An array of elements. Supported element types are: `INT`, `BIGINT`, `SMALLINT`, `BYTE`, `FLOAT`, `DOUBLE`, `DECIMAL`, `BOOLEAN`, `VARCHAR`, `TIMESTAMP`, `TIME`, `DATE`, `BINARY`, `IPV4`, `IP`, `UUID`, `HASH`, `ST_POINT`, `ST_LINESTRING`, and `ST_POLYGON`. |
| `array2` | Array | An array of elements with the same element type as `array1`. |

**Examples**

**Count Shared Elements Between Two Arrays**

Count the distinct elements shared between two integer arrays. Both arrays contain `1` and `2`, so the result is `2` even though duplicates are present.

```sql SQL theme={null}
SELECT ARRAY_INTERSECT_DISTINCT_COUNT(
    ARRAY[1, 2, 2, 1],
    ARRAY[1, 2, 2, 3]
);
```

Output: `2`

**Count Shared Elements with No Overlap**

Count the distinct elements shared between two arrays that have no elements in common.

```sql SQL theme={null}
SELECT ARRAY_INTERSECT_DISTINCT_COUNT(
    ARRAY[1, 2, 3],
    ARRAY[4, 5, 6]
);
```

Output: `0`

**Count Shared Elements with an Empty Array**

When one array is empty, no elements are shared.

```sql SQL theme={null}
SELECT ARRAY_INTERSECT_DISTINCT_COUNT(
    ARRAY[1, 2, 3],
    INT[]()
);
```

Output: `0`

## ARRAY\_LENGTH

Returns the length of the array for the specified dimension. If the array is a nested array, the function returns the length of the outermost array.

**Syntax**

```sql SQL theme={null}
ARRAY_LENGTH(input_array)
```

| **Argument** | **Data** **Type** | **Description** |
| - | - | - |
| `input_array` | ARRAY | The array that contains any type of elements. The array can be single or multi-dimensional. |

**Example**

Return the length of the array.

```sql SQL theme={null}
SELECT ARRAY_LENGTH(ARRAY[1,2]);
```

Output: `2`

## ARRAY\_SORT

Sorts and returns the input array based on the natural ordering of its elements. The behavior of this function varies depending on the syntax you use.

**Syntaxes**

**Basic Array Sort**

Sorts and returns the input array based on the natural ordering of its elements. If one of the array elements is NULL, then this function sorts the NULL values to the end of the array.

```sql SQL theme={null}
ARRAY_SORT(input_array)
```

| **Argument** | **Data** **Type** | **Description** |
| - | - | - |
| `input_array` | ARRAY | An array of elements with these types: `BIGINT`, `BOOLEAN`, `BYTE`, `DATE`, `DOUBLE`, `FLOAT`, `INT`, `SMALLINT`, `UUID`, or `VARCHAR`. |

**Sort an Array With a Lambda Function**

Sorts and returns the input array based on the results of the specified Lambda function. The function should have two arguments representing two elements of the array. This function should return a negative integer, 0, or a positive integer if the first element is less than, equal to, or greater than the second element, respectively.

```sql SQL theme={null}
ARRAY_SORT(input_array,function)
```

| **Argument** | **Data** **Type** | **Description** |
| - | - | - |
| `input_array` | ARRAY | An array that contains any type of elements. The array can be single or multi-dimensional. |
| `function` | FUNCTION | This is an optional argument. A lambda function with the format `(x T, y T) -> INT` or another SQL reference function. |

<Info>
  The Ocient System supports this syntax with the data pipeline functionality only. For details, see [Transform Data in Data Pipelines](/transform-data-in-data-pipelines).
</Info>

**Sort an Array in the Specified Order**

Determines how the array handles the order of elements in the array.

```sql SQL theme={null}
ARRAY_SORT(input_array,sort_order)
```

| **Argument** | **Data** **Type** | **Description** |
| - | - | - |
| `input_array` | ARRAY | An array of elements with these types: `BIGINT`, `BOOLEAN`, `BYTE`, `DATE`, `DOUBLE`, `FLOAT`, `INT`, `SMALLINT`, `UUID`, or `VARCHAR`. |
| `sort_order` | BOOLEAN | This is an optional argument. <br /><br />Determines the sort order of the array. The value `true` means ascending order. The value `false` means descending order. The default value is `true`. |

**Sort an Array in the Specified Order with NULL Elements**

Determines how the array handles the order of elements in the array and where NULL elements appear.

```sql SQL theme={null}
ARRAY_SORT(input_array,sort_order,nulls_first)
```

| **Argument** | **Data** **Type** | **Description** |
| - | - | - |
| `input_array` | ARRAY | An array of elements with these types: `BIGINT`, `BOOLEAN`, `BYTE`, `DATE`, `DOUBLE`, `FLOAT`, `INT`, `SMALLINT`, `UUID`, or `VARCHAR`. |
| `sort_order` | BOOLEAN | This is an optional argument. <br /><br />Determines the sort order of the array. The value `true` means ascending order. The value `false` means descending order. The default value is `true`. |
| `nulls_first` | BOOLEAN | This is an optional argument. <br /><br />Determines whether NULL elements appear first or last in the array. The `true` value means that NULLs appear first in the array. The `false` value means that they appear last. If the array is in ascending order, the default value is `false`. Otherwise, the default value is `true`. |

**Examples**

**Sort an Array**

Sort an array of three elements `[2,3,1]`.

```sql SQL theme={null}
SELECT ARRAY_SORT(ARRAY[2,3,1]);
```

Output: `[1,2,3]`

**Sort an Array by Specifying the Sort Order**

Sort an array of four elements `[2,3,1,NULL]` and specify the order of the elements in descending order.

```sql SQL theme={null}
SELECT ARRAY_SORT(ARRAY[2,3,1,NULL],false);
```

Output: `[NULL,3,2,1]`

**Sort an Array by Specifying the Sort Order and NULL Placement**

Sort an array of four elements `[2,NULL,3,1]` and specify the order of the elements in ascending order, and have the NULL element appear last.

```sql SQL theme={null}
SELECT ARRAY_SORT(ARRAY[2,NULL,3,1],true,false);
```

Output: `[1,2,3,NULL]`

## ARRAY\_TRANSFORM

Transforms one or more arrays based on the logic in a lambda expression that also receives the elements at the same position in every array. Use this function to transform every element in a single array, or to combine corresponding elements from multiple arrays element-wise.

The first one or more arguments are expressions that evaluate to arrays that contain elements to transform. The last argument is a lambda expression or other reference function that defines how to transform each element using the elements at the same position in every array.

The last argument must be of the form `(x1 [T1], ..., xN [TN]) -> U`. This form returns a value of type `U` from the elements `x1` through `xN` at the same position in each of the `N` array arguments. Declaring the type of each element is optional. If you omit a type, the system infers it from the corresponding array argument. If you declare a type, it must match the element type of the corresponding array.

The last argument can also be a reference to an existing function whose signature accepts one argument for each array argument.

The `ARRAY_TRANSFORM` function returns an array with the length of the longest input array. The returned array has the type returned by the lambda function. If the input arrays are not the same length, the system pads the shorter arrays with `NULL` values. If any input array is `NULL`, the result is `NULL`. For multi-dimensional arrays, the function applies to the outermost dimension. You can nest `ARRAY_TRANSFORM` functions to apply them over inner dimensions.

**Syntax**

```sql SQL theme={null}
ARRAY_TRANSFORM(array1 [, array2, ...], function)
```

| **Argument** | **Data Type** | **Description** |
| - | - | - |
| `array1` | Array | The array can be single or multi-dimensional. The `ARRAY_TRANSFORM` function only iterates over the first dimension. Provide additional array arguments to combine elements from multiple arrays. |
| `function` | Lambda function<br /> `(x1 [T1], ..., xN [TN]) -> U`<br />or<br />Reference function | A lambda function (e.g., `(a, b) -> a + b`) that takes one element from each array argument and returns a value of type `U`. The optional `T1` through `TN` values are the declared types of `x1` through `xN`; if you omit a type, the system infers it from the corresponding array argument. You can also pass a reference function with a matching signature that accepts one argument for each array. |

**Examples**

**Transform a Single Array Using a Lambda Function**

Use the `ARRAY_TRANSFORM` function to convert each element of an array to uppercase.

```sql SQL theme={null}
SELECT ARRAY_TRANSFORM(ARRAY['alice', 'bob', 'carol'], (x CHAR) -> UPPER(x));
```

Output: `["ALICE", "BOB", "CAROL"]`

**Transform a Single Array Using a Reference Function**

Use the `UPPER` function as a reference function with the `ARRAY_TRANSFORM` function.

```sql SQL theme={null}
SELECT ARRAY_TRANSFORM(ARRAY['alice', 'bob', 'carol'], UPPER);
```

Output: `["ALICE", "BOB", "CAROL"]`

**Combine Elements from Two Arrays**

Use the `ARRAY_TRANSFORM` function with two arrays to add the corresponding elements together.

```sql SQL theme={null}
SELECT ARRAY_TRANSFORM(
    ARRAY[CAST(1 AS BIGINT), CAST(2 AS BIGINT), CAST(3 AS BIGINT)],
    ARRAY[CAST(10 AS BIGINT), CAST(20 AS BIGINT), CAST(30 AS BIGINT)],
    (a BIGINT, b BIGINT) -> a + b
);
```

Output: `[11, 22, 33]`

**Use a Reference Function with Multiple Arrays**

Use the `POWER` function as a reference function with the `ARRAY_TRANSFORM` function to raise each base to the corresponding exponent from a second array. The `POWER` function accepts two numeric arguments, matching the required signature where the first argument is the element from the first array and the second argument is the element from the second array.

```sql SQL theme={null}
SELECT ARRAY_TRANSFORM(
    ARRAY[2, 3, 4],
    ARRAY[2, 2, 2],
    POWER
);
```

Output: `[4, 9, 16]`

## ARRAY\_TRANSFORM\_WITH\_ORD

Transforms an array based on the logic in a lambda expression that also receives the 1-based ordinal of each element. Use this function when the transformation depends on the position of the element in the array, such as generating sequence numbers, weighting elements by index, or pairing elements with their position.

The first argument is an expression that evaluates to an array that contains elements to transform. The second argument is a lambda expression or other reference function that defines how to transform each element using both its value and its position.

The second argument must be of the form `(x T, ord INT) -> U`. This form returns a value of type `U` for an element `x` at 1-based position `ord`. The lambda function specifies the type `T` of the element `x`. The ordinal variable always has the `INT` type. If the lambda function declares a type for the ordinal variable, it must be `INT`.

The second argument can also be a reference to an existing function whose signature accepts the element type followed by a trailing `INT` argument.

The `ARRAY_TRANSFORM_WITH_ORD` function returns an array of elements with the same length as the array argument. The returned array has the type returned by the lambda function. If the input array is `NULL`, the result is `NULL`. For multi-dimensional arrays, the function applies to the outermost dimension. You can nest `ARRAY_TRANSFORM_WITH_ORD` functions to apply them over inner dimensions.

**Syntax**

```sql SQL theme={null}
ARRAY_TRANSFORM_WITH_ORD(array, function)
```

| **Argument** | **Data Type** | **Description** |
| - | - | - |
| `array` | Array | The array can be single or multi-dimensional. The `ARRAY_TRANSFORM_WITH_ORD` function only iterates over the first dimension. |
| `function` | Lambda function<br /> `(x T, ord INT) -> U`<br />or<br />Reference function | A lambda function (e.g., `(elem INT, ord INT) -> elem * ord`) that takes an element of type `T` and the 1-based `INT` ordinal, and returns a value of type `U`. The return type can be the same type as `T` or a different type. You can also pass a reference function with a matching `(T, INT) -> U` signature. |

**Examples**

**Prefix Each Element with Its Position**

Use the `ARRAY_TRANSFORM_WITH_ORD` function with a lambda function that prepends the 1-based position of each element.

```sql SQL theme={null}
SELECT ARRAY_TRANSFORM_WITH_ORD(
    ARRAY['alice', 'bob', 'carol'],
    (elem CHAR, ord INT) -> CONCAT(CAST(ord AS CHAR), '. ', elem)
);
```

Output: `["1. alice", "2. bob", "3. carol"]`

**Multiply Each Element by Its Position**

Use the `ARRAY_TRANSFORM_WITH_ORD` function with a literal array to multiply each element by its 1-based position.

```sql SQL theme={null}
SELECT ARRAY_TRANSFORM_WITH_ORD(
    ARRAY[10, 20, 30],
    (elem BIGINT, ord INT) -> elem * CAST(ord AS BIGINT)
);
```

Output: `[10, 40, 90]`

**Use a Reference Function**

Use the `POWER` function as a reference function with the `ARRAY_TRANSFORM_WITH_ORD` function. The `POWER` function accepts two numeric arguments, matching the required `(T, INT) -> U` signature where the first argument is the element value and the second argument is the 1-based ordinal.

```sql SQL theme={null}
SELECT ARRAY_TRANSFORM_WITH_ORD(
    ARRAY[2, 3, 4],
    POWER
);
```

Output: `[2, 9, 256]`

## JACCARD\_SIMILARITY

Returns the Jaccard similarity coefficient of two arrays as a `DOUBLE` value between `0.0` and `1.0`. The Jaccard similarity measures the size of the intersection divided by the size of the union of the two sets: |A intersect B| / |A union B|. The function applies set semantics, collapsing duplicate elements within each array before computing the result. Both arrays must have the same element type.

A result of `1.0` means that the two arrays contain the same distinct elements. A result of `0.0` means that the two arrays share no elements.

If both array arguments are empty, the function returns `1.0`. If one array is non-empty and the other is empty, the function returns `0.0`. If both array arguments are `NULL`, the function returns `NULL`. NULL elements within arrays participate in the similarity calculation.

**Syntax**

```sql SQL theme={null}
JACCARD_SIMILARITY(array1, array2)
```

| **Argument** | **Data** **Type** | **Description** |
| - | - | - |
| `array1` | Array | An array of elements. Supported element types are: `INT`, `BIGINT`, `SMALLINT`, `BYTE`, `FLOAT`, `DOUBLE`, `DECIMAL`, `BOOLEAN`, `VARCHAR`, `TIMESTAMP`, `TIME`, `DATE`, `BINARY`, `IPV4`, `IP`, `UUID`, `HASH`, `ST_POINT`, `ST_LINESTRING`, and `ST_POLYGON`. |
| `array2` | Array | An array of elements with the same element type as `array1`. |

**Examples**

**Compute Jaccard Similarity of Overlapping Arrays**

Compute the Jaccard similarity of two arrays that share some elements. The distinct elements of the first array are `{1, 2, 3}` and the second are `{2, 3, 4}`. The intersection is `{2, 3}` (size two) and the union is `{1, 2, 3, 4}` (size four), so the Jaccard similarity is 2/4 = 0.5.

```sql SQL theme={null}
SELECT JACCARD_SIMILARITY(
    ARRAY[1, 2, 3],
    ARRAY[2, 3, 4]
);
```

Output: `0.5`

**Compute Jaccard Similarity of Identical Arrays**

When both arrays contain the same distinct elements, the result is `1.0`.

```sql SQL theme={null}
SELECT JACCARD_SIMILARITY(
    ARRAY[1, 2, 2, 1],
    ARRAY[1, 2]
);
```

Output: `1.0`

**Compute Jaccard Similarity with Duplicates**

The distinct elements of the first array are `{1, 2}` and the second one are `{1, 2, 3}`. The intersection is `{1, 2}` (size two) and the union is `{1, 2, 3}` (size three), so the result is approximately 0.667.

```sql SQL theme={null}
SELECT JACCARD_SIMILARITY(
    ARRAY[1, 2, 2, 1],
    ARRAY[1, 2, 2, 3]
);
```

Output: `0.6666666666666666`

**Compute Jaccard Similarity with No Overlap**

When the two arrays share no elements, the result is `0.0`.

```sql SQL theme={null}
SELECT JACCARD_SIMILARITY(
    ARRAY[1, 2, 3],
    ARRAY[4, 5, 6]
);
```

Output: `0.0`

## UNNEST

Expands each element in an input array into an individual row.

For example, the `UNNEST` function on an array column of type `ARRAY(INT)` with values `[2, 6]` yields two result rows with integers `2` and `6`.

The values of the other columns in each input row are unchanged in each corresponding output row. You can specify multiple array columns to unnest the specified arrays from each row in parallel.

**Syntax**

```sql SQL theme={null}
UNNEST( array_column [, ... ]​ )
   [ WITH <unnest_options> ]

<unnest_options> ::=
   { ORDINALITY [ ord_identifier ]
   | VALUE [ val_identifier ]
   | NULL_INPUT }
```

| **Argument** | **Data** **Type** | **Description** |
| - | - | - |
| `array_column` | `ARRAY` | The array column to expand. |
| `ord_identifier` | `CHAR` | When you use this argument in an `ORDINALITY` clause, the argument is the name for the output column. |
| `val_identifier` | `CHAR` | When you use this argument in a `VALUE` clause, the argument is the name for the unnested column. |

The `UNNEST` function supports these options. When you use only one of these options, enclosing the option in parentheses is optional. However, if you use more than one of these options, you must enclose them in parentheses.

| **Options** | **Description** |
| - | - |
| `ORDINALITY [ord_identifier]` | Optional.<br />Generates a new integer column that corresponds to the index position of each output element in the original array. The `ord_identifier` value specifies the name of the output ordinality column. <br />If you do not specify this clause, the column name defaults to the original column name with the suffix `_ord`. |
| `VALUE [val_identifier]` | Optional.<br />Rename the unnested column to the new name specified in the `val_identifier` argument. <br />If you do not specify this clause, the column name defaults to the original column name with the suffix `_val`. |
| `NULL_INPUT` | Optional.<br />When the input column array is empty or NULL, this clause specifies to replace each output row with an array with one NULL element. This replacement preserves the contents of other input rows. By default, this option is not enabled. |

These examples demonstrate how the `UNNEST` function operates on arrays and other objects. Most of the examples use the data rows from this table.

```Text Text theme={null}
a   b        c        d     e      f
--------------------------------------------
1   [1, 2]   [6, 9]   []    NULL   [[1, 2], [5], []]
2   [7, 5]   [4]      [3]   [8]    [[6], [9, 4, 7], [3, 8]]
```

**Examples**

**Expand a Column**

This example performs a simple `UNNEST` on column `b`.

```sql SQL theme={null}
SELECT UNNEST(b);
```

*Output*

```Text Text theme={null}
b_val
-----------
1
2
7
5
```

**Expand a Column Using the** `ORDINALITY` **Option**

In this example, the `ORDINALITY` option adds a second return column that represents the index position of the value in the input array.

```sql SQL theme={null}
SELECT UNNEST(b) WITH ORDINALITY;
```

*Output*

```Text Text theme={null}
b_val   b_ord
--------------------
1       1
2       2
7       1
5       2
```

**Expand a Column with Empty Data**

In this example, the `UNNEST` query returns only one row because one of the two rows in column `d` is an empty array.

If you use the `NULL_INPUT` option, the query returns a second row with the `NULL` value.

```sql SQL theme={null}
SELECT UNNEST(d);
```

*Output*

```Text Text theme={null}
d_val
------------
3
```

**Expand a Column with NULL Data**

`UNNEST` also does not return array values that are `NULL` unless you specify the `NULL_INPUT` option.

```sql SQL theme={null}
SELECT UNNEST(e);
```

*Output*

```Text Text theme={null}
e_val
------------
8
```

**Expand a Column with Multiple Array Layers**

The `UNNEST` function expands only one array layer.

<Info>
  The empty array remains in the returned values.
</Info>

```sql SQL theme={null}
SELECT UNNEST(f);
```

*Output*

```Text Text theme={null}
f_val
------------
[1,2]
[5]
[]
[6]
[9,4,7]
[3,8]
```

**Expand Columns Using Multiple** `UNNEST` **Statements**

Multiple `UNNEST` statements in a query produce a per-row Cartesian product of all the unnested values.

```sql SQL theme={null}
SELECT UNNEST(b), UNNEST(c);
```

*Output*

```Text Text theme={null}
b_val   c_val
------------
1       6
1       9
2       6
2       9
7       4
5       4
```

**Expand Columns Using Multiple** `UNNEST` **Statements in Reverse Order**

This example uses `UNNEST` on the same columns but in reverse order.

```sql SQL theme={null}
SELECT UNNEST(c), UNNEST(b);
```

*Output*

```Text Text theme={null}
c_val   b_val
------------
6       1
6       2
9       1
9       2
4       7
4       5
```

**Expand Multiple Columns**

In this example, the query unnests two array columns in parallel. Even though it does not include the `NULL_INPUT` option, the query still returns NULL for column `c` to correspond with column `b`.

```sql SQL theme={null}
SELECT UNNEST(b, c);
```

*Output*

```Text Text theme={null}
b_val   c_val
------------
1       6
2       9
7       4
5       NULL
```

**Expand Multiple Array Columns**

This query unnests two array columns.

```sql SQL theme={null}
SELECT UNNEST(b, d);
```

*Output*

```Text Text theme={null}
b_val   d_val
------------
1       NULL
2       NULL
7       3
5       NULL
```

**Expand Values of Arrays Within Arrays**

This example has two layers of `UNNEST` to capture values of arrays within arrays.

```sql SQL theme={null}
SELECT UNNEST(f_val) FROM (UNNEST(f));
```

*Output*

```Text Text theme={null}
f_val
------------
1
2
5
6
9
4
7
3
8

```

**Expand an Empty Array**

This example unnests an empty integer array.

```sql SQL theme={null}
SELECT UNNEST(INT[]());
```

*Output*

```Text Text theme={null}
col_val
------------
```

The query returns a column of type `INT` but no rows.

**Expand an Empty Array Column Without the** `NULL_INPUT` **Option**

This example selects the non-array column `a` and an empty array column. The query returns no rows because empty or `NULL` array values are not returned unless you specify the `NULL_INPUT` option.

```sql SQL theme={null}
SELECT a, UNNEST(INT[]());
```

*Output*

```Text Text theme={null}
a   col_val
------------
```

**Expand a Column Without the** `NULL_INPUT` **Option**

This query does not include the `NULL_INPUT` option, which causes the result to omit the NULL array values in column `e`.

```sql SQL theme={null}
SELECT a, UNNEST(e);
```

*Output*

```Text Text theme={null}
a   e_val
------------
1   8
2   8
```

**Expand a Column** **with the** `NULL_INPUT` **Option**

This query includes the `NULL_INPUT` option, which means that the query returns the NULL values in column `e`.

```sql SQL theme={null}
SELECT a, UNNEST(e) WITH NULL_INPUT;
```

*Output*

```Text Text theme={null}
a   e_val
------------
1   8
1   NULL
2   8
2   NULL
```

**Expand Arrays with NULL Rows**

This example attempts to unnest two arrays, one of which is cast as NULL, while the other is empty.

In both cases, the query returns a column of type `INTEGER` with no rows because there are no values to unnest.

```sql SQL theme={null}
SELECT UNNEST(CAST(NULL AS INT[]);

SELECT UNNEST(INT[]());
```

*Output*

```Text Text theme={null}
col_val
------------
```

## ZIP\_WITH\_ORD

Merges two or more input arrays, element-wise, into a single array using a combining function that also receives the 1-based ordinal of each tuple. Use this function when the combining logic depends on the position of the elements in the arrays. If one array is shorter than the others, the function pads the shorter array with `NULL` values to match the length of the longest array before applying the combining function.

The function returns an array of length `max(length(array1), length(array2), ...)` containing elements of type `V` as returned by the lambda function. If the arrays are not the same length, the system pads shorter arrays with `NULL` values.

The system rewrites the `ZIP_WITH_ORD` function internally to the `ARRAY_TRANSFORM_WITH_ORD` function. The system first merges the input arrays and then applies the ordinal-aware transform to the resulting tuples.

**Syntax**

```sql SQL theme={null}
ZIP_WITH_ORD(array1, array2 [, ...], function)
```

| **Argument** | **Data Type** | **Description** |
| - | - | - |
| `array1` | Array | The array can be single or multi-dimensional. The `ZIP_WITH_ORD` function only iterates over the first dimension. |
| `array2` | Array | The array can be single or multi-dimensional. The `ZIP_WITH_ORD` function only iterates over the first dimension. |
| `function` | Lambda function<br /> `(a T1, b T2, ord INT) -> V`<br />or<br />reference function | A lambda function for which `a` is the element from `array1`, `b` is the element from `array2`, and `ord` is the 1-based position. The lambda function specifies the types `T1` and `T2` and returns a value of type `V` (e.g., `(a INT, b INT, ord INT) -> a + b + ord`). <br /><br />When merging more than two arrays together, add more arguments before `ord` matching the order of the input arrays (e.g., `(a T1, b T2, c T3, ord INT) -> V`). The ordinal variable must always be the last parameter, and its type must be `INT`. You can also pass a reference function with a matching `(T1, T2, INT) -> V` signature. |

**Example**

Use the `ZIP_WITH_ORD` function to compute a weighted line total that includes the line number.

```sql SQL theme={null}
SELECT ZIP_WITH_ORD(
    ARRAY[CAST(100 AS BIGINT), CAST(200 AS BIGINT), CAST(300 AS BIGINT)],
    ARRAY[CAST(2 AS BIGINT), CAST(5 AS BIGINT), CAST(1 AS BIGINT)],
    (price BIGINT, qty BIGINT, ord INT) ->
        CONCAT('Line ', CAST(ord AS CHAR), ': ', CAST(price * qty AS CHAR))
);
```

Output: `["Line 1: 200", "Line 2: 1000", "Line 3: 300"]`

## Other Array Functions

| **Function** | **Syntax** | **Purpose** |
| - | - | - |
| Constructor | TYPE\[]\(e1, e2, …, en) | Construct an array of SQL type TYPE giving the elements. NULL is also supported as an element. |
| Constructor (2) | array\[e1, e2, …, en] | {PostgreSQL}-compliant constructor. The type of the array is deduced from the elements. |
| array concatenation | array\_cat(array, array) | Concatenate 2 arrays into a new one. |
| array softmax | softmax(array) | Returns the softmax of the array. The array must be one-dimensional and contain float or double values. The system transfers NULL-valued indices as 0.0. |
| array cross entropy loss | cross\_entropy\_loss(array, array) | Returns the cross entropy loss of two arrays. Both arrays must be one-dimensional and contain float or double values. NULL values do not contribute to the sum. |
| array log loss | log\_loss(array, array) | Returns the log loss of two arrays. Both arrays must be one-dimensional and contain float or double values. NULL values do not contribute to the sum. |
| array logits loss | logits\_loss(array, array) | Returns the logits loss of two arrays. Both arrays must be one-dimensional and contain float or double values. NULL values do not contribute to the sum. |
| array hinge loss | hinge\_loss(array, array) | Returns the hinge loss of two arrays. Both arrays must be one-dimensional and contain float or double values. NULL values do not contribute to the sum. |
| array sum | array\_sum(array) | Returns the sum of the array. The array must be one-dimensional and contain numeric values. NULL values do not contribute to the sum. |
| array min/max | array\_min(array), array\_max(array) | Returns the corresponding minimum or maximum of the array. This function only considers the first dimension of the array. If the array contains only NULL values, then the function returns NULL. |
| array argmax | array\_argmax(array) | Returns the 1-based position (index) of the maximum element in an array. The first element has index 1.<br /><br />This function only considers the first dimension of the array. If the array contains only NULL values, then the function returns NULL. |
| array argmin | array\_argmin(array) | Returns the 1-based position (index) of the minimum element in an array. The first element has index 1.<br /><br />This function only considers the first dimension of the array. If the array contains only NULL values, then the function returns NULL. |
| array casting | cast\_to\_array(array, format) | Casts the elements of the array to another type, as specified by the format string. The format is: `'ARRAY(INT)'` for an `int` array, `'ARRAY(CHAR)'` for a `char` array, and so on. Currently, only numeric casts are supported. |
| scalar position | array\_position(array, scalar, pos = 1 ) | Returns the position of the first matching scalar in the array. pos indicates the position to start searching, if not specified it defaults to 1. |
| array elements positions | array\_positions(array, array ) | Same as array\_position(). But this looks for all elements stored in the right array and returns their respective positions in the left array. |
| array append | array\_append(array, value) | Add value to the back of an array |
| array prepend | array\_prepend(value, array) | Add value to the front of an array |
| array remove | array\_remove(array, value) | remove value from the array |
| array replace | array\_replace(array, old\_value, new\_value) | replace value by another in an array |
| convert array to string | char(array) | Converts array to its string representation. |
| convert array to string | array\_to\_string(array, delimiter \[, nullstr]) | Converts array to a list of elements separated by 'delimiter'. Nested arrays are flattened. NULL values are either ignored or replaced by *nullstr* if provided. |
| convert string to array | string\_to\_array(str, format) | Converts the string representation of an array (e.g., `'int[1,2,NULL]'`) into an array. The format string is the same as in cast\_to\_array. |

### Function Examples

| **Function** | **SQL statement** | **Result** |
| - | - | - |
| type constructor | int\[]\(1,2) | int array with values 1 and 2 |
| constructor with no type | array\[1,2] | bigint array with values 1 and 2 |
| array\_cat | array\_cat(array\[2, 3], array\[4, 5]) | array\[2, 3, 4, 5] |
| array\_length | array\_length(array\[2, 3]) | 2 |
| cast\_to\_array | cast\_to\_array(array\[2.5, 3.4], 'ARRAY(INT)') | int\[]\(2,3) |
| array\_position | array\_position(array\[1, 2, 1], 1) | 1 |
| | array\_position(array\[1, 2, 3, 4, 1], 1, 3) | 5 |
| array\_positions | array\_positions(array\[1, 2, 3, 4, 1], 1) | int\[]\(1,5) |
| array\_append | array\_append(array\[1,2], 3) | array\[1,2,3] |
| array\_prepend | array\_prepend(0, array\[1,2]) | array\[0,1,2] |
| array\_remove | array\_remove(array\[1,2], 1) | array\[2] |
| array\_replace | array\_replace(array\[1,2,4],4,3) | array\[1,2,3] |
| softmax | softmax(float\[]\(2.0, 2.0, 2.0, NULL, 2.0)) | float\[0.25, 0.25, 0.25, 0.0, 0.25] |
| array\_sum | array\_sum(int\[]\(1, 2, 3)) | 6 |
| array\_min | array\_min(int\[]\(1, -2, 3)) | -2 |
| array\_argmax | array\_argmax(int\[]\(1, 3, 2)) | 2 |
| array\_argmin | array\_argmin(int\[]\(1, 3, 2)) | 1 |
| char(\<array value>) | char(array\[1,2]) | 'bigint\[1,2]' |
| array\_to\_string | array\_to\_string(int\[1,2,3,NULL,5], ',', '\*') | '1,2,3,\*,5' |
| string\_to\_array | string\_to\_array('int\[1,2]', 'ARRAY(INT)') | int\[]\(1,2) |

## Array Operators

Ocient array operators allow you to concatenate and check for containment or overlap of data. Also, you can retrieve specific elements or slices within arrays.

<Warning>
  Do not use array operators, including `@>`, `<@`, and `&&`, to evaluate `NULL` values in arrays. For information on how to check for `NULL` values in arrays, see [Array NULL Handling](#array-null-handling).
</Warning>

### Contains Operator (`@>`)

The `@>` operator determines whether a left-side array contains a scalar value or array elements on the right side.

`@>` **Syntax**

```sql SQL theme={null}
array @> scalar_or_array
```

| **Argument** | **Data** **Type** | **Description** |
| - | - | - |
| `array` | `ARRAY` | An array column or expression. |
| `scalar_or_array` | `ARRAY` or any numeric, character, time, or geospatial data type. | A value or an array of values to check whether they are contained in the array. For data types, see [Understanding Data Types](/understanding-data-types). |

**Examples**

These examples use the `@>` operator to test whether the left-side array contains all the elements from the right-side array.

**Array Containment (True Case)**

This example returns `true` because all right-side elements are in the left-side array.

```sql SQL theme={null}
SELECT ARRAY[1, 4, 3] @> ARRAY[3, 1];
```

*Output:* `true`

**Array Containment (False Case)**

If the right-side array contains at least one value not present in the left-side array, the query returns `false`. In this example, the right-side array has one value, `5`, not present on the left side.

```sql SQL theme={null}
SELECT ARRAY[1, 4, 3] @> ARRAY[3, 1, 5];
```

*Output:* `false`

**Scalar Containment in an Array**

This example checks whether a single scalar value is in the left-side array.

```sql SQL theme={null}
SELECT ARRAY[3, 1, 3] @> 1;
```

*Output:* `true`

### Contained In Operator (`<@`)

The `<@` operator checks whether a right-side array contains all the elements on the left side.

**Syntax**

```sql SQL theme={null}
scalar_or_array <@ array
```

| **Argument** | **Data** **Type** | **Description** |
| - | - | - |
| `array` | `ARRAY` | An array column or expression. |
| `scalar_or_array` | `ARRAY` or any numeric, character, time, or geospatial data type. | A value or an array of values to check whether they are contained in the array. For data types, see [Understanding Data Types](/understanding-data-types). |

**Examples**

**Array Contained Within Another Array (True Case)**

This example returns `true` because all left-side elements are in the right-side array.

```sql SQL theme={null}
SELECT VARCHAR[]('apples', 'oranges') <@ VARCHAR[]('apples', 'oranges', 'bananas')
```

*Output:* `true`

**String Membership Check**

`<@` and other array operators can check whether individual strings are present in an array. The `'oranges'` string is present in the right-side array.

```sql SQL theme={null}
SELECT 'oranges' <@ VARCHAR[]('apples', 'oranges', 'bananas')
```

*Output:* `true`

### Overlap Operator (`&&`)

The overlap operator `&&` determines whether any elements between two arrays are common.

**Syntax**

```sql SQL theme={null}
array1 && array2
```

| **Argument** | **Data** **Type** | **Description** |
| - | - | - |
| `array1` | `ARRAY` | An array column or expression to check whether any of its values are shared with `array2`. |
| `array2` | `ARRAY` | An array column or expression to check whether any of its values are shared with `array1`. |

**Examples**

**Check Overlap Between Arrays (True Case)**

As long as at least one value is present in both arrays, the `&&` operator returns `true`. Both of these arrays contain the value `3`.

```sql SQL theme={null}
SELECT ARRAY[1 ,2, 3] && ARRAY[3 ,4, 5];
```

*Output:* `true`

**Check Overlap Between Arrays (False Case)**

Both of these arrays have no values in common, so the `&&` operator returns `false`.

```sql SQL theme={null}
SELECT ARRAY[1 ,2, 3] && ARRAY[4 ,5, 6];
```

*Output:* `false`

### Slice Operator (`:`)

The slice operator `:` returns a subarray ranging from a left index to a right index, both of which are optional to specify. If you exclude both indexes, the slice operator returns the full array.

**Syntax**

```sql SQL theme={null}
array[left_index:right_index]
```

| **Argument** | **Data** **Type** | **Description** |
| - | - | - |
| `array` | `ARRAY` | An array column or expression. |
| `left_index` | `INT` | Optional.<br /><br />A starting index for the slice operator to begin the subarray. Index values start at `1`.<br /><br />If you do not specify a left index, or if this value is less than zero, then the slice starts at index `1`. |
| `right_index` | `INT` | Optional. <br /><br />An ending index for the slice operator to end the subarray. Index values start at `1`.<br /><br />If you do not specify a right index or if the value exceeds the array length, the right index defaults to the array length. |

The slice operator also follows these rules:

* Each index starts at `1`.
* If the array or either index value is NULL, the result of slicing is NULL.
* Ranges completely out of array bounds return an empty array. For example, `array[1, 2][4:5] = []`.
* Sequential slices slice each dimension of multidimensional arrays. For example, `ARRAY[ARRAY[1, 2, 3], ARRAY[4, 5, 6]][1:1][2:3] = ARRAY[ARRAY[2,3]]`.
* More than N sequential slices of an N-dimensional array, for example, three sequential slices on a two-dimensional array return an empty array. For example, `ARRAY[1, 2, 3][:][:] = []`.
* You cannot combine slicing with the access operator when slicing multidimensional arrays. Any access operator `[n]` converts to `[:n]`. For example, `Array_Val[4][1:6]` would be equivalent to `Array_Val[:4][1:6]`.
* Slicing an array of tuples such as `ARRAY[TUPLE<<INT,INT>>(1,2), TUPLE<<INT,INT>>(3,4)][1:1][2:3]` slices `[1:1]` on the array and `[2:3]` on the tuple elements within the sliced array, and returns the value `TUPLE<<INT>>[TUPLE<<INT>>(2)]`.

**Examples**

**Basic Array Slicing**

Slice an array with four numbers starting at index `2` and ending at index `3`.

```sql SQL theme={null}
SELECT ARRAY[1 ,2, 3, 4][2:3];
```

\*Output: \*`['2','3']`

**Array Slicing With No Right Index**

The query returns all values after the second value because it does not specify an ending index.

```sql SQL theme={null}
SELECT ARRAY[1 ,2, 3, 4][2:];
```

\*Output: \*`['2','3','4']`

**Array Slicing With No Left Index**

This query captures a subarray starting at the first index because it does not specify a starting value.

```sql SQL theme={null}
SELECT ARRAY[1 ,2, 3, 4][:3];
```

\*Output: \*`['1','2','3']`

## Array NULL Handling

Filtering with array functions can have different outcomes if they operate on an array containing NULL values or a NULL value of array type.

Array comparison operators, such as `@>`, `<@`, and `&&`, do not follow normal Boolean logic when evaluating NULL values. To evaluate arrays for NULL values, use the filter functions `FOR_ALL()` or `FOR_SOME()`. For details about these functions, see [Array Filters](/data-query-language-dql-statement-reference#array-filters).

**Examples**

**Evaluate a Single Array With No NULL Values**

This example evaluates to `false` because none of the array values are NULL.

```sql SQL theme={null}
SELECT FOR_SOME(ARRAY[1, 2, 3]) IS NULL;
```

\*Output: \*`false`

**Evaluate a Single Array With NULL Values**

This example evaluates to `true` because the array contains a NULL value.

```sql SQL theme={null}
SELECT FOR_SOME(ARRAY[1, 2, NULL]) IS NULL;
```

\*Output: \*`true`

**Select Only Arrays Containing NULL Values**

To further demonstrate array NULL behavior, these examples use this table loaded with a few array values, some of which are NULL or contain NULL values.

```sql SQL theme={null}
CREATE TABLE demo_array_table (
    id INT,
    tags VARCHAR[]
);

INSERT INTO demo_array_table (id, tags) VALUES
    (1, VARCHAR[]('alpha', 'beta', 'gamma')),               -- Regular array
    (2, VARCHAR[]('delta', NULL, 'epsilon')),               -- Array containing a NULL element
    (3, NULL),                                              -- Entirely NULL array
    (4, VARCHAR[]()),                                       -- Empty array
    (5, VARCHAR[](NULL, NULL));                             -- Array containing all NULL elements
```

In this example, the query uses a `FOR_SOME` function to filter the rows to return only arrays with NULL values. The output does not include the third row of the table because the row itself is NULL, and is not an array containing NULL values.

```sql SQL theme={null}
SELECT id, tags FROM demo_array_table
    WHERE FOR_SOME(tags) IS NULL;
```

*Output*

```sql SQL theme={null}
| id | tags                             |
|----|----------------------------------|
|  2 | ['delta', NULL, 'epsilon']       |
|  5 | [NULL, NULL]                     |
```

**Select NULL Values or Arrays Containing NULL Values**

This example includes an additional filter to include any NULL rows and arrays with NULL values.

The example uses the `demo_array_table` table, which has values that are NULL or contain NULL values.

```sql SQL theme={null}
SELECT id, tags FROM demo_array_table
    WHERE FOR_SOME(tags) IS NULL
    OR tags IS NULL;
```

*Output*

```sql SQL theme={null}
| id | tags                         |
|----|------------------------------|
|  3 | NULL                         |
|  5 | [NULL, NULL]                 |
|  2 | ['delta', NULL, 'epsilon']   |
```

**Select Arrays Without NULL Values**

This example filters the rows using the `FOR_ALL` filter function to return only arrays with no NULL values. The output also contains rows with empty arrays.

The example uses the `demo_array_table` table, which has values that are NULL or contain NULL values.

```sql SQL theme={null}
SELECT id, tags FROM demo_array_table
    WHERE FOR_ALL(tags) IS NOT NULL;
```

*Output*

```sql SQL theme={null}
| id | tags                          |
|----|-------------------------------|
|  4 | []                            |
|  1 | ['alpha', 'beta', 'gamma']    |
```

**Select Only Non-Empty Arrays Without NULL Values**

To omit empty arrays, you can add a filter to check the array length. The `ARRAY_LENGTH` function removes any empty arrays.

This example uses the `demo_array_table` table, which has values that are NULL or contain NULL values.

```sql SQL theme={null}
SELECT id, tags FROM demo_array_table
    WHERE FOR_ALL(tags) IS NOT NULL
    AND ARRAY_LENGTH(tags) > 0;
```

*Output*

```sql SQL theme={null}
| id | tags                          |
|----|-------------------------------|
|  1 | ['alpha', 'beta', 'gamma']    |
```

## Multidimensional Arrays

Multidimensional arrays are supported. As seen in these examples, each dimension, or length, at each level does not have to be the same. NULL values are allowed at each dimension of an array.

**Multidimensional Examples**

| **Function** | **SQL statement** | **Result** |
| - | - | - |
| type constructor two-dimensional | `int[][](int[](1, NULL), array[1])` | two-dimensional `INT` array with values `[1, NULL]` and `[1]` |
| constructor with no type two-dimensional | `array[int[]\(1), array[int(2)]]` | two-dimensional `INT` array with values `[1]` and `[2]` |
| two-dimensional array with NULL values | `array[array[1, NULL], NULL]` | two-dimensional `BIGINT` array with values `[1, NULL]` and `NULL` |
| two-dimensional array with different lengths | `array[array[1], array[1,2], array[1,2,3]]` | two-dimensional `BIGINT` array with values `[1]`, `[1,2]`, and `[1,2,3]` |

## Related Links

[Matrix Functions and Operators](/matrix-functions-and-operators)

[Tuple Functions and Operators](/tuple-functions-and-operators)

[Math Functions and Operators](/math-functions-and-operators)

[Array Data Transformation Functions](/transform-data-in-data-pipelines#array-data-transformation-functions)

[Data Types](/data-types)

[Data Types for Data Pipelines](/data-types-for-data-pipelines)

[Query Ocient](/query-ocient)