Array functions

{# Automated backup default retention period}

GoogleSQL for Bigtable supports the following array functions.

Function list

Name Summary
ARRAY_CONCAT Concatenates one or more arrays with the same element type into a single array.
ARRAY_FILTER Takes an array, filters out unwanted elements, and returns the results in a new array.
ARRAY_FIRST Gets the first element in an array.
ARRAY_INCLUDES Checks if there is an element in the array that is equal to a search value.
ARRAY_INCLUDES_ALL Checks if all search values are in an array.
ARRAY_INCLUDES_ANY Checks if any search values are in an array.
ARRAY_IS_DISTINCT Checks if an array contains no repeated elements.
ARRAY_LAST Gets the last element in an array.
ARRAY_LAST_N Gets the suffix of an array, consisting of the last n elements.
ARRAY_LENGTH Gets the number of elements in an array.
ARRAY_OFFSET Searches an array from the beginning or ending and produces the zero-based offset for the first matching element.
ARRAY_OFFSETS Searches an array and gets the zero-based offsets for matching elements.
ARRAY_REVERSE Reverses the order of elements in an array.
ARRAY_SLICE Produces an array containing zero or more consecutive elements from an input array.
ARRAY_TO_STRING Produces a concatenation of the elements in an array as a STRING value.
ARRAY_TRANSFORM Transforms the elements of an array, and returns the results in a new array.
GENERATE_ARRAY Generates an array of values in a range.
GENERATE_DATE_ARRAY Generates an array of dates in a range.
GENERATE_TIMESTAMP_ARRAY Generates an array of timestamps in a range.
JSON_QUERY_ARRAY Extracts a JSON array and converts it to a SQL ARRAY<JSON-formatted STRING> value.
For more information, see 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.

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

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.

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.

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.

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 }

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 is not 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 }

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 is not 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:

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

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. The value for array_expression can either be an array of STRING or BYTES data types.

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 is not used, the function omits the NULL value and its preceding delimiter.

Return type

STRING

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

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 always has the same length as the input array.

  • array_expression: The array to transform.
  • lambda_expression: Each element in array_expression is evaluated against the lambda expression. 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, 2, 3], e -> e + 1) AS a1,
  ARRAY_TRANSFORM([1, 2, 3], (e, i) -> e + i) AS a2;

/*---------+---------*
 | a1      | a2      |
 +---------+---------+
 | [2,3,4] | [1,3,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.

This function returns an error if step_expression is set to 0, or if any input is NaN.

If any argument is NULL, the function will return 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 INT64_expr date_part])

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 must be a DATE.
  • end_date must be a DATE.
  • INT64_expr must be an INT64.
  • date_part must be either DAY, WEEK, MONTH, QUARTER, or YEAR.

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

This function returns an error if INT64_expr is set to 0.

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_dateand 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 date_part 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_expression date_part)

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_expression: INT64
  • Allowed date_part values are: MICROSECOND, MILLISECOND, SECOND, MINUTE, HOUR, or DAY.

The step_expression parameter determines the increment used to generate timestamps.

Return Data Type

An ARRAY containing 0 or more TIMESTAMP values.

Examples

The following example returns an ARRAY of TIMESTAMPs 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 TIMESTAMPs 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 and Accessing array elements.