从字符串中提取 JSON 数据 - Amazon Athena
示例：提取属性
Amazon Web Services 文档中描述的 Amazon Web Services 服务或功能可能因区域而异。要查看适用于中国区域的差异，请参阅 中国的 Amazon Web Services 服务入门 (PDF)。

从字符串中提取 JSON 数据

您可能拥有包含 JSON 编码字符串的源数据，您不一定要将其反序列化到 Athena 的表中。在这种情况下，您仍然可以使用 Presto 中提供的 JSON 函数对此数据运行 SQL 操作。

请考虑以下 JSON 字符串作为示例数据集。

{"name": "Susan Smith",
"org": "engineering",
"projects":
    [
     {"name":"project1", "completed":false},
     {"name":"project2", "completed":true}
    ]
}

示例：提取属性

要从 JSON 字符串中提取 name 和 projects 属性，请使用 json_extract 函数，如以下示例所示。json_extract 函数将采用包含 JSON 字符串的列，并使用类似 JSONPath 的表达式 (使用点 . 表示法) 搜索它。

注意

JSONPath 执行简单的树遍历。它使用 $ 符号来表示 JSON 文档的根，后跟一个句点和一个直接位于根下面的嵌套元素，例如 $.name。

WITH dataset AS (
  SELECT '{"name": "Susan Smith",
           "org": "engineering",
           "projects": [{"name":"project1", "completed":false},
           {"name":"project2", "completed":true}]}'
    AS myblob
)
SELECT
  json_extract(myblob, '$.name') AS name,
  json_extract(myblob, '$.projects') AS projects
FROM dataset

返回的值是一个 JSON 编码的字符串，而不是本机 Athena 数据类型。

+-----------------------------------------------------------------------------------------------+
| name           | projects                                                                     |
+-----------------------------------------------------------------------------------------------+
| "Susan Smith"  | [{"name":"project1","completed":false},{"name":"project2","completed":true}] |
+-----------------------------------------------------------------------------------------------+

要从 JSON 字符串中提取标量值，请使用 json_extract_scalar 函数。它类似于 json_extract，但仅返回标量值 (布尔值、数字或字符串)。

注意

请勿对数组、映射或结构使用 json_extract_scalar 函数。

WITH dataset AS (
  SELECT '{"name": "Susan Smith",
           "org": "engineering",
           "projects": [{"name":"project1", "completed":false},{"name":"project2", "completed":true}]}'
    AS myblob
)
SELECT
  json_extract_scalar(myblob, '$.name') AS name,
  json_extract_scalar(myblob, '$.projects') AS projects
FROM dataset

此查询返回：

+---------------------------+
| name           | projects |
+---------------------------+
| Susan Smith    |          |
+---------------------------+

要获取示例数组中的 projects 属性的第一个元素，请使用 json_array_get 函数并指定索引位置。

WITH dataset AS (
  SELECT '{"name": "Bob Smith",
           "org": "engineering",
           "projects": [{"name":"project1", "completed":false},{"name":"project2", "completed":true}]}'
    AS myblob
)
SELECT json_array_get(json_extract(myblob, '$.projects'), 0) AS item
FROM dataset

它返回 JSON 编码数组中指定索引位置处的值。

+---------------------------------------+
| item                                  |
+---------------------------------------+
| {"name":"project1","completed":false} |
+---------------------------------------+

要返回一个 Athena 字符串类型，请在 JSONPath 表达式中使用 [] 运算符，然后使用 json_extract_scalar 函数。有关 [] 的更多信息，请参阅 访问数组元素。

WITH dataset AS (
   SELECT '{"name": "Bob Smith",
             "org": "engineering",
             "projects": [{"name":"project1", "completed":false},{"name":"project2", "completed":true}]}'
     AS myblob
)
SELECT json_extract_scalar(myblob, '$.projects[0].name') AS project_name
FROM dataset

它将返回此结果：

+--------------+
| project_name |
+--------------+
| project1     |
+--------------+