文字列から JSON データを抽出する
Athena のテーブルにデシリアライズしたくない、JSON でエンコードされた文字列が含まれるデータソースがある場合があります。このような場合でも、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
(ドット .
表記を含む式など) を使用して JSON 文字列を検索します。
注記
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 |
+--------------+