BigQueryのJSON型をSTRUCT型に変換する

これは　[MIXI DEVELOPERS Advent Calendar 2022](https://qiita.com/advent-calendar/2022/mixi) 13日目です。

# 概要
BigQueryのJSON文字列/JSON型を扱う際、構造がよくわからないのでSTRUCT型などに変換したビューを作っておいたほうが便利なことが多い。そこで、JSONを整理してSTRUCT型に変換する手順を整理して紹介する。また、変換は機械的に対応できるので、クエリを自動生成するプログラムの作り方、ソースコード(TypeScript)及び動作確認用のWebアプリを紹介する。

# はじめに
## JSON型
BigQueryのJSON型がついにGA（一般提供）になりました。[【新機能】BigQueryでJSON型が使えます！（BigQuery subscriptionsも対応） - DevelopersIO](https://dev.classmethod.jp/articles/bq-json-ga-pubsub/) にもあるように、いつのまにかGAになっています。

今までBigQueryでJSONを扱う際は、JSONデータを文字列として持ち、それに対してJSONにアクセスできる関数を使いました。JSON型では、JSONを半構造化データそのものとして保持します。

JSONの文字列に対してアクセスする例。`col`はただの文字列です。
```sql
SELECT
  JSON_QUERY(col, '$.country'), -- "Canada" 
  JSON_VALUE(col, '$.country'), -- Canada
  JSON_QUERY_ARRAY(col, '$.members'), -- ["Alice", "Bob"]
  JSON_VALUE_ARRAY(col, '$.members') -- [Alice, Bob]
FROM UNNEST([
'{"country": "Canada", "members": ["Alice", "Bob"]}'
]) col
```

JSON型を使う例。`col`はJSON型です。
```sql
SELECT
  col.country, -- "Canada" JSON型
  STRING(col.country), -- Canada
  col.members, -- ["Alice", "Bob"] JSON型
  JSON_VALUE_ARRAY(col, '$.members') -- [Alice, Bob]
FROM UNNEST([
  JSON'{"country": "Canada", "members": ["Alice", "Bob"]}'
]) col
```

参照: [JSON関数 - BigQueryリファレンス](https://cloud.google.com/bigquery/docs/reference/standard-sql/json_functions?hl=ja)


また、このような半構造化データを扱うもう一つの方法として、STRUCT型やARRAY型を扱う方法があります。事前にデータの構造を定義します。同じ列では同じ構造のデータしか扱えません。
```sql
SELECT
  STRUCT<name STRING>("Alice"),
  STRUCT<members ARRAY<STRING>>(["Alice", "Bob"])
```

## JSON型の用途
実際にJSON型を使う際、毎回全く異なる構造のJSONを扱うことはまれで、多くの場合は数種類〜数百種類程度の構造でしょう。たとえば、よくあるログでは以下のようにtypeの値で区別されスキーマが確定します(OpenAPIでいうdiscriminator)。


```json
{"type": "user.create", "body": { "name": "太郎", "year": 2004, "parents": ["一郎", "花子"]}}
{"type": "user.create", "body": { "name": "二郎", "year": 2008, "parents": ["一郎", "花子"]}}
{"type": "item.sell", "body": { "item_id": 12345, "count": 30, "price": 12000, "created_ts": "2022-12-13T12:00:00+09:00"}}
```
STRUCT型では異なるスキーマを同じ列で扱うことができず、JSONを文字列で持つか、[EAV](https://qiita.com/skyc_lin/items/37365a36416d0dc42431)のようなアンチパターンに手を染めるかですが、その点でJSON型は大変便利です。

### 問題提起
さて、そのようなテーブルに対して分析クエリを投げる際、JSON型をそのまま使うのは不便です。このテーブルに対して、"item_sell"ログの"item_id"ごとの"price"の合計を集計してみましょう。
```sql
SELECT
  INT64(col.body.id), SUM(INT64(col.body.price))
FROM 
UNNEST([
  JSON'{"type": "user.create", "body": { "name": "太郎", "year": 2004, "parents": ["一郎", "花子"]}}',
  JSON'{"type": "user.create", "body": { "name": "二郎", "year": 2008, "parents": ["一郎", "花子"]}}',
  JSON'{"type": "item.sell", "body": { "item_id": 12345, "count": 30, "price": 12000, "created_ts": "2022-12-13T12:00:00+09:00"}}'
]) col
WHERE STRING(col.type) = 'item.sell'
GROUP BY 1
```

`INT64(col.body.id), SUM(INT64(col.body.price))`なんて手入力しなければいけません。その上、このJSONの構造は別途リファレンスを参照するか、数件データを出力して構造を類推する必要があり大変不便です。

データを入れる際はJSON型は便利ですが、データを使う際は少しわかりにくいです。次のようなSTRUCT型を使ったり、列が展開された構造がわかりやすいViewがあると嬉しいです。

STRUCT型にした例
```sql
SELECT
    STRUCT(
      STRING(t.col.type) AS type,
      STRUCT(
        INT64(t.col.body.id) AS id,
        INT64(t.col.body.count) AS count,
        INT64(t.col.body.price) AS price,
        TIMESTAMP(STRING(t.col.body.created_ts)) AS created_ts
      ) AS body
    ) col
FROM t
WHERE STRING(t.col.type) = 'item.sell'
```

少し展開した例
```sql
SELECT
  STRING(t.col.type) type,
  STRUCT(
    INT64(t.col.body.id) AS id,
    INT64(t.col.body.count) AS count,
    INT64(t.col.body.price) AS price,
    TIMESTAMP(STRING(t.col.body.created_ts)) AS created_ts
  ) body
FROM t
WHERE STRING(t.col.type) = 'item.sell'
```

更に展開した例
```sql
SELECT
  INT64(t.col.body.id) id,
  INT64(t.col.body.count) count,
  INT64(t.col.body.price) price,
  TIMESTAMP(STRING(t.col.body.created_ts)) created_ts
FROM t
WHERE STRING(t.col.type) = 'item.sell'
```

ぱっと変換できますか？このくらい簡単なものならまだしも、もっと複雑なJSONではどうですか？

こういったとき、JSON型をSTRUCT型に変換する方法が必要になります。



# 変換の考え方

## 一階層のJSON

JSONと変換例を示します。
```json
{"name": "太郎", "age": 20, "updated_ts": "2006-01-02T15:04:05-07:00", "score": 3.14}
```

変換例1
```sql
SELECT
    STRUCT(
     STRING(col.name) AS name,
     INT64(col.age) AS age,
     TIMESTAMP(STRING(col.updated_ts)) AS updated_ts,
     FLOAT64(col.score) AS score
   ) s
FROM
UNNEST([
    JSON'{"name": "太郎", "age": 20, "updated_ts": "2006-01-02T15:04:05-07:00", "score": 3.14}'
    ]) col
```
変換例2
```sql
SELECT
    STRUCT<name STRING, age INT64, TIMESTAMP updated_ts, FLOAT64 score>(
     STRING(col.name),
     INT64(col.age),
     TIMESTAMP(STRING(col.updated_ts)),
     FLOAT64(col.score)
   ) s
FROM
UNNEST([
    JSON'{"name": "太郎", "age": 20, "updated_ts": "2006-01-02T15:04:05-07:00", "score": 3.14}'
    ]) col
```

### **STRUCT型**
STRUCT型はこのように
```sql
STRUCT(
  値1 AS フィールド名1,
  値2 AS フィールド名2
)
```
もしくは
```sql
STRUCT<フィールド名1 型1, フィールド名2 型2>(
  値1,
  値2
)
```
のように表現します。
[構造体型 - BigQueryリファレンス](https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types?hl=ja#struct_type)

### **JSON型をプリミティブな型に変換する**
JSON型の値は`col.foo.bar`など、通常のJSONにアクセスする方法で簡単に参照できます。しかし、その結果はJSON型になります。BigQueryで扱いやすくするにはJSON型をBigQueryのプリミティブな型に変換します。

STRING,INT64,FLOAT64,BOOLの各関数を用いて、JSON型をプリミティブな型に変換します。次のクエリの各列は全てtrueになります。
```sql
SELECT
  STRING(JSON'"Alice"') = "Alice", 
  INT64(JSON'64') = 64, 
  FLOAT64(JSON'3.14') = 3.14, 
  BOOL(JSON'true') = true 
```

データの品質が悪い場合は、`SAFE.STRING`などを用いれば、不正な値をNULLにすることができます。

[STRING関数 - BigQuery リファレンス](https://cloud.google.com/bigquery/docs/reference/standard-sql/json_functions?hl=ja#string_for_json)

それ以外のDATEやTIMESTAMPといった型を使う際は、一旦STRING型に変換してからめあての型に変換することになります。
```sql
SELECT
  TIMESTAMP(STRING(JSON'"2006-01-02T15:04:05-07"'))
```

## 二階層以上のJSON
JSONと変換例を示します。
```json
{"name": "太郎", "address": {"country": "日本", "prefecture": "東京都", "city": "渋谷区"}}
```
```sql
SELECT
  STRUCT(
    STRING(col.name) AS name,
    STRUCT(
      STRING(col.address.country) AS country,
      STRING(col.address.prefecture) AS prefecture,
      STRING(col.address.city) AS city
    ) AS address
  ) s
FROM
UNNEST([
    JSON'{"name": "太郎", "address": {"country": "日本", "prefecture": "東京都", "city": "渋谷区"}}'
    ]) col
```

それほど難しくありません。STRUCTの中にSTRUCTを入れればよいだけです。

ただ、型を前置しようとすると省略できず大変なことになります。
```sql
SELECT
  STRUCT<name STRING, address STRUCT<country STRING, prefecture STRING, city STRING>>(
    STRING(col.name),
    STRUCT<country STRING, prefecture STRING, city STRING>(
      STRING(col.address.country),
      STRING(col.address.prefecture),
      STRING(col.address.city)
    )
  ) s
FROM
UNNEST([
    JSON'{"name": "太郎", "address": {"country": "日本", "prefecture": "東京都", "city": "渋谷区"}}'
    ]) col
```

## プリミティブ型の配列を含むJSON
JSONと変換例を示します。
```json
{"request_id":9876, "users": [123,456,7890]}
```
```sql
SELECT
  STRUCT(
    INT64(col.request_id) AS request_id,
    ARRAY(
      SELECT
        INT64(users)
      FROM
        UNNEST(
          JSON_QUERY_ARRAY(col.users)
        ) users
        WITH OFFSET users_index
      ORDER BY users_index
    ) AS users
  ) s
FROM
UNNEST([
  JSON'{"request_id":9876, "users": [123,456,7890]}'
]) col
```
```sql
SELECT
  STRUCT<request_id INT64, users ARRAY<INT64>>(
    INT64(col.request_id),
    ARRAY(
      SELECT
        INT64(users)
      FROM
        UNNEST(
          JSON_QUERY_ARRAY(col.users)
        ) users
        WITH OFFSET users_index
      ORDER BY users_index
    )
  ) s
FROM
UNNEST([
  JSON'{"request_id":9876, "users": [123,456,7890]}'
]) col
```

まず、`JSON_QUERY_ARRAY(col.users)`だけでは、`ARRAY<JSON>`が返ってきます。
INT64型に変換したいため、ARRAYとUNNESTで変換します。その時、順序を保証するために WITH OFFSET と ORDER BY が必要になります。

以下はこの節で使った機能のBigQueryのリファレンスです。
- [JSON_QUERY_ARRAY](https://cloud.google.com/bigquery/docs/reference/standard-sql/json_functions?hl=ja#json_query_array)
- [サブクエリからの配列の作成](https://cloud.google.com/bigquery/docs/reference/standard-sql/arrays?hl=ja#creating_arrays_from_subqueries)
- [WITH OFFSET, ORDER BY](https://cloud.google.com/bigquery/docs/reference/standard-sql/arrays?hl=ja#flattening_arrays)

## オブジェクトの配列を含むJSON
JSONと変換例を示します。
```json
{ "users": [
    {"id": 123, "name": "太郎"},
    {"id": 124, "name": "二郎"}
  ]
}
```
```sql
SELECT
  STRUCT(
    ARRAY(
      SELECT AS STRUCT
        INT64(users.id) AS id,
        STRING(users.name) AS name
      FROM UNNEST(JSON_QUERY_ARRAY(col.users)) users
      WITH OFFSET users_index
      ORDER BY users_index
    ) AS users
  ) s
FROM
UNNEST([
JSON'{"users":[{"id": 123, "name": "太郎"},{"id": 124, "name": "二郎"}]}']) col
```
```sql
SELECT
  STRUCT<users ARRAY<STRUCT<id INT64, name STRING>>>(
    ARRAY(
      SELECT
        STRUCT<id INT64, name STRING>(
          INT64(users.id),
          STRING(users.name)
        )
      FROM UNNEST(JSON_QUERY_ARRAY(col.users)) users
      WITH OFFSET users_index
      ORDER BY users_index
    ) AS users
  ) s
FROM
UNNEST([
JSON'{"users":[{"id": 123, "name": "太郎"},{"id": 124, "name": "二郎"}]}']) col
```

 プリミティブ型の配列を含むJSONの作り方を理解していれば、同様に列を増やすだけです。`SELECT AS STRUCT`を使うと多少すっきりしますが、使わなくても作れます。
 
 - [SELECT AS STRUCT - BigQuery リファレンス](https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax?hl=ja#select_as_struct)

# JSONデータの構造を把握する
さて、どのように変換するかはわかりましたが、JSONのデータはあるものの構造がわからないことがあります。

そんなときは、JSONを読み取ってBigQueryのスキーマに変換する便利なOSSを使います。
https://github.com/bxparks/bigquery-schema-generator
これを用いると、次のように簡単に変換できます。
```
$ cat sample.json
{"users":[{"id": 123, "name": "太郎"},{"id": 124, "name": "二郎"}]}

$ generate-schema < sample.json 

[
  {
    "fields": [
      {
        "mode": "NULLABLE",
        "name": "id",
        "type": "INTEGER"
      },
      {
        "mode": "NULLABLE",
        "name": "name",
        "type": "STRING"
      }
    ],
    "mode": "REPEATED",
    "name": "users",
    "type": "RECORD"
  }
]
```

# 機械的にやる
## 目標
ここまでで、クエリを組み立てる手順を整理し、JSONのフォーマットをわかりやすく記述する方法を手に入れました。次は機械的にクエリを生成したくなります。

次のような関数を実装することが目標です。

次のようなJSONが入ったJSON列`col`をもつテーブル`t`を想定します。
```json
{
  "date": "2006-01-02",
  "timestamps": [
    "2006-01-02T15:04:05+0900",
    "2006-01-02T20:00:00+0900"
  ]
  "location": {
    "country": "Japan",
    "ZipCode": "123-4567"
  },
  "users": [
    {"id": 123, "name": "太郎"},
    {"id": 124, "name": "二郎"}
  ]
}
```

このテーブルに対して次のようなクエリを機械的に作成します。
```sql
SELECT
  SAFE.STRING(col.created_at) AS created_at,
  DATE(SAFE.STRING(col.date)) AS date,
  STRUCT(
    SAFE.STRING(col.location.country) AS country,
    SAFE.STRING(col.location.ZipCode) AS ZipCode
  ) AS location,
  ARRAY(
    SELECT
      SAFE_CAST(SAFE.STRING(timestamps) AS TIMESTAMP)
    FROM UNNEST(JSON_QUERY_ARRAY(col.timestamps)) timestamps WITH OFFSET timestamps_index
    ORDER BY timestamps_index
  ) AS timestamps,
  ARRAY(
    SELECT
      STRUCT(
        SAFE.INT64(users.id) AS id,
        SAFE.STRING(users.name) AS name
      )
    FROM UNNEST(JSON_QUERY_ARRAY(col.users)) users WITH OFFSET users_index
    ORDER BY users_index
  ) AS users
FROM t
```

なお、このJSONに対するスキーマは、[bigquery-schema-generator](https://github.com/bxparks/bigquery-schema-generator)などを用いて用意しておきます。
```json
[
  {
    "mode": "NULLABLE",
    "name": "date",
    "type": "DATE"
  },
  {
    "fields": [
      {
        "mode": "NULLABLE",
        "name": "country",
        "type": "STRING"
      },
      {
        "mode": "NULLABLE",
        "name": "ZipCode",
        "type": "STRING"
      }
    ],
    "mode": "NULLABLE",
    "name": "location",
    "type": "RECORD"
  },
  {
    "mode": "REPEATED",
    "name": "timestamps",
    "type": "STRING"
  },
  {
    "fields": [
      {
        "mode": "NULLABLE",
        "name": "id",
        "type": "INTEGER"
      },
      {
        "mode": "NULLABLE",
        "name": "name",
        "type": "STRING"
      }
    ],
    "mode": "REPEATED",
    "name": "users",
    "type": "RECORD"
  }
]
```
## 実装

ここでは例としてTypeScriptで実装していきます。手順を順に書いていきますので、他言語での実装も簡単ですね！


BigQueryのJSONで表されるSchemaは、次のように表現できます。ポイントは、
- `type`フィールドが`RECORD`の場合はSTRUCT型です。`fields`フィールドが追加され、再帰的な構造になります。
- `mode`フィールドが`REPEATED`の場合は配列となります。

```typescript
type BqJsonSchemaElemBase = {
  name: string,
  mode: "REPEATED" | "NULLABLE" | "REQUIRED",
  description?: string
}
type BqJsonTypes =  "INTEGER" | "STRING" | "DATE" | "TIMESTAMP" // 他にも型があれば足す
type BqJsonSchemaElemDefault = BqJsonSchemaElemBase & {
  type: BqJsonTypes
}
type BqJsonSchemaElemRecord = BqJsonSchemaElemBase & {
  type: "RECORD",
  fields: BqJsonSchemaElem[]
}
type BqJsonSchemaElem = BqJsonSchemaElemDefault | BqJsonSchemaElemRecord
type BqJsonSchema = BqJsonSchemaElem[]
```

まず、JSON型をそれぞれの型に変換する関数を用意しておきます。
```typescript
const cast :{[key in BqJsonTypes]:(column :string)=>string} = {
  "TIMESTAMP": (column :string): string => `SAFE_CAST(SAFE.STRING(${column}) AS TIMESTAMP)`,
  "STRING": (column :string): string => `SAFE.STRING(${column})`,
  "DATE": (column :string): string => `DATE(SAFE.STRING(${column}))`,
  "INTEGER": (column :string): string => `SAFE.INT64(${column})`,
  // 他にも型があれば足す
}
```

### 一階層
一階層に対応した実装は次のようになります。
```typescript
const generateSelect = (schema: BqJsonSchema, prefix: string) => {
  const columns: string[] = []
  for (let elem of schema) {
    let column = cast[elem.type](`${prefix}${elem.name}`)
    column += ` AS ${elem.name}`
    columns.push(column)
  }
  return columns.join(',\n')
}

console.log(
  generateSelect(
    [
      { "mode": "NULLABLE",  "name": "date", "type": "DATE"}
    ]
    ,'col.'
  )
);
```
出力は以下のようになります。`SELECT`と`FROM t`は最後につけることにして、ここからSELECT句の中身を作っていきます。
```
DATE(SAFE.STRING(col.date)) AS date
```

### 二階層以上
二階層以上に対応した実装は次のようになります。再帰的な構造になっているので再帰で実装します。
```typescript
const generateSelect = (schema: BqJsonSchema, prefix: string) => {
  const columns: string[] = []
  for (let elem of schema) {
    let column = ""
    if (elem.type === "RECORD") {
      column = `STRUCT(
${generateSelect(elem.fields, `${prefix}${elem.name}`)}
)`
    } else {
      column = cast[elem.type](`${prefix}${elem.name}`)
    }
    column += ` AS ${elem.name}`
    columns.push(column)
  }
  return columns.join(',\n')
}

console.log(
  generateSelect(
    [
      { "mode": "NULLABLE",  "name": "date", "type": "DATE"},
      {
        "fields": [
          { "mode": "NULLABLE", "name": "country", "type": "STRING"},
          { "mode": "NULLABLE", "name": "ZipCode", "type": "STRING"},
        ],
        "mode": "NULLABLE",  "name": "location", "type": "RECORD"
      }
    ],
    'col'
  )
);
```
出力は以下の通りです。
```
DATE(SAFE.STRING(col.date)) AS date,
STRUCT(
SAFE.STRING(location.country) AS country,
SAFE.STRING(location.ZipCode) AS ZipCode
) AS location
```

### 配列を含む構造

配列を実装します。

- 生成した`column`をARRAY()で囲みます。
- ARRAY()の中では、`column`はARRAY関数の内側のFROM句を参照するようにします。
- インデントも整えておきます。本筋から外れるので解説はしません。別途Formatterを使ってもいいと思います。
  - 最後なので`SELECT ... FROM t`も付けてしまいます。 

```typescript
const generateSelect = (schema: BqJsonSchema, prefix: string) => {
  const columns: string[] = []
    for (let elem of schema) {
    let column = ""
    let column_prefix = elem.mode === "REPEATED" ? "" : prefix
    if (elem.type === "RECORD") {
      column = `STRUCT(
${generateSelect(elem.fields, `${column_prefix}${elem.name}.`)}
)`
    } else {
      column = cast[elem.type](`${column_prefix}${elem.name}`)
    }
    if (elem.mode === "REPEATED") {
      column = `ARRAY(
SELECT
${column}
FROM UNNEST(JSON_QUERY_ARRAY(${prefix}${elem.name})) ${elem.name}
WITH OFFSET ${elem.name}_index
ORDER BY ${elem.name}_index
)`
    }
    column += ` AS ${elem.name}`
    columns.push(column)
  }
  const query = columns.join(",\n")
  // インデントを整える部分
  const lines = query.split("\n")
  const res_lines: string[] = []
  let indent_cnt = 1 // SELECT句分
  for (let i = 0; i< lines.length; ++i){
    let l = lines[i]
    const heading_close_cnt =  [...l.matchAll(/^([\)>}]|FROM|\s)+/g)].map(v=>v[0]).map(v=>[...v.matchAll(/(FROM|[\)}>])/g)]).flat().length
    indent_cnt -= heading_close_cnt
    l = "  ".repeat(Math.max(0,indent_cnt)) + l
    indent_cnt -= [...l.matchAll(/([>}\)]|FROM)/g)].length - heading_close_cnt
    indent_cnt += [...l.matchAll(/([<{\(]|SELECT)/g)].length
    res_lines.push(l)
  }
  return "SELECT\n" + res_lines.join('\n') + "\nFROM t"
}

console.log(
  generateSelect(
    [
      { "mode": "NULLABLE",  "name": "date", "type": "DATE"},
      {
        "fields": [
          { "mode": "NULLABLE", "name": "country", "type": "STRING"},
          { "mode": "NULLABLE", "name": "ZipCode", "type": "STRING"},
        ],
        "mode": "NULLABLE",  "name": "location", "type": "RECORD"
      },
      { "mode": "REPEATED",  "name": "timestamps", "type": "TIMESTAMP"},
      {
        "fields": [
          { "mode": "NULLABLE", "name": "id", "type": "INTEGER"},
          { "mode": "NULLABLE", "name": "name", "type": "STRING"},
        ],
        "mode": "REPEATED",  "name": "users", "type": "RECORD"
      },
    ],
    'col.'
  )
);
```

出力は以下のようになります。
```sql
SELECT
  DATE(SAFE.STRING(col.date)) AS date,
  STRUCT(
    SELECT
        SAFE.STRING(col.location.country) AS country,
        SAFE.STRING(col.location.ZipCode) AS ZipCode
    FROM t
  ) AS location,
  ARRAY(
    SELECT
      SAFE_CAST(SAFE.STRING(timestamps) AS TIMESTAMP)
    FROM UNNEST(JSON_QUERY_ARRAY(col.timestamps)) timestamps WITH OFFSET timestamps_index
    ORDER BY timestamps_index
  ) AS timestamps,
  ARRAY(
    SELECT
      STRUCT(
        SELECT
            SAFE.INT64(users.id) AS id,
            SAFE.STRING(users.name) AS name
        FROM t
      )
    FROM UNNEST(JSON_QUERY_ARRAY(col.users)) users WITH OFFSET users_index
    ORDER BY users_index
  ) AS users
FROM t
```

## 動作確認用のWebアプリ

BigQueryのJSONで表現したスキーマからSQLに変換するWebアプリです。元のJSONからスキーマへの変換はあらかじめする必要があります。

私の利用目的では、あらかじめスキーマがわかっていたため、元のJSONからスキーマへの変換は開発が不要で実装していません……。また、`DATE`, `TIMESTAMP`, `INTEGER`, `STRING` にしか対応していません。

App: https://m77so.github.io/bq-json-struct-conv/ 
GitHub: https://github.com/m77so/bq-json-struct-conv