今回の課題

BigQueryに、JSON形式の文字列でデータが格納されているテーブルがあり、
それらを一行ずつ並列に展開してテーブルに収めたかった。

今回の内容は、GoogleAnalyticsのデータをBigQueryにエクスポートした際などに役立つ。
（GoogleAnalyticsのデータはネストされた状態でBigQueryにデータがエクスポートされることがあるため。）
※参考：Google AnalyticsのデータをCloud Storageにエクスポートする

使用テーブル

このようにjson形式の文字列でデータが格納されているテーブルがあるので、
それらを並列に展開していく。

content_apps_json
{"app_id":[1,2,3],"content_id":100,"tag_id":[10,11,12]}
{"app_id":[1,2,3],"content_id":120},"tag_id":[10,11,12]}
{"app_id":[4,5],"content_id":110,"tag_id":[15,16]}
{"app_id":[4,5],"content_id":130,"tag_id":[15,16]}

進めた手順

1)JSON形式の文字列をARRAY型の型式に変換する。

select
    json_value(content_apps_json, '$.content_id') as content_id --jsonから値を取得
    , json_value_array(content_apps_json, '$.app_id') as app_id --jsonから配列を取得
    , json_value_array(content_apps_json, '$.tag_id') as tag_id --jsonから配列を取得
from
    `テーブル名`

上記のクエリの結果、下記の型式（ARRAY型）でデータが抽出される。

content_id	app_id	tag_id
100	1	10
	2	11
	3	12
120	1	10
	2	11
	3	12
110	4	15
	5	16
130	4	15
	5	16

2)ARRAY型のデータを並列に展開する

with content_apps_json as (
    select
        json_value(content_apps_json, '$.content_id') as content_id --jsonから値を取得
        , json_value_array(content_apps_json, '$.app_id') as app_id --jsonから配列を取得
        , json_value_array(content_apps_json, '$.tag_id') as tag_id --jsonから配列を取得
    from
        `テーブル名`
)
/* content_apps_jsonから抽出したデータの配列部分を並列化 */
select
    cast(content_id as int64) as content_id --json形式の文字列から文字列形式で抽出しているのでINT64に変換しておく。
    , cast(app_id[safe_offset(index)] as int64) as app_id
    , cast(tag_id[safe_offset(index)] as int64) as tag_id
from
    content_apps_json
    , unnest(generate_array(0, greatest(array_length(app_id), array_length(tag_id)) - 1)) as index --念の為に配列のLENGTHの最大値を取る
;

■上記のクエリのポイント

WITH句内の、JSON形式の文字列から値を取得するjson_valueやjson_value_arrayは戻り値がSTRINGなので、CAST関数を使用してint64に変換している。
FROM句のGENERATE_ARRAY関数で、0 ～ [app_idまたはtag_idの配列の値の数が多い方の値の数-1]で配列を作ってunnestしている。
└複数配列が存在する（app_idとtag_id）ので、並列化した時にどちらかの値が欠損しないように、app_idとtag_idの２つの配列の値の数が大きい方でunnestするようにしている。
(例えば、app_idしか配列が無い場合は、unnest(app_id) as app_idだけの記載でも問題無い）
└GREATEST関数の中で、[-1]しているのは、SELECT句のsafe_offsetが0からカウントするのでそれを考慮している。

参考：BigQueryで複数の配列をフラット化する

上記の手順で進めることで、下記のように出力され、
JSON形式の文字列を並列に展開することができた。

content_id	app_id	tag_id
100	1	10
100	2	11
100	3	12
120	1	10
120	2	11
120	3	12
110	4	15
110	5	16
130	4	15
130	5	16

おまけ：今回使用した関数など

GREATEST関数

GREATEST(X1. ..., XN)

X1, ..., XNの最大の値を返す。
参考：GREATEST(BigQuery公式ドキュメント)

ARRAY_LENGTH関数

ARRAY_LENGTH(配列）

配列のサイズを返す。
参考：ARRAY_LENGTH(BigQuery公式ドキュメント)

GENERATE_ARRAY関数

GENERATE_ARRAY(配列の開始点, 配列の終了点[, 間隔])

値の配列を返す。
※間隔のデフォルトは「1」
参考：GENERATE_ARRAY(BigQuery公式ドキュメント)

JSON_VALUE関数

JSON_VALUE(json形式の文字列[, json_path])

スカラーを抽出して文字列として返す。スカラー値は、文字列、数値、ブール値を表すことができる。
参考：JSON_VALUE(BigQuery公式ドキュメント)

JSON_VALUE_ARRAY関数

JSON_VALUE_ARRAY(json形式の文字列[, json_path])

スカラー値の配列を抽出し、文字列形式のスカラー値を返す。スカラー値は、文字列、数値、ブール値を表すことができる。
参考：JSON_VALUE_ARRAY(BigQuery公式ドキュメント)

JSON Pathについて

簡単に言うと、JSONから欲しいkey, value, lengthなどを取得することができる構文。
JSONPath形式を使用すると、JSON形式の文字列から取得する数値を識別できる。

参考：JSONPath形式(BigQuery公式ドキュメント)