GoogleSQL for Spanner supports the following array functions.

## Function list

| Name | Summary |
|---|---|
| [`ARRAY`](https://docs.cloud.google.com/spanner/docs/reference/standard-sql/array_functions.md.txt#array) | Produces an array with one element for each row in a subquery. |
| [`ARRAY_AGG`](https://docs.cloud.google.com/spanner/docs/reference/standard-sql/aggregate_functions.md.txt#array_agg) | Gets an array of values. For more information, see [Aggregate functions](https://docs.cloud.google.com/spanner/docs/reference/standard-sql/aggregate_functions.md.txt). |
| [`ARRAY_CONCAT`](https://docs.cloud.google.com/spanner/docs/reference/standard-sql/array_functions.md.txt#array_concat) | Concatenates one or more arrays with the same element type into a single array. |
| [`ARRAY_CONCAT_AGG`](https://docs.cloud.google.com/spanner/docs/reference/standard-sql/aggregate_functions.md.txt#array_concat_agg) | Concatenates arrays and returns a single array as a result. For more information, see [Aggregate functions](https://docs.cloud.google.com/spanner/docs/reference/standard-sql/aggregate_functions.md.txt). |
| [`ARRAY_FILTER`](https://docs.cloud.google.com/spanner/docs/reference/standard-sql/array_functions.md.txt#array_filter) | Takes an array, filters out unwanted elements, and returns the results in a new array. |
| [`ARRAY_FIRST`](https://docs.cloud.google.com/spanner/docs/reference/standard-sql/array_functions.md.txt#array_first) | Gets the first element in an array. |
| [`ARRAY_INCLUDES`](https://docs.cloud.google.com/spanner/docs/reference/standard-sql/array_functions.md.txt#array_includes) | Checks if there is an element in the array that is equal to a search value. |
| [`ARRAY_INCLUDES_ALL`](https://docs.cloud.google.com/spanner/docs/reference/standard-sql/array_functions.md.txt#array_includes_all) | Checks if all search values are in an array. |
| [`ARRAY_INCLUDES_ANY`](https://docs.cloud.google.com/spanner/docs/reference/standard-sql/array_functions.md.txt#array_includes_any) | Checks if any search values are in an array. |
| [`ARRAY_IS_DISTINCT`](https://docs.cloud.google.com/spanner/docs/reference/standard-sql/array_functions.md.txt#array_is_distinct) | Checks if an array contains no repeated elements. |
| [`ARRAY_LAST`](https://docs.cloud.google.com/spanner/docs/reference/standard-sql/array_functions.md.txt#array_last) | Gets the last element in an array. |
| [`ARRAY_LENGTH`](https://docs.cloud.google.com/spanner/docs/reference/standard-sql/array_functions.md.txt#array_length) | Gets the number of elements in an array. |
| [`ARRAY_MAX`](https://docs.cloud.google.com/spanner/docs/reference/standard-sql/array_functions.md.txt#array_max) | Gets the maximum non-`NULL` value in an array. |
| [`ARRAY_MIN`](https://docs.cloud.google.com/spanner/docs/reference/standard-sql/array_functions.md.txt#array_min) | Gets the minimum non-`NULL` value in an array. |
| [`ARRAY_REVERSE`](https://docs.cloud.google.com/spanner/docs/reference/standard-sql/array_functions.md.txt#array_reverse) | Reverses the order of elements in an array. |
| [`ARRAY_SLICE`](https://docs.cloud.google.com/spanner/docs/reference/standard-sql/array_functions.md.txt#array_slice) | Produces an array containing zero or more consecutive elements from an input array. |
| [`ARRAY_TO_STRING`](https://docs.cloud.google.com/spanner/docs/reference/standard-sql/array_functions.md.txt#array_to_string) | Produces a concatenation of the elements in an array as a `STRING` value. |
| [`ARRAY_TRANSFORM`](https://docs.cloud.google.com/spanner/docs/reference/standard-sql/array_functions.md.txt#array_transform) | Transforms the elements of an array, and returns the results in a new array. |
| [`GENERATE_ARRAY`](https://docs.cloud.google.com/spanner/docs/reference/standard-sql/array_functions.md.txt#generate_array) | Generates an array of values in a range. |
| [`GENERATE_DATE_ARRAY`](https://docs.cloud.google.com/spanner/docs/reference/standard-sql/array_functions.md.txt#generate_date_array) | Generates an array of dates in a range. |
| [`JSON_ARRAY`](https://docs.cloud.google.com/spanner/docs/reference/standard-sql/json_functions.md.txt#json_array) | Creates a JSON array. For more information, see [JSON functions](https://docs.cloud.google.com/spanner/docs/reference/standard-sql/json_functions.md.txt). |
| [`JSON_ARRAY_APPEND`](https://docs.cloud.google.com/spanner/docs/reference/standard-sql/json_functions.md.txt#json_array_append) | Appends JSON data to the end of a JSON array. For more information, see [JSON functions](https://docs.cloud.google.com/spanner/docs/reference/standard-sql/json_functions.md.txt). |
| [`JSON_ARRAY_INSERT`](https://docs.cloud.google.com/spanner/docs/reference/standard-sql/json_functions.md.txt#json_array_insert) | Inserts JSON data into a JSON array. For more information, see [JSON functions](https://docs.cloud.google.com/spanner/docs/reference/standard-sql/json_functions.md.txt). |
| [`JSON_QUERY_ARRAY`](https://docs.cloud.google.com/spanner/docs/reference/standard-sql/json_functions.md.txt#json_query_array) | Extracts a JSON array and converts it to a SQL `ARRAY<JSON-formatted STRING>` or `ARRAY<JSON>` value. For more information, see [JSON functions](https://docs.cloud.google.com/spanner/docs/reference/standard-sql/json_functions.md.txt). |
| [`JSON_VALUE_ARRAY`](https://docs.cloud.google.com/spanner/docs/reference/standard-sql/json_functions.md.txt#json_value_array) | Extracts a JSON array of scalar values and converts it to a SQL `ARRAY<STRING>` value. For more information, see [JSON functions](https://docs.cloud.google.com/spanner/docs/reference/standard-sql/json_functions.md.txt). |

## `ARRAY`

```googlesql
ARRAY(subquery)
```

**Description**

The `ARRAY` function returns an `ARRAY` with one element for each row in a
[subquery](https://docs.cloud.google.com/spanner/docs/reference/standard-sql/subqueries.md.txt).

If `subquery` produces a
SQL table,
the table must have exactly one column. Each element in the output `ARRAY` is
the value of the single column of a row in the table.

If `subquery` produces a
value table,
then each element in the output `ARRAY` is the entire corresponding row of the
value table.

**Constraints**

- Subqueries are unordered, so the elements of the output `ARRAY` aren't guaranteed to preserve any order in the source table for the subquery. However, if the subquery includes an `ORDER BY` clause, the `ARRAY` function will return an `ARRAY` that honors that clause.
- If the subquery returns more than one column, the `ARRAY` function returns an error.
- If the subquery returns an `ARRAY` typed column or `ARRAY` typed rows, the `ARRAY` function returns an error that GoogleSQL doesn't support `ARRAY`s with elements of type [`ARRAY`](https://docs.cloud.google.com/spanner/docs/reference/standard-sql/data-types.md.txt#array_type).
- If the subquery returns zero rows, the `ARRAY` function returns an empty `ARRAY`. It never returns a `NULL` `ARRAY`.

**Return type**

`ARRAY`

**Examples**

```googlesql
SELECT ARRAY
  (SELECT 1 UNION ALL
   SELECT 2 UNION ALL
   SELECT 3) AS new_array;

/*---+
 | new_array |
 +---+
 | [1, 2, 3] |
 +---*/
```

To construct an `ARRAY` from a subquery that contains multiple
columns, change the subquery to use `SELECT AS STRUCT`. Now
the `ARRAY` function will return an `ARRAY` of `STRUCT`s. The `ARRAY` will
contain one `STRUCT` for each row in the subquery, and each of these `STRUCT`s
will contain a field for each column in that row.

```googlesql
SELECT
  ARRAY
    (SELECT AS STRUCT 1, 2, 3
     UNION ALL SELECT AS STRUCT 4, 5, 6) AS new_array;

/*---+
 | new_array              |
 +---+
 | [{1, 2, 3}, {4, 5, 6}] |
 +---*/
```

Similarly, to construct an `ARRAY` from a subquery that contains
one or more `ARRAY`s, change the subquery to use `SELECT AS STRUCT`.

```googlesql
SELECT ARRAY
  (SELECT AS STRUCT [1, 2, 3] UNION ALL
   SELECT AS STRUCT [4, 5, 6]) AS new_array;

/*---+
 | new_array                  |
 +---+
 | [{[1, 2, 3]}, {[4, 5, 6]}] |
 +---*/
```

## `ARRAY_CONCAT`

```googlesql
ARRAY_CONCAT(array_expression[, ...])
```

**Description**

Concatenates one or more arrays with the same element type into a single array.

The function returns `NULL` if any input argument is `NULL`.

> [!NOTE]
> **Note:** You can also use the [\|\| concatenation operator](https://docs.cloud.google.com/spanner/docs/reference/standard-sql/operators.md.txt) to concatenate arrays.

**Return type**

`ARRAY`

**Examples**

```googlesql
SELECT ARRAY_CONCAT([1, 2], [3, 4], [5, 6]) as count_to_six;

/*---+
 | count_to_six                                     |
 +---+
 | [1, 2, 3, 4, 5, 6]                               |
 +---*/
```

## `ARRAY_FILTER`

```googlesql
ARRAY_FILTER(array_expression, lambda_expression)

lambda_expression:
  {
    element_alias -> boolean_expression
    | (element_alias, index_alias) -> boolean_expression
  }
```

**Description**

Takes an array, filters out unwanted elements, and returns the results in a new
array.

- `array_expression`: The array to filter.
- `lambda_expression`: Each element in `array_expression` is evaluated against the [lambda expression](https://docs.cloud.google.com/spanner/docs/reference/standard-sql/functions-reference.md.txt#lambdas). If the expression evaluates to `FALSE` or `NULL`, the element is removed from the resulting array.
- `element_alias`: An alias that represents an array element.
- `index_alias`: An alias that represents the zero-based offset of the array element.
- `boolean_expression`: The predicate used to filter the array elements.

Returns `NULL` if the `array_expression` is `NULL`. Otherwise, returns a new
array containing all elements in `array_expression` where the lambda expression
evaluates to `TRUE`, in the same relative order.

**Return type**

ARRAY

**Example**

```googlesql
SELECT
  ARRAY_FILTER([1 ,2, 3], e -> e > 1) AS a1,
  ARRAY_FILTER([0, 2, 3], (e, i) -> e > i) AS a2;

/*---+---+
 | a1    | a2    |
 +---+---+
 | [2,3] | [2,3] |
 +---+---*/
```

## `ARRAY_FIRST`

```googlesql
ARRAY_FIRST(array_expression)
```

**Description**

Takes an array and returns the first element in the array.

Produces an error if the array is empty.

Returns `NULL` if `array_expression` is `NULL`.

> [!NOTE]
> **Note:** To get the last element in an array, see [`ARRAY_LAST`](https://docs.cloud.google.com/spanner/docs/reference/standard-sql/array_functions.md.txt#array_last).

**Return type**

Matches the data type of elements in `array_expression`.

**Example**

```googlesql
SELECT ARRAY_FIRST(['a','b','c','d']) as first_element

/*---+
 | first_element |
 +---+
 | a             |
 +---*/
```

## `ARRAY_INCLUDES`

- [Signature 1](https://docs.cloud.google.com/spanner/docs/reference/standard-sql/array_functions.md.txt#array_includes_signature1): `ARRAY_INCLUDES(array_to_search, search_value)`
- [Signature 2](https://docs.cloud.google.com/spanner/docs/reference/standard-sql/array_functions.md.txt#array_includes_signature2): `ARRAY_INCLUDES(array_to_search, lambda_expression)`

#### Signature 1

```googlesql
ARRAY_INCLUDES(array_to_search, search_value)
```

**Description**

Takes an array and returns `TRUE` if there is an element in the array that is
equal to the search_value.

- `array_to_search`: The array to search.
- `search_value`: The element to search for in the array.

Returns `NULL` if `array_to_search` or `search_value` is `NULL`.

**Return type**

`BOOL`

**Example**

In the following example, the query first checks to see if `0` exists in an
array. Then the query checks to see if `1` exists in an array.

```googlesql
SELECT
  ARRAY_INCLUDES([1, 2, 3], 0) AS a1,
  ARRAY_INCLUDES([1, 2, 3], 1) AS a2;

/*---+---+
 | a1    | a2   |
 +---+---+
 | false | true |
 +---+---*/
```

#### Signature 2

```googlesql
ARRAY_INCLUDES(array_to_search, lambda_expression)

lambda_expression: element_alias -> boolean_expression
```

**Description**

Takes an array and returns `TRUE` if the lambda expression evaluates to `TRUE`
for any element in the array.

- `array_to_search`: The array to search.
- `lambda_expression`: Each element in `array_to_search` is evaluated against the [lambda expression](https://docs.cloud.google.com/spanner/docs/reference/standard-sql/functions-reference.md.txt#lambdas).
- `element_alias`: An alias that represents an array element.
- `boolean_expression`: The predicate used to evaluate the array elements.

Returns `NULL` if `array_to_search` is `NULL`.

**Return type**

`BOOL`

**Example**

In the following example, the query first checks to see if any elements that are
greater than 3 exist in an array (`e > 3`). Then the query checks to see if any
elements that are greater than 0 exist in an array (`e > 0`).

```googlesql
SELECT
  ARRAY_INCLUDES([1, 2, 3], e -> e > 3) AS a1,
  ARRAY_INCLUDES([1, 2, 3], e -> e > 0) AS a2;

/*---+---+
 | a1    | a2   |
 +---+---+
 | false | true |
 +---+---*/
```

## `ARRAY_INCLUDES_ALL`

```googlesql
ARRAY_INCLUDES_ALL(array_to_search, search_values)
```

**Description**

Takes an array to search and an array of search values. Returns `TRUE` if all
search values are in the array to search, otherwise returns `FALSE`.

- `array_to_search`: The array to search.
- `search_values`: The array that contains the elements to search for.

Returns `NULL` if `array_to_search` or `search_values` is
`NULL`.

**Return type**

`BOOL`

**Example**

In the following example, the query first checks to see if `3`, `4`, and `5`
exists in an array. Then the query checks to see if `4`, `5`, and `6` exists in
an array.

```googlesql
SELECT
  ARRAY_INCLUDES_ALL([1,2,3,4,5], [3,4,5]) AS a1,
  ARRAY_INCLUDES_ALL([1,2,3,4,5], [4,5,6]) AS a2;

/*---+---+
 | a1   | a2    |
 +---+---+
 | true | false |
 +---+---*/
```

## `ARRAY_INCLUDES_ANY`

```googlesql
ARRAY_INCLUDES_ANY(array_to_search, search_values)
```

**Description**

Takes an array to search and an array of search values. Returns `TRUE` if any
search values are in the array to search, otherwise returns `FALSE`.

- `array_to_search`: The array to search.
- `search_values`: The array that contains the elements to search for.

Returns `NULL` if `array_to_search` or `search_values` is
`NULL`.

**Return type**

`BOOL`

**Example**

In the following example, the query first checks to see if `3`, `4`, or `5`
exists in an array. Then the query checks to see if `4`, `5`, or `6` exists in
an array.

```googlesql
SELECT
  ARRAY_INCLUDES_ANY([1,2,3], [3,4,5]) AS a1,
  ARRAY_INCLUDES_ANY([1,2,3], [4,5,6]) AS a2;

/*---+---+
 | a1   | a2    |
 +---+---+
 | true | false |
 +---+---*/
```

## `ARRAY_IS_DISTINCT`

```googlesql
ARRAY_IS_DISTINCT(value)
```

**Description**

Returns `TRUE` if the array contains no repeated elements, using the same
equality comparison logic as `SELECT DISTINCT`.

**Return type**

`BOOL`

**Examples**

```googlesql
SELECT ARRAY_IS_DISTINCT([1, 2, 3]) AS is_distinct

/*---+
 | is_distinct |
 +---+
 | true        |
 +---*/
```

```googlesql
SELECT ARRAY_IS_DISTINCT([1, 1, 1]) AS is_distinct

/*---+
 | is_distinct |
 +---+
 | false       |
 +---*/
```

```googlesql
SELECT ARRAY_IS_DISTINCT([1, 2, NULL]) AS is_distinct

/*---+
 | is_distinct |
 +---+
 | true        |
 +---*/
```

```googlesql
SELECT ARRAY_IS_DISTINCT([1, 1, NULL]) AS is_distinct

/*---+
 | is_distinct |
 +---+
 | false       |
 +---*/
```

```googlesql
SELECT ARRAY_IS_DISTINCT([1, NULL, NULL]) AS is_distinct

/*---+
 | is_distinct |
 +---+
 | false       |
 +---*/
```

```googlesql
SELECT ARRAY_IS_DISTINCT([]) AS is_distinct

/*---+
 | is_distinct |
 +---+
 | true        |
 +---*/
```

```googlesql
SELECT ARRAY_IS_DISTINCT(NULL) AS is_distinct

/*---+
 | is_distinct |
 +---+
 | NULL        |
 +---*/
```

## `ARRAY_LAST`

```googlesql
ARRAY_LAST(array_expression)
```

**Description**

Takes an array and returns the last element in the array.

Produces an error if the array is empty.

Returns `NULL` if `array_expression` is `NULL`.

> [!NOTE]
> **Note:** To get the first element in an array, see [`ARRAY_FIRST`](https://docs.cloud.google.com/spanner/docs/reference/standard-sql/array_functions.md.txt#array_first).

**Return type**

Matches the data type of elements in `array_expression`.

**Example**

```googlesql
SELECT ARRAY_LAST(['a','b','c','d']) as last_element

/*---+
 | last_element  |
 +---+
 | d             |
 +---*/
```

## `ARRAY_LENGTH`

```googlesql
ARRAY_LENGTH(array_expression)
```

**Description**

Returns the size of the array. Returns 0 for an empty array. Returns `NULL` if
the `array_expression` is `NULL`.

**Return type**

`INT64`

**Examples**

```googlesql
SELECT
  ARRAY_LENGTH(["coffee", NULL, "milk" ]) AS size_a,
  ARRAY_LENGTH(["cake", "pie"]) AS size_b;

/*---+---+
 | size_a | size_b |
 +---+---+
 | 3      | 2      |
 +---+---*/
```

## `ARRAY_MAX`

```googlesql
ARRAY_MAX(input_array)
```

**Description**

Returns the maximum non-`NULL` value in an array.

Caveats:

- If the array is `NULL`, empty, or contains only `NULL`s, returns `NULL`.
- If the array contains `NaN`, returns `NaN`.

**Supported Argument Types**

In the input array, `ARRAY<T>`, `T` can be an
[orderable data type](https://docs.cloud.google.com/spanner/docs/reference/standard-sql/data-types.md.txt#data_type_properties).

**Return type**

The same data type as `T` in the input array.

**Examples**

```googlesql
SELECT ARRAY_MAX([8, 37, NULL, 55, 4]) as max

/*---+
 | max |
 +---+
 | 55  |
 +---*/
```

## `ARRAY_MIN`

```googlesql
ARRAY_MIN(input_array)
```

**Description**

Returns the minimum non-`NULL` value in an array.

Caveats:

- If the array is `NULL`, empty, or contains only `NULL`s, returns `NULL`.
- If the array contains `NaN`, returns `NaN`.

**Supported Argument Types**

In the input array, `ARRAY<T>`, `T` can be an
[orderable data type](https://docs.cloud.google.com/spanner/docs/reference/standard-sql/data-types.md.txt#data_type_properties).

**Return type**

The same data type as `T` in the input array.

**Examples**

```googlesql
SELECT ARRAY_MIN([8, 37, NULL, 4, 55]) as min

/*---+
 | min |
 +---+
 | 4   |
 +---*/
```

## `ARRAY_REVERSE`

```googlesql
ARRAY_REVERSE(value)
```

**Description**

Returns the input `ARRAY` with elements in reverse order.

**Return type**

`ARRAY`

**Examples**

```googlesql
SELECT ARRAY_REVERSE([1, 2, 3]) AS reverse_arr

/*---+
 | reverse_arr |
 +---+
 | [3, 2, 1]   |
 +---*/
```

## `ARRAY_SLICE`

```googlesql
ARRAY_SLICE(array_to_slice, start_offset, end_offset)
```

**Description**

Returns an array containing zero or more consecutive elements from the
input array.

- `array_to_slice`: The array that contains the elements you want to slice.
- `start_offset`: The inclusive starting offset.
- `end_offset`: The inclusive ending offset.

An offset can be positive or negative. A positive offset starts from the
beginning of the input array and is 0-based. A negative offset starts from
the end of the input array. Out-of-bounds offsets are supported. Here are some
examples:

<br />

| Input offset | Final offset in array | Notes |
|---|---|---|
| 0 | \[**'a'**, 'b', 'c', 'd'\] | The final offset is `0`. |
| 3 | \['a', 'b', 'c', **'d'**\] | The final offset is `3`. |
| 5 | \['a', 'b', 'c', **'d'**\] | Because the input offset is out of bounds, the final offset is `3` (`array length - 1`). |
| -1 | \['a', 'b', 'c', **'d'**\] | Because a negative offset is used, the offset starts at the end of the array. The final offset is `3` (`array length - 1`). |
| -2 | \['a', 'b', **'c'**, 'd'\] | Because a negative offset is used, the offset starts at the end of the array. The final offset is `2` (`array length - 2`). |
| -4 | \[**'a'**, 'b', 'c', 'd'\] | Because a negative offset is used, the offset starts at the end of the array. The final offset is `0` (`array length - 4`). |
| -5 | \[**'a'**, 'b', 'c', 'd'\] | Because the offset is negative and out of bounds, the final offset is `0` (`array length - array length`). |

<br />

Additional details:

- The input array can contain `NULL` elements. `NULL` elements are included in the resulting array.
- Returns `NULL` if `array_to_slice`, `start_offset`, or `end_offset` is `NULL`.
- Returns an empty array if `array_to_slice` is empty.
- Returns an empty array if the position of the `start_offset` in the array is after the position of the `end_offset`.

**Return type**

`ARRAY`

**Examples**

```googlesql
SELECT ARRAY_SLICE(['a', 'b', 'c', 'd', 'e'], 1, 3) AS result

/*---+
 | result    |
 +---+
 | [b, c, d] |
 +---*/
```

```googlesql
SELECT ARRAY_SLICE(['a', 'b', 'c', 'd', 'e'], -1, 3) AS result

/*---+
 | result    |
 +---+
 | []        |
 +---*/
```

```googlesql
SELECT ARRAY_SLICE(['a', 'b', 'c', 'd', 'e'], 1, -3) AS result

/*---+
 | result |
 +---+
 | [b, c] |
 +---*/
```

```googlesql
SELECT ARRAY_SLICE(['a', 'b', 'c', 'd', 'e'], -1, -3) AS result

/*---+
 | result    |
 +---+
 | []        |
 +---*/
```

```googlesql
SELECT ARRAY_SLICE(['a', 'b', 'c', 'd', 'e'], -3, -1) AS result

/*---+
 | result    |
 +---+
 | [c, d, e] |
 +---*/
```

```googlesql
SELECT ARRAY_SLICE(['a', 'b', 'c', 'd', 'e'], 3, 3) AS result

/*---+
 | result |
 +---+
 | [d]    |
 +---*/
```

```googlesql
SELECT ARRAY_SLICE(['a', 'b', 'c', 'd', 'e'], -3, -3) AS result

/*---+
 | result |
 +---+
 | [c]    |
 +---*/
```

```googlesql
SELECT ARRAY_SLICE(['a', 'b', 'c', 'd', 'e'], 1, 30) AS result

/*---+
 | result       |
 +---+
 | [b, c, d, e] |
 +---*/
```

```googlesql
SELECT ARRAY_SLICE(['a', 'b', 'c', 'd', 'e'], 1, -30) AS result

/*---+
 | result    |
 +---+
 | []        |
 +---*/
```

```googlesql
SELECT ARRAY_SLICE(['a', 'b', 'c', 'd', 'e'], -30, 30) AS result

/*---+
 | result          |
 +---+
 | [a, b, c, d, e] |
 +---*/
```

```googlesql
SELECT ARRAY_SLICE(['a', 'b', 'c', 'd', 'e'], -30, -5) AS result

/*---+
 | result |
 +---+
 | [a]    |
 +---*/
```

```googlesql
SELECT ARRAY_SLICE(['a', 'b', 'c', 'd', 'e'], 5, 30) AS result

/*---+
 | result |
 +---+
 | []     |
 +---*/
```

```googlesql
SELECT ARRAY_SLICE(['a', 'b', 'c', 'd', 'e'], 1, NULL) AS result

/*---+
 | result    |
 +---+
 | NULL      |
 +---*/
```

```googlesql
SELECT ARRAY_SLICE(['a', 'b', NULL, 'd', 'e'], 1, 3) AS result

/*---+
 | result       |
 +---+
 | [b, NULL, d] |
 +---*/
```

## `ARRAY_TO_STRING`

```googlesql
ARRAY_TO_STRING(array_expression, delimiter[, null_text])
```

**Description**

Returns a concatenation of the elements in `array_expression` as a `STRING`
or `BYTES` value. The value for `array_expression` can
either be an array of `STRING` or `BYTES` data type.

If the `null_text` parameter is used, the function replaces any `NULL` values in
the array with the value of `null_text`.

If the `null_text` parameter isn't used, the function omits the `NULL` value
and its preceding delimiter.

**Return type**

- `STRING` for a function signature with `STRING` input.
- `BYTES` for a function signature with `BYTES` input.

**Examples**

```googlesql
SELECT ARRAY_TO_STRING(['coffee', 'tea', 'milk', NULL], '--', 'MISSING') AS text

/*---+
 | text                           |
 +---+
 | coffee--tea--milk--MISSING     |
 +---*/
```

```googlesql

SELECT ARRAY_TO_STRING(['cake', 'pie', NULL], '--', 'MISSING') AS text

/*---+
 | text                           |
 +---+
 | cake--pie--MISSING             |
 +---*/
```

```googlesql

SELECT ARRAY_TO_STRING([b'prefix', b'middle', b'suffix', b'\x00'], b'--') AS data

/*---+
 | data                           |
 +---+
 | prefix--middle--suffix--\x00   |
 +---*/
```

## `ARRAY_TRANSFORM`

```googlesql
ARRAY_TRANSFORM(array_expression, lambda_expression)

lambda_expression:
  {
    element_alias -> transform_expression
    | (element_alias, index_alias) -> transform_expression
  }
```

**Description**

Takes an array, transforms the elements, and returns the results in a new
array. The output array has the same length and order as the input array. The
element at a given index is transformed to the same index in the new
array.

- `array_expression`: The array to transform.
- `lambda_expression`: Each element in `array_expression` is evaluated against the [lambda expression](https://docs.cloud.google.com/spanner/docs/reference/standard-sql/functions-reference.md.txt#lambdas). The evaluation results are returned in a new array.
- `element_alias`: An alias that represents an array element.
- `index_alias`: An alias that represents the zero-based offset of the array element.
- `transform_expression`: The expression used to transform the array elements.

Returns `NULL` if the `array_expression` is `NULL`.

**Return type**

`ARRAY`

**Example**

```googlesql
SELECT
  ARRAY_TRANSFORM([1, 4, 3], e -> e + 1) AS a1,
  ARRAY_TRANSFORM([1, 4, 3], (e, i) -> e + i) AS a2;

/*---+---+
 | a1      | a2      |
 +---+---+
 | [2,5,4] | [1,5,5] |
 +---+---*/
```

## `GENERATE_ARRAY`

```googlesql
GENERATE_ARRAY(start_expression, end_expression[, step_expression])
```

**Description**

Returns an array of values. The `start_expression` and `end_expression`
parameters determine the inclusive start and end of the array.

The `GENERATE_ARRAY` function accepts the following data types as inputs:

- `INT64`
- `NUMERIC`
- `FLOAT64`

The `step_expression` parameter determines the increment used to
generate array values. The default value for this parameter is `1`.

The `GENERATE_ARRAY` function returns an error if any of the following are true:

- `step_expression` evaluates to 0.
- Any input is `NaN`.
- The resulting array is too large.

If any argument is `NULL`, the function returns a `NULL` array.

**Return Data Type**

`ARRAY`

**Examples**

The following returns an array of integers, with a default step of 1.

```googlesql
SELECT GENERATE_ARRAY(1, 5) AS example_array;

/*---+
 | example_array   |
 +---+
 | [1, 2, 3, 4, 5] |
 +---*/
```

The following returns an array using a user-specified step size.

```googlesql
SELECT GENERATE_ARRAY(0, 10, 3) AS example_array;

/*---+
 | example_array |
 +---+
 | [0, 3, 6, 9]  |
 +---*/
```

The following returns an array using a negative value, `-3` for its step size.

```googlesql
SELECT GENERATE_ARRAY(10, 0, -3) AS example_array;

/*---+
 | example_array |
 +---+
 | [10, 7, 4, 1] |
 +---*/
```

The following returns an array using the same value for the `start_expression`
and `end_expression`.

```googlesql
SELECT GENERATE_ARRAY(4, 4, 10) AS example_array;

/*---+
 | example_array |
 +---+
 | [4]           |
 +---*/
```

The following returns an empty array, because the `start_expression` is greater
than the `end_expression`, and the `step_expression` value is positive.

```googlesql
SELECT GENERATE_ARRAY(10, 0, 3) AS example_array;

/*---+
 | example_array |
 +---+
 | []            |
 +---*/
```

The following returns a `NULL` array because `end_expression` is `NULL`.

```googlesql
SELECT GENERATE_ARRAY(5, NULL, 1) AS example_array;

/*---+
 | example_array |
 +---+
 | NULL          |
 +---*/
```

The following returns multiple arrays.

```googlesql
SELECT GENERATE_ARRAY(start, 5) AS example_array
FROM UNNEST([3, 4, 5]) AS start;

/*---+
 | example_array |
 +---+
 | [3, 4, 5]     |
 | [4, 5]        |
 | [5]           |
 +---*/
```

## `GENERATE_DATE_ARRAY`

```googlesql
GENERATE_DATE_ARRAY(start_date, end_date[, INTERVAL step_size step_unit])
```

**Description**

Returns an array of dates. The `start_date` and `end_date`
parameters determine the inclusive start and end of the array.

The `GENERATE_DATE_ARRAY` function accepts the following data types as inputs:

- `start_date`: `DATE`
- `end_date`: `DATE`
- `step_size`: `INT64`
- `step_unit`: `DAY`, `WEEK`, `MONTH`, `QUARTER`, or `YEAR`.

The `step_size` parameter determines the increment used to generate dates. The
default value for this parameter is 1 day.

The `GENERATE_DATE_ARRAY` function returns an error if any of the following are
true:

- `step_size` is set to 0.
- The resulting array exceeds the maximum size allowed by the engine. The GoogleSQL default limit is 16,000 elements, though individual query engines may configure a different limit.

**Return Data Type**

`ARRAY` containing 0 or more `DATE` values.

**Examples**

The following returns an array of dates, with a default step of 1.

```googlesql
SELECT GENERATE_DATE_ARRAY('2016-10-05', '2016-10-08') AS example;

/*---+
 | example                                          |
 +---+
 | [2016-10-05, 2016-10-06, 2016-10-07, 2016-10-08] |
 +---*/
```

The following returns an array using a user-specified step size.

```googlesql
SELECT GENERATE_DATE_ARRAY(
 '2016-10-05', '2016-10-09', INTERVAL 2 DAY) AS example;

/*---+
 | example                              |
 +---+
 | [2016-10-05, 2016-10-07, 2016-10-09] |
 +---*/
```

The following returns an array using a negative value, `-3` for its step size.

```googlesql
SELECT GENERATE_DATE_ARRAY('2016-10-05',
  '2016-10-01', INTERVAL -3 DAY) AS example;

/*---+
 | example                  |
 +---+
 | [2016-10-05, 2016-10-02] |
 +---*/
```

The following returns an array using the same value for the `start_date`and
`end_date`.

```googlesql
SELECT GENERATE_DATE_ARRAY('2016-10-05',
  '2016-10-05', INTERVAL 8 DAY) AS example;

/*---+
 | example      |
 +---+
 | [2016-10-05] |
 +---*/
```

The following returns an empty array, because the `start_date` is greater
than the `end_date`, and the `step` value is positive.

```googlesql
SELECT GENERATE_DATE_ARRAY('2016-10-05',
  '2016-10-01', INTERVAL 1 DAY) AS example;

/*---+
 | example |
 +---+
 | []      |
 +---*/
```

The following returns a `NULL` array, because one of its inputs is
`NULL`.

```googlesql
SELECT GENERATE_DATE_ARRAY('2016-10-05', NULL) AS example;

/*---+
 | example |
 +---+
 | NULL    |
 +---*/
```

The following returns an array of dates, using MONTH as the `step_unit`
interval:

```googlesql
SELECT GENERATE_DATE_ARRAY('2016-01-01',
  '2016-12-31', INTERVAL 2 MONTH) AS example;

/*---+
 | example                                                                  |
 +---+
 | [2016-01-01, 2016-03-01, 2016-05-01, 2016-07-01, 2016-09-01, 2016-11-01] |
 +---*/
```

The following uses non-constant dates to generate an array.

```googlesql
SELECT GENERATE_DATE_ARRAY(date_start, date_end, INTERVAL 1 WEEK) AS date_range
FROM (
  SELECT DATE '2016-01-01' AS date_start, DATE '2016-01-31' AS date_end
  UNION ALL SELECT DATE "2016-04-01", DATE "2016-04-30"
  UNION ALL SELECT DATE "2016-07-01", DATE "2016-07-31"
  UNION ALL SELECT DATE "2016-10-01", DATE "2016-10-31"
) AS items;

/*---+
 | date_range                                                   |
 +---+
 | [2016-01-01, 2016-01-08, 2016-01-15, 2016-01-22, 2016-01-29] |
 | [2016-04-01, 2016-04-08, 2016-04-15, 2016-04-22, 2016-04-29] |
 | [2016-07-01, 2016-07-08, 2016-07-15, 2016-07-22, 2016-07-29] |
 | [2016-10-01, 2016-10-08, 2016-10-15, 2016-10-22, 2016-10-29] |
 +---*/
```

## Supplemental materials

### OFFSET and ORDINAL

For information about using `OFFSET` and `ORDINAL` with arrays, see
[Array subscript operator](https://docs.cloud.google.com/spanner/docs/reference/standard-sql/operators.md.txt#array_subscript_operator) and [Accessing array
elements](https://docs.cloud.google.com/spanner/docs/reference/standard-sql/arrays#accessing_array_elements).