トップ > BigQuery > JSONの配列がある集合の要素を含んでいるかをSQLで判定する

JSONの配列がある集合の要素を含んでいるかをSQLで判定する

BigQuery SQL

前提

SQLを書いているとき、元データが[2,3,5,7]のようなJSONで入ってくることがある。データがJSONで入ってくると色々考慮しないといけないことが増えるので、あんまりぽんぽん出てきて欲しくはない。が、まあ現実には相手をしないといけない場合もある。世界は厳しい...。

元データが[2,3,5,7]であったとき、5または10(この集合の大きさは可変)を含むかといった判定をしないといけない場面に遭遇したが、ぱっとSQLが書けなかったので自分用のメモ書きです。

最終結果

結論としてはこういうクエリを書けばよさそう。

SELECT
  ARRAY_LENGTH(ARRAY(SELECT status FROM UNNEST(JSON_QUERY_ARRAY("[2,3,5,7]", '$')) AS status WHERE status IN ("5", "10"))) != 0 AS a,
  ARRAY_LENGTH(ARRAY(SELECT status FROM UNNEST(JSON_QUERY_ARRAY("[2,3,5,7]", '$')) AS status WHERE status IN ("1", "4", "6"))) != 0 AS b,

解説

JSON_QUERY_ARRAY関数

BigQueryでJSONをparseする関数は色々ある。

色々あるがゆえにこれまで書いたSQLに対してgit grepして引っかかったJSON_EXTRACT_ARRAYを使ってしまうこともあるが、JSON関数は最近色々整備されたようだ。推奨のものと「レガシー JSON 抽出関数」と呼ばれるものがある。JSON_EXTRACT_ARRAYはレガシーのものなので、JSON_QUERY_ARRAYを使うことにした。CASTが必要であれば適当にCASTするとよい(そのままだと文字列として数値が入ってくることに注意)。

BigQueryでのjsonデータの取り回しについてはゆずたそさんのエントリにまとまっているので、参考にさせてもらうことが多い。

UNNESTでほどく

ここはそのまま。配列になっているものを複数行に展開する。

考え方としては「ある集合の要素を含んでいるかを後段で判定したい」というのがまずあって、それを行なうためにはWHERE status IN (...)のような書き方をできるとよい。この書き方をするには配列だと都合が悪いため、UNNESTで複数行に展開しよう、という思考になる。

ちなみに、最初は「配列同士の積集合を取ればよい」と思ったんだけど、配列として扱おうとすると「UDFを書くほうがよい...」と思うくらいにはまどろっこしいことになってしまう。

さらに脱線するが「dbtでtemporary UDF書くのどうすればいいんだ...?」と調べていたところ、set_sql_headerを使うとmulti-line SQL statementが書けるようになるので、temporary UDFが書けるということが分かった。脱線おわり。

ARRAY関数を使って、1行に戻す

「JSONの配列がある集合の要素を含んでいるものを抽出する」というのはこれまでのステップでできたが、結果としてはUNNEST関係で複数行になってしまっている。最終的な結果は一行に戻したいため、ARRAY関数で1行の戻して、あとはARRAY_LENGTHで両方に出てきている要素があったかを判定する、という形。