Array functions ​

Array functions create and transform ordered collections. Use them to append or remove members, inspect dimensions, locate values, or turn a collection into rows or text.

Arrays retain their declared member type. Functions that add values validate the new members; nullable members and a NULL array follow the individual function contracts. Array positions are generally one-based, while negative positions are supported only where stated.

Example: Removing an unwanted label ​

SELECT array_to_string(array_remove(array('new', 'paid', 'new'), 'new'), ',') AS remaining
FORMAT csv;

"paid"

array_append ​

Append one member while retaining the native array member type; adding NULL permits nullable members.

Syntax ​

array_append(array, value)

Arguments ​

Native Array and one convertible member; NULL array becomes a one-element array; incompatible or out-of-range members error.

Returned value ​

Native Array with the retained member type, including empty arrays and exact numeric widths.

Example ​

SELECT array_to_string(array_append(array(1,2),3),',') FORMAT csv;

"1,2,3"

array_cat ​

Concatenate native arrays, preserving the first non-NULL array member contract.

Syntax ​

array_cat(left_array,right_array)

Arguments ​

NULL array acts as empty; both NULL yields NULL; right members coerce to the left contract; nested arrays retain their shape.

Returned value ​

Native Array with the retained definition.

Example ​

SELECT array_to_string(array_cat(array(1,2),array(3)),',') FORMAT csv;

"1,2,3"

array_dims ​

Describe all native array dimension bounds as text.

Syntax ​

array_dims(array)

Arguments ​

Validates rectangular shape up to 16 dimensions. Native lower bounds are one; nested empty dimensions retain their native shape.

Returned value ​

Bounds TEXT, or SQL NULL for an empty top-level array/NULL input.

Example ​

SELECT array_dims(array(array(1,2),array(3,4))) FORMAT csv;

"[1:2][1:2]"

array_fill ​

Construct a native array filled with copies of a value.

Syntax ​

array_fill(value,dimensions[,lower_bounds])

Arguments ​

Nonnegative dimensions up to 16 levels and one million logical members including containers. Optional lower bounds must all be one. NULL element values are supported.

Returned value ​

Native typed Array, retaining nested dimensions; NULL dimensions/bounds give SQL NULL.

Example ​

SELECT to_json(array_fill(7,array(3))) FORMAT csv;

"[7,7,7]"

array_length ​

Read the top-level Array length, QBit dimension, or a rectangular native array dimension.

Syntax ​

array_length(value[,dimension])

Arguments ​

Without dimension, value is an Array or QBit; empty Arrays return 0. With a one-based dimension, require an Array with rectangular nesting up to 16 levels; missing subarrays and inconsistent shapes error. Native lower bounds are one.

Returned value ​

Int64; NULL for SQL NULL input, or for empty or absent/nonpositive dimensions in the two-argument form.

Example ​

SELECT array_length(array()),array_length(array(1,2,3),1) FORMAT csv;

0,3

array_lower ​

Return the lower bound of one native array dimension.

Syntax ​

array_lower(array,dimension)

Arguments ​

Positive one-based dimension; validates rectangular shape up to 16 dimensions. Native lower bounds are one. Empty, missing and zero-length dimensions return NULL.

Returned value ​

INTEGER one, or SQL NULL.

Example ​

SELECT array_lower(array(10,20),1) FORMAT csv;

1

array_ndims ​

Read the number of rectangular native array dimensions.

Syntax ​

array_ndims(array)

Arguments ​

Same shape validation as array_length; native nested empty arrays retain their shape.

Returned value ​

Int64; empty top-level arrays and SQL NULL give NULL.

Example ​

SELECT array_ndims(array(array(1,2),array(3,4))) FORMAT csv;

2

array_position ​

Find the first matching native array member at or after a one-based start; NULL equals NULL.

Syntax ​

array_position(array, value[, start])

Arguments ​

Native Array, comparison value, and optional signed start; starts below 1 begin at 1; NULL start errors.

Returned value ​

INTEGER one-based position, or NULL for no match or NULL array.

Example ​

SELECT array_position(array(1,NULL,3),NULL) FORMAT csv;

2

array_positions ​

Find all one-based positions of equal native array members.

Syntax ​

array_positions(array,needle)

Arguments ​

NULL equals NULL; native comparison conversion and selected collation apply; only immediate members are searched.

Returned value ​

Array(Int64); empty for no match; NULL for NULL array.

Example ​

SELECT array_to_string(array_positions(array(1,2,1),1),',') FORMAT csv;

"1,3"

array_prepend ​

Prepend a member to a native array.

Syntax ​

array_prepend(value,array)

Arguments ​

NULL array creates a singleton; NULL member widens member nullability; other members obey the retained native type.

Returned value ​

Native Array.

Example ​

SELECT array_to_string(array_prepend(1,array(2,3)),',') FORMAT csv;

"1,2,3"

array_remove ​

Remove every matching member using native comparison conversion and selected SQL collation; NULL equals NULL.

Syntax ​

array_remove(array, value)

Arguments ​

Native Array and comparison value; members are compared without rounding numeric comparison boundaries.

Returned value ​

Native Array with the original member type and retained input order; NULL array returns NULL.

Example ​

SELECT array_to_string(array_remove(array(1,2,1),1),',') FORMAT csv;

"2"

array_replace ​

Replace every native array member equal to a searched value.

Syntax ​

array_replace(array,needle,replacement)

Arguments ​

NULL equals NULL; selected collation applies; replacement coerces to member type; replacing with NULL widens nullability.

Returned value ​

Native Array preserving order and nested shape; NULL input gives NULL.

Example ​

SELECT array_to_string(array_replace(array(1,2,1),1,9),',') FORMAT csv;

"9,2,9"

array_reverse ​

Reverse immediate native array members.

Syntax ​

array_reverse(array)

Arguments ​

Nested arrays move as whole members; their member order is unchanged; NULL propagates.

Returned value ​

Native Array preserving its definition.

Example ​

SELECT array_to_string(array_reverse(array(1,2,3)),',') FORMAT csv;

"3,2,1"

array_sample ​

Uniformly sample first-dimension items without replacement.

Syntax ​

array_sample(array,count)

Arguments ​

Rectangular native Array; count must be an integer from zero through the first dimension length. Multidimensional items are complete subarrays. Random calls are nondeterministic.

Returned value ​

Native Array preserving its element contract; SQL NULL propagates.

Example ​

SELECT array_length(array_sample(array(1,2,3),2)) FORMAT csv;

2

array_shuffle ​

Uniformly shuffle the first dimension of a native array.

Syntax ​

array_shuffle(array)

Arguments ​

Rectangular native Array; preserves every item, including NULL members and complete subarrays. Random calls are nondeterministic.

Returned value ​

Native Array preserving its element contract; SQL NULL propagates.

Example ​

SELECT array_length(array_shuffle(array(1,2,3))) FORMAT csv;

3

array_sort ​

Sort immediate array members using native comparisons and the selected collation.

Syntax ​

array_sort(array[,descending[,nulls_first]])

Arguments ​

Default ascending with NULL last; descending defaults NULL first; explicit Bool options override; equivalent ties have no stable-order guarantee.

Returned value ​

Native Array preserving its definition and nested shapes; NULL propagates.

Example ​

SELECT array_to_string(array_sort(array(3,1,2)),',') FORMAT csv;

"1,2,3"

array_to_string ​

Join native array members in depth-first order, skipping NULL unless a replacement is supplied.

Syntax ​

array_to_string(array, delimiter[, null_string])

Arguments ​

Native Array, delimiter text, optional NULL-member replacement; nested arrays are flattened.

Returned value ​

TEXT; empty array gives empty text; NULL array or delimiter gives NULL.

Example ​

SELECT array_to_string(array(1,NULL,2),',','?') FORMAT csv;

"1,?,2"

array_upper ​

Return the upper bound of one native array dimension.

Syntax ​

array_upper(array,dimension)

Arguments ​

Positive one-based dimension and rectangular native arrays. Bounds start at one; the upper bound equals the dimension length. Empty/missing dimensions return NULL.

Returned value ​

INTEGER upper bound, or SQL NULL.

Example ​

SELECT array_upper(array(10,20),1) FORMAT csv;

2

cardinality ​

Count scalar members across nested native arrays.

Syntax ​

cardinality(array)

Arguments ​

Depth-first traversal counts NULL scalar members; empty nested arrays add zero; NULL input gives NULL.

Returned value ​

Nonnegative INTEGER.

Example ​

SELECT cardinality(array(array(1,2),array(3,4))) FORMAT csv;

4

parse_ident ​

Parse a dotted SQL identifier into an array of components without truncation.

Syntax ​

parse_ident(text[,strict])

Arguments ​

Strict defaults to true; false ignores trailing text after the final identifier. Quoted names retain spelling and doubled double quotes; unquoted names fold ASCII case. Empty/malformed components and NUL error.

Returned value ​

Native Array(String), or NULL for a NULL argument.

Example ​

SELECT array_to_string(parse_ident('"SomeSchema".SomeTable'),'/') FORMAT csv;

"SomeSchema/sometable"

string_to_array ​

Split text into a native Array(String), replacing exact null_string fields with NULL.

Syntax ​

string_to_array(text, delimiter[, null_string])

Arguments ​

NULL delimiter splits Unicode characters; empty delimiter retains one field; empty input gives an empty array.

Returned value ​

Native text array; nullable members when a non-NULL null_string is supplied; NULL for NULL input.

Example ​

SELECT array_to_string(string_to_array('a,b,c',','),'|') FORMAT csv;

"a|b|c"

trim_array ​

Remove trailing members from the first array dimension.

Syntax ​

trim_array(array,count)

Arguments ​

Nonnegative count no greater than the first dimension length.

Returned value ​

Native Array preserving the original member contract; SQL NULL propagates.

Example ​

SELECT to_json(trim_array(array(1,2,3),1)) FORMAT csv;

"[1,2]"

Constructors and element access ​

array(...) constructs a typed Array. array_element(array,index) uses one-based or negative positions, returning the element type default for an out-of-range nonzero position. Index zero raises INVALID_ARGUMENT. has(array,value) tests immediate members. array_length reads the top-level length; its two-argument form reads a rectangular dimension.

SELECT array_element(array(10,20),-1),array_element(array(10,20),9),has(array(10,20),20) FORMAT csv;

20,0,true