# 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

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

```text
"paid"
```

## array_append

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

### Syntax

```text
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

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

```text
"1,2,3"
```

## array_cat

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

### Syntax

```text
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

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

```text
"1,2,3"
```

## array_dims

Describe all native array dimension bounds as text.

### Syntax

```text
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

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

```text
"[1:2][1:2]"
```

## array_fill

Construct a native array filled with copies of a value.

### Syntax

```text
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

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

```text
"[7,7,7]"
```

## array_length

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

### Syntax

```text
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

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

```text
0,3
```

## array_lower

Return the lower bound of one native array dimension.

### Syntax

```text
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

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

```text
1
```

## array_ndims

Read the number of rectangular native array dimensions.

### Syntax

```text
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

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

```text
2
```

## array_position

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

### Syntax

```text
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

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

```text
2
```

## array_positions

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

### Syntax

```text
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

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

```text
"1,3"
```

## array_prepend

Prepend a member to a native array.

### Syntax

```text
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

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

```text
"1,2,3"
```

## array_remove

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

### Syntax

```text
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

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

```text
"2"
```

## array_replace

Replace every native array member equal to a searched value.

### Syntax

```text
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

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

```text
"9,2,9"
```

## array_reverse

Reverse immediate native array members.

### Syntax

```text
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

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

```text
"3,2,1"
```

## array_sample

Uniformly sample first-dimension items without replacement.

### Syntax

```text
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

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

```text
2
```

## array_shuffle

Uniformly shuffle the first dimension of a native array.

### Syntax

```text
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

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

```text
3
```

## array_sort

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

### Syntax

```text
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

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

```text
"1,2,3"
```

## array_to_string

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

### Syntax

```text
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

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

```text
"1,?,2"
```

## array_upper

Return the upper bound of one native array dimension.

### Syntax

```text
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

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

```text
2
```

## cardinality

Count scalar members across nested native arrays.

### Syntax

```text
cardinality(array)
```

### Arguments

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

### Returned value

Nonnegative INTEGER.

### Example

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

```text
4
```

## parse_ident

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

### Syntax

```text
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

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

```text
"SomeSchema/sometable"
```

## string_to_array

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

### Syntax

```text
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

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

```text
"a|b|c"
```

## trim_array

Remove trailing members from the first array dimension.

### Syntax

```text
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

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

```text
"[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.

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

```text
20,0,true
```