配列の中のハッシュの要素で検索したい
cross join
と UNNEST
を利用する。
UNNEST はSQLの中では UNNEST 関数は、指定された配列の各エレメントにつき 1 行が含まれる結果表を戻す
ものだそうです。
前に書いた Athenaの記事 で少し調べたのですが、覚えられてなかったので上記を踏まえてもう一回。
{ "animals": [ {"name": "ぽち", "kind": "dog"}, {"name": "たま", "kind": "cat"} ] }
CREATE EXTERNAL TABLE IF NOT EXISTS animal_logs ( animals array<struct<name:string, kind:string>> ) ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe' LOCATION 's3://exmaple-woshidan-test/athena_nest_sample';
SELECT * FROM mydatabase."animal_logs" limit 10;
animals | |
---|---|
1 | [{name=ぽち, kind=dog}, {name=たま, kind=cat}] |
SELECT * FROM mydatabase."animal_logs" cross join UNNEST (animals) AS t (animal);
animals | animal | |
---|---|---|
1 | [{name=ぽち, kind=dog}, {name=たま, kind=cat}] | {name=ぽち, kind=dog} |
2 | [{name=ぽち, kind=dog}, {name=たま, kind=cat}] | {name=たま, kind=cat} |
animals | |
---|---|
1 | [{name=ぽち, kind=dog}, {name=たま, kind=cat}] |
の表と UNNSET
で作成される
animal | |
---|---|
1 | {name=ぽち, kind=dog} |
2 | {name=たま, kind=cat} |
の表を掛け合わせているので、元のテーブルの一行 x UNNESTしたanimalsの中の行ある結果の表が返ってくる。
これで、 UNNEST
した列の配列の各要素へアクセス可能になるので、これを利用して、
SELECT * FROM mydatabase."animal_logs" cross join UNNEST (animals) AS t (animal) WHERE animal.kind='dog';
animals | animal | |
---|---|---|
1 | [{name=ぽち, kind=dog}, {name=たま, kind=cat}] | {name=ぽち, kind=dog} |
のように絞り込むことができる。