BigQueryへExportしたFirestoreの情報をDataPortalで閲覧する際にハマったこと

この記事について

FirestoreのデータをBigQueryに流し
DataPortalで閲覧したい場合にハマったことについての記事です。

関連知識まわり
Export Collections to BigQuery
データポータルを使用して BigQuery データを可視化する
google/re2

ハマったこと

dataに含まれる指定のkeyに紐づくvalueを取得すること。

ExportされたFirestoreのデータがBigQueryでどのように保存されるか

CollectionからBigQueryへExportされるテーブルのスキーマは以下になります。
Firestore上のDocumentはdataにjson形式で型はStringで格納されています。

例としてBigQueryへ流す対象のCollectionに含まれるDocumentの内容は以下とします。

このDocument情報はBigQuery上ではdataFieldに保存されます。

{"defense": 250,"name": "スライム","power": 200}

powerだけを取得したい場合Queryを作る場合以下で済むんですが・・・

SELECT JSON_EXTRACT(data, '$.power') FROM table_name

DataPortal上でも同じことをしてみた・・・

Queryと同じようにDataPortal上でdataからpowerを抽出し新しくFieldを追加しようとするもJSON_EXTRACTはサポートされてない・・・

解決法

DataPortalでサポートされている関数の一つであるREGEXP_EXTRACTを使いdataから正規表現で指定Keyである"power"のvalueを取得する。

REGEXP_EXTRACTはBigQueryでもサポートされているのでQueryを書くと以下になります。

SELECT REGEXP_EXTRACT(data,'"power":+([^,]+)') FROM table_name

最後に

今回はハマったことに対してサポートしてる関数を使用し正規表現で指定のkeyに紐づくvalueを取得したんですがもっと簡単な方法あるぞ！とご存じの方いらっしゃいましたらぜひご共有ください