【データ基盤構築/BigQuery】GAから取り込んだJSON形式の文字列型データを、並列に展開してBigQueryのテーブルに収める

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

今回の内容は、GoogleAnalyticsのデータをBigQueryにエクスポートした際などに役立つ。
（GoogleAnalyticsのデータはネストされた状態でBigQueryにデータがエクスポートされることがあるため。）
※参考：[Google AnalyticsのデータをCloud Storageにエクスポートする](https://qiita.com/ysmmb12/items/9b21ea428bea14e7d437)


### 使用テーブル
このように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型の型式に変換する。
```SQL
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型のデータを並列に展開する
```SQL
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で複数の配列をフラット化する](https://labs.septeni.co.jp/entry/2018/11/06/120000)



上記の手順で進めることで、下記のように出力され、
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公式ドキュメント)](https://cloud.google.com/bigquery/docs/reference/standard-sql/mathematical_functions?hl=ja#greatest)

## ARRAY_LENGTH関数
```
ARRAY_LENGTH(配列）
```
配列のサイズを返す。
参考：[ARRAY_LENGTH(BigQuery公式ドキュメント)](https://cloud.google.com/bigquery/docs/reference/standard-sql/array_functions?hl=ja#array_length)

## GENERATE_ARRAY関数
```
GENERATE_ARRAY(配列の開始点, 配列の終了点[, 間隔])
```
値の配列を返す。
※間隔のデフォルトは「1」
参考：[GENERATE_ARRAY(BigQuery公式ドキュメント)](https://cloud.google.com/bigquery/docs/reference/standard-sql/array_functions?hl=ja#generate_array)

## JSON_VALUE関数
```
JSON_VALUE(json形式の文字列[, json_path])
```
スカラーを抽出して文字列として返す。スカラー値は、文字列、数値、ブール値を表すことができる。
参考：[JSON_VALUE(BigQuery公式ドキュメント)](https://cloud.google.com/bigquery/docs/reference/standard-sql/json_functions?hl=ja#json_value)

## JSON_VALUE_ARRAY関数
```
JSON_VALUE_ARRAY(json形式の文字列[, json_path])
```
スカラー値の配列を抽出し、文字列形式のスカラー値を返す。スカラー値は、文字列、数値、ブール値を表すことができる。
参考：[JSON_VALUE_ARRAY(BigQuery公式ドキュメント)](https://cloud.google.com/bigquery/docs/reference/standard-sql/json_functions?hl=ja#json_value_array)

## JSON Pathについて
簡単に言うと、JSONから欲しいkey, value, lengthなどを取得することができる構文。
JSONPath形式を使用すると、JSON形式の文字列から取得する数値を識別できる。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/883573/df4f79f4-bb8b-e58c-f683-b4cfd281582f.png)

参考：[JSONPath形式(BigQuery公式ドキュメント)](https://cloud.google.com/bigquery/docs/reference/standard-sql/json_functions?hl=ja#JSONPath_format)