RedshiftでJSON配列をUNNESTしてみた
この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
はじめに
仙台オフィス所属、データアナリティクス事業本部のおざわです。
昨年12月に仙台オフィスが開設してから、徐々に仙台でイベントが走り始めています。6月14日(金)にはビール片手にわいわい交流するゆるめのイベントがあります。近くにお住まいでご興味がある方はぜひご参加いただければと思います。
また7月4日(木)にはDevelopersIO仙台、TohokuTechが開催されます！こちらもぜひご参加ください。
やりたいこと
今回やりたかったのは、Redshiftに格納されている下のようなJSON配列から値を抜き出して他のテーブルと結合するというものです。
{
"name": "my_lovely_json",
"target_json_array": [
{"user_id": 1, "json_col1":"foo1", "json_col2": "bar1", "json_col3": "fuga1"},
{"user_id": 2, "json_col1":"foo2", "json_col2": "bar2", "json_col3": "fuga2"},
{"user_id": 3, "json_col1":"foo3", "json_col2": "bar3", "json_col3": "fuga3"},
]
}
target_json_arrayからjson_col1とjson_col2の値を抜き出して、別テーブルと結合してこんな形↓にしたい
| user_id | username | col1 | col2 |
|---|---|---|---|
| 1 | a | foo1 | bar1 |
| 2 | b | foo2 | bar2 |
| 3 | c | foo3 | bar3 |
方法
さっそくやり方ですが、こちらのブログ記事に書かれているとおりでした。
FROM句でUNNEST
ポイントとしては4行目の文字列にcastしてからjson_parseしている部分と16行目のFROM句にJSON配列を指定するところです。super型のカラムに格納されていても、そのままでは扱えないデータもあったので、一旦文字列にcastしてからjson_parseするのがよいかと思います。
with my_json as (
select
user_id,
json_parse(my_json_col::varchar) as my_json_col
from
"my_schema"."my_json_table_text"
),
my_table as (
select
user_id,
-- ダブルクォートが残るので置換
replace(ary.json_col1::varchar, '"', '') as json_col1,
replace(ary.json_col2::varchar, '"', '') as json_col2
from
my_json as js,
js.my_json_col.target_json_array as ary
),
joined_users as (
select
t.user_id,
u.username,
t.json_col1 as col1,
t.json_col2 as col2
from
my_table as t
left join "my_schema"."my_users" as u on t.user_id = u.user_id
),
final as (
select
user_id,
username,
col1,
col2
from
joined_users
)
select *
from final;
ちなみにインデックスが必要な場合は以下のように at 〜で取得できます。
my_table as (
select
i,
user_id,
-- ダブルクォートが残るので置換
replace(ary.json_col1::varchar, '"', '') as json_col1,
replace(ary.json_col2::varchar, '"', '') as json_col2
from
my_json as js,
js.my_json_col.target_json_array as ary at i
)
この内容で記事としては終了なのですが、他の方法でも一応同じことができましたのでメモを残したいと思います。
JSON関数でがんばる
Stack Overflowや比較的古めの記事で紹介されているのは下記のような手法でした。恐らくsuper型が登場する以前に文字列として格納されたJSONを扱うために先人たちが残した方法と思います。
with recursive seq(i) as (
select 0 as i
union all
select i + 1 as i from seq
where i < 10
),
my_json as (
select
user_id,
json_extract_path_text(my_json_col, 'target_json_array') as json_array
from
"my_schema"."my_json_table_text"
),
my_json_with_size AS (
select
user_id,
json_array,
json_array_length(json_array) as array_count
from
my_json
),
-- seqと結合して項目を取得
my_table as (
select
seq.i,
user_id,
json_extract_path_text(
json_extract_array_element_text(json_array, seq.i), 'json_col1'
) as json_col1,
json_extract_path_text(
json_extract_array_element_text(json_array, seq.i), 'json_col2'
) as json_col2
from
my_json_with_size
cross join
seq
where seq.i < array_count
),
joined_users as (
select
t.user_id,
u.username,
t.json_col1 as col1,
t.json_col2 as col2
from
my_table as t
left join
"my_schema"."my_users" as u on t.user_id = u.user_id
),
final as (
select
user_id,
username,
col1,
col2
from
joined_users
)
select *
from final;
今回は配列の要素が一定数に収まることがわかっていたので、最初に必要なだけ連番を生成しています。json_extract_array_element_text関数でインデックスを指定してオブジェクトを抜き出してから、json_extract_path_text関数で値を取得しています。最初の方法と比較すると余計なステップがある分わかりにくくなっています。
終わりに
以上、RedshiftでJSON配列をUNNESTする方法２つでした。
