Array functions

GoogleSQL for BigQuery supports the following array functions.

Function list

Name Summary
ARRAY Produces an array with one element for each row in a subquery.
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_FIRST Gets the first element in an array.
ARRAY_LAST Gets the last element in an array.
ARRAY_LENGTH Gets the number of elements in an array.
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.
GENERATE_ARRAY Generates an array of values in a range.
GENERATE_DATE_ARRAY Generates an array of dates in a range.
GENERATE_RANGE_ARRAY Splits a range into an array of subranges.
For more information, see Range functions.
GENERATE_TIMESTAMP_ARRAY Generates an array of timestamps in a range.
JSON_ARRAY Creates a JSON array.
For more information, see JSON functions.
JSON_ARRAY_APPEND Appends JSON data to the end of a JSON array.
For more information, see JSON functions.
JSON_ARRAY_INSERT Inserts JSON data into a JSON array.
For more information, see JSON functions.
JSON_EXTRACT_ARRAY (Deprecated) Extracts a JSON array and converts it to a SQL ARRAY<JSON-formatted STRING> or ARRAY<JSON> value.
For more information, see JSON functions.
JSON_EXTRACT_STRING_ARRAY (Deprecated) Extracts a JSON array of scalar values and converts it to a SQL ARRAY<STRING> value.
For more information, see JSON functions.
JSON_QUERY_ARRAY Extracts a JSON array and converts it to a SQL ARRAY<JSON-formatted STRING> or ARRAY<JSON> value.
For more information, see JSON functions.
JSON_VALUE_ARRAY Extracts a JSON array of scalar values and converts it to a SQL ARRAY<STRING> value.
For more information, see JSON functions.
RANGE_BUCKET Scans through a sorted array and returns the 0-based position of a point's upper bound.
For more information, see Mathematical functions.

ARRAY

ARRAY(subquery)

Description

The ARRAY function returns an ARRAY with one element for each row in a subquery.

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

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

Constraints

  • Subqueries are unordered, so the elements of the output ARRAY aren't guaranteed to preserve any order in the source table for the subquery. However, if the subquery includes an ORDER BY clause, the ARRAY function will return an ARRAY that honors that clause.
  • If the subquery returns more than one column, the ARRAY function returns an error.
  • If the subquery returns an ARRAY typed column or ARRAY typed rows, the ARRAY function returns an error that GoogleSQL doesn't support ARRAYs with elements of type ARRAY.
  • If the subquery returns zero rows, the ARRAY function returns an empty ARRAY. It never returns a NULL ARRAY.

Return type

ARRAY

Examples

SELECTARRAY
(SELECT1UNIONALL
SELECT2UNIONALL
SELECT3)ASnew_array;
/*-----------*
 | new_array |
 +-----------+
 | [1, 2, 3] |
 *-----------*/

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

SELECT
ARRAY
(SELECTASSTRUCT1,2,3
UNIONALLSELECTASSTRUCT4,5,6)ASnew_array;
/*------------------------*
 | new_array |
 +------------------------+
 | [{1, 2, 3}, {4, 5, 6}] |
 *------------------------*/

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

SELECTARRAY
(SELECTASSTRUCT[1,2,3]UNIONALL
SELECTASSTRUCT[4,5,6])ASnew_array;
/*----------------------------*
 | new_array |
 +----------------------------+
 | [{[1, 2, 3]}, {[4, 5, 6]}] |
 *----------------------------*/

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

SELECTARRAY_CONCAT([1,2],[3,4],[5,6])ascount_to_six;
/*--------------------------------------------------*
 | count_to_six |
 +--------------------------------------------------+
 | [1, 2, 3, 4, 5, 6] |
 *--------------------------------------------------*/

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

SELECTARRAY_FIRST(['a','b','c','d'])asfirst_element
/*---------------*
 | first_element |
 +---------------+
 | a |
 *---------------*/

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

SELECTARRAY_LAST(['a','b','c','d'])aslast_element
/*---------------*
 | last_element |
 +---------------+
 | d |
 *---------------*/

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"])ASsize_a,
ARRAY_LENGTH(["cake","pie"])ASsize_b;
/*--------+--------*
 | size_a | size_b |
 +--------+--------+
 | 3 | 2 |
 *--------+--------*/

ARRAY_REVERSE

ARRAY_REVERSE(value)

Description

Returns the input ARRAY with elements in reverse order.

Return type

ARRAY

Examples

SELECTARRAY_REVERSE([1,2,3])ASreverse_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

SELECTARRAY_SLICE(['a','b','c','d','e'],1,3)ASresult
/*-----------*
 | result |
 +-----------+
 | [b, c, d] |
 *-----------*/

SELECTARRAY_SLICE(['a','b','c','d','e'],-1,3)ASresult
/*-----------*
 | result |
 +-----------+
 | [] |
 *-----------*/

SELECTARRAY_SLICE(['a','b','c','d','e'],1,-3)ASresult
/*--------*
 | result |
 +--------+
 | [b, c] |
 *--------*/

SELECTARRAY_SLICE(['a','b','c','d','e'],-1,-3)ASresult
/*-----------*
 | result |
 +-----------+
 | [] |
 *-----------*/

SELECTARRAY_SLICE(['a','b','c','d','e'],-3,-1)ASresult
/*-----------*
 | result |
 +-----------+
 | [c, d, e] |
 *-----------*/

SELECTARRAY_SLICE(['a','b','c','d','e'],3,3)ASresult
/*--------*
 | result |
 +--------+
 | [d] |
 *--------*/

SELECTARRAY_SLICE(['a','b','c','d','e'],-3,-3)ASresult
/*--------*
 | result |
 +--------+
 | [c] |
 *--------*/

SELECTARRAY_SLICE(['a','b','c','d','e'],1,30)ASresult
/*--------------*
 | result |
 +--------------+
 | [b, c, d, e] |
 *--------------*/

SELECTARRAY_SLICE(['a','b','c','d','e'],1,-30)ASresult
/*-----------*
 | result |
 +-----------+
 | [] |
 *-----------*/

SELECTARRAY_SLICE(['a','b','c','d','e'],-30,30)ASresult
/*-----------------*
 | result |
 +-----------------+
 | [a, b, c, d, e] |
 *-----------------*/

SELECTARRAY_SLICE(['a','b','c','d','e'],-30,-5)ASresult
/*--------*
 | result |
 +--------+
 | [a] |
 *--------*/

SELECTARRAY_SLICE(['a','b','c','d','e'],5,30)ASresult
/*--------*
 | result |
 +--------+
 | [] |
 *--------*/

SELECTARRAY_SLICE(['a','b','c','d','e'],1,NULL)ASresult
/*-----------*
 | result |
 +-----------+
 | NULL |
 *-----------*/

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

SELECTARRAY_TO_STRING(['coffee','tea','milk',NULL],'--','MISSING')AStext
/*--------------------------------*
 | text |
 +--------------------------------+
 | coffee--tea--milk--MISSING |
 *--------------------------------*/


SELECTARRAY_TO_STRING(['cake','pie',NULL],'--','MISSING')AStext
/*--------------------------------*
 | text |
 +--------------------------------+
 | cake--pie--MISSING |
 *--------------------------------*/

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
  • NUMERIC
  • BIGNUMERIC
  • 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.

SELECTGENERATE_ARRAY(1,5)ASexample_array;
/*-----------------*
 | example_array |
 +-----------------+
 | [1, 2, 3, 4, 5] |
 *-----------------*/

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

SELECTGENERATE_ARRAY(0,10,3)ASexample_array;
/*---------------*
 | example_array |
 +---------------+
 | [0, 3, 6, 9] |
 *---------------*/

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

SELECTGENERATE_ARRAY(10,0,-3)ASexample_array;
/*---------------*
 | example_array |
 +---------------+
 | [10, 7, 4, 1] |
 *---------------*/

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

SELECTGENERATE_ARRAY(4,4,10)ASexample_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.

SELECTGENERATE_ARRAY(10,0,3)ASexample_array;
/*---------------*
 | example_array |
 +---------------+
 | [] |
 *---------------*/

The following returns a NULL array because end_expression is NULL.

SELECTGENERATE_ARRAY(5,NULL,1)ASexample_array;
/*---------------*
 | example_array |
 +---------------+
 | NULL |
 *---------------*/

The following returns multiple arrays.

SELECTGENERATE_ARRAY(start,5)ASexample_array
FROMUNNEST([3,4,5])ASstart;
/*---------------*
 | example_array |
 +---------------+
 | [3, 4, 5] |
 | [4, 5] |
 | [5] |
 +---------------*/

GENERATE_DATE_ARRAY

GENERATE_DATE_ARRAY(start_date,end_date[,INTERVALINT64_exprdate_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.

SELECTGENERATE_DATE_ARRAY('2016-10-05','2016-10-08')ASexample;
/*--------------------------------------------------*
 | example |
 +--------------------------------------------------+
 | [2016年10月05日, 2016年10月06日, 2016年10月07日, 2016年10月08日] |
 *--------------------------------------------------*/

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

SELECTGENERATE_DATE_ARRAY(
'2016-10-05','2016-10-09',INTERVAL2DAY)ASexample;
/*--------------------------------------*
 | example |
 +--------------------------------------+
 | [2016年10月05日, 2016年10月07日, 2016年10月09日] |
 *--------------------------------------*/

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

SELECTGENERATE_DATE_ARRAY('2016-10-05',
'2016-10-01',INTERVAL-3DAY)ASexample;
/*--------------------------*
 | example |
 +--------------------------+
 | [2016年10月05日, 2016年10月02日] |
 *--------------------------*/

The following returns an array using the same value for the start_dateand end_date.

SELECTGENERATE_DATE_ARRAY('2016-10-05',
'2016-10-05',INTERVAL8DAY)ASexample;
/*--------------*
 | 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.

SELECTGENERATE_DATE_ARRAY('2016-10-05',
'2016-10-01',INTERVAL1DAY)ASexample;
/*---------*
 | example |
 +---------+
 | [] |
 *---------*/

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

SELECTGENERATE_DATE_ARRAY('2016-10-05',NULL)ASexample;
/*---------*
 | example |
 +---------+
 | NULL |
 *---------*/

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

SELECTGENERATE_DATE_ARRAY('2016-01-01',
'2016-12-31',INTERVAL2MONTH)ASexample;
/*--------------------------------------------------------------------------*
 | 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.

SELECTGENERATE_DATE_ARRAY(date_start,date_end,INTERVAL1WEEK)ASdate_range
FROM(
SELECTDATE'2016-01-01'ASdate_start,DATE'2016-01-31'ASdate_end
UNIONALLSELECTDATE"2016-04-01",DATE"2016-04-30"
UNIONALLSELECTDATE"2016-07-01",DATE"2016-07-31"
UNIONALLSELECTDATE"2016-10-01",DATE"2016-10-31"
)ASitems;
/*--------------------------------------------------------------*
 | 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,
INTERVALstep_expressiondate_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.

SELECTGENERATE_TIMESTAMP_ARRAY('2016-10-05 00:00:00','2016-10-07 00:00:00',
INTERVAL1DAY)AStimestamp_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.

SELECTGENERATE_TIMESTAMP_ARRAY('2016-10-05 00:00:00','2016-10-05 00:00:02',
INTERVAL1SECOND)AStimestamp_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.

SELECTGENERATE_TIMESTAMP_ARRAY('2016-10-06 00:00:00','2016-10-01 00:00:00',
INTERVAL-2DAY)AStimestamp_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.

SELECTGENERATE_TIMESTAMP_ARRAY('2016-10-05 00:00:00','2016-10-05 00:00:00',
INTERVAL1HOUR)AStimestamp_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.

SELECTGENERATE_TIMESTAMP_ARRAY('2016-10-06 00:00:00','2016-10-05 00:00:00',
INTERVAL1HOUR)AStimestamp_array;
/*-----------------*
 | timestamp_array |
 +-----------------+
 | [] |
 *-----------------*/

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

SELECTGENERATE_TIMESTAMP_ARRAY('2016-10-05 00:00:00',NULL,INTERVAL1HOUR)
AStimestamp_array;
/*-----------------*
 | timestamp_array |
 +-----------------+
 | NULL |
 *-----------------*/

The following example generates ARRAYs of TIMESTAMPs from columns containing values for start_timestamp and end_timestamp.

SELECTGENERATE_TIMESTAMP_ARRAY(start_timestamp,end_timestamp,INTERVAL1HOUR)
AStimestamp_array
FROM
(SELECT
TIMESTAMP'2016-10-05 00:00:00'ASstart_timestamp,
TIMESTAMP'2016-10-05 02:00:00'ASend_timestamp
UNIONALL
SELECT
TIMESTAMP'2016-10-05 12:00:00'ASstart_timestamp,
TIMESTAMP'2016-10-05 14:00:00'ASend_timestamp
UNIONALL
SELECT
TIMESTAMP'2016-10-05 23:59:00'ASstart_timestamp,
TIMESTAMP'2016-10-06 01:59:00'ASend_timestamp);
/*--------------------------------------------------------------------------*
 | timestamp_array |
 +--------------------------------------------------------------------------+
 | [2016年10月05日 00:00:00+00, 2016年10月05日 01:00:00+00, 2016年10月05日 02:00:00+00] |
 | [2016年10月05日 12:00:00+00, 2016年10月05日 13:00:00+00, 2016年10月05日 14:00:00+00] |
 | [2016年10月05日 23:59:00+00, 2016年10月06日 00:59:00+00, 2016年10月06日 01:59:00+00] |
 *--------------------------------------------------------------------------*/

Supplemental materials

OFFSET and ORDINAL

For information about using OFFSET and ORDINAL with arrays, see Array subscript operator and Accessing array elements.

Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 4.0 License, and code samples are licensed under the Apache 2.0 License. For details, see the Google Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025年10月15日 UTC.