从字符串中提取 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, json_path) 函数。它类似于 json_extract，但返回的是 varchar 字符串值而不是 JSON 编码的字符串。json_path 参数的值必须是标量（布尔值、数字或字符串）。

注意

请勿对数组、映射或结构使用 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     |
+--------------+