Work with arrays

In GoogleSQL for BigQuery, an array is an ordered list consisting of zero or morevalues of the same data type. You can construct arrays of a simple data type,such asINT64, or a complex data type, such asSTRUCT. However,arrays of arrays aren't supported. To learn more about theARRAYdata type, includingNULL handling, seeArray type.

With GoogleSQL, you can construct array literals, build arrays from subqueries using theARRAY function, and aggregate values into an array using theARRAY_AGGfunction.

You can combine arrays using functions likeARRAY_CONCAT(), and convert arrays to strings usingARRAY_TO_STRING().

Accessing array elements

Consider the following table calledSequences. This table containsthe columnsome_numbers of theARRAY data type.

WITHSequencesAS(SELECT[0,1,1,2,3,5]ASsome_numbersUNIONALLSELECT[2,4,8,16,32]UNIONALLSELECT[5,10])SELECT*FROMSequences/*---------------------+ | some_numbers        | +---------------------+ | [0, 1, 1, 2, 3, 5]  | | [2, 4, 8, 16, 32]   | | [5, 10]             | +---------------------*/

To access array elements in thesome_numbers column, specify whichtype of indexing you want to use:eitherindexorOFFSET(index) forzero-based indexes, orORDINAL(index) forone-based indexes.

For example:

SELECTsome_numbers,some_numbers[0]ASindex_0,some_numbers[OFFSET(1)]ASoffset_1,some_numbers[ORDINAL(1)]ASordinal_1FROMSequences/*--------------------+---------+----------+-----------+ | some_numbers       | index_0 | offset_1 | ordinal_1 | +--------------------+---------+----------+-----------+ | [0, 1, 1, 2, 3, 5] | 0       | 1        | 0         | | [2, 4, 8, 16, 32]  | 2       | 4        | 2         | | [5, 10]            | 5       | 10       | 5         | +--------------------+---------+----------+-----------*/
Note:OFFSET andORDINAL will raise errors if the index is out ofrange. To avoid this, you can useSAFE_OFFSET orSAFE_ORDINAL to returnNULL instead of raising an error.

Finding lengths

TheARRAY_LENGTH function returns the length of an array.

WITHSequencesAS(SELECT[0,1,1,2,3,5]ASsome_numbersUNIONALLSELECT[2,4,8,16,32]ASsome_numbersUNIONALLSELECT[5,10]ASsome_numbers)SELECTsome_numbers,ARRAY_LENGTH(some_numbers)ASlenFROMSequences;/*--------------------+--------+ | some_numbers       | len    | +--------------------+--------+ | [0, 1, 1, 2, 3, 5] | 6      | | [2, 4, 8, 16, 32]  | 5      | | [5, 10]            | 2      | +--------------------+--------*/

Converting elements in an array to rows in a table

To convert anARRAY into a set of rows, also known as "flattening," use theUNNESToperator.UNNEST takes anARRAY and returns a table with a single row foreach element in theARRAY.

BecauseUNNEST destroys the order of theARRAY elements, you maywish to restore order to the table. To do so, use the optionalWITH OFFSETclause to return an additional column with the offset for each array element,then use theORDER BY clause to order the rows by their offset.

Example

SELECT*FROMUNNEST(['foo','bar','baz','qux','corge','garply','waldo','fred'])ASelementWITHOFFSETASoffsetORDERBYoffset;/*----------+--------+ | element  | offset | +----------+--------+ | foo      | 0      | | bar      | 1      | | baz      | 2      | | qux      | 3      | | corge    | 4      | | garply   | 5      | | waldo    | 6      | | fred     | 7      | +----------+--------*/

To flatten an entire column of typeARRAY while preserving the values of the othercolumns in each row, use a correlatedINNER JOIN to jointhe table containing theARRAY column to theUNNEST output of thatARRAYcolumn.

With acorrelated join, theUNNEST operatorreferences theARRAY typed column from each row in the source table, whichappears previously in theFROM clause. For each rowN in the source table,UNNEST flattens theARRAY from rowN into a set of rows containing theARRAY elements, and then a correlatedINNER JOIN orCROSS JOIN combinesthis new set of rows with the single rowN from the source table.

Examples

The following example usesUNNEST to return a row for eachelement in the array column. Because of theINNER JOIN, theid columncontains theid values for the row inSequences that contains each number.

WITHSequencesAS(SELECT1ASid,[0,1,1,2,3,5]ASsome_numbersUNIONALLSELECT2ASid,[2,4,8,16,32]ASsome_numbersUNIONALLSELECT3ASid,[5,10]ASsome_numbers)SELECTid,flattened_numbersFROMSequencesINNERJOINUNNEST(Sequences.some_numbers)ASflattened_numbers;/*------+-------------------+ | id   | flattened_numbers | +------+-------------------+ |    1 |                 0 | |    1 |                 1 | |    1 |                 1 | |    1 |                 2 | |    1 |                 3 | |    1 |                 5 | |    2 |                 2 | |    2 |                 4 | |    2 |                 8 | |    2 |                16 | |    2 |                32 | |    3 |                 5 | |    3 |                10 | +------+-------------------*/

Note that for correlated joins theUNNEST operator is optional and theINNER JOIN can be expressed as aCROSS JOIN or a comma cross join. Using thecomma cross join shorthand notation, the previous example is consolidated asfollows:

WITHSequencesAS(SELECT1ASid,[0,1,1,2,3,5]ASsome_numbersUNIONALLSELECT2ASid,[2,4,8,16,32]ASsome_numbersUNIONALLSELECT3ASid,[5,10]ASsome_numbers)SELECTid,flattened_numbersFROMSequences,Sequences.some_numbersASflattened_numbers;/*------+-------------------+ | id   | flattened_numbers | +------+-------------------+ |    1 |                 0 | |    1 |                 1 | |    1 |                 1 | |    1 |                 2 | |    1 |                 3 | |    1 |                 5 | |    2 |                 2 | |    2 |                 4 | |    2 |                 8 | |    2 |                16 | |    2 |                32 | |    3 |                 5 | |    3 |                10 | +------+-------------------*/

Querying nested arrays

If a table contains anARRAY ofSTRUCTs, you canflatten theARRAY to query the fields of theSTRUCT.You can also flattenARRAY type fields ofSTRUCT values.

QueryingSTRUCT elements in an array

The following example usesUNNEST withINNER JOIN to flatten anARRAY ofSTRUCTs.

WITHRacesAS(SELECT"800M"ASrace,[STRUCT("Rudisha"ASname,[23.4,26.3,26.4,26.1]ASlaps),STRUCT("Makhloufi"ASname,[24.5,25.4,26.6,26.1]ASlaps),STRUCT("Murphy"ASname,[23.9,26.0,27.0,26.0]ASlaps),STRUCT("Bosse"ASname,[23.6,26.2,26.5,27.1]ASlaps),STRUCT("Rotich"ASname,[24.7,25.6,26.9,26.4]ASlaps),STRUCT("Lewandowski"ASname,[25.0,25.7,26.3,27.2]ASlaps),STRUCT("Kipketer"ASname,[23.2,26.1,27.3,29.4]ASlaps),STRUCT("Berian"ASname,[23.7,26.1,27.0,29.3]ASlaps)]ASparticipants)SELECTrace,participantFROMRacesASrINNERJOINUNNEST(r.participants)ASparticipant;/*------+---------------------------------------+ | race | participant                           | +------+---------------------------------------+ | 800M | {Rudisha, [23.4, 26.3, 26.4, 26.1]}   | | 800M | {Makhloufi, [24.5, 25.4, 26.6, 26.1]} | | 800M | {Murphy, [23.9, 26, 27, 26]}          | | 800M | {Bosse, [23.6, 26.2, 26.5, 27.1]}     | | 800M | {Rotich, [24.7, 25.6, 26.9, 26.4]}    | | 800M | {Lewandowski, [25, 25.7, 26.3, 27.2]} | | 800M | {Kipketer, [23.2, 26.1, 27.3, 29.4]}  | | 800M | {Berian, [23.7, 26.1, 27, 29.3]}      | +------+---------------------------------------*/

You can find specific information from repeated fields. For example, thefollowing query returns the fastest racer in an 800M race.

Note: This example doesn't involve flattening an array, but doesrepresent a common way to get information from a repeated field.

Example

WITHRacesAS(SELECT"800M"ASrace,[STRUCT("Rudisha"ASname,[23.4,26.3,26.4,26.1]ASlaps),STRUCT("Makhloufi"ASname,[24.5,25.4,26.6,26.1]ASlaps),STRUCT("Murphy"ASname,[23.9,26.0,27.0,26.0]ASlaps),STRUCT("Bosse"ASname,[23.6,26.2,26.5,27.1]ASlaps),STRUCT("Rotich"ASname,[24.7,25.6,26.9,26.4]ASlaps),STRUCT("Lewandowski"ASname,[25.0,25.7,26.3,27.2]ASlaps),STRUCT("Kipketer"ASname,[23.2,26.1,27.3,29.4]ASlaps),STRUCT("Berian"ASname,[23.7,26.1,27.0,29.3]ASlaps)]ASparticipants)SELECTrace,(SELECTnameFROMUNNEST(participants)ORDERBY(SELECTSUM(duration)FROMUNNEST(laps)ASduration)ASCLIMIT1)ASfastest_racerFROMRaces;/*------+---------------+ | race | fastest_racer | +------+---------------+ | 800M | Rudisha       | +------+---------------*/

QueryingARRAY-type fields in a struct

You can also get information from nested repeated fields. For example, thefollowing statement returns the runner who had the fastest lap in an 800M race.

WITHRacesAS(SELECT"800M"ASrace,[STRUCT("Rudisha"ASname,[23.4,26.3,26.4,26.1]ASlaps),STRUCT("Makhloufi"ASname,[24.5,25.4,26.6,26.1]ASlaps),STRUCT("Murphy"ASname,[23.9,26.0,27.0,26.0]ASlaps),STRUCT("Bosse"ASname,[23.6,26.2,26.5,27.1]ASlaps),STRUCT("Rotich"ASname,[24.7,25.6,26.9,26.4]ASlaps),STRUCT("Lewandowski"ASname,[25.0,25.7,26.3,27.2]ASlaps),STRUCT("Kipketer"ASname,[23.2,26.1,27.3,29.4]ASlaps),STRUCT("Berian"ASname,[23.7,26.1,27.0,29.3]ASlaps)]ASparticipants)SELECTrace,(SELECTnameFROMUNNEST(participants),UNNEST(laps)ASdurationORDERBYdurationASCLIMIT1)ASrunner_with_fastest_lapFROMRaces;/*------+-------------------------+ | race | runner_with_fastest_lap | +------+-------------------------+ | 800M | Kipketer                | +------+-------------------------*/

Notice that the preceding query uses the comma operator (,) to perform a crossjoin and flatten the array. This is equivalent to using an explicitCROSS JOIN, or the following example which uses an explicitINNER JOIN:

WITHRacesAS(SELECT"800M"ASrace,[STRUCT("Rudisha"ASname,[23.4,26.3,26.4,26.1]ASlaps),STRUCT("Makhloufi"ASname,[24.5,25.4,26.6,26.1]ASlaps),STRUCT("Murphy"ASname,[23.9,26.0,27.0,26.0]ASlaps),STRUCT("Bosse"ASname,[23.6,26.2,26.5,27.1]ASlaps),STRUCT("Rotich"ASname,[24.7,25.6,26.9,26.4]ASlaps),STRUCT("Lewandowski"ASname,[25.0,25.7,26.3,27.2]ASlaps),STRUCT("Kipketer"ASname,[23.2,26.1,27.3,29.4]ASlaps),STRUCT("Berian"ASname,[23.7,26.1,27.0,29.3]ASlaps)]ASparticipants)SELECTrace,(SELECTnameFROMUNNEST(participants)INNERJOINUNNEST(laps)ASdurationORDERBYdurationASCLIMIT1)ASrunner_with_fastest_lapFROMRaces;/*------+-------------------------+ | race | runner_with_fastest_lap | +------+-------------------------+ | 800M | Kipketer                | +------+-------------------------*/

Flattening arrays withINNER JOIN excludes rows that have empty orNULLarrays. If you want to include these rows, useLEFT JOIN.

WITHRacesAS(SELECT"800M"ASrace,[STRUCT("Rudisha"ASname,[23.4,26.3,26.4,26.1]ASlaps),STRUCT("Makhloufi"ASname,[24.5,25.4,26.6,26.1]ASlaps),STRUCT("Murphy"ASname,[23.9,26.0,27.0,26.0]ASlaps),STRUCT("Bosse"ASname,[23.6,26.2,26.5,27.1]ASlaps),STRUCT("Rotich"ASname,[24.7,25.6,26.9,26.4]ASlaps),STRUCT("Lewandowski"ASname,[25.0,25.7,26.3,27.2]ASlaps),STRUCT("Kipketer"ASname,[23.2,26.1,27.3,29.4]ASlaps),STRUCT("Berian"ASname,[23.7,26.1,27.0,29.3]ASlaps),STRUCT("Nathan"ASname,ARRAY<FLOAT64>[]ASlaps),STRUCT("David"ASname,NULLASlaps)]ASparticipants)SELECTParticipant.name,SUM(duration)ASfinish_timeFROMRacesINNERJOINRaces.participantsASParticipantLEFTJOINParticipant.lapsASdurationGROUPBYname;/*-------------+--------------------+ | name        | finish_time        | +-------------+--------------------+ | Murphy      | 102.9              | | Rudisha     | 102.19999999999999 | | David       | NULL               | | Rotich      | 103.6              | | Makhloufi   | 102.6              | | Berian      | 106.1              | | Bosse       | 103.4              | | Kipketer    | 106                | | Nathan      | NULL               | | Lewandowski | 104.2              | +-------------+--------------------*/

Constructing arrays

You can construct an array using array literals or array functions. To learnmore about constructing arrays, seeArray type.

Creating arrays from subqueries

A common task when working with arrays is turning a subquery result into anarray. In GoogleSQL, you can accomplish this using theARRAY() function.

For example, consider the following operation on theSequences table:

WITHSequencesAS(SELECT[0,1,1,2,3,5]ASsome_numbersUNIONALLSELECT[2,4,8,16,32]ASsome_numbersUNIONALLSELECT[5,10]ASsome_numbers)SELECTsome_numbers,ARRAY(SELECTx*2FROMUNNEST(some_numbers)ASx)ASdoubledFROMSequences;/*--------------------+---------------------+ | some_numbers       | doubled             | +--------------------+---------------------+ | [0, 1, 1, 2, 3, 5] | [0, 2, 2, 4, 6, 10] | | [2, 4, 8, 16, 32]  | [4, 8, 16, 32, 64]  | | [5, 10]            | [10, 20]            | +--------------------+---------------------*/

This example starts with a table named Sequences. This table contains a column,some_numbers, of typeARRAY<INT64>.

The query itself contains a subquery. This subquery selects each row in thesome_numbers column and usesUNNEST to return thearray as a set of rows. Next, it multiplies each value by two, and thenre-combines the rows back into an array using theARRAY() operator.

Filtering arrays

The following example uses aWHERE clause in theARRAY() operator's subqueryto filter the returned rows.

Note: In the following examples, the resulting rows arenot ordered.
WITHSequencesAS(SELECT[0,1,1,2,3,5]ASsome_numbersUNIONALLSELECT[2,4,8,16,32]ASsome_numbersUNIONALLSELECT[5,10]ASsome_numbers)SELECTARRAY(SELECTx*2FROMUNNEST(some_numbers)ASxWHEREx <5)ASdoubled_less_than_fiveFROMSequences;/*------------------------+ | doubled_less_than_five | +------------------------+ | [0, 2, 2, 4, 6]        | | [4, 8]                 | | []                     | +------------------------*/

Notice that the third row contains an empty array, because the elements in thecorresponding original row ([5, 10]) didn't meet the filter requirement ofx < 5.

You can also filter arrays by usingSELECT DISTINCT to return onlyunique elements within an array.

WITHSequencesAS(SELECT[0,1,1,2,3,5]ASsome_numbers)SELECTARRAY(SELECTDISTINCTxFROMUNNEST(some_numbers)ASx)ASunique_numbersFROMSequences;/*-----------------+ | unique_numbers  | +-----------------+ | [0, 1, 2, 3, 5] | +-----------------*/

You can also filter rows of arrays by using theIN keyword. Thiskeyword filters rows containing arrays by determining if a specificvalue matches an element in the array.

WITHSequencesAS(SELECT[0,1,1,2,3,5]ASsome_numbersUNIONALLSELECT[2,4,8,16,32]ASsome_numbersUNIONALLSELECT[5,10]ASsome_numbers)SELECTARRAY(SELECTxFROMUNNEST(some_numbers)ASxWHERE2INUNNEST(some_numbers))AScontains_twoFROMSequences;/*--------------------+ | contains_two       | +--------------------+ | [0, 1, 1, 2, 3, 5] | | [2, 4, 8, 16, 32]  | | []                 | +--------------------*/

Notice again that the third row contains an empty array, because the array inthe corresponding original row ([5, 10]) didn't contain2.

Scanning arrays

To check if an array contains a specific value, use theINoperator withUNNEST. To check if an array contains a valuematching a condition, use theEXISTS operator withUNNEST.

Scanning for specific values

To scan an array for a specific value, use theIN operator withUNNEST.

Example

The following example returnstrue if the array contains the number 2.

SELECT2INUNNEST([0,1,1,2,3,5])AScontains_value;/*----------------+ | contains_value | +----------------+ | true           | +----------------*/

To return the rows of a table where the array column contains a specific value,filter the results ofIN UNNEST using theWHERE clause.

Example

The following example returns theid value for the rows where the arraycolumn contains the value 2.

WITHSequencesAS(SELECT1ASid,[0,1,1,2,3,5]ASsome_numbersUNIONALLSELECT2ASid,[2,4,8,16,32]ASsome_numbersUNIONALLSELECT3ASid,[5,10]ASsome_numbers)SELECTidASmatching_rowsFROMSequencesWHERE2INUNNEST(Sequences.some_numbers)ORDERBYmatching_rows;/*---------------+ | matching_rows | +---------------+ | 1             | | 2             | +---------------*/

Scanning for values that satisfy a condition

To scan an array for values that match a condition, useUNNEST to return atable of the elements in the array, useWHERE to filter the resulting table ina subquery, and useEXISTS to check if the filtered table contains any rows.

Example

The following example returns theid value for the rows where the arraycolumn contains values greater than 5.

WITHSequencesAS(SELECT1ASid,[0,1,1,2,3,5]ASsome_numbersUNIONALLSELECT2ASid,[2,4,8,16,32]ASsome_numbersUNIONALLSELECT3ASid,[5,10]ASsome_numbers)SELECTidASmatching_rowsFROMSequencesWHEREEXISTS(SELECT*FROMUNNEST(some_numbers)ASxWHEREx >5);/*---------------+ | matching_rows | +---------------+ | 2             | | 3             | +---------------*/

Scanning forSTRUCT field values that satisfy a condition

To search an array ofSTRUCT values for a field whose value matches a condition, useUNNEST to return a table with a column for eachSTRUCT field, then filternon-matching rows from the table usingWHERE EXISTS.

Example

The following example returns the rows where the array column contains aSTRUCT whose fieldb has a value greater than 3.

WITHSequencesAS(SELECT1ASid,[STRUCT(0ASa,1ASb)]ASsome_numbersUNIONALLSELECT2ASid,[STRUCT(2ASa,4ASb)]ASsome_numbersUNIONALLSELECT3ASid,[STRUCT(5ASa,3ASb),STRUCT(7ASa,4ASb)]ASsome_numbers)SELECTidASmatching_rowsFROMSequencesWHEREEXISTS(SELECT1FROMUNNEST(some_numbers)WHEREb >3);/*---------------+ | matching_rows | +---------------+ | 2             | | 3             | +---------------*/

Arrays and aggregation

With GoogleSQL, you can aggregate values into an array usingARRAY_AGG().

WITHFruitsAS(SELECT"apple"ASfruitUNIONALLSELECT"pear"ASfruitUNIONALLSELECT"banana"ASfruit)SELECTARRAY_AGG(fruit)ASfruit_basketFROMFruits;/*-----------------------+ | fruit_basket          | +-----------------------+ | [apple, pear, banana] | +-----------------------*/

The array returned byARRAY_AGG() is in an arbitrary order, since the order inwhich the function concatenates values isn't guaranteed. To order the arrayelements, useORDER BY. For example:

WITHFruitsAS(SELECT"apple"ASfruitUNIONALLSELECT"pear"ASfruitUNIONALLSELECT"banana"ASfruit)SELECTARRAY_AGG(fruitORDERBYfruit)ASfruit_basketFROMFruits;/*-----------------------+ | fruit_basket          | +-----------------------+ | [apple, banana, pear] | +-----------------------*/

You can also apply aggregate functions such asSUM() to the elements in anarray. For example, the following query returns the sum of array elements foreach row of theSequences table.

WITHSequencesAS(SELECT[0,1,1,2,3,5]ASsome_numbersUNIONALLSELECT[2,4,8,16,32]ASsome_numbersUNIONALLSELECT[5,10]ASsome_numbers)SELECTsome_numbers,(SELECTSUM(x)FROMUNNEST(s.some_numbers)ASx)ASsumsFROMSequencesASs;/*--------------------+------+ | some_numbers       | sums | +--------------------+------+ | [0, 1, 1, 2, 3, 5] | 12   | | [2, 4, 8, 16, 32]  | 62   | | [5, 10]            | 15   | +--------------------+------*/

GoogleSQL also supports an aggregate function,ARRAY_CONCAT_AGG(),which concatenates the elements of an array column across rows.

WITHAggregatesAS(SELECT[1,2]ASnumbersUNIONALLSELECT[3,4]ASnumbersUNIONALLSELECT[5,6]ASnumbers)SELECTARRAY_CONCAT_AGG(numbers)AScount_to_six_aggFROMAggregates;/*--------------------------------------------------+ | count_to_six_agg                                 | +--------------------------------------------------+ | [1, 2, 3, 4, 5, 6]                               | +--------------------------------------------------*/
Note: The array returned byARRAY_CONCAT_AGG() isnon-deterministic, since the order in which the function concatenates values isnot guaranteed.

Converting arrays to strings

TheARRAY_TO_STRING() function allows you to convert anARRAY<STRING> to asingleSTRING value or anARRAY<BYTES> to a singleBYTES value where theresulting value is the ordered concatenation of the array elements.

The second argument is the separator that the function will insert betweeninputs to produce the output; this second argument must be of the sametype as the elements of the first argument.

Example:

WITHWordsAS(SELECT["Hello","World"]ASgreeting)SELECTARRAY_TO_STRING(greeting," ")ASgreetingsFROMWords;/*-------------+ | greetings   | +-------------+ | Hello World | +-------------*/

The optional third argument takes the place ofNULL values in the inputarray.

  • If you omit this argument, then the function ignoresNULL arrayelements.

  • If you provide an empty string, the function inserts aseparator forNULL array elements.

Example:

SELECTARRAY_TO_STRING(arr,".","N")ASnon_empty_string,ARRAY_TO_STRING(arr,".","")ASempty_string,ARRAY_TO_STRING(arr,".")ASomittedFROM(SELECT["a",NULL,"b",NULL,"c",NULL]ASarr);/*------------------+--------------+---------+ | non_empty_string | empty_string | omitted | +------------------+--------------+---------+ | a.N.b.N.c.N      | a..b..c.     | a.b.c   | +------------------+--------------+---------*/

Combining arrays

In some cases, you might want to combine multiple arrays into a single array.You can accomplish this using theARRAY_CONCAT() function.

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

Updating arrays

Consider the following table calledarrays_table. The first column in thetable is an array of integers and the second column contains two nested arraysof integers.

WITHarrays_tableAS(SELECT[1,2]ASregular_array,STRUCT([10,20]ASfirst_array,[100,200]ASsecond_array)ASnested_arraysUNIONALLSELECT[3,4]ASregular_array,STRUCT([30,40]ASfirst_array,[300,400]ASsecond_array)ASnested_arrays)SELECT*FROMarrays_table;/*---------------*---------------------------*----------------------------+ | regular_array | nested_arrays.first_array | nested_arrays.second_array | +---------------+---------------------------+----------------------------+ | [1, 2]        | [10, 20]                  | [100, 200]                 | | [3, 4]        | [30, 40]                  | [130, 400]                 | +---------------*---------------------------*----------------------------*/

You can update arrays in a table by using theUPDATE statement. The followingexample inserts the number 5 into theregular_array column,and inserts the elements from thefirst_array field of thenested_arrayscolumn into thesecond_array field:

UPDATEarrays_tableSETregular_array=ARRAY_CONCAT(regular_array,[5]),nested_arrays.second_array=ARRAY_CONCAT(nested_arrays.second_array,nested_arrays.first_array)WHERETRUE;SELECT*FROMarrays_table;/*---------------*---------------------------*----------------------------+ | regular_array | nested_arrays.first_array | nested_arrays.second_array | +---------------+---------------------------+----------------------------+ | [1, 2, 5]     | [10, 20]                  | [100, 200, 10, 20]         | | [3, 4, 5]     | [30, 40]                  | [130, 400, 30, 40]         | +---------------*---------------------------*----------------------------*/

Zipping arrays

Given two arrays of equal size, you can merge them into a single arrayconsisting of pairs of elements from input arrays, taken from theircorresponding positions. This operation is sometimes calledzipping.

You can zip arrays withUNNEST andWITH OFFSET. In this example, each valuepair is stored as aSTRUCT in an array.

WITHCombinationsAS(SELECT['a','b']ASletters,[1,2,3]ASnumbers)SELECTARRAY(SELECTASSTRUCTletters[SAFE_OFFSET(index)]ASletter,numbers[SAFE_OFFSET(index)]ASnumberFROMCombinationsINNERJOINUNNEST(GENERATE_ARRAY(0,LEAST(ARRAY_LENGTH(letters),ARRAY_LENGTH(numbers))-1))ASindexORDERBYindex)ASpairs;/*------------------------------+ | pairs                        | +------------------------------+ | [{ letter: "a", number: 1 }, | |  { letter: "b", number: 2 }] | +------------------------------*/

You can use input arrays of different lengths as long as the first arrayis equal to or less than the length of the second array. The zipped arraywill be the length of the shortest input array.

To get a zipped array that includes all the elements even when the input arraysare different lengths, changeLEAST toGREATEST. Elements of either arraythat have no associated element in the other array will be paired withNULL.

WITHCombinationsAS(SELECT['a','b']ASletters,[1,2,3]ASnumbers)SELECTARRAY(SELECTASSTRUCTletters[SAFE_OFFSET(index)]ASletter,numbers[SAFE_OFFSET(index)]ASnumberFROMCombinationsINNERJOINUNNEST(GENERATE_ARRAY(0,GREATEST(ARRAY_LENGTH(letters),ARRAY_LENGTH(numbers))-1))ASindexORDERBYindex)ASpairs;/*-------------------------------+ | pairs                         | +-------------------------------+ | [{ letter: "a", number: 1 },  | |  { letter: "b", number: 2 },  | |  { letter: null, number: 3 }] | +-------------------------------*/

Building arrays of arrays

GoogleSQL doesn't support buildingarrays of arraysdirectly. Instead, you must create an array of structs, with each structcontaining a field of typeARRAY. To illustrate this, consider the followingPoints table:

/*----------+ | point    | +----------+ | [1, 5]   | | [2, 8]   | | [3, 7]   | | [4, 1]   | | [5, 7]   | +----------*/

Now, let's say you wanted to create an array consisting of eachpoint in thePoints table. To accomplish this, wrap the array returned from each row in aSTRUCT, as shown below.

WITHPointsAS(SELECT[1,5]ASpointUNIONALLSELECT[2,8]ASpointUNIONALLSELECT[3,7]ASpointUNIONALLSELECT[4,1]ASpointUNIONALLSELECT[5,7]ASpoint)SELECTARRAY(SELECTSTRUCT(point)FROMPoints)AScoordinates;/*-------------------+ | coordinates       | +-------------------+ | [{point: [1,5]},  | |  {point: [2,8]},  | |  {point: [5,7]},  | |  {point: [3,7]},  | |  {point: [4,1]}]  | +-------------------*/

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-12-15 UTC.