概要
- Amazon Athenaのクエリーで
json_extract関数を利用する場面 - ソースとなる情報はJSONのためjson_extract()関数を使用して目的の値を取得しようと考えました
- 一部JSONのキー値に
.（ピリオド）を含むケースがありました - 目的のキーはJSONPathで指示しますが、キー値をそのまま指示しても期待通りの動作をさせられず困りました
- その回避方法を記録します。ただし、条件付きであり、ベストな解決方法でないかもしれません
json_extract 関数について
書式はおよそ下記のようになります
json_extract(<入力JSON文字列>, <取得するJSONPath文字列>)
正確にはドキュメント 1 参照なのですが、JSONPath-like expression と記載があり、JSONPath風の記述、ということかと思います。
実施した内容
具体的に状況を示します
想定するソースjsonは下記のようにキー値に . を含みます
キー値 key.name を指定し、your-name が取得できることが期待する動作です
ソースとなるjson
{"key.name": "your-name"}
なにも考えずAthenaで実行したqueryと結果
SELECT json_extract('{"key.name": "your-name"}','$.key.name') AS key_name
key_name
------
null
既に記述した通り、 $.key.name は、下記のような構造の場合のPathを示しますので、指示が間違っています
{"key": {"name": "your-name"}}
念の為確認してみました。
SELECT json_extract('{"key": {"name": "your-name"}}','$.key.name') AS key_name
key_name
------
"your-name"
JSONPathを少し調べて試したqueryと結果
そもそも、JSONPath2について良く知らなかったので少し検索したところ、$.['key.name'] のような記述が適しているようです
SELECT json_extract('{"key.name": "your-name"}','$.[''key.name'']') AS key_name
INVALID_FUNCTION_ARGUMENT: Invalid JSON path: '$.['key.name']'
エラーになってしまいました。JSONPathとして正しく解釈されないようです。
Athenaで利用しているJSONPath（のサブセット？）では実装されていないということかもしれません。
暫定対応（一応の解決）
入力JSON文字列のキーを書き換えてしまう方法です。
SELECT json_extract(
replace('{"key.name": "your-name"}', '"key.name"', '"key_name"') /* "key.name" -> "key_name" と置換 */
,'$.key_name') AS key_name /* JSONPathも key_nameとする */
key_name
------
"your-name"
同じキー値や、値部分にマッチしてしまった場合はすべて書き換えてしまうことになりますので、危険です。
入力値の仕様上問題なければ、という条件付きになりますが、一応の解決となりそうです。