从字符串中提取 JSON 数据 - Amazon Athena

从字符串中提取 JSON 数据

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

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

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

示例:提取属性

要从 JSON 字符串中提取 nameprojects 属性,请使用 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 | +--------------+