Amazon Athena
ユーザーガイド

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 | +---------------------+

このページの内容: