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, seeAggregate 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, seeAggregate 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, seeRange functions.
`GENERATE_TIMESTAMP_ARRAY`	Generates an array of timestamps in a range.
`JSON_ARRAY`	Creates a JSON array. For more information, seeJSON functions.
`JSON_ARRAY_APPEND`	Appends JSON data to the end of a JSON array. For more information, seeJSON functions.
`JSON_ARRAY_INSERT`	Inserts JSON data into a JSON array. For more information, seeJSON 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, seeJSON 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, seeJSON 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, seeJSON functions.
`JSON_VALUE_ARRAY`	Extracts a JSON array of scalar values and converts it to a SQL`ARRAY<STRING>` value. For more information, seeJSON functions.
`RANGE_BUCKET`	Scans through a sorted array and returns the 0-based position of a point's upper bound. For more information, seeMathematical functions.

`ARRAY`

ARRAY(subquery)

Description

TheARRAY function returns anARRAY with one element for each row in asubquery.

Ifsubquery produces aSQL table,the table must have exactly one column. Each element in the outputARRAY isthe value of the single column of a row in the table.

Ifsubquery produces avalue table,then each element in the outputARRAY is the entire corresponding row of thevalue table.

Constraints

Subqueries are unordered, so the elements of the outputARRAY aren'tguaranteed to preserve any order in the source table for the subquery. However,if the subquery includes anORDER BY clause, theARRAY function will returnanARRAY that honors that clause.
If the subquery returns more than one column, theARRAY function returns anerror.
If the subquery returns anARRAY typed column orARRAY typed rows, theARRAY function returns an error that GoogleSQL doesn't supportARRAYs with elements of typeARRAY.
If the subquery returns zero rows, theARRAY function returns an emptyARRAY. It never returns aNULLARRAY.

Return type

ARRAY

Examples

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

To construct anARRAY from a subquery that contains multiplecolumns, change the subquery to useSELECT AS STRUCT. NowtheARRAY function will return anARRAY ofSTRUCTs. TheARRAY willcontain oneSTRUCT for each row in the subquery, and each of theseSTRUCTswill contain a field for each column in that row.

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

Similarly, to construct anARRAY from a subquery that containsone or moreARRAYs, change the subquery to useSELECT AS STRUCT.

SELECTARRAY(SELECTASSTRUCT[1,2,3]UNIONALLSELECTASSTRUCT[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 returnsNULL if any input argument isNULL.

Note: You can also use the || concatenation operatorto concatenate arrays.

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.

ReturnsNULL ifarray_expression isNULL.

Note: To get the last element in an array, see ARRAY_LAST.

Return type

Matches the data type of elements inarray_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.

ReturnsNULL ifarray_expression isNULL.

Note: To get the first element in an array, see ARRAY_FIRST.

Return type

Matches the data type of elements inarray_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. ReturnsNULL ifthearray_expression isNULL.

Return type

INT64

Examples

SELECTARRAY_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 inputARRAY 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 theinput 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 thebeginning of the input array and is 0-based. A negative offset starts fromthe end of the input array. Out-of-bounds offsets are supported. Here are someexamples:

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 containNULL elements.NULL elements are includedin the resulting array.
ReturnsNULL ifarray_to_slice,start_offset, orend_offset isNULL.
Returns an empty array ifarray_to_slice is empty.
Returns an empty array if the position of thestart_offset in the array isafter the position of theend_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 inarray_expressionas aSTRING. The value forarray_expressioncan either be an array ofSTRING orBYTES data types.

If thenull_text parameter is used, the function replaces anyNULL values inthe array with the value ofnull_text.

If thenull_text parameter isn't used, the function omits theNULL valueand 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. Thestart_expression andend_expressionparameters determine the inclusive start and end of the array.

TheGENERATE_ARRAY function accepts the following data types as inputs:

INT64
NUMERIC
BIGNUMERIC
FLOAT64

Thestep_expression parameter determines the increment used togenerate array values. The default value for this parameter is1.

This function returns an error ifstep_expression is set to 0, or if anyinput isNaN.

If any argument isNULL, the function will return aNULL 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 thestart_expressionandend_expression.

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

The following returns an empty array, because thestart_expression is greaterthan theend_expression, and thestep_expression value is positive.

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

The following returns aNULL array becauseend_expression isNULL.

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

The following returns multiple arrays.

SELECTGENERATE_ARRAY(start,5)ASexample_arrayFROMUNNEST([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. Thestart_date andend_dateparameters determine the inclusive start and end of the array.

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

TheINT64_expr parameter determines the increment used to generate dates. Thedefault value for this parameter is 1 day.

This function returns an error ifINT64_expr is set to 0.

Return Data Type

ARRAY containing 0 or moreDATE 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 thestart_dateandend_date.

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

The following returns an empty array, because thestart_date is greaterthan theend_date, and thestep value is positive.

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

The following returns aNULL array, because one of its inputs isNULL.

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

The following returns an array of dates, using MONTH as thedate_partinterval:

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_rangeFROM(SELECTDATE'2016-01-01'ASdate_start,DATE'2016-01-31'ASdate_endUNIONALLSELECTDATE"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 anARRAY ofTIMESTAMPS separated by a given interval. Thestart_timestamp andend_timestamp parameters determine the inclusivelower and upper bounds of theARRAY.

TheGENERATE_TIMESTAMP_ARRAY function accepts the following data types asinputs:

start_timestamp:TIMESTAMP
end_timestamp:TIMESTAMP
step_expression:INT64
Alloweddate_part values are:MICROSECOND,MILLISECOND,SECOND,MINUTE,HOUR, orDAY.

Thestep_expression parameter determines the increment used to generatetimestamps.

Return Data Type

AnARRAY containing 0 or moreTIMESTAMP values.

Examples

The following example returns anARRAY ofTIMESTAMPs 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 anARRAY ofTIMESTAMPs at intervals of 1second.

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 anARRAY ofTIMESTAMPS with a negativeinterval.

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 anARRAY with a single element, becausestart_timestamp andend_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 emptyARRAY, becausestart_timestamp islater thanend_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 nullARRAY, because one of the inputs isNULL.

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

The following example generatesARRAYs ofTIMESTAMPs from columns containingvalues forstart_timestamp andend_timestamp.

SELECTGENERATE_TIMESTAMP_ARRAY(start_timestamp,end_timestamp,INTERVAL1HOUR)AStimestamp_arrayFROM(SELECTTIMESTAMP'2016-10-05 00:00:00'ASstart_timestamp,TIMESTAMP'2016-10-05 02:00:00'ASend_timestampUNIONALLSELECTTIMESTAMP'2016-10-05 12:00:00'ASstart_timestamp,TIMESTAMP'2016-10-05 14:00:00'ASend_timestampUNIONALLSELECTTIMESTAMP'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 usingOFFSET andORDINAL with arrays, seeArray subscript operator andAccessing arrayelements.

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

Last updated 2025-10-02 UTC.

Movatterモバイル変換

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

Function list

ARRAY

ARRAY_CONCAT

ARRAY_FIRST

ARRAY_LAST

ARRAY_LENGTH

ARRAY_REVERSE

ARRAY_SLICE

ARRAY_TO_STRING

GENERATE_ARRAY

GENERATE_DATE_ARRAY

GENERATE_TIMESTAMP_ARRAY

Supplemental materials

OFFSET and ORDINAL

Array functions

`ARRAY`

`ARRAY_CONCAT`

`ARRAY_FIRST`

`ARRAY_LAST`

`ARRAY_LENGTH`

`ARRAY_REVERSE`

`ARRAY_SLICE`

`ARRAY_TO_STRING`

`GENERATE_ARRAY`

`GENERATE_DATE_ARRAY`

`GENERATE_TIMESTAMP_ARRAY`