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 inarray_expression
is evaluated against the lambda expression. If the expression evaluates toFALSE
orNULL
, 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
isNULL
, returnsNULL
. - If
n
isNULL
, returnsNULL
. - If
n
is0
, returns an empty array. - If
n
is longer thaninput_array
, returnsinput_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 ininput_array
is evaluated against the lambda expression. If the expression evaluates toTRUE
, 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 ininput_array
is evaluated against the lambda expression. If the expression evaluates toTRUE
, 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
ifarray_to_slice
,start_offset
, orend_offset
isNULL
. - 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 theend_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 inarray_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 aDATE
.end_date
must be aDATE
.INT64_expr
must be anINT64
.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_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 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
, orDAY
.
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 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 and Accessing array
elements.