BigqueryでARRAY型にはいった文字列それぞれが含まれる個数をまとめて集計したい。

Question

実現したいこと

['りんご',’ゴリラ’]などのようにArray型に入ったもののそれぞれが、とある列のテキスト情報にいくつ含まれるのか計算したのですが、1個1個ループ処理をするしか思いつかず配列の要素が多ければ多いほど時間がかかってしまいます。これをループではない処理で終わらすことはできないでしょうか？

set list = [
  'ロマンス','ジェニファー','ボブ','ミランダ','小説','会話','ねとげ','青春','プライベート タイムズ']

CREATE TEMP TABLE Example
(
  timestamp date,
  kw STRING,
  cnt INT64
);

SET list_num = array_length(list);

SET i = 0;
WHILE i < list_num DO
  insert into Example (
    SELECT timestamp,list[OFFSET(i)] kw, count(kw) cnt FROM `xxxxx` where regexp_contains(lower(text),lower(list[OFFSET(i)])) group by 1,2 order by 1 asc
  );
  SET i = i + 1;
END WHILE;

Answer 1

unnest演算子を使用して配列を表に変換したものとすれば、表同士の結合などで対処でき、ループ処理は不要になります。
配列内の要素をテーブル内の行に変換する