本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。
# 查詢陣列
Amazon Athena 可讓您建立陣列、串連陣列、將陣列轉換為不同的資料類型，然後篩選、展開和排序陣列。
**Topics**
+ [建立陣列](creating-arrays.md)
+ [串連字串和陣列](concatenating-strings-and-arrays.md)
+ [轉換陣列資料類型](converting-array-data-types.md)
+ [尋找陣列長度](finding-lengths.md)
+ [存取陣列元素](accessing-array-elements.md)
+ [將巢狀陣列扁平化](flattening-arrays.md)
+ [從子查詢建立陣列](creating-arrays-from-subqueries.md)
+ [篩選陣列](filtering-arrays.md)
+ [排序陣列](sorting-arrays.md)
+ [搭配陣列使用彙總函式](arrays-and-aggregation.md)
+ [將陣列轉換為字串](converting-arrays-to-strings.md)
+ [使用陣列建立映射](arrays-create-maps.md)
+ [查詢具有複雜類型的陣列](rows-and-structs.md)
# 建立陣列
若要在 Athena 中建置陣列常值，請使用 `ARRAY` 關鍵字，後面接著方括弧 `[ ]` 並包含陣列元素 (以逗號分隔)。
## 範例
此查詢會建立具有四個元素的陣列。
```
SELECT ARRAY [1,2,3,4] AS items
```
它會傳回：
```
+-----------+
| items |
+-----------+
| [1,2,3,4] |
+-----------+
```
此查詢會建立兩個陣列。
```
SELECT ARRAY[ ARRAY[1,2], ARRAY[3,4] ] AS items
```
它會傳回：
```
+--------------------+
| items |
+--------------------+
| [[1, 2], [3, 4]] |
+--------------------+
```
若要從選取的相容類型資料欄建立陣列，請使用查詢，如此範例所示：
```
WITH
dataset AS (
SELECT 1 AS x, 2 AS y, 3 AS z
)
SELECT ARRAY [x,y,z] AS items FROM dataset
```
此查詢會傳回：
```
+-----------+
| items |
+-----------+
| [1,2,3] |
+-----------+
```
在以下範例中，選取了兩個陣列，並以歡迎訊息傳回。
```
WITH
dataset AS (
SELECT
ARRAY ['hello', 'amazon', 'athena'] AS words,
ARRAY ['hi', 'alexa'] AS alexa
)
SELECT ARRAY[words, alexa] AS welcome_msg
FROM dataset
```
此查詢會傳回：
```
+----------------------------------------+
| welcome_msg |
+----------------------------------------+
| [[hello, amazon, athena], [hi, alexa]] |
+----------------------------------------+
```
若要建立一系列的金鑰值組，請使用 `MAP` 運算子，其會使用一系列的金鑰，接著是一系列的值，如此範例所示：
```
SELECT ARRAY[
MAP(ARRAY['first', 'last', 'age'],ARRAY['Bob', 'Smith', '40']),
MAP(ARRAY['first', 'last', 'age'],ARRAY['Jane', 'Doe', '30']),
MAP(ARRAY['first', 'last', 'age'],ARRAY['Billy', 'Smith', '8'])
] AS people
```
此查詢會傳回：
```
+-----------------------------------------------------------------------------------------------------+
| people |
+-----------------------------------------------------------------------------------------------------+
| [{last=Smith, first=Bob, age=40}, {last=Doe, first=Jane, age=30}, {last=Smith, first=Billy, age=8}] |
+-----------------------------------------------------------------------------------------------------+
```
# 串連字串和陣列
串連字串和串連陣列會使用類似的技術。
## 串連字串
若要串連兩個字串，您可以使用雙縱線 `||` 運算子，如下列範例所示。
```
SELECT 'This' || ' is' || ' a' || ' test.' AS Concatenated_String
```
此查詢會傳回：
****
| \$1 | Concatenated\$1String |
| --- | --- |
| 1 | `This is a test.` |
您可以使用 `concat()` 函數達到相同的效果。
```
SELECT concat('This', ' is', ' a', ' test.') AS Concatenated_String
```
此查詢會傳回：
****
| \$1 | Concatenated\$1String |
| --- | --- |
| 1 | `This is a test.` |
您可以使用 `concat_ws()` 函式將字串與第一個引數中指定的分隔符號串連。
```
SELECT concat_ws(' ', 'This', 'is', 'a', 'test.') as Concatenated_String
```
此查詢會傳回：
****
| \$1 | Concatenated\$1String |
| --- | --- |
| 1 | `This is a test.` |
若要使用點串連字串資料類型的兩個資料欄，請使用雙引號引用這兩個資料欄，並以單引號括住該點做為硬式編碼字串。如果資料欄不是字串資料類型，您可以先使用 `CAST("column_name" as VARCHAR)` 轉換資料欄類型。
```
SELECT "col1" || '.' || "col2" as Concatenated_String
FROM my_table
```
此查詢會傳回：
****
| \$1 | Concatenated\$1String |
| --- | --- |
| 1 | `col1_string_value.col2_string_value` |
## 串連陣列
您可以使用相同的技巧串連陣列。
若要串連多個陣列，請使用雙縱線 `||` 運算子。
```
SELECT ARRAY [4,5] || ARRAY[ ARRAY[1,2], ARRAY[3,4] ] AS items
```
此查詢會傳回：
****
| \$1 | items |
| --- | --- |
| 1 | `[[4, 5], [1, 2], [3, 4]]` |
若要將多個陣列合併到單一陣列，請使用雙縱線運算子或 `concat()` 函數。
```
WITH
dataset AS (
SELECT
ARRAY ['Hello', 'Amazon', 'Athena'] AS words,
ARRAY ['Hi', 'Alexa'] AS alexa
)
SELECT concat(words, alexa) AS welcome_msg
FROM dataset
```
此查詢會傳回：
****
| \$1 | welcome\$1msg |
| --- | --- |
| 1 | `[Hello, Amazon, Athena, Hi, Alexa]` |
如需有關 `concat()` 其他字串函數的詳細資訊，請參閱 Trino 文件中的 [String functions and operators](https://trino.io/docs/current/functions/string.html) (字串函數和運算子)。
# 轉換陣列資料類型
若要將陣列中的資料轉換為支援的資料類型，請以 `CAST(value AS type)` 的格式使用 `CAST` 運算子。Athena 支援所有原生 Presto 資料類型。
```
SELECT
ARRAY [CAST(4 AS VARCHAR), CAST(5 AS VARCHAR)]
AS items
```
此查詢會傳回：
```
+-------+
| items |
+-------+
| [4,5] |
+-------+
```
以鍵值組元素建立兩個陣列，將它們轉換為 JSON 並串連在一起，如這個範例所示：
```
SELECT
ARRAY[CAST(MAP(ARRAY['a1', 'a2', 'a3'], ARRAY[1, 2, 3]) AS JSON)] ||
ARRAY[CAST(MAP(ARRAY['b1', 'b2', 'b3'], ARRAY[4, 5, 6]) AS JSON)]
AS items
```
此查詢會傳回：
```
+--------------------------------------------------+
| items |
+--------------------------------------------------+
| [{"a1":1,"a2":2,"a3":3}, {"b1":4,"b2":5,"b3":6}] |
+--------------------------------------------------+
```
# 尋找陣列長度
`cardinality` 函數會傳回陣列的長度，如此範例所示：
```
SELECT cardinality(ARRAY[1,2,3,4]) AS item_count
```
此查詢會傳回：
```
+------------+
| item_count |
+------------+
| 4 |
+------------+
```
# 存取陣列元素
若要存取陣列元素，使用 `[]` 運算子，並以 1 指定第一個元素，2 指定第二個元素，依此類推，如此範例所示：
```
WITH dataset AS (
SELECT
ARRAY[CAST(MAP(ARRAY['a1', 'a2', 'a3'], ARRAY[1, 2, 3]) AS JSON)] ||
ARRAY[CAST(MAP(ARRAY['b1', 'b2', 'b3'], ARRAY[4, 5, 6]) AS JSON)]
AS items )
SELECT items[1] AS item FROM dataset
```
此查詢會傳回：
```
+------------------------+
| item |
+------------------------+
| {"a1":1,"a2":2,"a3":3} |
+------------------------+
```
若要存取指定位置的陣列元素 (稱為索引位置)，請使用 `element_at()` 函數，並指定陣列名稱和索引位置：
+ 如果索引大於 0，`element_at()` 會傳回您指定的元素，從陣列的開頭開始往結尾算。它的行為和 `[]` 運算子一樣。
+ 如果索引小於 0，`element_at()` 傳回的元素是從陣列的結尾開始往開頭算。
以下查詢會建立陣列 `words`，並從中選取第一個元素 `hello` 做為 `first_word`，選取第二個元素 `amazon` (從陣列結尾往前算) 做為 `middle_word`，選取第三個元素 `athena` 做為 `last_word`。
```
WITH dataset AS (
SELECT ARRAY ['hello', 'amazon', 'athena'] AS words
)
SELECT
element_at(words, 1) AS first_word,
element_at(words, -2) AS middle_word,
element_at(words, cardinality(words)) AS last_word
FROM dataset
```
此查詢會傳回：
```
+----------------------------------------+
| first_word | middle_word | last_word |
+----------------------------------------+
| hello | amazon | athena |
+----------------------------------------+
```
# 將巢狀陣列扁平化
使用巢狀陣列時，您經常需要將巢狀陣列元素展開至單一陣列，或將陣列展開至多個資料列。
## 使用扁平化函式
若要將巢狀陣列的元素扁平化至值的單一陣列，請使用 `flatten` 函數。此查詢會為陣列中的每個元素傳回一個資料列。
```
SELECT flatten(ARRAY[ ARRAY[1,2], ARRAY[3,4] ]) AS items
```
此查詢會傳回：
```
+-----------+
| items |
+-----------+
| [1,2,3,4] |
+-----------+
```
## 使用 CROSS JOIN 和 UNNEST
若要將陣列扁平化至多個資料列，請使用 `CROSS JOIN` 結合 `UNNEST` 運算子，如此範例所示：
```
WITH dataset AS (
SELECT
'engineering' as department,
ARRAY['Sharon', 'John', 'Bob', 'Sally'] as users
)
SELECT department, names FROM dataset
CROSS JOIN UNNEST(users) as t(names)
```
此查詢會傳回：
```
+----------------------+
| department | names |
+----------------------+
| engineering | Sharon |
+----------------------|
| engineering | John |
+----------------------|
| engineering | Bob |
+----------------------|
| engineering | Sally |
+----------------------+
```
若要將一系列的金鑰值組扁平化，請將選取的金鑰調換至資料欄，如此範例所示：
```
WITH
dataset AS (
SELECT
'engineering' as department,
ARRAY[
MAP(ARRAY['first', 'last', 'age'],ARRAY['Bob', 'Smith', '40']),
MAP(ARRAY['first', 'last', 'age'],ARRAY['Jane', 'Doe', '30']),
MAP(ARRAY['first', 'last', 'age'],ARRAY['Billy', 'Smith', '8'])
] AS people
)
SELECT names['first'] AS
first_name,
names['last'] AS last_name,
department FROM dataset
CROSS JOIN UNNEST(people) AS t(names)
```
此查詢會傳回：
```
+--------------------------------------+
| first_name | last_name | department |
+--------------------------------------+
| Bob | Smith | engineering |
| Jane | Doe | engineering |
| Billy | Smith | engineering |
+--------------------------------------+
```
從員工清單中選擇具有最高組合評分的員工。`UNNEST` 可用於 `FROM` 子句，而不需先有`CROSS JOIN`，因為它是預設的聯結運算子，因此已隱含具備。
```
WITH
dataset AS (
SELECT ARRAY[
CAST(ROW('Sally', 'engineering', ARRAY[1,2,3,4]) AS ROW(name VARCHAR, department VARCHAR, scores ARRAY(INTEGER))),
CAST(ROW('John', 'finance', ARRAY[7,8,9]) AS ROW(name VARCHAR, department VARCHAR, scores ARRAY(INTEGER))),
CAST(ROW('Amy', 'devops', ARRAY[12,13,14,15]) AS ROW(name VARCHAR, department VARCHAR, scores ARRAY(INTEGER)))
] AS users
),
users AS (
SELECT person, score
FROM
dataset,
UNNEST(dataset.users) AS t(person),
UNNEST(person.scores) AS t(score)
)
SELECT person.name, person.department, SUM(score) AS total_score FROM users
GROUP BY (person.name, person.department)
ORDER BY (total_score) DESC
LIMIT 1
```
此查詢會傳回：
```
+---------------------------------+
| name | department | total_score |
+---------------------------------+
| Amy | devops | 54 |
+---------------------------------+
```
從員工清單中選擇具有最高個別評分的員工。
```
WITH
dataset AS (
SELECT ARRAY[
CAST(ROW('Sally', 'engineering', ARRAY[1,2,3,4]) AS ROW(name VARCHAR, department VARCHAR, scores ARRAY(INTEGER))),
CAST(ROW('John', 'finance', ARRAY[7,8,9]) AS ROW(name VARCHAR, department VARCHAR, scores ARRAY(INTEGER))),
CAST(ROW('Amy', 'devops', ARRAY[12,13,14,15]) AS ROW(name VARCHAR, department VARCHAR, scores ARRAY(INTEGER)))
] AS users
),
users AS (
SELECT person, score
FROM
dataset,
UNNEST(dataset.users) AS t(person),
UNNEST(person.scores) AS t(score)
)
SELECT person.name, score FROM users
ORDER BY (score) DESC
LIMIT 1
```
此查詢會傳回：
```
+--------------+
| name | score |
+--------------+
| Amy | 15 |
+--------------+
```
### CROSS JOIN 和 UNNEST 的注意事項
如果 `UNNEST` 在查詢中的一個或多個陣列上使用，且其中一個陣列是 `NULL`，則查詢不會傳回任何資料列。如果 `UNNEST` 在一個空字串陣列上使用，則會傳回空字串。
例如，在下列查詢中，由於第二個陣列為 Null，因此查詢不會傳回任何資料列。
```
SELECT
col1,
col2
FROM UNNEST (ARRAY ['apples','oranges','lemons']) AS t(col1)
CROSS JOIN UNNEST (ARRAY []) AS t(col2)
```
在下面的範例中，第二個數組修改為包含一個空字串。針對每次資料列，查詢會傳回 `col1` 中的值，以及 `col2` 中的值的空字串。第二個陣列中的空字串為必要，以便傳回第一個陣列中的值。
```
SELECT
col1,
col2
FROM UNNEST (ARRAY ['apples','oranges','lemons']) AS t(col1)
CROSS JOIN UNNEST (ARRAY ['']) AS t(col2)
```
# 從子查詢建立陣列
從資料列的集合建立陣列。
```
WITH
dataset AS (
SELECT ARRAY[1,2,3,4,5] AS items
)
SELECT array_agg(i) AS array_items
FROM dataset
CROSS JOIN UNNEST(items) AS t(i)
```
此查詢會傳回：
```
+-----------------+
| array_items |
+-----------------+
| [1, 2, 3, 4, 5] |
+-----------------+
```
若要從一組資料列建立一系列的多個唯一值，請使用 `distinct` 關鍵字。
```
WITH
dataset AS (
SELECT ARRAY [1,2,2,3,3,4,5] AS items
)
SELECT array_agg(distinct i) AS array_items
FROM dataset
CROSS JOIN UNNEST(items) AS t(i)
```
此查詢會傳回下列結果。請注意，無法保證順序。
```
+-----------------+
| array_items |
+-----------------+
| [1, 2, 3, 4, 5] |
+-----------------+
```
如需有關使用 `array_agg` 函數的詳細資訊，請參閱 Trino 文件中的 [Aggregate functions](https://trino.io/docs/current/functions/aggregate.html) (彙總函數)。
# 篩選陣列
如果資料列的集合符合篩選條件，則從資料列的集合建立陣列。
```
WITH
dataset AS (
SELECT ARRAY[1,2,3,4,5] AS items
)
SELECT array_agg(i) AS array_items
FROM dataset
CROSS JOIN UNNEST(items) AS t(i)
WHERE i > 3
```
此查詢會傳回：
```
+-------------+
| array_items |
+-------------+
| [4, 5] |
+-------------+
```
根據其元素是否包含特定的值 (例如 2) 來篩選陣列，如此範例所示：
```
WITH
dataset AS (
SELECT ARRAY
[
ARRAY[1,2,3,4],
ARRAY[5,6,7,8],
ARRAY[9,0]
] AS items
)
SELECT i AS array_items FROM dataset
CROSS JOIN UNNEST(items) AS t(i)
WHERE contains(i, 2)
```
此查詢會傳回：
```
+--------------+
| array_items |
+--------------+
| [1, 2, 3, 4] |
+--------------+
```
## 使用 `filter` 函式
```
filter(ARRAY [list_of_values], boolean_function)
```
您可以在 `ARRAY` 表達式上使用 `filter` 函數建立一個新的陣列，該陣列是 *list\$1of\$1values* 中項目的子集，其 *boolean\$1function* 為 true。`filter` 函數在您無法使用 *UNNEST* 函數的情況下十分有用。
以下範例會篩選陣列 `[1,0,5,-1]` 中大於零的值。
```
SELECT filter(ARRAY [1,0,5,-1], x -> x>0)
```
**結果**
`[1,5]`
以下範例會篩選陣列 `[-1, NULL, 10, NULL]` 中的非零值。
```
SELECT filter(ARRAY [-1, NULL, 10, NULL], q -> q IS NOT NULL)
```
**結果**
`[-1,10]`
# 排序陣列
若要從一組資料行建立已排序的唯一值陣列，您可以使用 [array\$1sort](https://prestodb.io/docs/current/functions/array.html#array_sort) 函數，如下列範例所示。
```
WITH
dataset AS (
SELECT ARRAY[3,1,2,5,2,3,6,3,4,5] AS items
)
SELECT array_sort(array_agg(distinct i)) AS array_items
FROM dataset
CROSS JOIN UNNEST(items) AS t(i)
```
此查詢會傳回：
```
+--------------------+
| array_items |
+--------------------+
| [1, 2, 3, 4, 5, 6] |
+--------------------+
```
如需將陣列展開成多個資料列的相關資訊，請參閱[將巢狀陣列扁平化](flattening-arrays.md)。
# 搭配陣列使用彙總函式
+ 若要在陣列中新增值，請使用 `SUM`，如下列範例所示。
+ 若要彙總陣列中的多個資料列，請使用 `array_agg`。如需相關資訊，請參閱[從子查詢建立陣列](creating-arrays-from-subqueries.md)。
**注意**
自 Athena 引擎版本 2 開始，彙總函數支援 `ORDER BY`。
```
WITH
dataset AS (
SELECT ARRAY
[
ARRAY[1,2,3,4],
ARRAY[5,6,7,8],
ARRAY[9,0]
] AS items
),
item AS (
SELECT i AS array_items
FROM dataset, UNNEST(items) AS t(i)
)
SELECT array_items, sum(val) AS total
FROM item, UNNEST(array_items) AS t(val)
GROUP BY array_items;
```
在最後一個 `SELECT` 陳述式中，與其使用 `sum()` 和 `UNNEST`，您可以使用 `reduce()` 來減少處理時間和資料傳輸，如下列範例所示。
```
WITH
dataset AS (
SELECT ARRAY
[
ARRAY[1,2,3,4],
ARRAY[5,6,7,8],
ARRAY[9,0]
] AS items
),
item AS (
SELECT i AS array_items
FROM dataset, UNNEST(items) AS t(i)
)
SELECT array_items, reduce(array_items, 0 , (s, x) -> s + x, s -> s) AS total
FROM item;
```
此查詢會傳回下列結果。無法保證傳回結果的順序。
```
+----------------------+
| array_items | total |
+----------------------+
| [1, 2, 3, 4] | 10 |
| [5, 6, 7, 8] | 26 |
| [9, 0] | 9 |
+----------------------+
```
# 將陣列轉換為字串
若要將陣列為單一字串，請使用 `array_join` 函數。以下個別範例會建立名為 `dataset` 的資料表，其中包含名為 `words` 的別名陣列。查詢會使用 `array_join` 讓陣列元素加入 `words`、以空格分隔元素，然後在名為 `welcome_msg` 的別名資料欄中傳回產生的字串。
```
WITH
dataset AS (
SELECT ARRAY ['hello', 'amazon', 'athena'] AS words
)
SELECT array_join(words, ' ') AS welcome_msg
FROM dataset
```
此查詢會傳回：
```
+---------------------+
| welcome_msg |
+---------------------+
| hello amazon athena |
+---------------------+
```
# 使用陣列建立映射
映射是由 Athena 中可用的資料類型組成的鍵值組。若要建立映射，請使用 `MAP` 運算子並傳入兩個陣列：第一個是欄 (金鑰) 名稱，第二個是值。陣列的所有值必須是相同類型。如果任何映射值陣列元素必須是不同類型，您稍後可以轉換它們。
## 範例
此範例從資料集選取使用者。它使用 `MAP` 運算子並傳入兩個陣列。第一個陣列包含欄名稱的值，例如 "first"、"last" 和 "age"。第二個陣列包含每個欄的值，例如 "Bob"、"Smith"、"35"。
```
WITH dataset AS (
SELECT MAP(
ARRAY['first', 'last', 'age'],
ARRAY['Bob', 'Smith', '35']
) AS user
)
SELECT user FROM dataset
```
此查詢會傳回：
```
+---------------------------------+
| user |
+---------------------------------+
| {last=Smith, first=Bob, age=35} |
+---------------------------------+
```
您可以選取欄位名稱，後面接著 `[key_name]`，以擷取 `Map` 值，如以下範例所示：
```
WITH dataset AS (
SELECT MAP(
ARRAY['first', 'last', 'age'],
ARRAY['Bob', 'Smith', '35']
) AS user
)
SELECT user['first'] AS first_name FROM dataset
```
此查詢會傳回：
```
+------------+
| first_name |
+------------+
| Bob |
+------------+
```
# 查詢具有複雜類型和巢狀結構的陣列
您的來源資料通常包含具有複雜資料類型和巢狀結構的陣列。本節中的範例展示如何使用 Athena 查詢來變更元素的資料類型、在陣列內尋找元素，以及尋找關鍵字。
**Topics**
+ [建立 `ROW`](creating-row.md)
+ [使用 `CAST` 變更陣列中的欄位名稱](changing-row-arrays-with-cast.md)
+ [使用 `.` 標記法篩選陣列](filtering-with-dot.md)
+ [篩選含有巢狀值的陣列](filtering-nested-with-dot.md)
+ [使用 `UNNEST` 篩選陣列](filtering-with-unnest.md)
+ [使用 `regexp_like` 在陣列中尋找關鍵字](filtering-with-regexp.md)
# 建立 `ROW`
**注意**
本節中的範例使用 `ROW` 當為方法來建立要使用的範例資料。當您在 Athena 內查詢資料表時，您不需要建立 `ROW` 資料類型，因為已經從您的資料來源建立它們。當您使用 `CREATE_TABLE` 時，Athena 會在其中定義 `STRUCT`，在其中填入資料，然後替您為資料集的每個資料行建立 `ROW` 資料類型。基礎 `ROW` 資料類型由任何受支援 SQL 資料類型的具名欄位組成。
```
WITH dataset AS (
SELECT
ROW('Bob', 38) AS users
)
SELECT * FROM dataset
```
此查詢會傳回：
```
+-------------------------+
| users |
+-------------------------+
| {field0=Bob, field1=38} |
+-------------------------+
```
# 使用 `CAST` 變更陣列中的欄位名稱
若要變更陣列中含有 `ROW` 值的欄位名稱，您可以 `CAST` `ROW` 宣告：
```
WITH dataset AS (
SELECT
CAST(
ROW('Bob', 38) AS ROW(name VARCHAR, age INTEGER)
) AS users
)
SELECT * FROM dataset
```
此查詢會傳回：
```
+--------------------+
| users |
+--------------------+
| {NAME=Bob, AGE=38} |
+--------------------+
```
**注意**
在上述範例中，您將 `name` 宣告為 `VARCHAR`，因為這是它在 Presto 中的類型。如果您在 `CREATE TABLE` 陳述式內宣告這個 `STRUCT`，請使用 `String` 類型，因為 Hive 將此資料類型定義為 `String`。
# 使用 `.` 標記法篩選陣列
在下列範例中，使用點`.`符號從 AWS CloudTrail 日誌資料表的 `userIdentity` 欄中選取 `accountId` 欄位。如需詳細資訊，請參閱[查詢 AWS CloudTrail 日誌](cloudtrail-logs.md)。
```
SELECT
CAST(useridentity.accountid AS bigint) as newid
FROM cloudtrail_logs
LIMIT 2;
```
此查詢會傳回：
```
+--------------+
| newid |
+--------------+
| 112233445566 |
+--------------+
| 998877665544 |
+--------------+
```
若要查詢值陣列，請發出此查詢：
```
WITH dataset AS (
SELECT ARRAY[
CAST(ROW('Bob', 38) AS ROW(name VARCHAR, age INTEGER)),
CAST(ROW('Alice', 35) AS ROW(name VARCHAR, age INTEGER)),
CAST(ROW('Jane', 27) AS ROW(name VARCHAR, age INTEGER))
] AS users
)
SELECT * FROM dataset
```
它會傳回此結果：
```
+-----------------------------------------------------------------+
| users |
+-----------------------------------------------------------------+
| [{NAME=Bob, AGE=38}, {NAME=Alice, AGE=35}, {NAME=Jane, AGE=27}] |
+-----------------------------------------------------------------+
```
# 篩選含有巢狀值的陣列
大型陣列通常包含巢狀結構，您必須能夠篩選或搜尋其中的值。
若要為包含巢狀 `BOOLEAN` 值的值陣列定義資料集，請發出此查詢：
```
WITH dataset AS (
SELECT
CAST(
ROW('aws.amazon.com', ROW(true)) AS ROW(hostname VARCHAR, flaggedActivity ROW(isNew BOOLEAN))
) AS sites
)
SELECT * FROM dataset
```
它會傳回此結果：
```
+----------------------------------------------------------+
| sites |
+----------------------------------------------------------+
| {HOSTNAME=aws.amazon.com, FLAGGEDACTIVITY={ISNEW=true}} |
+----------------------------------------------------------+
```
接著，若要篩選並存取該元素的 `BOOLEAN` 值，請繼續使用點 `.` 標記法。
```
WITH dataset AS (
SELECT
CAST(
ROW('aws.amazon.com', ROW(true)) AS ROW(hostname VARCHAR, flaggedActivity ROW(isNew BOOLEAN))
) AS sites
)
SELECT sites.hostname, sites.flaggedactivity.isnew
FROM dataset
```
此查詢會選取巢狀欄位，並傳回此結果：
```
+------------------------+
| hostname | isnew |
+------------------------+
| aws.amazon.com | true |
+------------------------+
```
# 使用 `UNNEST` 篩選陣列
若要依其中一個子元素來篩選含有巢狀結構的陣列，請以 `UNNEST` 運算子發出查詢。如需 `UNNEST` 的詳細資訊，請參閱[展開巢狀陣列](flattening-arrays.md)。
例如，此查詢會在資料集尋找網站的主機名稱。
```
WITH dataset AS (
SELECT ARRAY[
CAST(
ROW('aws.amazon.com', ROW(true)) AS ROW(hostname VARCHAR, flaggedActivity ROW(isNew BOOLEAN))
),
CAST(
ROW('news.cnn.com', ROW(false)) AS ROW(hostname VARCHAR, flaggedActivity ROW(isNew BOOLEAN))
),
CAST(
ROW('netflix.com', ROW(false)) AS ROW(hostname VARCHAR, flaggedActivity ROW(isNew BOOLEAN))
)
] as items
)
SELECT sites.hostname, sites.flaggedActivity.isNew
FROM dataset, UNNEST(items) t(sites)
WHERE sites.flaggedActivity.isNew = true
```
它會傳回：
```
+------------------------+
| hostname | isnew |
+------------------------+
| aws.amazon.com | true |
+------------------------+
```
# 使用 `regexp_like` 在陣列中尋找關鍵字
以下範例說明如何使用 [regexp\$1like](https://prestodb.io/docs/current/functions/regexp.html) 函數，在資料集搜尋陣列內某個元素內的關鍵字。它接受規則表達式模式或以管線 (\$1) 分隔的詞彙清單作為輸入來評估，評估模式，然後判斷指定的字串是否包含它。
規則表達式模式必須存在於字串內，但不一定要符合它。若要比對整個字串，請在模式開頭加上 ^，在尾端加上 &，以括住模式，例如 `'^pattern$'`。
假設有一個包含主機名稱的網站陣列，還有一個 `flaggedActivity` 元素。這個元素包含 `ARRAY`，其中包含幾個 `MAP` 元素，各列出不同的熱門關鍵字及熱門度計數。假設您想在此陣列中的 `MAP` 內尋找特定的關鍵字。
為了依特定關鍵字在此資料集搜尋網站，我們使用 `regexp_like` 而不是類似的 SQL `LIKE` 運算子，因為以 `regexp_like` 搜尋大量關鍵字更有效率。
**Example 範例 1：使用 `regexp_like`**
這個範例中的查詢使用 `regexp_like` 函數來搜尋詞彙 `'politics|bigdata'`，並於陣列內的值中找到：
```
WITH dataset AS (
SELECT ARRAY[
CAST(
ROW('aws.amazon.com', ROW(ARRAY[
MAP(ARRAY['term', 'count'], ARRAY['bigdata', '10']),
MAP(ARRAY['term', 'count'], ARRAY['serverless', '50']),
MAP(ARRAY['term', 'count'], ARRAY['analytics', '82']),
MAP(ARRAY['term', 'count'], ARRAY['iot', '74'])
])
) AS ROW(hostname VARCHAR, flaggedActivity ROW(flags ARRAY(MAP(VARCHAR, VARCHAR)) ))
),
CAST(
ROW('news.cnn.com', ROW(ARRAY[
MAP(ARRAY['term', 'count'], ARRAY['politics', '241']),
MAP(ARRAY['term', 'count'], ARRAY['technology', '211']),
MAP(ARRAY['term', 'count'], ARRAY['serverless', '25']),
MAP(ARRAY['term', 'count'], ARRAY['iot', '170'])
])
) AS ROW(hostname VARCHAR, flaggedActivity ROW(flags ARRAY(MAP(VARCHAR, VARCHAR)) ))
),
CAST(
ROW('netflix.com', ROW(ARRAY[
MAP(ARRAY['term', 'count'], ARRAY['cartoons', '1020']),
MAP(ARRAY['term', 'count'], ARRAY['house of cards', '112042']),
MAP(ARRAY['term', 'count'], ARRAY['orange is the new black', '342']),
MAP(ARRAY['term', 'count'], ARRAY['iot', '4'])
])
) AS ROW(hostname VARCHAR, flaggedActivity ROW(flags ARRAY(MAP(VARCHAR, VARCHAR)) ))
)
] AS items
),
sites AS (
SELECT sites.hostname, sites.flaggedactivity
FROM dataset, UNNEST(items) t(sites)
)
SELECT hostname
FROM sites, UNNEST(sites.flaggedActivity.flags) t(flags)
WHERE regexp_like(flags['term'], 'politics|bigdata')
GROUP BY (hostname)
```
此查詢會傳回兩個網站：
```
+----------------+
| hostname |
+----------------+
| aws.amazon.com |
+----------------+
| news.cnn.com |
+----------------+
```
**Example 範例 2：使用 `regexp_like`**
下列範例中的查詢對於 `regexp_like` 函數中符合搜尋詞彙的網站，合計其熱門度總分，然後依最高到最低排序。
```
WITH dataset AS (
SELECT ARRAY[
CAST(
ROW('aws.amazon.com', ROW(ARRAY[
MAP(ARRAY['term', 'count'], ARRAY['bigdata', '10']),
MAP(ARRAY['term', 'count'], ARRAY['serverless', '50']),
MAP(ARRAY['term', 'count'], ARRAY['analytics', '82']),
MAP(ARRAY['term', 'count'], ARRAY['iot', '74'])
])
) AS ROW(hostname VARCHAR, flaggedActivity ROW(flags ARRAY(MAP(VARCHAR, VARCHAR)) ))
),
CAST(
ROW('news.cnn.com', ROW(ARRAY[
MAP(ARRAY['term', 'count'], ARRAY['politics', '241']),
MAP(ARRAY['term', 'count'], ARRAY['technology', '211']),
MAP(ARRAY['term', 'count'], ARRAY['serverless', '25']),
MAP(ARRAY['term', 'count'], ARRAY['iot', '170'])
])
) AS ROW(hostname VARCHAR, flaggedActivity ROW(flags ARRAY(MAP(VARCHAR, VARCHAR)) ))
),
CAST(
ROW('netflix.com', ROW(ARRAY[
MAP(ARRAY['term', 'count'], ARRAY['cartoons', '1020']),
MAP(ARRAY['term', 'count'], ARRAY['house of cards', '112042']),
MAP(ARRAY['term', 'count'], ARRAY['orange is the new black', '342']),
MAP(ARRAY['term', 'count'], ARRAY['iot', '4'])
])
) AS ROW(hostname VARCHAR, flaggedActivity ROW(flags ARRAY(MAP(VARCHAR, VARCHAR)) ))
)
] AS items
),
sites AS (
SELECT sites.hostname, sites.flaggedactivity
FROM dataset, UNNEST(items) t(sites)
)
SELECT hostname, array_agg(flags['term']) AS terms, SUM(CAST(flags['count'] AS INTEGER)) AS total
FROM sites, UNNEST(sites.flaggedActivity.flags) t(flags)
WHERE regexp_like(flags['term'], 'politics|bigdata')
GROUP BY (hostname)
ORDER BY total DESC
```
此查詢會傳回兩個網站：
```
+------------------------------------+
| hostname | terms | total |
+----------------+-------------------+
| news.cnn.com | politics | 241 |
+----------------+-------------------+
| aws.amazon.com | bigdata | 10 |
+----------------+-------------------+
```