woshidan's loose leaf

ぼんやり勉強しています

配列の中のハッシュの要素で検索したい

cross joinUNNEST を利用する。

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}

のように絞り込むことができる。

参考