从字符串中提取 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 |
+--------------+