Athenaのunnestでjson配列を分解したら便利だった
はい。タイトル通りなのですが、Athenaでjsonの配列を扱うときに
unnestで分解したら便利だったので、備忘録として残しておきたいと思います。
まあ他にも同様の記事はあるので、そちらも参考にしてみてください。
以下のようなjson形式のデータが、S3などに入っているとします。
(実際のデータじゃないので適当なサンプルですが）
{"name": "test1", "ary": [{"key": "key1", "value", "value1"}, {"key": "key2", "value", "value2"}, {"key": "key3", "value", "value3"}]
一つのjsonの中に「ary」のような配列形式の要素を持っている場合、unnestを使うと
これを以下のように分割して表示することができます。
| name | key | value |
|---|---|---|
| test1 | key1 | value1 |
| test1 | key2 | value2 |
| test1 | key3 | value3 |
3つの要素がある「ary」を分解して、3行で表示しています。
この分解にunnestを使うのですが、以下のようなクエリとなります。
（元データを参照するテーブル名は「table1」とします）
SELECT name, json_extract(ary_unnest, '$.key') AS key, --3. json_extract(ary_unnest, '$.value') AS value --3. FROM ( SELECT name, CAST(ary AS array(json)) AS ary_json -- 1. FROM table1, unnest(ary_json) AS t(ary_unnest) -- 2. )
クエリにはポイントとなる所に番号をコメントしておきました。それぞれの簡単な説明を書くと
1.「ary」をjson配列の型にcastする
2. castしたjson配列をunnsetで分割して「ary_unnest」と別名をつける
3. 分割した「ary_unnest」から、配列内の要素の「key」「value」の値を抽出する
となります。
実際に書いたクエリを見ながら書いているわけではないので、そのままでは動かないかもしれませんが
ポイントとしては大体こんな感じです。
詳細などは（私も参考にした）以下の公式の記事を参考にしてください。
