获取 JSON 数组的长度和大小 - Amazon Athena
AWS 文档中描述的 AWS 服务或功能可能因区域而异。要查看适用于中国区域的差异,请参阅中国的 AWS 服务入门

获取 JSON 数组的长度和大小

示例:json_array_length

要获取 JSON 编码数组的长度,请使用 json_array_length 函数。

WITH dataset AS ( SELECT * FROM (VALUES (JSON '{"name": "Bob Smith", "org": "legal", "projects": [{"name":"project1", "completed":false}]}'), (JSON '{"name": "Susan Smith", "org": "engineering", "projects": [{"name":"project2", "completed":true}, {"name":"project3", "completed":true}]}'), (JSON '{"name": "Jane Smith", "org": "finance", "projects": [{"name":"project2", "completed":true}]}') ) AS t (users) ) SELECT json_extract_scalar(users, '$.name') as name, json_array_length(json_extract(users, '$.projects')) as count FROM dataset ORDER BY count DESC

此查询返回以下结果:

+---------------------+ | name | count | +---------------------+ | Susan Smith | 2 | +---------------------+ | Bob Smith | 1 | +---------------------+ | Jane Smith | 1 | +---------------------+

示例:json_size

要获取 JSON 编码的数组或对象的大小,请使用 json_size 函数,并指定将 JSON 字符串和 JSONPath 表达式包含到该数组或对象中的列。

WITH dataset AS ( SELECT * FROM (VALUES (JSON '{"name": "Bob Smith", "org": "legal", "projects": [{"name":"project1", "completed":false}]}'), (JSON '{"name": "Susan Smith", "org": "engineering", "projects": [{"name":"project2", "completed":true},{"name":"project3", "completed":true}]}'), (JSON '{"name": "Jane Smith", "org": "finance", "projects": [{"name":"project2", "completed":true}]}') ) AS t (users) ) SELECT json_extract_scalar(users, '$.name') as name, json_size(users, '$.projects') as count FROM dataset ORDER BY count DESC

此查询返回以下结果:

+---------------------+ | name | count | +---------------------+ | Susan Smith | 2 | +---------------------+ | Bob Smith | 1 | +---------------------+ | Jane Smith | 1 | +---------------------+