# 查询数组
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 | 项目 |
| --- | --- |
| 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 文档中的 [字符串函数和运算符](https://trino.io/docs/current/functions/string.html)。
# 转换数组数据类型
要将数组中的数据转换为支持的数据类型，请使用 `CAST` 运算符，例如 `CAST(value AS type)`。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 函数
要将嵌套数组的元素展平为单个值数组，请使用 `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 |
+--------------------------------------+
```
从员工列表中，选择具有最高组合分数的员工。可以在 `FROM` 子句中使用 `UNNEST`，而无需前面的 `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`，则返回空字符串。
例如，在以下查询中，由于第二个数组为空值，因此查询不返回任何行。
```
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 文档的[聚合函数](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)
```
您可以使用 `filter` 表达式上的 `ARRAY` 函数来创建新数组，该数组是 *boolean\$1function* 为真的 *list\$1of\$1values* 中项目的子集。`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` 语句中，您可以使用 `reduce()` 而不是 `sum()` 和 `UNNEST` 来减少处理时间和数据传输，如以下示例所示。
```
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` 运算符并将其传递给两个数组。第一个数组包含列名称的值，例如“第一个”、“最后一个”和“年龄”。第二个数组包含每个列的值，例如“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` 值的数组中的字段名称，您可以对 `ROW` 声明执行 `CAST` 操作：
```
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) 分隔的搜索词列表）作为输入，计算此模式，并确定指定的字符串是否包含此模式。
此正则表达式模式需要包含在此字符串内，但并不一定要与此字符串匹配。要匹配整个字符串，请在模式开头使用 ^ 并在末尾使用 \$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 |
+----------------+-------------------+
```