In GoogleSQL for BigQuery, an array is an ordered list consisting of zero or more values of the same data type. You can construct arrays of a simple data type, such as INT64, or a complex data type, such as STRUCT. However, arrays of arrays aren't supported. To learn more about the ARRAY data type, including NULL handling, see Array type.
With GoogleSQL, you can construct array literals, build arrays from subqueries using the ARRAY function, and aggregate values into an array using the ARRAY_AGG function.
You can combine arrays using functions like ARRAY_CONCAT(), and convert arrays to strings using ARRAY_TO_STRING().
Accessing array elements
Consider the following table called Sequences. This table contains the column some_numbers of the ARRAY data type.
WITH Sequences AS ( SELECT [0, 1, 1, 2, 3, 5] AS some_numbers UNION ALL SELECT [2, 4, 8, 16, 32] UNION ALL SELECT [5, 10] ) SELECT * FROM Sequences /*---------------------* | some_numbers | +---------------------+ | [0, 1, 1, 2, 3, 5] | | [2, 4, 8, 16, 32] | | [5, 10] | *---------------------*/ To access array elements in the some_numbers column, specify which type of indexing you want to use: either index or OFFSET(index) for zero-based indexes, or ORDINAL(index) for one-based indexes.
For example:
SELECT some_numbers, some_numbers[0] AS index_0, some_numbers[OFFSET(1)] AS offset_1, some_numbers[ORDINAL(1)] AS ordinal_1 FROM Sequences /*--------------------+---------+----------+-----------* | 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 | *--------------------+---------+----------+-----------*/ Finding lengths
The ARRAY_LENGTH function returns the length of an array.
WITH Sequences AS (SELECT [0, 1, 1, 2, 3, 5] AS some_numbers UNION ALL SELECT [2, 4, 8, 16, 32] AS some_numbers UNION ALL SELECT [5, 10] AS some_numbers) SELECT some_numbers, ARRAY_LENGTH(some_numbers) AS len FROM Sequences; /*--------------------+--------* | 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 an ARRAY into a set of rows, also known as "flattening," use the UNNEST operator. UNNEST takes an ARRAY and returns a table with a single row for each element in the ARRAY.
Because UNNEST destroys the order of the ARRAY elements, you may wish to restore order to the table. To do so, use the optional WITH OFFSET clause to return an additional column with the offset for each array element, then use the ORDER BY clause to order the rows by their offset.
Example
SELECT * FROM UNNEST(['foo', 'bar', 'baz', 'qux', 'corge', 'garply', 'waldo', 'fred']) AS element WITH OFFSET AS offset ORDER BY offset; /*----------+--------* | element | offset | +----------+--------+ | foo | 0 | | bar | 1 | | baz | 2 | | qux | 3 | | corge | 4 | | garply | 5 | | waldo | 6 | | fred | 7 | *----------+--------*/ To flatten an entire column of type ARRAY while preserving the values of the other columns in each row, use a correlated INNER JOIN to join the table containing the ARRAY column to the UNNEST output of that ARRAY column.
With a correlated join, the UNNEST operator references the ARRAY typed column from each row in the source table, which appears previously in the FROM clause. For each row N in the source table, UNNEST flattens the ARRAY from row N into a set of rows containing the ARRAY elements, and then a correlated INNER JOIN or CROSS JOIN combines this new set of rows with the single row N from the source table.
Examples
The following example uses UNNEST to return a row for each element in the array column. Because of the INNER JOIN, the id column contains the id values for the row in Sequences that contains each number.
WITH Sequences AS ( SELECT 1 AS id, [0, 1, 1, 2, 3, 5] AS some_numbers UNION ALL SELECT 2 AS id, [2, 4, 8, 16, 32] AS some_numbers UNION ALL SELECT 3 AS id, [5, 10] AS some_numbers ) SELECT id, flattened_numbers FROM Sequences INNER JOIN UNNEST(Sequences.some_numbers) AS flattened_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 the UNNEST operator is optional and the INNER JOIN can be expressed as a CROSS JOIN or a comma cross join. Using the comma cross join shorthand notation, the previous example is consolidated as follows:
WITH Sequences AS ( SELECT 1 AS id, [0, 1, 1, 2, 3, 5] AS some_numbers UNION ALL SELECT 2 AS id, [2, 4, 8, 16, 32] AS some_numbers UNION ALL SELECT 3 AS id, [5, 10] AS some_numbers ) SELECT id, flattened_numbers FROM Sequences, Sequences.some_numbers AS flattened_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 an ARRAY of STRUCTs, you can flatten the ARRAY to query the fields of the STRUCT. You can also flatten ARRAY type fields of STRUCT values.
Querying STRUCT elements in an array
The following example uses UNNEST with INNER JOIN to flatten an ARRAY of STRUCTs.
WITH Races AS ( SELECT "800M" AS race, [ STRUCT("Rudisha" AS name, [23.4, 26.3, 26.4, 26.1] AS laps), STRUCT("Makhloufi" AS name, [24.5, 25.4, 26.6, 26.1] AS laps), STRUCT("Murphy" AS name, [23.9, 26.0, 27.0, 26.0] AS laps), STRUCT("Bosse" AS name, [23.6, 26.2, 26.5, 27.1] AS laps), STRUCT("Rotich" AS name, [24.7, 25.6, 26.9, 26.4] AS laps), STRUCT("Lewandowski" AS name, [25.0, 25.7, 26.3, 27.2] AS laps), STRUCT("Kipketer" AS name, [23.2, 26.1, 27.3, 29.4] AS laps), STRUCT("Berian" AS name, [23.7, 26.1, 27.0, 29.3] AS laps) ] AS participants ) SELECT race, participant FROM Races AS r INNER JOIN UNNEST(r.participants) AS participant; /*------+---------------------------------------* | 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, the following query returns the fastest racer in an 800M race.
Example
WITH Races AS ( SELECT "800M" AS race, [ STRUCT("Rudisha" AS name, [23.4, 26.3, 26.4, 26.1] AS laps), STRUCT("Makhloufi" AS name, [24.5, 25.4, 26.6, 26.1] AS laps), STRUCT("Murphy" AS name, [23.9, 26.0, 27.0, 26.0] AS laps), STRUCT("Bosse" AS name, [23.6, 26.2, 26.5, 27.1] AS laps), STRUCT("Rotich" AS name, [24.7, 25.6, 26.9, 26.4] AS laps), STRUCT("Lewandowski" AS name, [25.0, 25.7, 26.3, 27.2] AS laps), STRUCT("Kipketer" AS name, [23.2, 26.1, 27.3, 29.4] AS laps), STRUCT("Berian" AS name, [23.7, 26.1, 27.0, 29.3] AS laps) ] AS participants ) SELECT race, ( SELECT name FROM UNNEST(participants) ORDER BY (SELECT SUM(duration) FROM UNNEST(laps) AS duration) ASC LIMIT 1 ) AS fastest_racer FROM Races; /*------+---------------* | race | fastest_racer | +------+---------------+ | 800M | Rudisha | *------+---------------*/ Querying ARRAY-type fields in a struct
You can also get information from nested repeated fields. For example, the following statement returns the runner who had the fastest lap in an 800M race.
WITH Races AS ( SELECT "800M" AS race, [ STRUCT("Rudisha" AS name, [23.4, 26.3, 26.4, 26.1] AS laps), STRUCT("Makhloufi" AS name, [24.5, 25.4, 26.6, 26.1] AS laps), STRUCT("Murphy" AS name, [23.9, 26.0, 27.0, 26.0] AS laps), STRUCT("Bosse" AS name, [23.6, 26.2, 26.5, 27.1] AS laps), STRUCT("Rotich" AS name, [24.7, 25.6, 26.9, 26.4] AS laps), STRUCT("Lewandowski" AS name, [25.0, 25.7, 26.3, 27.2] AS laps), STRUCT("Kipketer" AS name, [23.2, 26.1, 27.3, 29.4] AS laps), STRUCT("Berian" AS name, [23.7, 26.1, 27.0, 29.3] AS laps) ]AS participants ) SELECT race, ( SELECT name FROM UNNEST(participants), UNNEST(laps) AS duration ORDER BY duration ASC LIMIT 1 ) AS runner_with_fastest_lap FROM Races; /*------+-------------------------* | race | runner_with_fastest_lap | +------+-------------------------+ | 800M | Kipketer | *------+-------------------------*/ Notice that the preceding query uses the comma operator (,) to perform a cross join and flatten the array. This is equivalent to using an explicit CROSS JOIN, or the following example which uses an explicit INNER JOIN:
WITH Races AS ( SELECT "800M" AS race, [ STRUCT("Rudisha" AS name, [23.4, 26.3, 26.4, 26.1] AS laps), STRUCT("Makhloufi" AS name, [24.5, 25.4, 26.6, 26.1] AS laps), STRUCT("Murphy" AS name, [23.9, 26.0, 27.0, 26.0] AS laps), STRUCT("Bosse" AS name, [23.6, 26.2, 26.5, 27.1] AS laps), STRUCT("Rotich" AS name, [24.7, 25.6, 26.9, 26.4] AS laps), STRUCT("Lewandowski" AS name, [25.0, 25.7, 26.3, 27.2] AS laps), STRUCT("Kipketer" AS name, [23.2, 26.1, 27.3, 29.4] AS laps), STRUCT("Berian" AS name, [23.7, 26.1, 27.0, 29.3] AS laps) ] AS participants ) SELECT race, ( SELECT name FROM UNNEST(participants) INNER JOIN UNNEST(laps) AS duration ORDER BY duration ASC LIMIT 1 ) AS runner_with_fastest_lap FROM Races; /*------+-------------------------* | race | runner_with_fastest_lap | +------+-------------------------+ | 800M | Kipketer | *------+-------------------------*/ Flattening arrays with INNER JOIN excludes rows that have empty or NULL arrays. If you want to include these rows, use LEFT JOIN.
WITH Races AS ( SELECT "800M" AS race, [ STRUCT("Rudisha" AS name, [23.4, 26.3, 26.4, 26.1] AS laps), STRUCT("Makhloufi" AS name, [24.5, 25.4, 26.6, 26.1] AS laps), STRUCT("Murphy" AS name, [23.9, 26.0, 27.0, 26.0] AS laps), STRUCT("Bosse" AS name, [23.6, 26.2, 26.5, 27.1] AS laps), STRUCT("Rotich" AS name, [24.7, 25.6, 26.9, 26.4] AS laps), STRUCT("Lewandowski" AS name, [25.0, 25.7, 26.3, 27.2] AS laps), STRUCT("Kipketer" AS name, [23.2, 26.1, 27.3, 29.4] AS laps), STRUCT("Berian" AS name, [23.7, 26.1, 27.0, 29.3] AS laps), STRUCT("Nathan" AS name, ARRAY<FLOAT64>[] AS laps), STRUCT("David" AS name, NULL AS laps) ] AS participants ) SELECT Participant.name, SUM(duration) AS finish_time FROM Races INNER JOIN Races.participants AS Participant LEFT JOIN Participant.laps AS duration GROUP BY name; /*-------------+--------------------* | 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 learn more about constructing arrays, see Array type.
Creating arrays from subqueries
A common task when working with arrays is turning a subquery result into an array. In GoogleSQL, you can accomplish this using the ARRAY() function.
For example, consider the following operation on the Sequences table:
WITH Sequences AS (SELECT [0, 1, 1, 2, 3, 5] AS some_numbers UNION ALL SELECT [2, 4, 8, 16, 32] AS some_numbers UNION ALL SELECT [5, 10] AS some_numbers) SELECT some_numbers, ARRAY(SELECT x * 2 FROM UNNEST(some_numbers) AS x) AS doubled FROM Sequences; /*--------------------+---------------------* | 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 type ARRAY<INT64>.
The query itself contains a subquery. This subquery selects each row in the some_numbers column and uses UNNEST to return the array as a set of rows. Next, it multiplies each value by two, and then re-combines the rows back into an array using the ARRAY() operator.
Filtering arrays
The following example uses a WHERE clause in the ARRAY() operator's subquery to filter the returned rows.
WITH Sequences AS (SELECT [0, 1, 1, 2, 3, 5] AS some_numbers UNION ALL SELECT [2, 4, 8, 16, 32] AS some_numbers UNION ALL SELECT [5, 10] AS some_numbers) SELECT ARRAY(SELECT x * 2 FROM UNNEST(some_numbers) AS x WHERE x < 5) AS doubled_less_than_five FROM Sequences; /*------------------------* | doubled_less_than_five | +------------------------+ | [0, 2, 2, 4, 6] | | [4, 8] | | [] | *------------------------*/ Notice that the third row contains an empty array, because the elements in the corresponding original row ([5, 10]) didn't meet the filter requirement of x < 5.
You can also filter arrays by using SELECT DISTINCT to return only unique elements within an array.
WITH Sequences AS (SELECT [0, 1, 1, 2, 3, 5] AS some_numbers) SELECT ARRAY(SELECT DISTINCT x FROM UNNEST(some_numbers) AS x) AS unique_numbers FROM Sequences; /*-----------------* | unique_numbers | +-----------------+ | [0, 1, 2, 3, 5] | *-----------------*/ You can also filter rows of arrays by using the IN keyword. This keyword filters rows containing arrays by determining if a specific value matches an element in the array.
WITH Sequences AS (SELECT [0, 1, 1, 2, 3, 5] AS some_numbers UNION ALL SELECT [2, 4, 8, 16, 32] AS some_numbers UNION ALL SELECT [5, 10] AS some_numbers) SELECT ARRAY(SELECT x FROM UNNEST(some_numbers) AS x WHERE 2 IN UNNEST(some_numbers)) AS contains_two FROM Sequences; /*--------------------* | 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 in the corresponding original row ([5, 10]) didn't contain 2.
Scanning arrays
To check if an array contains a specific value, use the IN operator with UNNEST. To check if an array contains a value matching a condition, use the EXISTS operator with UNNEST.
Scanning for specific values
To scan an array for a specific value, use the IN operator with UNNEST.
Example
The following example returns true if the array contains the number 2.
SELECT 2 IN UNNEST([0, 1, 1, 2, 3, 5]) AS contains_value; /*----------------* | contains_value | +----------------+ | true | *----------------*/ To return the rows of a table where the array column contains a specific value, filter the results of IN UNNEST using the WHERE clause.
Example
The following example returns the id value for the rows where the array column contains the value 2.
WITH Sequences AS (SELECT 1 AS id, [0, 1, 1, 2, 3, 5] AS some_numbers UNION ALL SELECT 2 AS id, [2, 4, 8, 16, 32] AS some_numbers UNION ALL SELECT 3 AS id, [5, 10] AS some_numbers) SELECT id AS matching_rows FROM Sequences WHERE 2 IN UNNEST(Sequences.some_numbers) ORDER BY matching_rows; /*---------------* | matching_rows | +---------------+ | 1 | | 2 | *---------------*/ Scanning for values that satisfy a condition
To scan an array for values that match a condition, use UNNEST to return a table of the elements in the array, use WHERE to filter the resulting table in a subquery, and use EXISTS to check if the filtered table contains any rows.
Example
The following example returns the id value for the rows where the array column contains values greater than 5.
WITH Sequences AS ( SELECT 1 AS id, [0, 1, 1, 2, 3, 5] AS some_numbers UNION ALL SELECT 2 AS id, [2, 4, 8, 16, 32] AS some_numbers UNION ALL SELECT 3 AS id, [5, 10] AS some_numbers ) SELECT id AS matching_rows FROM Sequences WHERE EXISTS(SELECT * FROM UNNEST(some_numbers) AS x WHERE x > 5); /*---------------* | matching_rows | +---------------+ | 2 | | 3 | *---------------*/ Scanning for STRUCT field values that satisfy a condition
To search an array of STRUCT values for a field whose value matches a condition, use UNNEST to return a table with a column for each STRUCT field, then filter non-matching rows from the table using WHERE EXISTS.
Example
The following example returns the rows where the array column contains a STRUCT whose field b has a value greater than 3.
WITH Sequences AS ( SELECT 1 AS id, [STRUCT(0 AS a, 1 AS b)] AS some_numbers UNION ALL SELECT 2 AS id, [STRUCT(2 AS a, 4 AS b)] AS some_numbers UNION ALL SELECT 3 AS id, [STRUCT(5 AS a, 3 AS b), STRUCT(7 AS a, 4 AS b)] AS some_numbers ) SELECT id AS matching_rows FROM Sequences WHERE EXISTS(SELECT 1 FROM UNNEST(some_numbers) WHERE b > 3); /*---------------* | matching_rows | +---------------+ | 2 | | 3 | *---------------*/ Arrays and aggregation
With GoogleSQL, you can aggregate values into an array using ARRAY_AGG().
WITH Fruits AS (SELECT "apple" AS fruit UNION ALL SELECT "pear" AS fruit UNION ALL SELECT "banana" AS fruit) SELECT ARRAY_AGG(fruit) AS fruit_basket FROM Fruits; /*-----------------------* | fruit_basket | +-----------------------+ | [apple, pear, banana] | *-----------------------*/ The array returned by ARRAY_AGG() is in an arbitrary order, since the order in which the function concatenates values isn't guaranteed. To order the array elements, use ORDER BY. For example:
WITH Fruits AS (SELECT "apple" AS fruit UNION ALL SELECT "pear" AS fruit UNION ALL SELECT "banana" AS fruit) SELECT ARRAY_AGG(fruit ORDER BY fruit) AS fruit_basket FROM Fruits; /*-----------------------* | fruit_basket | +-----------------------+ | [apple, banana, pear] | *-----------------------*/ You can also apply aggregate functions such as SUM() to the elements in an array. For example, the following query returns the sum of array elements for each row of the Sequences table.
WITH Sequences AS (SELECT [0, 1, 1, 2, 3, 5] AS some_numbers UNION ALL SELECT [2, 4, 8, 16, 32] AS some_numbers UNION ALL SELECT [5, 10] AS some_numbers) SELECT some_numbers, (SELECT SUM(x) FROM UNNEST(s.some_numbers) AS x) AS sums FROM Sequences AS s; /*--------------------+------* | 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.
WITH Aggregates AS (SELECT [1,2] AS numbers UNION ALL SELECT [3,4] AS numbers UNION ALL SELECT [5, 6] AS numbers) SELECT ARRAY_CONCAT_AGG(numbers) AS count_to_six_agg FROM Aggregates; /*--------------------------------------------------* | count_to_six_agg | +--------------------------------------------------+ | [1, 2, 3, 4, 5, 6] | *--------------------------------------------------*/ Converting arrays to strings
The ARRAY_TO_STRING() function allows you to convert an ARRAY<STRING> to a single STRING value or an ARRAY<BYTES> to a single BYTES value where the resulting value is the ordered concatenation of the array elements.
The second argument is the separator that the function will insert between inputs to produce the output; this second argument must be of the same type as the elements of the first argument.
Example:
WITH Words AS (SELECT ["Hello", "World"] AS greeting) SELECT ARRAY_TO_STRING(greeting, " ") AS greetings FROM Words; /*-------------* | greetings | +-------------+ | Hello World | *-------------*/ The optional third argument takes the place of NULL values in the input array.
If you omit this argument, then the function ignores
NULLarray elements.If you provide an empty string, the function inserts a separator for
NULLarray elements.
Example:
SELECT ARRAY_TO_STRING(arr, ".", "N") AS non_empty_string, ARRAY_TO_STRING(arr, ".", "") AS empty_string, ARRAY_TO_STRING(arr, ".") AS omitted FROM (SELECT ["a", NULL, "b", NULL, "c", NULL] AS arr); /*------------------+--------------+---------* | 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 the ARRAY_CONCAT() function.
SELECT ARRAY_CONCAT([1, 2], [3, 4], [5, 6]) AS count_to_six; /*--------------------------------------------------* | count_to_six | +--------------------------------------------------+ | [1, 2, 3, 4, 5, 6] | *--------------------------------------------------*/ Updating arrays
Consider the following table called arrays_table. The first column in the table is an array of integers and the second column contains two nested arrays of integers.
WITH arrays_table AS ( SELECT [1, 2] AS regular_array, STRUCT([10, 20] AS first_array, [100, 200] AS second_array) AS nested_arrays UNION ALL SELECT [3, 4] AS regular_array, STRUCT([30, 40] AS first_array, [300, 400] AS second_array) AS nested_arrays ) SELECT * FROM arrays_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 the UPDATE statement. The following example inserts the number 5 into the regular_array column, and inserts the elements from the first_array field of the nested_arrays column into the second_array field:
UPDATE arrays_table SET regular_array = ARRAY_CONCAT(regular_array, [5]), nested_arrays.second_array = ARRAY_CONCAT(nested_arrays.second_array, nested_arrays.first_array) WHERE TRUE; SELECT * FROM arrays_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 array consisting of pairs of elements from input arrays, taken from their corresponding positions. This operation is sometimes called zipping.
You can zip arrays with UNNEST and WITH OFFSET. In this example, each value pair is stored as a STRUCT in an array.
WITH Combinations AS ( SELECT ['a', 'b'] AS letters, [1, 2, 3] AS numbers ) SELECT ARRAY( SELECT AS STRUCT letters[SAFE_OFFSET(index)] AS letter, numbers[SAFE_OFFSET(index)] AS number FROM Combinations INNER JOIN UNNEST( GENERATE_ARRAY( 0, LEAST(ARRAY_LENGTH(letters), ARRAY_LENGTH(numbers)) - 1)) AS index ORDER BY index ) AS pairs; /*------------------------------* | pairs | +------------------------------+ | [{ letter: "a", number: 1 }, | | { letter: "b", number: 2 }] | *------------------------------*/ You can use input arrays of different lengths as long as the first array is equal to or less than the length of the second array. The zipped array will be the length of the shortest input array.
To get a zipped array that includes all the elements even when the input arrays are different lengths, change LEAST to GREATEST. Elements of either array that have no associated element in the other array will be paired with NULL.
WITH Combinations AS ( SELECT ['a', 'b'] AS letters, [1, 2, 3] AS numbers ) SELECT ARRAY( SELECT AS STRUCT letters[SAFE_OFFSET(index)] AS letter, numbers[SAFE_OFFSET(index)] AS number FROM Combinations INNER JOIN UNNEST( GENERATE_ARRAY( 0, GREATEST(ARRAY_LENGTH(letters), ARRAY_LENGTH(numbers)) - 1)) AS index ORDER BY index ) AS pairs; /*-------------------------------* | pairs | +-------------------------------+ | [{ letter: "a", number: 1 }, | | { letter: "b", number: 2 }, | | { letter: null, number: 3 }] | *-------------------------------*/ Building arrays of arrays
GoogleSQL doesn't support building arrays of arrays directly. Instead, you must create an array of structs, with each struct containing a field of type ARRAY. To illustrate this, consider the following Points table:
/*----------* | point | +----------+ | [1, 5] | | [2, 8] | | [3, 7] | | [4, 1] | | [5, 7] | *----------*/ Now, let's say you wanted to create an array consisting of each point in the Points table. To accomplish this, wrap the array returned from each row in a STRUCT, as shown below.
WITH Points AS (SELECT [1, 5] AS point UNION ALL SELECT [2, 8] AS point UNION ALL SELECT [3, 7] AS point UNION ALL SELECT [4, 1] AS point UNION ALL SELECT [5, 7] AS point) SELECT ARRAY( SELECT STRUCT(point) FROM Points) AS coordinates; /*-------------------* | coordinates | +-------------------+ | [{point: [1,5]}, | | {point: [2,8]}, | | {point: [5,7]}, | | {point: [3,7]}, | | {point: [4,1]}] | *-------------------*/