# 查询具有复杂类型和嵌套结构的数组
<a name="rows-and-structs"></a>

您的源数据通常包含具有复杂数据类型和嵌套结构的数组。本部分中的示例显示如何使用 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`
<a name="creating-row"></a>

**注意**  
本部分中的示例使用 `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` 更改数组中的字段名称
<a name="changing-row-arrays-with-cast"></a>

要更改包含 `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`。

# 使用 `.` 表示法筛选数组
<a name="filtering-with-dot"></a>

在以下示例中，使用点 `.` 表示法从 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}] |
+-----------------------------------------------------------------+
```

# 筛选具有嵌套值的数组
<a name="filtering-nested-with-dot"></a>

大型数组通常包含嵌套结构，您需要能够对其中的值进行筛选或搜索。

要为包含嵌套 `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` 筛选数组
<a name="filtering-with-unnest"></a>

要按照一个子元素筛选包含嵌套结构的数组，请发出具有 `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` 在数组中查找关键字
<a name="filtering-with-regexp"></a>

以下示例说明如何使用 [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    |
+----------------+-------------------+
```