[Amazon Athena]JSONのデータ抽出とORDER BYを組み合わせて利用する方法

json_extractとORDER BYを組み合わせるとエラーになる

JSONから特定のデータを抽出する為のjson_extractと、ORDER BYを組み合わせてAmazon Athenaでクエリをかけると下記エラーが発生します。

SYNTAX_ERROR: Type json is not orderable, and therefore cannot be used in ORDER BY

翻訳

タイプ json は順序付けできないため、ORDER BY では使用できません

JSONのデータ抽出とORDER BYを組み合わせて利用したかった為、やり方について記載します。
(正確にはQuickSightの”テーブル”タイプを利用すると自動的にORDER BY入るのですが、そことJSONの文字列抽出を組み合わせて利用したかったという背景になります)
※本記事は下記のQuickSightで表示させようと検証した時の内容です。SQLやテーブルは下記記事がベースとなっています。

いきなり結論

• json_extractで返す値が単純な文字列ではなくJSONでエンコードされた文字列を返している
• ORDER BYはJSONでエンコードされた文字列に対応していない
• ORDER BYと組み合わせて使う時はjson_extractではなくjson_extract_scalarを使う

json_extract

json_extractはJSONの文字列からname等の特定のデータを抽出する関数です。
例えば、下記JSONの中に下記情報があり、Queueの"Name"の情報だけ取り出したいとします。

    "Queue": {
        "ARN": "arn:aws:connect:ap-northeast-1:xxxxxxxx:instance/xxxxxxxxxxxxxxx/queue/xxxxxxxxxxxxxxx",
        "DequeueTimestamp": null,
        "Duration": 0,
        "EnqueueTimestamp": null,
        "Name": "suzaki"
    },

Athenaでクエリをかける際にjson_extract(Queue, '$.name') AS Queueと入力します。
すると、Queueの中の”Name”情報を取り出して、Queue："suzaki"といった結果を表示します。
実際にjson_extractを利用する場合としない場合で比較してみます。

json_extractで特定のデータ抽出をしない場合

json_extractで特定のデータ抽出した場合

結果をみると、後者のjson_extractで実施したほうが、欲しい情報(今回だと"Suzaki")のみをすぐに手に入れられることが分かります。

ORDER BY

ORDER BYは、SQLのSELECT文でカラムを基準に並べ替える機能です。
昇順、または降順に並び替えます。
今回のケースでは、意図してORDER BYを入れたいわけではなかったのですが、 QuickSightの"テーブル”を利用すると、自動的にORDER BYとGROUP BYが入る仕様でした。
(QuickSightのORDER BY,GROUP BYの追記について下記を参照ください)

json_extractとQuickSightの”テーブル”(ORDER BY)を両方使いたい。。ただ、両方使うとエラーが起きてしまう。。

タイプ json は順序付けできないため、ORDER BY では使用できません

そこで、json_extract_scalarを利用することで、JSONの特定のデータを抽出をしながらQuickSightの”テーブル”(ORDER BY)を利用することができました。

json_extract_scalar

今回のエラーは、json_extractで返す値が単純な文字列ではなくJSONでエンコードされた文字列を返しており、ORDER BYはJSONでエンコードされた文字列に対応していなかったのが原因です。
そこでjson_extract_scalarを利用しました。
json_extract_scalarはスカラー値(ブール値、数値、文字列) を返す関数です。
json_extractと違いJSONでエンコードされておらず、一つの数値として出力される為、ORDER BYでもエラーが起きず実行することができました。

ちなみに、出力結果内容はjson_extractとjson_extract_scalarで若干異なります。
json_extractの結果は””(ダブルクォーテーション)で出力されます。
json_extract_scalarはダブルクォーテーションなしで、シンプルに文字列のみを返します。

比較

json_extractで行うとエラーのままです。

json_extract_scalarを利用すると、エラーが発生せず実行することができました！

注意点

ドキュメントに配列やマップ、構造体について、json_extract_scalarは利用しないでくださいと記載がありました。
これらを利用する場合は他の関数を利用するようにしましょう。

配列、マップ、または構造体に対して json_extract_scalar 関数は使用しないでください。

最後に

JSONの文字列抽出とORDER BYを組み合わせて実行する方法について書きました。
もし同じ悩みを持っている方がいましたら、少しでも役に立てれば幸いです。

ではまた！コンサルティング部の洲崎でした。

参考