Array functions

GoogleSQL for Bigtable supports the following array functions.

Function list

Name	Summary
`ARRAY_AGG`	Gets an array of values. For more information, see Aggregate functions .
`ARRAY_CONCAT`	Concatenates one or more arrays with the same element type into a single array.
`ARRAY_CONCAT_AGG`	Concatenates arrays and returns a single array as a result. For more information, see Aggregate functions .
`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 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 
  
 } 
 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:

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 isn't 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 
 , 
  
 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 .

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

Array functions Stay organized with collections Save and categorize content based on your preferences.

Function list

ARRAY_CONCAT

ARRAY_FILTER

ARRAY_FIRST

ARRAY_INCLUDES

ARRAY_INCLUDES_ALL

ARRAY_INCLUDES_ANY

ARRAY_IS_DISTINCT

ARRAY_LAST

ARRAY_LAST_N

ARRAY_LENGTH

ARRAY_OFFSET

ARRAY_OFFSETS

ARRAY_REVERSE

ARRAY_SLICE

ARRAY_TO_STRING

ARRAY_TRANSFORM

GENERATE_ARRAY

GENERATE_DATE_ARRAY

GENERATE_TIMESTAMP_ARRAY

Supplemental materials

OFFSET and ORDINAL

Array functions

`ARRAY_CONCAT`

`ARRAY_FILTER`

`ARRAY_FIRST`

`ARRAY_INCLUDES`

`ARRAY_INCLUDES_ALL`

`ARRAY_INCLUDES_ANY`

`ARRAY_IS_DISTINCT`

`ARRAY_LAST`

`ARRAY_LAST_N`

`ARRAY_LENGTH`

`ARRAY_OFFSET`

`ARRAY_OFFSETS`

`ARRAY_REVERSE`

`ARRAY_SLICE`

`ARRAY_TO_STRING`

`ARRAY_TRANSFORM`

`GENERATE_ARRAY`

`GENERATE_DATE_ARRAY`

`GENERATE_TIMESTAMP_ARRAY`