在 Cloud Spanner SQL 中，陣列是一種排序清單，由資料類型相同的零或多個值組成。您可以建構簡單資料類型 (如 INT64) 的陣列，以及複合式資料類型 (如 STRUCT) 的陣列。目前的例外是 ARRAY 資料類型，系統不支援陣列的陣列。
您可以透過 Cloud Spanner SQL 建構陣列文字，運用 ARRAY 函式從子查詢建構陣列，然後使用 ARRAY_AGG 函式將值匯總到陣列中。
您可以採用 ARRAY_CONCAT() 之類的函式合併陣列，然後用 ARRAY_TO_STRING() 將陣列轉換成字串。
建構陣列
使用陣列文字
您可以使用方括號 ([ 和 ])，透過 Cloud Spanner SQL 建立陣列文字。陣列中的每個元素以半形逗號分隔。
SELECT [1, 2, 3] as numbers;
SELECT ["apple", "pear", "orange"] as fruit;
SELECT [true, false, true] as booleans;您也可以從任何具有相容類型的運算式建立陣列。例如：
SELECT [a, b, c]
FROM
(SELECT 5 AS a,
37 AS b,
406 AS c);
SELECT [a, b, c]
FROM
(SELECT CAST(5 AS INT64) AS a,
CAST(37 AS FLOAT64) AS b,
406 AS c);請注意，第二個範例含有三個運算式：一個傳回 INT64，一個傳回 FLOAT64，而一個宣告文字。這個運算式之所以能作用，是因為這三個運算式均共用 FLOAT64 作為超級類型。
如要宣告陣列的特定資料類型，請使用角括號 (< 和 >)。例如：
SELECT ARRAY<FLOAT64>[1, 2, 3] as floats;大部分資料類型的陣列 (例如 INT64 或 STRING) 不需要先宣告。
SELECT [1, 2, 3] as numbers;您可以使用 ARRAY<type>[] 寫入特定類型的空陣列，也可以使用 [] 寫入未設定類型的空陣列，在此情況下，Cloud Spanner SQL 會嘗試從上下文推斷陣列類型。如果 Cloud Spanner SQL 無法推斷出類型，則會使用預設類型 ARRAY<INT64>。
使用產生的值
您也可以使用產生的值建構 ARRAY。
產生整數陣列
GENERATE_ARRAY 會從開始值、結束值和步驟值產生值的陣列。舉例來說，以下查詢產生的陣列含有 11 到 33 (含) 之間所有奇數整數。
SELECT GENERATE_ARRAY(11, 33, 2) AS odds;
+--------------------------------------------------+
| odds |
+--------------------------------------------------+
| [11, 13, 15, 17, 19, 21, 23, 25, 27, 29, 31, 33] |
+--------------------------------------------------+若提供負步驟值，也可以產生遞減順序的值陣列：
SELECT GENERATE_ARRAY(21, 14, -1) AS countdown;
+----------------------------------+
| countdown |
+----------------------------------+
| [21, 20, 19, 18, 17, 16, 15, 14] |
+----------------------------------+產生日期陣列
GENERATE_DATE_ARRAY 會從開始和結束 DATE 以及步驟 INTERVAL 產生 DATE 陣列。
您可以使用 GENERATE_DATE_ARRAY 產生一組 DATE 值。舉例來說，這個查詢會傳回目前的 DATE 及後續的 DATE，間隔為 1 WEEK，一直到之後的 DATE (含) 為止：
SELECT
GENERATE_DATE_ARRAY('2017-11-21', '2017-12-31', INTERVAL 1 WEEK)
AS date_array;
+--------------------------------------------------------------------------+
| date_array |
+--------------------------------------------------------------------------+
| [2017-11-21, 2017-11-28, 2017-12-05, 2017-12-12, 2017-12-19, 2017-12-26] |
+--------------------------------------------------------------------------+存取陣列元素
請查看以下資料表 sequences：
CREATE TABLE sequences (
id INT64 NOT NULL,
some_numbers ARRAY<INT64> NOT NULL
) PRIMARY KEY(id);假設資料表填入了以下資料列：
+----+---------------------+
| id | some_numbers |
+----+---------------------+
| 1 | [0, 1, 1, 2, 3, 5] |
| 2 | [2, 4, 8, 16, 32] |
| 3 | [5, 10] |
+----+---------------------+這個資料表含有 ARRAY 資料類型的資料欄 some_numbers。如要從陣列中存取這個資料欄的元素，您必須指定要使用的索引類型：OFFSET 代表索引從零開始，或 ORDINAL 代表索引從一開始。
以下查詢顯示如何使用 OFFSET() 和 ORDINAL()：
SELECT some_numbers,
some_numbers[OFFSET(1)] AS offset_1,
some_numbers[ORDINAL(1)] AS ordinal_1
FROM sequences;
+---------------+----------+-----------+
| some_numbers | offset_1 | ordinal_1 |
+---------------+----------+-----------+
| [0,1,1,2,3,5] | 1 | 0 |
+---------------+----------+-----------+
| [2,4,8,16,32] | 4 | 2 |
+---------------+----------+-----------+
| [5,10] | 10 | 5 |
+---------------+----------+-----------+瞭解長度
ARRAY_LENGTH() 函式會傳回陣列長度。
以下是查詢範例，假設使用與上述 sequences 資料表相同的定義，並使用相同的範例資料列：
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 |
+---------------+------+整併陣列
如要將 ARRAY 轉換成一組資料列，亦稱為「整併」，請使用 UNNEST 運算子。UNNEST 會使用 ARRAY，然後傳回代表 ARRAY 中每個元素的個別單列資料表。.
因為 UNNEST 會毀損 ARRAY 元素的順序，您也許會想要還原資料表的順序。為此，請使用選用的 WITH OFFSET 子句以傳回其他資料欄和每個陣列元素的偏移，然後使用 ORDER BY 子句以按照偏移排序列。
範例
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 |
+----------+--------+如要整併 ARRAY 的整個資料欄，同時還要保留每一列中其他資料欄的值，請使用 CROSS JOIN，將含有 ARRAY 資料欄的資料表聯結到這個 ARRAY 資料欄的 UNNEST 輸出。
這是一個關聯交叉聯結：UNNEST 運算子會從之前出現在 FROM 子句中來源資料表的每一列參照 ARRAY 的資料欄。對於來源資料表中的每一列 N，UNNEST 會將 ARRAY 從列 N 整併成一組含有 ARRAY 元素的列，然後 CROSS JOIN 會將新的這組列與來源資料表中的單列 N 聯結在一起。
範例
以下範例使用 UNNEST，讓陣列資料欄中的每個元素都會傳回一列。因為使用 CROSS JOIN，所以 id 資料欄所含的 id 值代表 sequences 中每一個數字所在的列。
SELECT id, flattened_numbers FROM sequences
CROSS 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 |
+------+-------------------+查詢巢狀陣列
如果資料表含有 ARRAY 的 STRUCT，您可以整併 ARRAY 以查詢 STRUCT 欄位，也可以整併 STRUCT 值的 ARRAY 欄位類型。
查詢 ARRAY 中的 STRUCT 元素
以下範例使用 UNNEST 與 CROSS JOIN 來整併 STRUCT 的 ARRAY。
SELECT race,
participant.name,
participant.splits
FROM
(SELECT "800M" AS race,
[STRUCT("Rudisha" as name, [23.4, 26.3, 26.4, 26.1] AS splits),
STRUCT("Makhloufi" AS name, [24.5, 25.4, 26.6, 26.1] AS splits),
STRUCT("Murphy" AS name, [23.9, 26.0, 27.0, 26.0] AS splits),
STRUCT("Bosse" AS name, [23.6, 26.2, 26.5, 27.1] AS splits),
STRUCT("Rotich" AS name, [24.7, 25.6, 26.9, 26.4] AS splits),
STRUCT("Lewandowski" AS name, [25.0, 25.7, 26.3, 27.2] AS splits),
STRUCT("Kipketer" AS name, [23.2, 26.1, 27.3, 29.4] AS splits),
STRUCT("Berian" AS name, [23.7, 26.1, 27.0, 29.3] as splits)]
AS participants
) AS r
CROSS JOIN UNNEST(r.participants) AS participant;
+------+-------------+-----------------------+
| race | name | splits |
+------+-------------+-----------------------+
| 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] |
+------+-------------+-----------------------+您可以在重複的欄位中發現特定資訊。舉例來說，以下查詢會傳回 800 公尺競賽中速度最快的跑者。
此範例不需要整併陣列，但卻代表從重複欄位中取得資訊的一種常見方法。
範例
SELECT race,
(SELECT name
FROM UNNEST(participants)
ORDER BY (
SELECT SUM(duration)
FROM UNNEST(splits) AS duration) ASC
LIMIT 1) AS fastest_racer
FROM
(SELECT "800M" AS race,
[STRUCT("Rudisha" as name, [23.4, 26.3, 26.4, 26.1] AS splits),
STRUCT("Makhloufi" AS name, [24.5, 25.4, 26.6, 26.1] AS splits),
STRUCT("Murphy" AS name, [23.9, 26.0, 27.0, 26.0] AS splits),
STRUCT("Bosse" AS name, [23.6, 26.2, 26.5, 27.1] AS splits),
STRUCT("Rotich" AS name, [24.7, 25.6, 26.9, 26.4] AS splits),
STRUCT("Lewandowski" AS name, [25.0, 25.7, 26.3, 27.2] AS splits),
STRUCT("Kipketer" AS name, [23.2, 26.1, 27.3, 29.4] AS splits),
STRUCT("Berian" AS name, [23.7, 26.1, 27.0, 29.3] as splits)]
AS participants
) AS r;
+------+---------------+
| race | fastest_racer |
+------+---------------+
| 800M | Rudisha |
+------+---------------+查詢 STRUCT 中的 ARRAY 類型欄位
您也可以從巢狀的重複欄位取得資訊。舉例來說，下列陳述式會傳回 800 公尺競賽中圈速最快的跑者。
SELECT race,
(SELECT name
FROM UNNEST(participants),
UNNEST(splits) AS duration
ORDER BY duration ASC LIMIT 1) AS runner_with_fastest_lap
FROM
(SELECT "800M" AS race,
[STRUCT("Rudisha" as name, [23.4, 26.3, 26.4, 26.1] AS splits),
STRUCT("Makhloufi" AS name, [24.5, 25.4, 26.6, 26.1] AS splits),
STRUCT("Murphy" AS name, [23.9, 26.0, 27.0, 26.0] AS splits),
STRUCT("Bosse" AS name, [23.6, 26.2, 26.5, 27.1] AS splits),
STRUCT("Rotich" AS name, [24.7, 25.6, 26.9, 26.4] AS splits),
STRUCT("Lewandowski" AS name, [25.0, 25.7, 26.3, 27.2] AS splits),
STRUCT("Kipketer" AS name, [23.2, 26.1, 27.3, 29.4] AS splits),
STRUCT("Berian" AS name, [23.7, 26.1, 27.0, 29.3] as splits)]
AS participants
) AS r;
+------+-------------------------+
| race | runner_with_fastest_lap |
+------+-------------------------+
| 800M | Kipketer |
+------+-------------------------+請注意，之前的查詢使用逗號運算子 (,) 來執行隱含的 CROSS JOIN。作用與使用明確 CROSS JOIN 的下列範例相等。
SELECT race,
(SELECT name
FROM UNNEST(participants)
CROSS JOIN UNNEST(splits) AS duration
ORDER BY duration ASC LIMIT 1) AS runner_with_fastest_lap
FROM
(SELECT "800M" AS race,
[STRUCT("Rudisha" as name, [23.4, 26.3, 26.4, 26.1] AS splits),
STRUCT("Makhloufi" AS name, [24.5, 25.4, 26.6, 26.1] AS splits),
STRUCT("Murphy" AS name, [23.9, 26.0, 27.0, 26.0] AS splits),
STRUCT("Bosse" AS name, [23.6, 26.2, 26.5, 27.1] AS splits),
STRUCT("Rotich" AS name, [24.7, 25.6, 26.9, 26.4] AS splits),
STRUCT("Lewandowski" AS name, [25.0, 25.7, 26.3, 27.2] AS splits),
STRUCT("Kipketer" AS name, [23.2, 26.1, 27.3, 29.4] AS splits),
STRUCT("Berian" AS name, [23.7, 26.1, 27.0, 29.3] as splits)]
AS participants
) AS r;
+------+-------------------------+
| race | runner_with_fastest_lap |
+------+-------------------------+
| 800M | Kipketer |
+------+-------------------------+請注意，整併陣列與 CROSS JOIN 會排除含有空陣列或 NULL 陣列的列。如果要併入這些列，請使用 LEFT JOIN。
SELECT
name, sum(duration) as duration
FROM
(SELECT "800M" AS race,
[STRUCT("Rudisha" AS name, [23.4, 26.3, 26.4, 26.1] AS splits),
STRUCT("Makhloufi" AS name, [24.5, 25.4, 26.6, 26.1] AS splits),
STRUCT("Murphy" AS name, [23.9, 26.0, 27.0, 26.0] AS splits),
STRUCT("Bosse" AS name, [23.6, 26.2, 26.5, 27.1] AS splits),
STRUCT("Rotich" AS name, [24.7, 25.6, 26.9, 26.4] AS splits),
STRUCT("Lewandowski" AS name, [25.0, 25.7, 26.3, 27.2] AS splits),
STRUCT("Kipketer" AS name, [23.2, 26.1, 27.3, 29.4] AS splits),
STRUCT("Nathan" as name, ARRAY<FLOAT64>[] as splits),
STRUCT("David" as name, NULL as splits)]
AS participants) AS races,
races.participants LEFT JOIN participants.splits duration
GROUP BY name;
+-------------+--------------------+
| name | duration |
+-------------+--------------------+
| Murphy | 102.9 |
| Rudisha | 102.19999999999999 |
| David | NULL |
| Rotich | 103.6 |
| Makhloufi | 102.6 |
| Bosse | 103.4 |
| Kipketer | 106 |
| Nathan | NULL |
| Lewandowski | 104.2 |
+-------------+--------------------+從子查詢建立陣列
處理陣列的常見工作是將子查詢結果轉成陣列。在 Cloud Spanner SQL 中，您可以使用下列函式完成此工作：
ARRAY() 函式。
舉例來說，請細想對 sequences 資料表執行的下列作業：
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] |
+---------------+----------------+此範例從名稱為 sequences 的資料表開始。這個資料表含有類型 ARRAY<INT64> 的資料欄 some_numbers。
查詢本身含有子查詢。這個子查詢選取 some_numbers 資料欄中的每一列，然後使用 UNNEST 以一組列傳回陣列。接下來，將每個值乘以 2，然後使用 ARRAY() 運算子將這些列重新合併到陣列。
篩選陣列
以下範例在 ARRAY() 運算子的子查詢中使用 WHERE 子句來篩選傳回的列。
注意事項：在下例中，結果列不會排序。
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] |
+------------------------+
| [] |
+------------------------+請注意，第三列含有空陣列，因為對應原始列 ([5, 10]) 中的元素不符合 x < 5 的篩選需求。
您也可以使用 SELECT DISTINCT，只傳回陣列中非重複的元素，藉以篩選陣列。
SELECT ARRAY(SELECT DISTINCT x
FROM UNNEST(some_numbers) AS x) AS unique_numbers
FROM sequences
WHERE id = 1;
+----------------+
| unique_numbers |
+----------------+
| [0,1,2,3,5] |
+----------------+您也可以使用 IN 關鍵字來篩選陣列的資料列。這個關鍵字藉由判定特定值是否符合陣列中的元素，篩選含有陣列的列。
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] |
+---------------+
| [] |
+---------------+請再次注意，第三列含有空陣列，因為對應原始列 ([5, 10]) 中的陣列不含 2。
掃描陣列
如要檢查陣列是否含有特定值，請使用 IN 運算子與 UNNEST。如要檢查陣列是否含有符合條件的值，請使用 EXISTS 函式與 UNNEST。
掃描特定值
如要掃描陣列是否含有特定值，請使用 IN 運算子與 UNNEST。
範例
以下範例顯示陣列含有數字 2 時會傳回 true。
SELECT 2 IN UNNEST([0, 1, 1, 2, 3, 5]) AS contains_value;
+----------------+
| contains_value |
+----------------+
| true |
+----------------+如要傳回陣列資料欄含有特定值的資料表列，請使用 WHERE 子句來篩選 IN UNNEST 的結果。
範例
以下範例傳回的 id 值代表陣列資料欄含有值 2 的列。
SELECT id AS matching_rows
FROM (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)
WHERE 2 IN UNNEST(some_numbers)
ORDER BY matching_rows;
+---------------+
| matching_rows |
+---------------+
| 1 |
| 2 |
+---------------+掃描符合條件的值
如要掃描陣列尋找符合條件的值，請使用 UNNEST 傳回陣列中的元素資料表，使用 WHERE 來篩選子查詢中的結果資料表，並使用 EXISTS 來檢查篩選的資料表是否含有任何列。
範例
以下範例傳回的 id 值代表陣列資料欄所含值大於 5 的列。
SELECT id AS matching_rows
FROM (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)
WHERE EXISTS (SELECT *
FROM UNNEST(some_numbers) AS x
WHERE x > 5);
+---------------+
| matching_rows |
+---------------+
| 2 |
| 3 |
+---------------+掃描符合條件的 STRUCT 欄位值
如要掃描 STRUCT 的陣列尋找值符合條件的欄位，請使用 UNNEST 傳回每一個 STRUCT 欄位所在資料欄的資料表，然後使用 WHERE EXISTS 篩選資料表中不符條件的列。
範例
以下範例傳回陣列資料欄所含的 STRUCT 中欄位 b 的值大於 3 的列。
SELECT id AS matching_rows
FROM (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)
WHERE EXISTS (
SELECT 1
FROM UNNEST(some_numbers)
WHERE b > 3
);
+---------------+
| matching_rows |
+---------------+
| 2 |
| 3 |
+---------------+陣列與匯總
使用 Cloud Spanner SQL 時，您可以透過 ARRAY_AGG() 將值匯總到陣列。
請查看下列資料表 fruits：
CREATE TABLE fruits (
fruit STRING(MAX),
id INT64 NOT NULL
) PRIMARY KEY(id);假設資料表填入了以下資料：
+----+--------------+
| id | fruit |
+----+--------------+
| 1 | "apple" |
| 2 | "pear" |
| 3 | "banana" |
+----+--------------+此查詢顯示如何使用 ARRAY_AGG()：
SELECT ARRAY_AGG(fruit) AS fruit_basket
FROM fruits;
+---------------------+
| fruit_basket |
+---------------------+
| [apple,pear,banana] |
+---------------------+ARRAY_AGG() 傳回的陣列採任意順序，這是因為系統無法保證函式串連值所採用的順序。
您也可以將匯總函式 (如 SUM()) 套用於陣列中的元素。舉例來說，下列查詢會傳回 sequences 資料表中每一列的陣列元素總和。
SELECT some_numbers,
(SELECT SUM(x)
FROM UNNEST(s.some_numbers) x) AS sums
FROM sequences s;
+---------------+------+
| some_numbers | sums |
+---------------+------+
| [0,1,1,2,3,5] | 12 |
+---------------+------+
| [2,4,8,16,32] | 62 |
+---------------+------+
| [5,10] | 15 |
+---------------+------+將陣列轉換成字串
ARRAY_TO_STRING() 函式可讓您將 ARRAY<STRING> 轉換成一個 STRING 值或將 ARRAY<BYTES> 轉換成一個 BYTES 值，其中的結果值是排序串連的陣列元素。
第二個引數是函式會在輸入之間插入以產生輸出的分隔符；第二個引數的類型必須與第一個引數的元素相同。
範例：
SELECT ARRAY_TO_STRING(["Hello", "World"], " ") AS greetings;
+-------------+
| greetings |
+-------------+
| Hello World |
+-------------+選用的第三個引數會取代輸入陣列中的 NULL 值。
-
如果省略這個引數，則函式會忽略
NULL陣列元素。 -
如果您提供空字串，則函式會插入
NULL陣列元素的分隔符。
範例：
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 |
+------------------+--------------+---------+合併陣列
在某些情況下，您可能想要將多個陣列合併成一個陣列。您可以使用 ARRAY_CONCAT() 函式來完成此工作。
SELECT ARRAY_CONCAT([1, 2], [3, 4], [5, 6]) as count_to_six;
+--------------------------------------------------+
| count_to_six |
+--------------------------------------------------+
| [1, 2, 3, 4, 5, 6] |
+--------------------------------------------------+建立陣列的陣列
Cloud Spanner SQL 不支援直接建立陣列的陣列。您必須先建立 struct 的陣列，每一個 struct 含有 ARRAY 類型的欄位。為了說明這項工作，請考慮下列 points 資料表：
CREATE TABLE points (
point ARRAY<INT64>,
id INT64 NOT NULL
) PRIMARY KEY(id);假設資料表填入了以下資料列：
+----+----------+
| id | point |
+----+----------+
| 1 | [1, 5] |
| 2 | [2, 8] |
| 3 | [3, 7] |
| 4 | [4, 1] |
| 5 | [5, 7] |
+----+----------+現在，我們假設您要建立一個陣列，該陣列由 points 資料表中的每一個 point 組成。如要完成這項工作，請將每一列傳回的陣列納入 STRUCT，如下所示。
SELECT ARRAY(
SELECT STRUCT(point)
FROM points)
AS coordinates;
+--------------+
| coordinates |
+--------------+
| point: [1,5] |
| point: [2,8] |
| point: [3,7] |
| point: [4,1] |
| point: [5,7] |
+--------------+