# Array functions

> 95 Array functions in NYXDB: names, aliases, signatures and behaviour.

{/* AUTO-GENERATED from the function catalog. DO NOT EDIT — overwritten on every build. */}

95 functions. Names resolve case-insensitively; an alias binds the same function as its canonical name. Each entry gives the signatures, the behaviour and an example.

| Function | Summary |
| --- | --- |
| [`array_all`](#array_all) | Tests whether every position satisfies a Boolean array or aligned predicate. |
| [`array_avg`](#array_avg) | Computes the arithmetic mean of numeric sequence entries or mapped measurements. |
| [`array_build`](#array_build) | Constructs a homogeneous sequence from a variadic list of compatible values. |
| [`array_compact`](#array_compact) | Collapses consecutive repeated entries while preserving separated occurrences. |
| [`array_concat`](#array_concat) | Joins two to sixteen compatible sequences end to end in argument order. |
| [`array_count`](#array_count) | Tallies true Boolean entries or successful tests at aligned array positions. |
| [`array_count_equal`](#array_count_equal) | Counts occurrences of a searched value using NULL-safe structural comparison. |
| [`array_cum_sum`](#array_cum_sum) | Produces running totals at successive positions within one numeric sequence. |
| [`array_cum_sum_non_negative`](#array_cum_sum_non_negative) | Tracks a running balance that resets to zero whenever a step would become negative. |
| [`array_difference`](#array_difference) | Calculates successive numeric deltas, beginning with a typed zero. |
| [`array_dims`](#array_dims) | Formats the complete rectangular shape as concatenated one-based bounds. |
| [`array_distinct`](#array_distinct) | Keeps the earliest occurrence of each structurally equal sequence member. |
| [`array_dot_product`](#array_dot_product) | Sums pairwise products from two equal-length numeric vectors. |
| [`array_element`](#array_element) | Reads a one-based or backward-counted element, using its type default outside bounds. |
| [`array_element_or_null`](#array_element_or_null) | Looks up a position and reports NULL for zero, missing positions or absent input. |
| [`array_empty_to_single`](#array_empty_to_single) | Replaces an empty sequence with one element containing the descriptor default. |
| [`array_enumerate`](#array_enumerate) | Generates ordinal positions for every slot, including NULL-valued slots. |
| [`array_enumerate_dense`](#array_enumerate_dense) | Assigns first-seen group numbers to repeated values without gaps. |
| [`array_enumerate_dense_ranked`](#array_enumerate_dense_ranked) | Labels distinct nested values with dense ranks at selected traversal depths. |
| [`array_enumerate_uniq`](#array_enumerate_uniq) | Numbers each value occurrence within its own equality group. |
| [`array_enumerate_uniq_ranked`](#array_enumerate_uniq_ranked) | Counts repeated leaf encounters through nested arrays with configurable resets. |
| [`array_except`](#array_except) | Removes left-hand values found in any exclusion sequence, retaining surviving duplicates. |
| [`array_exists`](#array_exists) | Detects whether at least one Boolean entry or predicate evaluation succeeds. |
| [`array_fill`](#array_fill) | Carries the last accepted value forward across failed predicate positions. |
| [`array_filter`](#array_filter) | Keeps source entries whose corresponding lambda evaluation is true. |
| [`array_first`](#array_first) | Finds the earliest qualifying entry, falling back to the element type default. |
| [`array_first_index`](#array_first_index) | Locates the earliest successful predicate position, using zero when absent. |
| [`array_first_or_null`](#array_first_or_null) | Retrieves the leading matching value with a nullable result for unsuccessful searches. |
| [`array_flatten`](#array_flatten) | Removes all nested ARRAY layers while retaining leaf traversal order. |
| [`array_fold`](#array_fold) | Combines successive elements into an accumulator initialized by an explicit seed. |
| [`array_has`](#array_has) | Checks structural membership of a value, including an explicitly searched NULL. |
| [`array_has_all`](#array_has_all) | Tests set containment of every requested member within a source sequence. |
| [`array_has_any`](#array_has_any) | Reports whether two sequences overlap in at least one distinct member. |
| [`array_has_substr`](#array_has_substr) | Recognizes an ordered contiguous subsequence inside another sequence. |
| [`array_index_of`](#array_index_of) | Returns the first one-based location of an equal value, or zero if missing. |
| [`array_intersect`](#array_intersect) | Retains unique members shared by every supplied sequence. |
| [`array_jaccard_index`](#array_jaccard_index) | Measures set overlap as shared distinct members divided by their combined membership. |
| [`array_join`](#array_join) | Expands sequence members into individual relational rows. |
| [`array_join_string`](#array_join_string) | Builds text by placing a delimiter between non-NULL string elements. |
| [`array_last`](#array_last) | Chooses the final qualifying entry, returning a typed default if none qualifies. |
| [`array_last_index`](#array_last_index) | Identifies the rightmost true predicate location as an ordinal or zero. |
| [`array_last_or_null`](#array_last_or_null) | Fetches the trailing successful match, leaving unsuccessful lookups NULL. |
| [`array_length`](#array_length) | Counts outer elements or reports a uniform requested dimension size. |
| [`array_lower`](#array_lower) | Reports the lower index of an existing uniform nonempty dimension. |
| [`array_map`](#array_map) | Transforms aligned entries into an output sequence using a typed lambda. |
| [`array_max`](#array_max) | Selects the greatest nonmissing entry or mapped ordering value. |
| [`array_min`](#array_min) | Finds the least available element after an optional numeric or ordering transformation. |
| [`array_ndims`](#array_ndims) | Inspects descriptor nesting depth even when runtime contents are empty or ragged. |
| [`array_normalized_gini`](#array_normalized_gini) | Returns predicted Gini, ideal-label Gini and their normalized ratio. |
| [`array_partial_reverse_sort`](#array_partial_reverse_sort) | Places the largest requested number of entries at the front in descending order. |
| [`array_partial_shuffle`](#array_partial_shuffle) | Randomizes an unbiased prefix while leaving the remaining ordering unspecified. |
| [`array_partial_sort`](#array_partial_sort) | Orders a limited leading selection from smallest to greatest. |
| [`array_pop_back`](#array_pop_back) | Drops the final sequence entry while preserving the preceding prefix. |
| [`array_pop_front`](#array_pop_front) | Removes the leading entry and returns the remaining suffix. |
| [`array_positions`](#array_positions) | Lists every matching ordinal in encounter order for a searched value. |
| [`array_pr_auc`](#array_pr_auc) | Measures precision-recall area from prediction scores and binary outcome labels. |
| [`array_product`](#array_product) | Multiplies available numeric entries or lambda outputs into a floating result. |
| [`array_push_back`](#array_push_back) | Appends one compatible item after the existing sequence tail. |
| [`array_push_front`](#array_push_front) | Prepends a supplied item before all existing entries. |
| [`array_random_sample`](#array_random_sample) | Draws a uniform sample without replacement from available positions. |
| [`array_range`](#array_range) | Constructs a half-open integer progression from start, end and step. |
| [`array_reduce`](#array_reduce) | Evaluates a bound aggregate over aligned elements within one row. |
| [`array_reduce_in_ranges`](#array_reduce_in_ranges) | Calculates one aggregate result for each clipped start-and-length interval. |
| [`array_remove`](#array_remove) | Deletes every NULL-safe match while keeping survivors in their original order. |
| [`array_replace`](#array_replace) | Substitutes all matching elements without changing sequence length or ordering. |
| [`array_resize`](#array_resize) | Truncates a sequence or extends its tail to a requested cardinality. |
| [`array_reverse`](#array_reverse) | Mirrors element order so the final entry becomes the first. |
| [`array_reverse_fill`](#array_reverse_fill) | Carries the next accepted value backward over predicate failures. |
| [`array_reverse_sort`](#array_reverse_sort) | Stably orders all entries by descending values or descending lambda keys. |
| [`array_reverse_split`](#array_reverse_split) | Partitions a sequence immediately after positions accepted by a predicate. |
| [`array_roc_auc`](#array_roc_auc) | Computes receiver-operating-characteristic area from ranked scores and binary outcomes. |
| [`array_rotate_left`](#array_rotate_left) | Cycles leading entries to the tail by a signed rotation distance. |
| [`array_rotate_right`](#array_rotate_right) | Moves trailing entries around to the front with modular displacement. |
| [`array_shift_left`](#array_shift_left) | Discards leading positions and fills newly vacant tail slots. |
| [`array_shift_right`](#array_shift_right) | Slides existing entries toward the tail and pads the exposed front. |
| [`array_shingles`](#array_shingles) | Builds overlapping contiguous windows of a fixed positive width. |
| [`array_shuffle`](#array_shuffle) | Produces a uniform random permutation of all input positions. |
| [`array_slice`](#array_slice) | Extracts a clipped segment using signed offsets and optional length rules. |
| [`array_sort`](#array_sort) | Arranges a complete sequence stably from lowest key to highest. |
| [`array_split`](#array_split) | Starts a new consecutive segment before each true predicate position. |
| [`array_sum`](#array_sum) | Totals non-NULL numeric elements using the SQL aggregate widening rules. |
| [`array_symmetric_difference`](#array_symmetric_difference) | Keeps unique values absent from at least one participating sequence. |
| [`array_trim_strings`](#array_trim_strings) | Strips selected edge characters from each text element without altering positions. |
| [`array_union`](#array_union) | Combines distinct members across all inputs in first-encounter order. |
| [`array_uniq`](#array_uniq) | Counts distinct scalar members or aligned tuples across parallel sequences. |
| [`array_upper`](#array_upper) | Finds the highest valid index for a uniform requested nesting dimension. |
| [`array_with_constant`](#array_with_constant) | Repeats a supplied value to construct a sequence of the requested length. |
| [`array_zip`](#array_zip) | Combines same-position entries from equally sized inputs into ordered tuples. |
| [`array_zip_unaligned`](#array_zip_unaligned) | Pairs parallel entries through the longest input, padding missing fields with NULL. |
| [`group_array`](#group_array) | Collects nonmissing group observations into a sequence with duplicates intact. |
| [`group_array_array`](#group_array_array) | Flattens grouped input sequences into one accumulated list of their members. |
| [`group_uniq_array`](#group_uniq_array) | Accumulates the unique non-NULL values observed across a group. |
| [`max_k`](#max_k) | Selects the K greatest readings, sorted from highest downward without deduplication. |
| [`min_k`](#min_k) | Returns the smallest K observations in ascending order, retaining repeated minima. |
| [`unnest`](#unnest) | Exposes collection contents as lateral rows with optional one-based ordinality. |

## array_all

Tests whether every position satisfies a Boolean array or aligned predicate.

```text
array_all(array(bool)) -> bool
array_all(lambda(T1,...)->bool,array(T1),...) -> bool
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/variadic+generic+lambda` |
| Returns | `bool` |
| Aliases | `arrayAll` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL outer array gives NULL; NULL predicate result is false |

Returns true only when every Boolean element or lambda result is true; NULL predicate results count as false, and an empty array returns true. A NULL outer array returns NULL. Lambda arity must match the number of equal-length arrays, with TYPE_MISMATCH for incompatible argument types and INVALID_ARGUMENT for length disagreement.

**Example**

```sql
array_all(x -> x > 0, [2, 5])
-- → true
```

## array_avg

Computes the arithmetic mean of numeric sequence entries or mapped measurements.

```text
array_avg(array(T:number)) -> float64
array_avg(lambda(T1,...)->R:number,array(T1),...) -> float64
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/variadic+generic+lambda` |
| Returns | `float64` |
| Aliases | `arrayAvg` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL outer array gives NULL; NULL numeric elements skipped |

Averages the non-NULL numeric elements, or numeric results produced by a lambda, and returns float64. Empty and all-NULL arrays return NULL, as does a NULL outer array. The numeric accumulator uses SQL sum widening; nonnumeric inputs raise TYPE_MISMATCH and checked accumulator overflow raises NUMERIC_OVERFLOW.

**Example**

```sql
array_avg([2, 4, 9])
-- → 5.0
```

## array_build

Constructs a homogeneous sequence from a variadic list of compatible values.

```text
array_build(T,...) -> array(T)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/variadic+generic` |
| Returns | `array(T)` |
| Aliases | `array` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | construct array and retain explicit NULL elements |

Creates an array whose element descriptor is the common lossless type of the supplied arguments. Explicit NULL elements are retained with nullable element metadata, and an empty construction requires an expected ARRAY type. Arguments without a common lossless descriptor raise TYPE_MISMATCH; the constructor is distinct from ARRAY literal syntax.

**Example**

```sql
array_build(2, 4, 6)
-- → [2, 4, 6]
```

## array_compact

Collapses consecutive repeated entries while preserving separated occurrences.

```text
array_compact(array(T)) -> array(T)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/unary+generic` |
| Returns | `array(T)` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Scans the array in order and retains only the first element of each adjacent equality run. NULL elements use NULL-safe equality, so consecutive NULLs collapse to one, while a NULL outer array returns NULL. Empty arrays remain empty, and element types lacking the required equality capability raise TYPE_MISMATCH.

**Example**

```sql
array_compact([1, 1, 2, 1])
-- → [1, 2, 1]
```

## array_concat

Joins two to sixteen compatible sequences end to end in argument order.

```text
array_concat(array(T),array(T),...) -> array(T)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/variadic+generic` |
| Returns | `array(T)` |
| Aliases | `arrayConcat` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Appends every input array in argument order without removing duplicates or element NULLs. A NULL outer input propagates NULL, whereas empty arrays contribute no positions. The arrays need a common element descriptor or TYPE_MISMATCH is raised; exceeding admitted output element or byte bounds raises RESOURCE_LIMIT.

**Example**

```sql
array_concat([1, 2], [3])
-- → [1, 2, 3]
```

## array_count

Tallies true Boolean entries or successful tests at aligned array positions.

```text
array_count(array(bool)) -> uint64
array_count(lambda(T1,...)->bool,array(T1),...) -> uint64
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/variadic+generic+lambda` |
| Returns | `uint64` |
| Aliases | `arrayCount` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL outer array gives NULL; NULL predicates are false |

Returns the number of true elements, or positions where the bound lambda evaluates to true, as uint64. NULL predicates are false, an empty array yields zero, and a NULL outer argument returns NULL. Lambda arguments must have compatible types and equal lengths; failures raise TYPE_MISMATCH or INVALID_ARGUMENT respectively.

**Example**

```sql
array_count(x -> x > 3, [1, 4, 6])
-- → 2
```

## array_count_equal

Counts occurrences of a searched value using NULL-safe structural comparison.

```text
array_count_equal(array(T),T) -> uint64
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+generic` |
| Returns | `uint64` |
| Aliases | `count_equal` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL outer array gives NULL; searched NULL matches NULL elements |

Examines every element and counts matches to the supplied value using NULL-safe equality, including nested structural equality. Searching for NULL therefore counts NULL elements, but a NULL outer array returns NULL. An empty array yields zero, and incompatible descriptors or missing equality capability raise TYPE_MISMATCH.

**Example**

```sql
array_count_equal([2, 4, 2], 2)
-- → 2
```

## array_cum_sum

Produces running totals at successive positions within one numeric sequence.

```text
array_cum_sum(array(T:number)) -> array(sum_type(T))
array_cum_sum(lambda(T1,...)->R:number,array(T1),...) -> array(sum_type(R))
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/variadic+generic+lambda` |
| Returns | `array of SQL SUM widened descriptor of input element or lambda result` |
| Aliases | `arrayCumSum` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL outer array gives NULL; NULL element yields NULL at that position and leaves running total unchanged |

Emits a widened running sum after each numeric element or mapped lambda result, with no state retained between rows. A NULL element produces NULL at its position without changing the accumulator; a NULL outer array returns NULL and an empty array stays empty. Arithmetic that cannot fit the SQL sum result descriptor raises NUMERIC_OVERFLOW.

**Example**

```sql
array_cum_sum([2, -5, 4])
-- → [2, -3, 1]
```

## array_cum_sum_non_negative

Tracks a running balance that resets to zero whenever a step would become negative.

```text
array_cum_sum_non_negative(array(T:number)) -> array(sum_type(T))
array_cum_sum_non_negative(lambda(T1,...)->R:number,array(T1),...) -> array(sum_type(R))
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/variadic+generic+lambda` |
| Returns | `array of SQL SUM widened descriptor of input element or lambda result` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL outer array gives NULL; NULL element yields NULL at that position and leaves running total unchanged |

Adds each numeric input or lambda result to the previous balance and clamps that balance to zero after every step. NULL elements emit NULL without changing the balance, and NULL outer arrays propagate NULL. The accumulator uses SQL sum widening, checked overflow raises NUMERIC_OVERFLOW, and calculation is confined to the current row.

**Example**

```sql
array_cum_sum_non_negative([2, -5, 4])
-- → [2, 0, 4]
```

## array_difference

Calculates successive numeric deltas, beginning with a typed zero.

```text
array_difference(array(T:number)) -> array(difference_type(T))
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/unary+generic` |
| Returns | `array(difference_type(T))` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL outer array gives NULL; adjacent NULL operand yields NULL difference |

Returns zero for the first position and subtracts each preceding element from its successor for subsequent positions. An adjacent NULL operand makes that delta NULL, and a NULL outer array returns NULL; empty input yields an empty result. Unsigned integers widen to preserve signed differences, while unrepresentable uint256 subtraction raises NUMERIC_OVERFLOW.

**Example**

```sql
array_difference([3, 8, 6])
-- → [0, 5, -2]
```

## array_dims

Formats the complete rectangular shape as concatenated one-based bounds.

```text
array_dims(values:array(T)) -> nullable(string)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/unary+generic` |
| Returns | `nullable(string)` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Returns text such as \[1:3]\[1:2] when every nested dimension is nonempty, rectangular and free of NULL subarrays. Empty, ragged or partially absent shapes return NULL, as does a NULL outer array; scalar leaf NULLs do not invalidate shape. Bounds always start at one and represent the nested ARRAY value rather than stored lower-bound metadata.

**Example**

```sql
array_dims([[1, 2], [3, 4]])
-- → '[1:2][1:2]'
```

## array_distinct

Keeps the earliest occurrence of each structurally equal sequence member.

```text
array_distinct(array(T)) -> array(T)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/unary+generic` |
| Returns | `array(T)` |
| Aliases | `arrayDistinct` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Removes repeated elements using NULL-safe structural equality and preserves the first occurrence order. Multiple NULL elements become one retained NULL, while a NULL outer array returns NULL. Empty input remains empty, and the element descriptor must support equality or binding raises TYPE_MISMATCH.

**Example**

```sql
array_distinct([3, 1, 3, 2, 1])
-- → [3, 1, 2]
```

## array_dot_product

Sums pairwise products from two equal-length numeric vectors.

```text
array_dot_product(array(T:number),array(U:number)) -> product_sum_type(T,U)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+generic` |
| Returns | `product_sum_type(T,U)` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL outer array gives NULL; any NULL pair yields NULL |

Multiplies corresponding numeric elements and accumulates their products in the checked widened product-sum descriptor. A NULL outer array or any NULL element pair makes the result NULL; two empty arrays yield typed zero. Unequal lengths raise INVALID_ARGUMENT, incompatible numeric types raise TYPE_MISMATCH, and unrepresentable arithmetic raises NUMERIC_OVERFLOW.

**Example**

```sql
array_dot_product([2, 3], [4, 5])
-- → 23
```

## array_element

Reads a one-based or backward-counted element, using its type default outside bounds.

```text
array_element(array(T),int64) -> T
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+generic` |
| Returns | `T` |
| Aliases | `arrayElement` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL outer array or index gives NULL; out-of-range gives default(T) |

Selects an element using a positive one-based index or a negative index counted from the end. A NULL array or index returns NULL, while an index beyond either end returns default(T), which follows the element descriptor. Index zero raises INVALID_ARGUMENT; use the nullable lookup when absent positions should return NULL.

**Example**

```sql
array_element([10, 20, 30], -1)
-- → 30
```

## array_element_or_null

Looks up a position and reports NULL for zero, missing positions or absent input.

```text
array_element_or_null(array(T),int64) -> T
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+generic` |
| Returns | `T` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL outer array/index, zero or out-of-range gives NULL |

Retrieves an element by one-based index, with negative indices addressing positions backward from the final element. Zero, out-of-range positions and NULL array or index arguments all produce NULL instead of a type default. The result is nullable even when the array element descriptor is nonnullable, implementing the nullable subscript contract.

**Example**

```sql
array_element_or_null([10, 20], 5)
-- → NULL
```

## array_empty_to_single

Replaces an empty sequence with one element containing the descriptor default.

```text
array_empty_to_single(array(T)) -> array(T)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/unary+generic` |
| Returns | `array(T)` |
| Aliases | `empty_array_to_single` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Returns nonempty arrays unchanged, including their element NULLs and ordering. An empty array becomes a singleton containing default(T), while a NULL outer value remains NULL. The default comes from the already bound element descriptor rather than from implicit conversion of a supplied replacement.

**Example**

```sql
array_empty_to_single(cast([] AS ARRAY(INT64)))
-- → [0]
```

## array_enumerate

Generates ordinal positions for every slot, including NULL-valued slots.

```text
array_enumerate(array(T)) -> array(uint64)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/unary+generic` |
| Returns | `array(uint64)` |
| Aliases | `arrayEnumerate` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Produces the uint64 indices 1 through the input array length, independently of the stored element values. NULL elements still occupy positions; only a NULL outer array returns NULL. An empty input produces an empty index array, and enumeration retains no state across incoming rows.

**Example**

```sql
array_enumerate([8, 8, 3])
-- → [1, 2, 3]
```

## array_enumerate_dense

Assigns first-seen group numbers to repeated values without gaps.

```text
array_enumerate_dense(array(T)) -> array(uint64)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/unary+generic` |
| Returns | `array(uint64)` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Labels each element with the one-based group number assigned when that distinct value first appeared. Equal values share the same number, including NULL elements under NULL-safe equality; a NULL outer array returns NULL. The original shape and position count are preserved, and empty input yields an empty array.

**Example**

```sql
array_enumerate_dense([8, 3, 8, 5])
-- → [1, 2, 1, 3]
```

## array_enumerate_dense_ranked

Labels distinct nested values with dense ranks at selected traversal depths.

```text
array_enumerate_dense_ranked(array(array(T)),max_depth:uint64 const) -> array(array(uint64))
array_enumerate_dense_ranked(clear_depth:uint64 const,array(array(T)),max_depth:uint64 const) -> array(array(uint64))
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/2..3-ary+generic` |
| Returns | `array(array(uint64))` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Traverses nested arrays and replaces leaves with first-occurrence dense group identifiers while preserving recursive shape. The optional clear_depth restarts ranking at that level, and max_depth bounds traversal; depths outside the actual descriptor nesting raise INVALID_ARGUMENT. A NULL outer array returns NULL, and NULL values participating in equality share a rank.

**Example**

```sql
array_enumerate_dense_ranked([[5, 5], [7, 5]], 2)
-- → [[1, 1], [2, 1]]
```

## array_enumerate_uniq

Numbers each value occurrence within its own equality group.

```text
array_enumerate_uniq(array(T)) -> array(uint64)
array_enumerate_uniq(array(T1),array(T2),...) -> array(uint64)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/variadic+generic` |
| Returns | `array(uint64)` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

For each position, returns how many times that value has appeared so far; multiple arrays form positional tuples for comparison. NULL elements compare equal, a NULL outer input returns NULL, and empty input gives an empty result. Parallel arrays must have equal lengths or INVALID_ARGUMENT is raised, and compared descriptors must support equality.

**Example**

```sql
array_enumerate_uniq([8, 3, 8, 8])
-- → [1, 1, 2, 3]
```

## array_enumerate_uniq_ranked

Counts repeated leaf encounters through nested arrays with configurable resets.

```text
array_enumerate_uniq_ranked(array(array(T)),max_depth:uint64 const) -> array(array(uint64))
array_enumerate_uniq_ranked(clear_depth:uint64 const,array(array(T)),max_depth:uint64 const) -> array(array(uint64))
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/2..3-ary+generic` |
| Returns | `array(array(uint64))` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Replaces nested leaves with their occurrence number within the equality group encountered during traversal. Recursive array shape is retained, max_depth limits traversal, and clear_depth can restart counters at a chosen nesting level; invalid depth constants raise INVALID_ARGUMENT. NULL outer input propagates NULL and compared NULL values belong to the same occurrence group.

**Example**

```sql
array_enumerate_uniq_ranked([[5, 5], [7, 5]], 2)
-- → [[1, 2], [1, 3]]
```

## array_except

Removes left-hand values found in any exclusion sequence, retaining surviving duplicates.

```text
array_except(array(T),array(T),...) -> array(T)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/variadic+generic` |
| Returns | `array(T)` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Scans the first array in its original order and keeps only values absent from every later array. Membership uses NULL-safe equality, so an excluded NULL removes matching NULL elements, while a NULL outer argument returns NULL. Repeated surviving values remain repeated; incompatible element descriptors raise TYPE_MISMATCH.

**Example**

```sql
array_except([1, 2, 1, 3], [2, 3])
-- → [1, 1]
```

## array_exists

Detects whether at least one Boolean entry or predicate evaluation succeeds.

```text
array_exists(array(bool)) -> bool
array_exists(lambda(T1,...)->bool,array(T1),...) -> bool
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/variadic+generic+lambda` |
| Returns | `bool` |
| Aliases | `arrayExists` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL outer array gives NULL; NULL predicates are false |

Returns true when any Boolean element or aligned lambda result is true; NULL predicate results do not satisfy the test. Empty arrays yield false, and a NULL outer array yields NULL. Every lambda input array must have matching length, otherwise INVALID_ARGUMENT is raised, and the bound lambda determines invocation restrictions.

**Example**

```sql
array_exists(x -> x > 5, [2, 7, 4])
-- → true
```

## array_fill

Carries the last accepted value forward across failed predicate positions.

```text
array_fill(lambda(T1,...)->bool,array(T1),...) -> array(T1)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/variadic+generic+lambda` |
| Returns | `array(T1)` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL outer array gives NULL; NULL predicate is false |

Scans left to right, keeps the first element, and replaces later failed-predicate positions with the preceding retained value. Predicates inspect original input values and treat NULL as false; a NULL outer array returns NULL and empty input stays empty. Aligned lambda arrays must have equal lengths or INVALID_ARGUMENT is raised.

**Example**

```sql
array_fill(x -> x > 0, [2, 0, 0, 5])
-- → [2, 2, 2, 5]
```

## array_filter

Keeps source entries whose corresponding lambda evaluation is true.

```text
array_filter(lambda(T1,...)->bool,array(T1),...) -> array(T1)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/variadic+generic+lambda` |
| Returns | `array(T1)` |
| Aliases | `arrayFilter` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL outer array gives NULL; NULL predicate is false |

Evaluates the predicate at each aligned position and retains the first array element only where the result is true. NULL predicate results are rejected, a NULL outer array returns NULL, and survivor order is unchanged. Empty input returns an empty array; unequal argument lengths raise INVALID_ARGUMENT and incompatible predicate types raise TYPE_MISMATCH.

**Example**

```sql
array_filter(x -> x > 2, [1, 4, 2, 5])
-- → [4, 5]
```

## array_first

Finds the earliest qualifying entry, falling back to the element type default.

```text
array_first(array(T)) -> T
array_first(lambda(T1,...)->bool,array(T1),...) -> T1
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/variadic+generic+lambda` |
| Returns | `first array argument element descriptor` |
| Aliases | `arrayFirst` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL outer array gives NULL; no matching element gives default(T); NULL predicates are false |

Without a predicate, returns the initial element; with a lambda, returns the first element whose predicate is true. NULL predicates are false, a NULL outer array returns NULL, and empty or unmatched arrays return default(T). Predicate arrays must align in length or INVALID_ARGUMENT is raised, and the result keeps the first array element descriptor.

**Example**

```sql
array_first(x -> x > 4, [2, 7, 9])
-- → 7
```

## array_first_index

Locates the earliest successful predicate position, using zero when absent.

```text
array_first_index(lambda(T1,...)->bool,array(T1),...) -> uint64
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/variadic+generic+lambda` |
| Returns | `uint64` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL outer array gives NULL; NULL predicates are false |

Searches from the beginning and returns the one-based uint64 index of the first true lambda result. NULL predicates never match; empty and unmatched arrays return zero, while a NULL outer input returns NULL. The predicate receives aligned original values, and differing array lengths raise INVALID_ARGUMENT.

**Example**

```sql
array_first_index(x -> x > 4, [2, 7, 9])
-- → 2
```

## array_first_or_null

Retrieves the leading matching value with a nullable result for unsuccessful searches.

```text
array_first_or_null(array(T)) -> T
array_first_or_null(lambda(T1,...)->bool,array(T1),...) -> T1
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/variadic+generic+lambda` |
| Returns | `first array argument element descriptor` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL outer array gives NULL; no matching element gives NULL; NULL predicates are false |

Returns the first element directly or the earliest element satisfying the supplied lambda. If no such element exists, including for empty input, the result is NULL rather than default(T); NULL predicate results are false. NULL outer arrays also return NULL, and unequal parallel lengths raise INVALID_ARGUMENT.

**Example**

```sql
array_first_or_null(x -> x > 9, [2, 7])
-- → NULL
```

## array_flatten

Removes all nested ARRAY layers while retaining leaf traversal order.

```text
array_flatten(array(array(T))) -> array(T)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/unary+generic` |
| Returns | `array(T)` |
| Aliases | `arrayFlatten` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL outer array gives NULL; NULL subarrays contribute no elements; NULL leaf elements retained |

Recursively concatenates leaf elements into a single array rather than removing only one nesting level. NULL subarrays contribute no elements, nullable leaf values remain NULL, and a NULL outer array returns NULL. Empty branches contribute nothing, and the resulting element descriptor comes from the bound leaf type.

**Example**

```sql
array_flatten([[1, 2], [], [3]])
-- → [1, 2, 3]
```

## array_fold

Combines successive elements into an accumulator initialized by an explicit seed.

```text
array_fold(lambda(A,T1,...)->A,array(T1),...,initial:A) -> A
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/variadic+generic+lambda` |
| Returns | `A` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL outer array gives NULL; accumulator and element NULLs are passed to lambda |

Applies the lambda from left to right, passing the current accumulator and each aligned set of element values. The result descriptor must remain exactly the initial accumulator descriptor or TYPE_MISMATCH is raised; NULL accumulator and element values are passed into the lambda. Empty arrays return the initial value, NULL outer arrays return NULL, and unequal lengths raise INVALID_ARGUMENT.

**Example**

```sql
array_fold((total, x) -> total + x, [1, 2, 3], 10)
-- → 16
```

## array_has

Checks structural membership of a value, including an explicitly searched NULL.

```text
array_has(array(T),T) -> bool
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+generic` |
| Returns | `bool` |
| Aliases | `has` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL outer array gives NULL; searched NULL matches NULL elements |

Returns true if any element is NULL-safely equal to the searched value, comparing nested values structurally. NULL searches can match NULL elements, whereas a NULL outer array produces NULL. Empty arrays return false; unavailable equality or incompatible element and search descriptors raise TYPE_MISMATCH.

**Example**

```sql
array_has([2, 4, 6], 4)
-- → true
```

## array_has_all

Tests set containment of every requested member within a source sequence.

```text
array_has_all(array(T),array(T)) -> bool
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+generic` |
| Returns | `bool` |
| Aliases | `has_all` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Returns true when every distinct value in the second array occurs in the first, without requiring matching multiplicities or order. Equality is NULL-safe, an empty requested array is always contained, and a NULL outer argument returns NULL. Element descriptors must support a common equality comparison or TYPE_MISMATCH is raised.

**Example**

```sql
array_has_all([1, 2, 3], [3, 1])
-- → true
```

## array_has_any

Reports whether two sequences overlap in at least one distinct member.

```text
array_has_any(array(T),array(T)) -> bool
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+generic` |
| Returns | `bool` |
| Aliases | `has_any` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Tests for any common element using NULL-safe equality, so NULL in both arrays establishes overlap. A NULL outer array propagates NULL, while an empty array has no overlap and returns false. Input order and duplicate counts do not affect the Boolean result; incompatible equality descriptors raise TYPE_MISMATCH.

**Example**

```sql
array_has_any([1, 2], [2, 5])
-- → true
```

## array_has_substr

Recognizes an ordered contiguous subsequence inside another sequence.

```text
array_has_substr(array(T),array(T)) -> bool
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+generic` |
| Returns | `bool` |
| Aliases | `has_substr` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Returns true when the second array occurs contiguously in the first with the same element order. NULL elements participate through NULL-safe equality, an empty subsequence matches, and a NULL outer argument returns NULL. This operation checks adjacency rather than set containment, and incompatible element descriptors raise TYPE_MISMATCH.

**Example**

```sql
array_has_substr([1, 2, 3, 4], [2, 3])
-- → true
```

## array_index_of

Returns the first one-based location of an equal value, or zero if missing.

```text
array_index_of(array(T),T) -> uint64
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+generic` |
| Returns | `uint64` |
| Aliases | `array_position`, `index_of`, `indexOf` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL outer array gives NULL; searched NULL matches NULL elements |

Scans for the earliest NULL-safe match and returns its one-based index as uint64. A searched NULL matches a NULL element; a NULL outer array returns NULL, and an empty or unmatched array returns zero. Structural element comparison requires compatible equality-capable descriptors or TYPE_MISMATCH is raised.

**Example**

```sql
array_index_of([7, 4, 7], 7)
-- → 1
```

## array_intersect

Retains unique members shared by every supplied sequence.

```text
array_intersect(array(T),array(T),...) -> array(T)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/variadic+generic` |
| Returns | `array(T)` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Computes membership common to all input arrays and emits each surviving value once in first-encounter order. NULL-safe equality treats NULL as a comparable member, while NULL outer arguments return NULL. Any empty input makes the intersection empty; incompatible element descriptors raise TYPE_MISMATCH.

**Example**

```sql
array_intersect([3, 1, 3, 2], [2, 3])
-- → [3, 2]
```

## array_jaccard_index

Measures set overlap as shared distinct members divided by their combined membership.

```text
array_jaccard_index(array(T),array(T)) -> float64
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+generic` |
| Returns | `float64` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Computes the size of the NULL-safe set intersection divided by the size of the union, ignoring duplicate occurrences. Two empty arrays return 1.0, one empty array against a nonempty one returns 0.0, and NULL outer arguments return NULL. Equality must be supported by the common element descriptor or TYPE_MISMATCH is raised.

**Example**

```sql
array_jaccard_index([1, 2], [2, 3])
-- → 0.3333333333333333
```

## array_join

Expands sequence members into individual relational rows.

```text
array_join(array(T)) -> table(T)
```

| | |
| --- | --- |
| Kind | table |
| Signature class | `table/unary+generic` |
| Returns | `table(T)` |
| Aliases | `arrayJoin` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL or empty array emits zero rows |

Emits one output row per element while preserving element NULLs; NULL or empty outer arrays emit no rows. Expansion is stateless across input rows, but output ordinality participates in retraction identity for continuous queries. The admitted maximum_unnest_elements cap applies to each source row and excess expansion raises RESOURCE_LIMIT.

**Example**

```sql
(SELECT * FROM array_join([2, 4]))
-- → [(2), (4)]
```

## array_join_string

Builds text by placing a delimiter between non-NULL string elements.

```text
array_join_string(array(string)) -> string
array_join_string(array(string),delimiter:string) -> string
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/1..2-ary` |
| Returns | `string` |
| Aliases | `array_string_concat`, `array_to_string`, `arrayStringConcat` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL outer array/delimiter gives NULL; NULL elements skipped |

Concatenates string elements in array order, inserting the supplied delimiter only between retained elements. NULL elements are skipped, the omitted delimiter defaults to an empty string, and an empty or all-NULL array returns empty text. A NULL outer array or delimiter returns NULL; non-string elements require explicit conversion or TYPE_MISMATCH is raised.

**Example**

```sql
array_join_string(['red', 'blue'], ', ')
-- → 'red, blue'
```

## array_last

Chooses the final qualifying entry, returning a typed default if none qualifies.

```text
array_last(array(T)) -> T
array_last(lambda(T1,...)->bool,array(T1),...) -> T1
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/variadic+generic+lambda` |
| Returns | `first array argument element descriptor` |
| Aliases | `arrayLast` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL outer array gives NULL; no matching element gives default(T); NULL predicates are false |

Without a predicate, selects the final element; otherwise searches for the rightmost element whose lambda result is true. Empty and unmatched arrays yield default(T), NULL predicates are false, and a NULL outer array returns NULL. The first array defines the result descriptor, and mismatched parallel array lengths raise INVALID_ARGUMENT.

**Example**

```sql
array_last(x -> x > 4, [2, 7, 9])
-- → 9
```

## array_last_index

Identifies the rightmost true predicate location as an ordinal or zero.

```text
array_last_index(lambda(T1,...)->bool,array(T1),...) -> uint64
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/variadic+generic+lambda` |
| Returns | `uint64` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL outer array gives NULL; NULL predicates are false |

Returns the greatest one-based index whose aligned predicate evaluates to true. A NULL predicate does not match, a NULL outer input returns NULL, and empty or unsuccessful searches return zero. Predicate argument types are checked at binding, and inconsistent input lengths raise INVALID_ARGUMENT.

**Example**

```sql
array_last_index(x -> x > 4, [2, 7, 9])
-- → 3
```

## array_last_or_null

Fetches the trailing successful match, leaving unsuccessful lookups NULL.

```text
array_last_or_null(array(T)) -> T
array_last_or_null(lambda(T1,...)->bool,array(T1),...) -> T1
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/variadic+generic+lambda` |
| Returns | `first array argument element descriptor` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL outer array gives NULL; no matching element gives NULL; NULL predicates are false |

Selects the final element or, when a lambda is supplied, the last element for which the lambda is true. The result is always nullable: empty arrays, no qualifying element and NULL outer input all produce NULL. Predicate NULLs count as false, and parallel arrays of different lengths raise INVALID_ARGUMENT.

**Example**

```sql
array_last_or_null(x -> x > 4, [2, 7, 9])
-- → 9
```

## array_length

Counts outer elements or reports a uniform requested dimension size.

```text
array_length(values:array(T)) -> uint64
array_length(values:array(T),dimension:int64) -> nullable(uint64)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/1..2-ary+generic` |
| Returns | `uint64 or nullable(uint64) for an explicit dimension` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL input propagates; nonexistent, empty or ragged explicit dimension returns NULL |

The one-argument form counts outer positions and returns zero for an empty array, while NULL input returns NULL. With a one-based dimension, a length exists only if all reachable subarrays at that level are non-NULL, nonempty and equally sized. Missing dimensions, empty bounds and ragged requested dimensions return NULL instead of inventing a rectangular shape.

**Example**

```sql
array_length([[1, 2, 3], [4, 5, 6]], 2)
-- → 3
```

## array_lower

Reports the lower index of an existing uniform nonempty dimension.

```text
array_lower(values:array(T),dimension:int64) -> nullable(uint64)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+generic` |
| Returns | `nullable(uint64)` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Returns one when the requested one-based dimension has a uniform nonempty bound across all reachable subarrays. NULL input, NULL subarrays, empty or ragged bounds and dimensions outside the descriptor depth return NULL. Nested arrays never store arbitrary lower-bound metadata, so a successful result cannot differ from one.

**Example**

```sql
array_lower([[1, 2], [3, 4]], 2)
-- → 1
```

## array_map

Transforms aligned entries into an output sequence using a typed lambda.

```text
array_map(lambda(T1,...)->R,array(T1),...) -> array(R)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/variadic+generic+lambda` |
| Returns | `array(R)` |
| Aliases | `arrayMap` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL outer array gives NULL; nullable elements are passed to the lambda |

Applies the lambda once per aligned position and collects its results using the descriptor resolved at binding. Nullable elements are passed to the lambda, whose result determines element nullability; any NULL outer array returns NULL. The invocation accepts at most sixteen expression arguments, and incompatible arity or unequal array lengths raise TYPE_MISMATCH or INVALID_ARGUMENT respectively.

**Example**

```sql
array_map(x -> x * 2, [1, 3, 5])
-- → [2, 6, 10]
```

## array_max

Selects the greatest nonmissing entry or mapped ordering value.

```text
array_max(array(T:total_order)) -> T
array_max(lambda(T1,...)->R:total_order,array(T1),...) -> R
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/variadic+generic+lambda` |
| Returns | `input element descriptor T; lambda result descriptor R` |
| Aliases | `arrayMax` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL outer array gives NULL; NULL elements skipped; empty/all-NULL gives NULL |

Returns the largest non-NULL element, or largest lambda result, under the admitted total-order capability. Empty and all-NULL arrays produce NULL, as do NULL outer inputs. Incompatible ordering types raise TYPE_MISMATCH; floating-point NaN handling must be explicitly admitted by the numeric kernel, and parallel lambda arrays must align.

**Example**

```sql
array_max([3, 8, 2])
-- → 8
```

## array_min

Finds the least available element after an optional numeric or ordering transformation.

```text
array_min(array(T:total_order)) -> T
array_min(lambda(T1,...)->R:total_order,array(T1),...) -> R
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/variadic+generic+lambda` |
| Returns | `input element descriptor T; lambda result descriptor R` |
| Aliases | `arrayMin` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL outer array gives NULL; NULL elements skipped; empty/all-NULL gives NULL |

Chooses the smallest non-NULL input value or bound lambda result and retains that value descriptor. A NULL outer array, an empty sequence or an all-NULL result set returns NULL. The descriptor must provide a total order or TYPE_MISMATCH is raised; floating-point NaN comparisons follow the explicitly admitted kernel policy.

**Example**

```sql
array_min([3, 8, 2])
-- → 2
```

## array_ndims

Inspects descriptor nesting depth even when runtime contents are empty or ragged.

```text
array_ndims(values:array(T)) -> uint8
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/unary+generic` |
| Returns | `uint8` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Returns the number of nested ARRAY descriptors as uint8, independently of rectangular shape or element count. Nullable wrappers do not add dimensions, and a NULL outer value returns NULL. Descriptor depth is fixed at binding and must remain within the admitted nesting limit, whose violation raises RESOURCE_LIMIT.

**Example**

```sql
array_ndims([[1], [2, 3]])
-- → 2
```

## array_normalized_gini

Returns predicted Gini, ideal-label Gini and their normalized ratio.

```text
array_normalized_gini(predicted:array(number),labels:array(number)) -> tuple(float64,float64,float64)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary` |
| Returns | `tuple(float64,float64,float64)` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Computes a three-field tuple containing the Gini coefficient under predicted ordering, the ideal label ordering coefficient and their ratio. Prediction and label arrays must have equal lengths or INVALID_ARGUMENT is raised; NULL outer inputs return NULL. If the ideal coefficient makes normalization undefined, the ratio is NULL while the first two components are preserved.

**Example**

```sql
array_normalized_gini([1, 2], [1, 2])
-- → (0.08333333333333333, 0.08333333333333333, 1.0)
```

## array_partial_reverse_sort

Places the largest requested number of entries at the front in descending order.

```text
array_partial_reverse_sort(limit:uint64,array(T:total_order)) -> array(T)
array_partial_reverse_sort(lambda(T1,...)->K:total_order,limit:uint64,array(T1),...) -> array(T1)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/variadic+generic+lambda` |
| Returns | `first array argument descriptor` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Orders the first min(limit, length) positions from greatest to least using input values or a bound lambda key; the remainder has unspecified order. NULL keys sort last and a NULL outer array returns NULL. Keys require total-order support or TYPE_MISMATCH is raised, and unequal parallel array lengths raise INVALID_ARGUMENT.

**Example**

```sql
array_partial_reverse_sort(3, [2, 1, 3])
-- → [3, 2, 1]
```

## array_partial_shuffle

Randomizes an unbiased prefix while leaving the remaining ordering unspecified.

```text
array_partial_shuffle(array(T)) -> array(T)
array_partial_shuffle(array(T),limit:uint64) -> array(T)
array_partial_shuffle(array(T),limit:uint64,seed:uint64) -> array(T)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/1..3-ary+generic` |
| Returns | `array(T)` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Volatile — may differ on every call. |
| NULL handling | Any NULL argument gives NULL. |

Selects a random prefix up to the requested limit, preserving the array length and element multiset. NULL elements are ordinary shuffle candidates, while a NULL outer array returns NULL; a fixed explicit seed makes that invocation reproducible. The form without a seed is volatile, and empty input remains empty.

**Example**

```sql
array_partial_shuffle([7], 1, 42)
-- → [7]
```

## array_partial_sort

Orders a limited leading selection from smallest to greatest.

```text
array_partial_sort(limit:uint64,array(T:total_order)) -> array(T)
array_partial_sort(lambda(T1,...)->K:total_order,limit:uint64,array(T1),...) -> array(T1)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/variadic+generic+lambda` |
| Returns | `first array argument descriptor` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Moves the smallest min(limit, length) entries to the front in ascending key order, without promising an order for the tail. Input values supply keys unless a lambda is provided; NULL keys follow non-NULL keys and a NULL outer input propagates NULL. Missing total-order capability raises TYPE_MISMATCH, and parallel lambda arrays of different lengths raise INVALID_ARGUMENT.

**Example**

```sql
array_partial_sort(3, [2, 1, 3])
-- → [1, 2, 3]
```

## array_pop_back

Drops the final sequence entry while preserving the preceding prefix.

```text
array_pop_back(array(T)) -> array(T)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/unary+generic` |
| Returns | `array(T)` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Returns all input elements except the last one, preserving their order, descriptors and NULL values. An empty array remains empty, a singleton becomes empty, and a NULL outer array returns NULL. Removal is confined to the current value and does not retain cross-row state.

**Example**

```sql
array_pop_back([1, 2, 3])
-- → [1, 2]
```

## array_pop_front

Removes the leading entry and returns the remaining suffix.

```text
array_pop_front(array(T)) -> array(T)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/unary+generic` |
| Returns | `array(T)` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Discards the element at position one and shifts the surviving entries into the result without changing their order or nullability. Empty input yields an empty array, and NULL outer input yields NULL. A one-element sequence therefore produces an empty sequence of the same element descriptor.

**Example**

```sql
array_pop_front([1, 2, 3])
-- → [2, 3]
```

## array_positions

Lists every matching ordinal in encounter order for a searched value.

```text
array_positions(values:array(T),value:T) -> array(uint64)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+generic` |
| Returns | `array(uint64)` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL outer array gives NULL; NULL search value matches NULL elements |

Returns the one-based uint64 indices of all elements IS NOT DISTINCT FROM the search value. NULL search values match NULL elements, unmatched searches produce an empty array, and NULL outer input returns NULL. Nested values compare structurally; a common equality-capable descriptor is required or TYPE_MISMATCH is raised.

**Example**

```sql
array_positions([4, 2, 4, 4], 4)
-- → [1, 3, 4]
```

## array_pr_auc

Measures precision-recall area from prediction scores and binary outcome labels.

```text
array_pr_auc(scores:array(number),labels:array(uint8)) -> float64
array_pr_auc(scores:array(number),labels:array(uint8),partial_offsets:array(uint64)) -> float64
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/2..3-ary` |
| Returns | `float64` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Computes precision-recall area using aligned numeric scores and binary uint8 labels, returning NULL when there are no positive labels. For an admitted score partition, partial_offsets is \[higher_partitions_tp, higher_partitions_fp, total_positives], containing true-positive and false-positive counts from higher-scored partitions and the global positive count. NULL outer arguments propagate NULL. Unequal lengths, labels outside zero and one, or malformed or inconsistent offset counts raise INVALID_ARGUMENT.

**Example**

```sql
array_pr_auc([0.1, 0.9], [0, 1])
-- → 1.0
```

## array_product

Multiplies available numeric entries or lambda outputs into a floating result.

```text
array_product(array(T:number)) -> float64
array_product(lambda(T1,...)->R:number,array(T1),...) -> float64
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/variadic+generic+lambda` |
| Returns | `float64` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL outer array gives NULL; NULL numeric elements skipped |

Computes the product of non-NULL numeric values, optionally obtained by applying a lambda to aligned arrays, and returns float64. The identity for empty or all-NULL input is 1.0, while a NULL outer input returns NULL. Nonnumeric descriptors raise TYPE_MISMATCH and differing lambda-array lengths raise INVALID_ARGUMENT.

**Example**

```sql
array_product([2, 3, 4])
-- → 24.0
```

## array_push_back

Appends one compatible item after the existing sequence tail.

```text
array_push_back(array(T),T) -> array(T)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+generic` |
| Returns | `array(T)` |
| Aliases | `array_append`, `arrayPushBack` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL array gives NULL; NULL item retained only for nullable element type |

Adds the supplied item as the final element and preserves all original entries and their order. A NULL outer array returns NULL, while a NULL item is retained only when the element descriptor is nullable. Incompatible item types raise TYPE_MISMATCH, and output beyond the admitted element or byte cap raises RESOURCE_LIMIT.

**Example**

```sql
array_push_back([1, 2], 3)
-- → [1, 2, 3]
```

## array_push_front

Prepends a supplied item before all existing entries.

```text
array_push_front(array(T),T) -> array(T)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+generic` |
| Returns | `array(T)` |
| Aliases | `array_prepend`, `arrayPushFront` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL array gives NULL; NULL item retained only for nullable element type |

Places the new item at position one and shifts the entire input sequence behind it without changing relative order. NULL outer input produces NULL, and an explicit NULL item requires nullable element metadata. The item must match the bound array descriptor or TYPE_MISMATCH is raised; admitted output size is enforced with RESOURCE_LIMIT.

**Example**

```sql
array_push_front([2, 3], 1)
-- → [1, 2, 3]
```

## array_random_sample

Draws a uniform sample without replacement from available positions.

```text
array_random_sample(array(T),size:uint64) -> array(T)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+generic` |
| Returns | `array(T)` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Volatile — may differ on every call. |
| NULL handling | Any NULL argument gives NULL. |

Selects up to the requested number of array positions without replacement and randomizes the selected output order. Equal-valued entries remain separate candidates because sampling operates on positions, and NULL elements may be selected. NULL outer input returns NULL; requesting more than the input cardinality selects at most the existing entries.

**Example**

```sql
array_random_sample([7], 1)
-- → [7]
```

## array_range

Constructs a half-open integer progression from start, end and step.

```text
array_range(end:int64) -> array(int64)
array_range(start:int64,end:int64) -> array(int64)
array_range(start:int64,end:int64,step:int64) -> array(int64)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/1..3-ary` |
| Returns | `array(int64)` |
| Aliases | `range` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Generates int64 values beginning at start and stopping before end, with omitted start and step defaulting to zero and one. NULL arguments return NULL; zero step or a direction that cannot reach the bound raises INVALID_ARGUMENT. Checked endpoint arithmetic raises NUMERIC_OVERFLOW, and allocation beyond the admitted array limits raises RESOURCE_LIMIT.

**Example**

```sql
array_range(2, 8, 2)
-- → [2, 4, 6]
```

## array_reduce

Evaluates a bound aggregate over aligned elements within one row.

```text
array_reduce(aggregate_name:string const,array(T1),...) -> aggregate_result(aggregate_name,T1,...)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/variadic+generic` |
| Returns | `aggregate_result(aggregate_name,T1,...)` |
| Aliases | `arrayReduce` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL outer array gives NULL; element NULL policy follows selected aggregate |

Resolves the constant aggregate selector, including parameters and combinators, against an accepted aggregate revision at binding. Same-length arrays provide positional arguments, element NULL handling follows that aggregate, and NULL outer input returns NULL. An invalid selector or length mismatch raises INVALID_ARGUMENT; reduction uses only temporary per-value aggregate state and returns the selected aggregate descriptor.

**Example**

```sql
array_reduce('sum', [1, 2, 3])
-- → 6
```

## array_reduce_in_ranges

Calculates one aggregate result for each clipped start-and-length interval.

```text
array_reduce_in_ranges(aggregate_name:string const,ranges:array(tuple(int64,uint64)),array(T1),...) -> array(aggregate_result(aggregate_name,T1,...))
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/variadic+generic` |
| Returns | `array(aggregate_result(aggregate_name,T1,...))` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL outer input gives NULL; element NULL policy follows selected aggregate |

Applies the bound aggregate separately to each range, whose start is one-based and may be negative to count from the end. Ranges clip to available elements, result NULL policy follows the selected aggregate, and NULL outer arguments return NULL. The constant selector must identify an accepted aggregate revision or INVALID_ARGUMENT is raised; all work lowers to temporary shared aggregate IR within the row.

**Example**

```sql
array_reduce_in_ranges('sum', [ (1, 2), (3, 1)], [2, 4, 8])
-- → [6, 8]
```

## array_remove

Deletes every NULL-safe match while keeping survivors in their original order.

```text
array_remove(values:array(T),value:T) -> array(T)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+generic` |
| Returns | `array(T)` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL outer array gives NULL; NULL search removes NULL elements |

Removes all elements IS NOT DISTINCT FROM the search value, retaining duplicates of other values and preserving survivor order. Searching for NULL removes NULL elements, while NULL outer input returns NULL. Empty input remains empty; compared values need a common equality-capable descriptor or TYPE_MISMATCH is raised.

**Example**

```sql
array_remove([2, 4, 2, 6], 2)
-- → [4, 6]
```

## array_replace

Substitutes all matching elements without changing sequence length or ordering.

```text
array_replace(values:array(T),search:T,replacement:T) -> array(T)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/ternary+generic` |
| Returns | `array(T)` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL outer array gives NULL; search and replacement NULLs are element values |

Replaces every element IS NOT DISTINCT FROM search with the replacement value, including NULL matches when search is NULL. A NULL replacement makes output elements nullable, whereas a NULL outer input produces NULL. The element, search and replacement descriptors must unify at binding or TYPE_MISMATCH is raised; array length and positions remain unchanged.

**Example**

```sql
array_replace([2, 4, 2], 2, 9)
-- → [9, 4, 9]
```

## array_resize

Truncates a sequence or extends its tail to a requested cardinality.

```text
array_resize(array(T),size:uint64) -> array(T)
array_resize(array(T),size:uint64,fill:T) -> array(T)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/2..3-ary+generic` |
| Returns | `array(T)` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL outer array/size gives NULL; explicit NULL fill requires nullable elements |

Keeps the leading requested number of entries when shrinking and appends default(T) or the supplied fill when expanding. A NULL array or size returns NULL, and explicit NULL fill requires nullable elements. Requested size must satisfy admitted element and byte limits or RESOURCE_LIMIT is raised; incompatible fill descriptors raise TYPE_MISMATCH.

**Example**

```sql
array_resize([1, 2], 4, 9)
-- → [1, 2, 9, 9]
```

## array_reverse

Mirrors element order so the final entry becomes the first.

```text
array_reverse(array(T)) -> array(T)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/unary+generic` |
| Returns | `array(T)` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Returns the input elements in reverse positional order while retaining each value and its nullability. A NULL outer array returns NULL, and empty or singleton arrays are unchanged. Reversal depends only on the current array and preserves its element descriptor and cardinality.

**Example**

```sql
array_reverse([1, 2, 3])
-- → [3, 2, 1]
```

## array_reverse_fill

Carries the next accepted value backward over predicate failures.

```text
array_reverse_fill(lambda(T1,...)->bool,array(T1),...) -> array(T1)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/variadic+generic+lambda` |
| Returns | `array(T1)` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL outer array gives NULL; NULL predicate is false |

Scans right to left, retains the final element and fills failed-predicate positions with the next retained value in original order. Predicates evaluate original entries and treat NULL as false, while a NULL outer input returns NULL. Empty input remains empty, and unequal lengths among aligned lambda arrays raise INVALID_ARGUMENT.

**Example**

```sql
array_reverse_fill(x -> x > 0, [2, 0, 0, 5])
-- → [2, 5, 5, 5]
```

## array_reverse_sort

Stably orders all entries by descending values or descending lambda keys.

```text
array_reverse_sort(array(T:total_order)) -> array(T)
array_reverse_sort(lambda(T1,...)->K:total_order,array(T1),...) -> array(T1)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/variadic+generic+lambda` |
| Returns | `first array argument descriptor` |
| Aliases | `arrayReverseSort` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Sorts the first array from greatest to least using its values or an aligned lambda key, preserving encounter order for equal keys. NULL keys are placed last in this descending order, and NULL outer input returns NULL. Key descriptors without total-order capability raise TYPE_MISMATCH; parallel array length disagreement raises INVALID_ARGUMENT.

**Example**

```sql
array_reverse_sort([2, 1, 3])
-- → [3, 2, 1]
```

## array_reverse_split

Partitions a sequence immediately after positions accepted by a predicate.

```text
array_reverse_split(lambda(T1,...)->bool,array(T1),...) -> array(array(T1))
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/variadic+generic+lambda` |
| Returns | `array(array(T1))` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL outer array gives NULL; NULL predicate is false |

Creates consecutive subarrays by placing a boundary after each true predicate position, preserving original element order. No extra empty endpoint segment is emitted, NULL predicate results do not split, and NULL outer input returns NULL. Parallel lambda inputs must have equal lengths or INVALID_ARGUMENT is raised.

**Example**

```sql
array_reverse_split(x -> x = 2, [1, 2, 3, 4])
-- → [[1, 2], [3, 4]]
```

## array_roc_auc

Computes receiver-operating-characteristic area from ranked scores and binary outcomes.

```text
array_roc_auc(scores:array(number),labels:array(uint8)) -> float64
array_roc_auc(scores:array(number),labels:array(uint8),scale:bool) -> float64
array_roc_auc(scores:array(number),labels:array(uint8),scale:bool,partial_offsets:array(uint64)) -> float64
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/2..4-ary` |
| Returns | `float64` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Processes descending scores with ties grouped: scale defaults to true and divides the raw area by total_positives times total_negatives, while false returns unnormalized area in true-positive/false-positive count coordinates. For an admitted score partition, partial_offsets is \[higher_partitions_tp, higher_partitions_fp, total_positives, total_negatives], containing preceding higher-score counts and global class totals. NULL outer arguments and invalid class distributions return NULL. Unequal score-label lengths, nonbinary labels, or malformed or inconsistent offset counts raise INVALID_ARGUMENT.

**Example**

```sql
array_roc_auc([0.1, 0.9], [0, 1])
-- → 1.0
```

## array_rotate_left

Cycles leading entries to the tail by a signed rotation distance.

```text
array_rotate_left(array(T),distance:int64) -> array(T)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+generic` |
| Returns | `array(T)` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Rotates the sequence toward lower indices, wrapping displaced leading elements around to the end. The distance is reduced modulo array length, negative distances reverse direction, and empty input stays empty. NULL array or distance arguments return NULL, and element NULLs move with their original positions.

**Example**

```sql
array_rotate_left([1, 2, 3, 4], 1)
-- → [2, 3, 4, 1]
```

## array_rotate_right

Moves trailing entries around to the front with modular displacement.

```text
array_rotate_right(array(T),distance:int64) -> array(T)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+generic` |
| Returns | `array(T)` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Shifts positions cyclically toward higher indices and wraps entries crossing the tail back to the beginning. Signed distance uses the array length as its modulus, with negative values rotating in the opposite direction. Empty arrays remain empty, NULL outer arguments propagate NULL, and no values or NULL elements are discarded.

**Example**

```sql
array_rotate_right([1, 2, 3, 4], 1)
-- → [4, 1, 2, 3]
```

## array_shift_left

Discards leading positions and fills newly vacant tail slots.

```text
array_shift_left(array(T),distance:int64) -> array(T)
array_shift_left(array(T),distance:int64,fill:T) -> array(T)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/2..3-ary+generic` |
| Returns | `array(T)` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL array/distance gives NULL; NULL fill requires nullable elements |

Moves entries toward lower indices while preserving array length, filling vacated positions with default(T) or an explicit fill value. Negative distances shift in the opposite direction, and distances reaching the full length leave only fill values. A NULL array or distance returns NULL; NULL fill requires nullable elements and incompatible fills raise TYPE_MISMATCH.

**Example**

```sql
array_shift_left([1, 2, 3, 4], 2, 9)
-- → [3, 4, 9, 9]
```

## array_shift_right

Slides existing entries toward the tail and pads the exposed front.

```text
array_shift_right(array(T),distance:int64) -> array(T)
array_shift_right(array(T),distance:int64,fill:T) -> array(T)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/2..3-ary+generic` |
| Returns | `array(T)` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL array/distance gives NULL; NULL fill requires nullable elements |

Moves entries toward higher indices and discards those pushed past the final position, keeping the original cardinality. Exposed positions receive default(T) or the supplied fill; negative distances reverse the motion. NULL array or distance arguments return NULL, and explicit NULL padding needs a nullable element descriptor or TYPE_MISMATCH is raised.

**Example**

```sql
array_shift_right([1, 2, 3, 4], 2, 9)
-- → [9, 9, 1, 2]
```

## array_shingles

Builds overlapping contiguous windows of a fixed positive width.

```text
array_shingles(array(T),width:uint64) -> array(array(T))
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+generic` |
| Returns | `array(array(T))` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Returns every contiguous subarray having the requested width, advancing the start position one element at a time. Width greater than the input length produces an empty array, while width zero raises INVALID_ARGUMENT. NULL outer arguments return NULL, and NULL elements remain present inside any window that includes them.

**Example**

```sql
array_shingles([1, 2, 3, 4], 2)
-- → [[1, 2], [2, 3], [3, 4]]
```

## array_shuffle

Produces a uniform random permutation of all input positions.

```text
array_shuffle(array(T)) -> array(T)
array_shuffle(array(T),seed:uint64) -> array(T)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/1..2-ary+generic` |
| Returns | `array(T)` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Volatile — may differ on every call. |
| NULL handling | Any NULL argument gives NULL. |

Reorders every position without changing the element multiset, including repeated values and NULL elements. NULL outer input returns NULL and empty input remains empty. Supplying an explicit seed makes a particular invocation reproducible; omitting the seed makes evaluation volatile, so repeated evaluations may differ.

**Example**

```sql
array_shuffle([7], 42)
-- → [7]
```

## array_slice

Extracts a clipped segment using signed offsets and optional length rules.

```text
array_slice(array(T),offset:int64) -> array(T)
array_slice(array(T),offset:int64,length:int64) -> array(T)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/2..3-ary+generic` |
| Returns | `array(T)` |
| Aliases | `arraySlice` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Begins at a one-based offset, with negative offsets counting from the end; offset zero returns an empty array. Positive length limits the number of elements, negative length excludes that many tail entries, and omitted length keeps the remaining suffix. Bounds clip to available positions, NULL arguments return NULL, and element NULLs are preserved.

**Example**

```sql
array_slice([1, 2, 3, 4], 2, 2)
-- → [2, 3]
```

## array_sort

Arranges a complete sequence stably from lowest key to highest.

```text
array_sort(array(T:total_order)) -> array(T)
array_sort(lambda(T1,...)->K:total_order,array(T1),...) -> array(T1)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/variadic+generic+lambda` |
| Returns | `first array argument descriptor` |
| Aliases | `arraySort` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Orders the first array by its values or by a lambda evaluated over aligned arrays, retaining encounter order for equal keys. NULL keys follow non-NULL keys, and NULL outer arrays return NULL. A key type without total ordering raises TYPE_MISMATCH, while mismatched lambda-array lengths raise INVALID_ARGUMENT.

**Example**

```sql
array_sort([3, 1, 2])
-- → [1, 2, 3]
```

## array_split

Starts a new consecutive segment before each true predicate position.

```text
array_split(lambda(T1,...)->bool,array(T1),...) -> array(array(T1))
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/variadic+generic+lambda` |
| Returns | `array(array(T1))` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL outer array gives NULL; NULL predicate is false |

Divides the first array immediately before positions whose aligned lambda evaluates to true and preserves element order within and across segments. A boundary at the beginning does not create an extra empty segment; NULL predicates are false and NULL outer input returns NULL. Unequal lambda-array lengths raise INVALID_ARGUMENT.

**Example**

```sql
array_split(x -> x = 3, [1, 2, 3, 4])
-- → [[1, 2], [3, 4]]
```

## array_sum

Totals non-NULL numeric elements using the SQL aggregate widening rules.

```text
array_sum(array(T:number)) -> sum_type(T)
array_sum(lambda(T1,...)->R:number,array(T1),...) -> sum_type(R)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/variadic+generic+lambda` |
| Returns | `SQL SUM widened descriptor of input element or lambda result` |
| Aliases | `arraySum`, `sum_array` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL outer array gives NULL; NULL numeric elements skipped |

Adds numeric elements, or numeric lambda results from aligned arrays, into the SQL sum widened result descriptor. NULL elements are skipped, empty and all-NULL arrays return typed zero, and NULL outer arrays return NULL. Nonnumeric input raises TYPE_MISMATCH, unequal lambda-array lengths raise INVALID_ARGUMENT, and checked arithmetic overflow raises NUMERIC_OVERFLOW.

**Example**

```sql
array_sum([1, 2, 3])
-- → 6
```

## array_symmetric_difference

Keeps unique values absent from at least one participating sequence.

```text
array_symmetric_difference(array(T),array(T),...) -> array(T)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/variadic+generic` |
| Returns | `array(T)` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Emits each encountered value once unless that value appears in every input array, preserving first-encounter order. For more than two arrays this means union minus intersection, rather than odd multiplicity across sets. NULL-safe equality treats NULL as a member, NULL outer arrays propagate NULL, and incompatible descriptors raise TYPE_MISMATCH.

**Example**

```sql
array_symmetric_difference([1, 2], [2, 3])
-- → [1, 3]
```

## array_trim_strings

Strips selected edge characters from each text element without altering positions.

```text
array_trim_strings(values:array(string)) -> array(string)
array_trim_strings(values:array(string),characters:string) -> array(string)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/1..2-ary` |
| Returns | `array(string)` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL outer array or character set gives NULL; NULL elements remain NULL |

Trims both ends of each string using ASCII whitespace by default or an explicitly supplied set of Unicode scalar values. Element NULLs remain NULL and original order is retained, while a NULL outer array or character set returns NULL. An empty character set leaves strings unchanged; non-string element descriptors raise TYPE_MISMATCH.

**Example**

```sql
array_trim_strings(['  red ', ' blue  '])
-- → ['red', 'blue']
```

## array_union

Combines distinct members across all inputs in first-encounter order.

```text
array_union(array(T),array(T),...) -> array(T)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/variadic+generic` |
| Returns | `array(T)` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Visits each array in argument order and emits a value only on its first NULL-safe encounter. Repeated NULL elements therefore contribute one NULL, while any NULL outer array makes the result NULL. Empty inputs contribute no members, and all arrays need compatible equality-capable element descriptors or TYPE_MISMATCH is raised.

**Example**

```sql
array_union([3, 1], [1, 2])
-- → [3, 1, 2]
```

## array_uniq

Counts distinct scalar members or aligned tuples across parallel sequences.

```text
array_uniq(array(T)) -> uint64
array_uniq(array(T1),array(T2),...) -> uint64
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/variadic+generic` |
| Returns | `uint64` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Returns the number of unique values for one array or unique positional tuples formed from multiple arrays. Equality is NULL-safe, empty arrays return zero, and a NULL outer input returns NULL. Multiple arrays must have equal length or INVALID_ARGUMENT is raised; incompatible comparison capabilities raise TYPE_MISMATCH.

**Example**

```sql
array_uniq([1, 2, 1, 3])
-- → 3
```

## array_upper

Finds the highest valid index for a uniform requested nesting dimension.

```text
array_upper(values:array(T),dimension:int64) -> nullable(uint64)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+generic` |
| Returns | `nullable(uint64)` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Returns the common nonempty subarray length at the supplied one-based dimension, which is also the upper bound under one-based indexing. NULL input, missing dimensions and reachable NULL, empty or unequal subarrays produce NULL. The result describes uniform runtime bounds and does not infer a rectangular shape from a ragged value.

**Example**

```sql
array_upper([[1, 2, 3], [4, 5, 6]], 2)
-- → 3
```

## array_with_constant

Repeats a supplied value to construct a sequence of the requested length.

```text
array_with_constant(length:uint64,value:T) -> array(T)
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/binary+generic` |
| Returns | `array(T)` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL length gives NULL; NULL value constructs nullable elements |

Creates an array containing the supplied value at every position, including NULL when nullable elements are constructed. A NULL length returns NULL and zero length creates an empty array with the bound element descriptor. Requested element count and encoded bytes are admitted before construction; exceeding either cap raises RESOURCE_LIMIT.

**Example**

```sql
array_with_constant(3, 7)
-- → [7, 7, 7]
```

## array_zip

Combines same-position entries from equally sized inputs into ordered tuples.

```text
array_zip(array(T1),array(T2),...) -> array(tuple(T1,T2,...))
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/variadic+generic` |
| Returns | `array(tuple(T1,T2,...))` |
| Aliases | `arrayZip` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Forms one tuple for each position using between one and sixteen arrays, preserving the descriptor and nullability of each field. A NULL outer input returns NULL, while empty aligned arrays produce an empty result. All input lengths must match or INVALID_ARGUMENT is raised; argument count and descriptor incompatibilities raise TYPE_MISMATCH.

**Example**

```sql
array_zip([1, 2], ['a', 'b'])
-- → [(1, 'a'), (2, 'b')]
```

## array_zip_unaligned

Pairs parallel entries through the longest input, padding missing fields with NULL.

```text
array_zip_unaligned(array(T1),array(T2),...) -> array(tuple(T1 null,T2 null,...))
```

| | |
| --- | --- |
| Kind | scalar |
| Signature class | `scalar/variadic+generic` |
| Returns | `array(tuple(T1 null,T2 null,...))` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | Any NULL argument gives NULL. |

Creates positional tuples until the longest input array is exhausted, inserting NULL wherever a shorter array lacks a position. Every resulting tuple field is therefore nullable, independently of the original element nullability. NULL outer input returns NULL; when all arrays are empty, the result is an empty array of the bound tuple descriptor.

**Example**

```sql
array_zip_unaligned([1, 2], ['a'])
-- → [(1, 'a'), (2, NULL)]
```

## group_array

Collects nonmissing group observations into a sequence with duplicates intact.

```text
group_array(T) -> array(T)
group_array(max_size:uint64 const; T) -> array(T)
```

| | |
| --- | --- |
| Kind | aggregate |
| Signature class | `aggregate/1..2-ary+generic+parametric` |
| Returns | `array(T)` |
| Aliases | `array_agg`, `groupArray` |
| Streaming | Stateful — keeps per-group state. State: declared group element cap and encoded-value bytes |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL inputs are skipped. |

Builds an array from non-NULL group inputs, preserving duplicates and producing an empty array when no value is admitted. Reproducible ordering requires explicitly ordered input; the optional constant maximum size limits collection. Continuous execution requires admitted element and byte caps and removes one occurrence per retraction; exceeding the admitted state budget raises RESOURCE_LIMIT.

**Example**

```sql
(SELECT group_array(reading) FROM (VALUES (5)) AS sensors(reading))
-- → [5]
```

## group_array_array

Flattens grouped input sequences into one accumulated list of their members.

```text
group_array_array(array(T)) -> array(T)
```

| | |
| --- | --- |
| Kind | aggregate |
| Signature class | `aggregate/unary+generic` |
| Returns | `array(T)` |
| Aliases | `groupArrayArray` |
| Streaming | Stateful — keeps per-group state. State: declared total accumulated element and byte caps |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL inputs are skipped. |

Collects the non-NULL elements of each admitted input array into the group result, skipping NULL outer arrays as well. Empty input arrays contribute no elements, and ordered input is required to make concatenation order reproducible. Streaming execution shares collection state and must admit total element and byte limits; state beyond those limits raises RESOURCE_LIMIT.

**Example**

```sql
(SELECT group_array_array(readings) FROM (VALUES ([2, 4])) AS sensors(readings))
-- → [2, 4]
```

## group_uniq_array

Accumulates the unique non-NULL values observed across a group.

```text
group_uniq_array(T) -> array(T)
group_uniq_array(max_size:uint64 const; T) -> array(T)
```

| | |
| --- | --- |
| Kind | aggregate |
| Signature class | `aggregate/1..2-ary+generic+parametric` |
| Returns | `array(T)` |
| Aliases | `groupUniqArray` |
| Streaming | Stateful — keeps per-group state. State: declared distinct-value and byte caps |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL inputs are skipped. |

Produces one array entry for each distinct non-NULL group value, with unspecified output order and an empty result for an empty group. The optional constant maximum constrains admitted distinct values. Retractions need occurrence counts so a value disappears only after its final occurrence is removed; continuous distinct-value and byte caps raise RESOURCE_LIMIT when exceeded.

**Example**

```sql
(
  SELECT group_uniq_array(reading)
  FROM (VALUES (3), (3), (NULL)) AS sensors(reading)
)
-- → [3]
```

## max_k

Selects the K greatest readings, sorted from highest downward without deduplication.

```text
max_k(T:total_order,k:uint64 const) -> array(T)
```

| | |
| --- | --- |
| Kind | aggregate |
| Signature class | `aggregate/binary+generic` |
| Returns | `array(T)` |
| Streaming | Stateful — keeps per-group state. State: declared window cardinality for retractions; K candidates on append-only input |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL inputs are skipped. |

Retains up to K non-NULL values and emits them in descending order, counting each duplicate independently. Empty groups return an empty array; an out-of-range or nonconstant K raises INVALID_ARGUMENT, with the accepted range 1 through 16777215. Append-only processing retains K candidates, while arbitrary streaming retractions require the complete admitted candidate multiset and enforce RESOURCE_LIMIT.

**Example**

```sql
(SELECT max_k(reading, 2) FROM (VALUES (7), (2), (4)) AS sensors(reading))
-- → [7, 4]
```

## min_k

Returns the smallest K observations in ascending order, retaining repeated minima.

```text
min_k(T:total_order,k:uint64 const) -> array(T)
```

| | |
| --- | --- |
| Kind | aggregate |
| Signature class | `aggregate/binary+generic` |
| Returns | `array(T)` |
| Streaming | Stateful — keeps per-group state. State: declared window cardinality for retractions; K candidates on append-only input |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL inputs are skipped. |

Collects up to K non-NULL values from a group and sorts them from smallest to largest; repeated values occupy separate slots. K must be a constant between 1 and 16777215, otherwise INVALID_ARGUMENT is raised. An empty group produces an empty array, and streaming retractions require an admitted candidate multiset with RESOURCE_LIMIT enforcement.

**Example**

```sql
(SELECT min_k(reading, 2) FROM (VALUES (7), (2), (4)) AS sensors(reading))
-- → [2, 4]
```

## unnest

Exposes collection contents as lateral rows with optional one-based ordinality.

```text
unnest(values:array(T)) -> table(tuple(value:T))
unnest(values:array(T1),values:array(T2),...) -> table(tuple(T1,T2,...))
unnest(values:map(K,V)) -> table(tuple(key:K,value:V))
unnest(values:json) -> table(tuple(value:json))
```

| | |
| --- | --- |
| Kind | table |
| Signature class | `table/variadic+generic` |
| Returns | `table of typed array elements, aligned element tuples, map key/value pairs, or JSON array elements` |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL or empty array produces zero rows; element NULLs preserved |

Expands arrays, maps or a JSON array root into typed rows, preserving element NULLs and emitting no rows for NULL or empty input. Multiple arrays align by position and pad shorter inputs with SQL NULL; maps use canonical key order and WITH ORDINALITY adds a one-based uint64 position. A non-array JSON root raises TYPE_MISMATCH, and the per-row expansion cap raises RESOURCE_LIMIT without retaining cross-row state.

**Example**

```sql
(SELECT * FROM unnest([2, 4]))
-- → [(2), (4)]
```

---

Source: https://nyxdb.io/docs/functions/array/