Array functions

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.

array_all

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

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

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

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

array_avg

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

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

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

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

array_build

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

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

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

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

array_compact

Collapses consecutive repeated entries while preserving separated occurrences.

array_compact(array(T)) -> array(T)

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

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

array_concat

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

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

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

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

array_count

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

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

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

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

array_count_equal

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

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

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

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

array_cum_sum

Produces running totals at successive positions within one numeric sequence.

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

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

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.

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))

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

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

array_difference

Calculates successive numeric deltas, beginning with a typed zero.

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

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

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

array_dims

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

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

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

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

array_distinct

Keeps the earliest occurrence of each structurally equal sequence member.

array_distinct(array(T)) -> array(T)

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

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

array_dot_product

Sums pairwise products from two equal-length numeric vectors.

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

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

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

array_element

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

array_element(array(T),int64) -> 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

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.

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

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

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

array_empty_to_single

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

array_empty_to_single(array(T)) -> array(T)

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

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

array_enumerate

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

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

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

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

array_enumerate_dense

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

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

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

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.

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))

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

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.

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

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

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.

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))

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

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.

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

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

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

array_exists

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

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

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

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

array_fill

Carries the last accepted value forward across failed predicate positions.

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

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

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

array_filter

Keeps source entries whose corresponding lambda evaluation is true.

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

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

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.

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

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

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

array_first_index

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

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

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

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.

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

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

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

array_flatten

Removes all nested ARRAY layers while retaining leaf traversal order.

array_flatten(array(array(T))) -> array(T)

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

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

array_fold

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

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

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

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

array_has

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

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

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

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

array_has_all

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

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

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

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

array_has_any

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

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

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

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

array_has_substr

Recognizes an ordered contiguous subsequence inside another sequence.

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

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

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.

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

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

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

array_intersect

Retains unique members shared by every supplied sequence.

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

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

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.

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

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

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

array_join

Expands sequence members into individual relational rows.

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

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

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

array_join_string

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

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

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

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

array_last

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

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

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

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

array_last_index

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

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

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

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

array_last_or_null

Fetches the trailing successful match, leaving unsuccessful lookups NULL.

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

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

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

array_length

Counts outer elements or reports a uniform requested dimension size.

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

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

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

array_lower

Reports the lower index of an existing uniform nonempty dimension.

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

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

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

array_map

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

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

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

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

array_max

Selects the greatest nonmissing entry or mapped ordering value.

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

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

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

array_min

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

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

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

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

array_ndims

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

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

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

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

array_normalized_gini

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

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

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

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.

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)

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

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

array_partial_shuffle

Randomizes an unbiased prefix while leaving the remaining ordering unspecified.

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)

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

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

array_partial_sort

Orders a limited leading selection from smallest to greatest.

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)

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

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

array_pop_back

Drops the final sequence entry while preserving the preceding prefix.

array_pop_back(array(T)) -> array(T)

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

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

array_pop_front

Removes the leading entry and returns the remaining suffix.

array_pop_front(array(T)) -> array(T)

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

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

array_positions

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

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

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

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

array_pr_auc

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

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

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

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

array_product

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

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

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

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

array_push_back

Appends one compatible item after the existing sequence tail.

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

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

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

array_push_front

Prepends a supplied item before all existing entries.

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

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

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

array_random_sample

Draws a uniform sample without replacement from available positions.

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

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

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

array_range

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

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

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

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

array_reduce

Evaluates a bound aggregate over aligned elements within one row.

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

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

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

array_reduce_in_ranges

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

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

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

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.

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

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

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

array_replace

Substitutes all matching elements without changing sequence length or ordering.

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

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

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

array_resize

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

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

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

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

array_reverse

Mirrors element order so the final entry becomes the first.

array_reverse(array(T)) -> array(T)

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

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

array_reverse_fill

Carries the next accepted value backward over predicate failures.

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

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

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.

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

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

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

array_reverse_split

Partitions a sequence immediately after positions accepted by a predicate.

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

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

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.

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

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

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.

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

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

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.

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

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

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

array_shift_left

Discards leading positions and fills newly vacant tail slots.

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

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

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.

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

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

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

array_shingles

Builds overlapping contiguous windows of a fixed positive width.

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

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

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

array_shuffle

Produces a uniform random permutation of all input positions.

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

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

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

array_slice

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

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

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

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

array_sort

Arranges a complete sequence stably from lowest key to highest.

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

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

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

array_split

Starts a new consecutive segment before each true predicate position.

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

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

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.

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

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

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

array_symmetric_difference

Keeps unique values absent from at least one participating sequence.

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

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

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

array_trim_strings

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

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

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

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

array_union

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

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

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

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

array_uniq

Counts distinct scalar members or aligned tuples across parallel sequences.

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

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

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

array_upper

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

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

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

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.

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

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

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

array_zip

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

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

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

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.

array_zip_unaligned(array(T1),array(T2),...) -> array(tuple(T1 null,T2 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

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

group_array

Collects nonmissing group observations into a sequence with duplicates intact.

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

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

(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.

group_array_array(array(T)) -> array(T)

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

(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.

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

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

(
  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.

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

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

(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.

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

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

(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.

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))

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

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