GoogleSQL for Bigtable supports the following array functions.

## Function list

| Name | Summary |
|---|---|
| [`ARRAY_AGG`](https://docs.cloud.google.com/bigtable/docs/reference/sql/aggregate_functions#array_agg) | Gets an array of values. For more information, see [Aggregate functions](https://docs.cloud.google.com/bigtable/docs/reference/sql/aggregate_functions). |
| [`ARRAY_CONCAT`](https://docs.cloud.google.com/bigtable/docs/reference/sql/array_functions#array_concat) | Concatenates one or more arrays with the same element type into a single array. |
| [`ARRAY_CONCAT_AGG`](https://docs.cloud.google.com/bigtable/docs/reference/sql/aggregate_functions#array_concat_agg) | Concatenates arrays and returns a single array as a result. For more information, see [Aggregate functions](https://docs.cloud.google.com/bigtable/docs/reference/sql/aggregate_functions). |
| [`ARRAY_FILTER`](https://docs.cloud.google.com/bigtable/docs/reference/sql/array_functions#array_filter) | Takes an array, filters out unwanted elements, and returns the results in a new array. |
| [`ARRAY_FIRST`](https://docs.cloud.google.com/bigtable/docs/reference/sql/array_functions#array_first) | Gets the first element in an array. |
| [`ARRAY_INCLUDES`](https://docs.cloud.google.com/bigtable/docs/reference/sql/array_functions#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/bigtable/docs/reference/sql/array_functions#array_includes_all) | Checks if all search values are in an array. |
| [`ARRAY_INCLUDES_ANY`](https://docs.cloud.google.com/bigtable/docs/reference/sql/array_functions#array_includes_any) | Checks if any search values are in an array. |
| [`ARRAY_IS_DISTINCT`](https://docs.cloud.google.com/bigtable/docs/reference/sql/array_functions#array_is_distinct) | Checks if an array contains no repeated elements. |
| [`ARRAY_LAST`](https://docs.cloud.google.com/bigtable/docs/reference/sql/array_functions#array_last) | Gets the last element in an array. |
| [`ARRAY_LAST_N`](https://docs.cloud.google.com/bigtable/docs/reference/sql/array_functions#array_last_n) | Gets the suffix of an array, consisting of the last `n` elements. |
| [`ARRAY_LENGTH`](https://docs.cloud.google.com/bigtable/docs/reference/sql/array_functions#array_length) | Gets the number of elements in an array. |
| [`ARRAY_OFFSET`](https://docs.cloud.google.com/bigtable/docs/reference/sql/array_functions#array_offset) | Searches an array from the beginning or ending and produces the zero-based offset for the first matching element. |
| [`ARRAY_OFFSETS`](https://docs.cloud.google.com/bigtable/docs/reference/sql/array_functions#array_offsets) | Searches an array and gets the zero-based offsets for matching elements. |
| [`ARRAY_REVERSE`](https://docs.cloud.google.com/bigtable/docs/reference/sql/array_functions#array_reverse) | Reverses the order of elements in an array. |
| [`ARRAY_SLICE`](https://docs.cloud.google.com/bigtable/docs/reference/sql/array_functions#array_slice) | Produces an array containing zero or more consecutive elements from an input array. |
| [`ARRAY_TO_STRING`](https://docs.cloud.google.com/bigtable/docs/reference/sql/array_functions#array_to_string) | Produces a concatenation of the elements in an array as a `STRING` value. |
| [`ARRAY_TRANSFORM`](https://docs.cloud.google.com/bigtable/docs/reference/sql/array_functions#array_transform) | Transforms the elements of an array, and returns the results in a new array. |
| [`GENERATE_ARRAY`](https://docs.cloud.google.com/bigtable/docs/reference/sql/array_functions#generate_array) | Generates an array of values in a range. |
| [`GENERATE_DATE_ARRAY`](https://docs.cloud.google.com/bigtable/docs/reference/sql/array_functions#generate_date_array) | Generates an array of dates in a range. |
| [`GENERATE_TIMESTAMP_ARRAY`](https://docs.cloud.google.com/bigtable/docs/reference/sql/array_functions#generate_timestamp_array) | Generates an array of timestamps in a range. |
| [`JSON_QUERY_ARRAY`](https://docs.cloud.google.com/bigtable/docs/reference/sql/json_functions#json_query_array) | Extracts a JSON array and converts it to a SQL `ARRAY<JSON-formatted STRING>` value. For more information, see [JSON functions](https://docs.cloud.google.com/bigtable/docs/reference/sql/json_functions). |

## `ARRAY_CONCAT`

    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/bigtable/docs/reference/sql/operators) to concatenate arrays.

**Return type**

`ARRAY`

**Examples**

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

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

## `ARRAY_FILTER`

    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/bigtable/docs/reference/sql/functions-reference#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**

    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`

    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/bigtable/docs/reference/sql/array_functions#array_last).

**Return type**

Matches the data type of elements in `array_expression`.

**Example**

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

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

## `ARRAY_INCLUDES`

    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.

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

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

## `ARRAY_INCLUDES_ALL`

    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.

    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`

    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.

    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`

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

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

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

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

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

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

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

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

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

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

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

    SELECT ARRAY_IS_DISTINCT([]) AS is_distinct

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

    SELECT ARRAY_IS_DISTINCT(NULL) AS is_distinct

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

## `ARRAY_LAST`

    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/bigtable/docs/reference/sql/array_functions#array_first).

**Return type**

Matches the data type of elements in `array_expression`.

**Example**

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

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

## `ARRAY_LAST_N`

    ARRAY_LAST_N(input_array, n)

**Description**

Returns a suffix of `input_array` consisting of the last `n` elements.

Caveats:

- If `input_array` is `NULL`, returns `NULL`.
- If `n` is `NULL`, returns `NULL`.
- If `n` is `0`, returns an empty array.
- If `n` is longer than `input_array`, returns `input_array`.
- If `n` is negative, produces an error.

> [!NOTE]
> **Note:** To return a prefix in a similar way, see \[`ARRAY_FIRST_N`\]\[array-first-n\].

**Return type**

`ARRAY`

**Example**

    SELECT
      ARRAY_LAST_N([1, 2, 3, 4, 5], 0) AS a,
      ARRAY_LAST_N([1, 2, 3, 4, 5], 3) AS b,
      ARRAY_LAST_N([1, 2, 3, 4, 5], 7) AS c

    /*---+
     | a  | b         | c               |
     +---+
     | [] | [3, 4, 5] | [1, 2, 3, 4, 5] |
     +---*/

    -- Error: out of bounds
    SELECT ARRAY_LAST_N([1, 2, 3, 4, 5], -1)

## `ARRAY_LENGTH`

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

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

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

## `ARRAY_OFFSET`

    ARRAY_OFFSET(input_array, element_to_find[, first_or_last])

    element_to_find:
      { element_expression | element_lambda_expression }

    element_lambda_expression:
      element_alias -> boolean_expression

**Description**

Searches an array from the beginning or ending and gets the zero-based offset
for the first matching element. If no element is found, returns `NULL`.

Arguments:

- `input_array`: The array to search.
- `element_expression`: The element to find in the array. Must be a comparable data type.
- `element_lambda_expression`: Each element in `input_array` is evaluated against the lambda expression. If the expression evaluates to `TRUE`, the element is included in the search results.
- `element_alias`: An alias that represents the element to find.
- `boolean_expression`: The predicate used to filter the array elements.
- `first_or_last`: Search from the beginning (`FIRST`) or ending (`LAST`) of the array. By default the function searches from the beginning.

**Return type**

`INT64`

**Examples**

The following queries get the offset for the first `4` in an array.

    SELECT ARRAY_OFFSET([1, 4, 4, 4, 6], 4) AS result

    /*---+
     | result |
     +---+
     | 1      |
     +---*/

    SELECT ARRAY_OFFSET([1, 4, 4, 4, 6], 4, 'FIRST') AS result

    /*---+
     | result |
     +---+
     | 1      |
     +---*/

The following queries get the offset for the last `4` in an array.

    SELECT ARRAY_OFFSET([1, 4, 4, 4, 6], 4, 'LAST') AS result

    /*---+
     | result |
     +---+
     | 3      |
     +---*/

    SELECT ARRAY_OFFSET([1, 4, 4, 4, 6], e -> e = 4, 'LAST') AS result

    /*---+
     | result |
     +---+
     | 3      |
     +---*/

The following query gets the offset for the last element in an array that is
greater than `2` and less than `5`.

    SELECT ARRAY_OFFSET([1, 4, 4, 4, 6], e -> e > 2 AND e < 5, 'LAST') AS result

    /*---+
     | result |
     +---+
     | 3      |
     +---*/

The following query produces `NULL` because `5` isn't in the array.

    SELECT ARRAY_OFFSET([1, 4, 4, 4, 6], 5) AS result

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

The following query produces `NULL` because there are no elements greater
than `7` in the array.

    SELECT ARRAY_OFFSET([1, 4, 4, 4, 6], e -> e > 7) AS result

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

## `ARRAY_OFFSETS`

    ARRAY_OFFSETS(input_array, element_to_find)

    element_to_find:
      { element_expression | element_lambda_expression }

    element_lambda_expression:
      element_alias -> boolean_expression

**Description**

Searches an array and gets the zero-based offsets for matching elements.
If no matching element is found, returns an empty array.

Arguments:

- `input_array`: The array to search.
- `element_expression`: The element to find in the array. Must be a comparable data type.
- `element_lambda_expression`: Each element in `input_array` is evaluated against the lambda expression. If the expression evaluates to `TRUE`, the element is included in the search results.
- `element_alias`: An alias that represents the element to find.
- `boolean_expression`: The predicate used to filter the array elements.

**Return type**

`ARRAY<INT64>`

**Examples**

The following query gets all offsets for `4` in an array.

    SELECT ARRAY_OFFSETS([1, 4, 4, 4, 6, 4], 4) AS result

    /*---+
     | result       |
     +---+
     | [1, 2, 3, 5] |
     +---*/

The following query gets the offsets for elements in an array that are
greater than `2` and less than `5`.

    SELECT ARRAY_OFFSETS([1, 4, 7, 3, 6, 4], e -> e > 2 AND e < 5) AS result

    /*---+
     | result    |
     +---+
     | [1, 3, 5] |
     +---*/

The following query produces an empty array because `5` isn't in the array.

    SELECT ARRAY_OFFSETS([1, 4, 4, 4, 6], 5) AS result

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

The following query produces an empty array because there are no elements
greater than `7` in the array.

    SELECT ARRAY_OFFSETS([1, 4, 4, 4, 6], e -> e > 7) AS result

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

## `ARRAY_REVERSE`

    ARRAY_REVERSE(value)

**Description**

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

**Return type**

`ARRAY`

**Examples**

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

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

## `ARRAY_SLICE`

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

## `ARRAY_TO_STRING`

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

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

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


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

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


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

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

## `ARRAY_TRANSFORM`

    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/bigtable/docs/reference/sql/functions-reference#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**

    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`

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

    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.

    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.

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

    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.

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

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

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

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

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

The following returns multiple arrays.

    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`

    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.

    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.

    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.

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

    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.

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

    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:

    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.

    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] |
     +---*/

## `GENERATE_TIMESTAMP_ARRAY`

    GENERATE_TIMESTAMP_ARRAY(start_timestamp, end_timestamp,
                             INTERVAL step_size step_unit)

**Description**

Returns an `ARRAY` of `TIMESTAMPS` separated by a given interval. The
`start_timestamp` and `end_timestamp` parameters determine the inclusive
lower and upper bounds of the `ARRAY`.

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

- `start_timestamp`: `TIMESTAMP`
- `end_timestamp`: `TIMESTAMP`
- `step_size`: `INT64`
- `step_unit`: `MICROSECOND`, `MILLISECOND`, `SECOND`, `MINUTE`, `HOUR`, or `DAY`.

The `step_size` parameter determines the increment used to generate
timestamps.

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

- `step_size` evaluates 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**

An `ARRAY` containing 0 or more `TIMESTAMP` values.

**Examples**

The following example returns an `ARRAY` of `TIMESTAMP`s at intervals of 1 day.

    SELECT GENERATE_TIMESTAMP_ARRAY('2016-10-05 00:00:00', '2016-10-07 00:00:00',
                                    INTERVAL 1 DAY) AS timestamp_array;

    /*---+
     | timestamp_array                                                          |
     +---+
     | [2016-10-05 00:00:00+00, 2016-10-06 00:00:00+00, 2016-10-07 00:00:00+00] |
     +---*/

The following example returns an `ARRAY` of `TIMESTAMP`s at intervals of 1
second.

    SELECT GENERATE_TIMESTAMP_ARRAY('2016-10-05 00:00:00', '2016-10-05 00:00:02',
                                    INTERVAL 1 SECOND) AS timestamp_array;

    /*---+
     | timestamp_array                                                          |
     +---+
     | [2016-10-05 00:00:00+00, 2016-10-05 00:00:01+00, 2016-10-05 00:00:02+00] |
     +---*/

The following example returns an `ARRAY` of `TIMESTAMPS` with a negative
interval.

    SELECT GENERATE_TIMESTAMP_ARRAY('2016-10-06 00:00:00', '2016-10-01 00:00:00',
                                    INTERVAL -2 DAY) AS timestamp_array;

    /*---+
     | timestamp_array                                                          |
     +---+
     | [2016-10-06 00:00:00+00, 2016-10-04 00:00:00+00, 2016-10-02 00:00:00+00] |
     +---*/

The following example returns an `ARRAY` with a single element, because
`start_timestamp` and `end_timestamp` have the same value.

    SELECT GENERATE_TIMESTAMP_ARRAY('2016-10-05 00:00:00', '2016-10-05 00:00:00',
                                    INTERVAL 1 HOUR) AS timestamp_array;

    /*---+
     | timestamp_array          |
     +---+
     | [2016-10-05 00:00:00+00] |
     +---*/

The following example returns an empty `ARRAY`, because `start_timestamp` is
later than `end_timestamp`.

    SELECT GENERATE_TIMESTAMP_ARRAY('2016-10-06 00:00:00', '2016-10-05 00:00:00',
                                    INTERVAL 1 HOUR) AS timestamp_array;

    /*---+
     | timestamp_array |
     +---+
     | []              |
     +---*/

The following example returns a null `ARRAY`, because one of the inputs is
`NULL`.

    SELECT GENERATE_TIMESTAMP_ARRAY('2016-10-05 00:00:00', NULL, INTERVAL 1 HOUR)
      AS timestamp_array;

    /*---+
     | timestamp_array |
     +---+
     | NULL            |
     +---*/

## Supplemental materials

### OFFSET and ORDINAL

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