これは　MIXI DEVELOPERS Advent Calendar 2022 13日目です。

概要

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

はじめに

JSON型

BigQueryのJSON型がついにGA（一般提供）になりました。【新機能】BigQueryでJSON型が使えます！（BigQuery subscriptionsも対応） - DevelopersIO にもあるように、いつのまにかGAになっています。

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

JSONの文字列に対してアクセスする例。colはただの文字列です。

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型です。

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リファレンス

また、このような半構造化データを扱うもう一つの方法として、STRUCT型やARRAY型を扱う方法があります。事前にデータの構造を定義します。同じ列では同じ構造のデータしか扱えません。

SELECT
  STRUCT<name STRING>("Alice"),
  STRUCT<members ARRAY<STRING>>(["Alice", "Bob"])

JSON型の用途

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

{"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のようなアンチパターンに手を染めるかですが、その点でJSON型は大変便利です。

問題提起

さて、そのようなテーブルに対して分析クエリを投げる際、JSON型をそのまま使うのは不便です。このテーブルに対して、"item_sell"ログの"item_id"ごとの"price"の合計を集計してみましょう。

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型にした例

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'

少し展開した例

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'

更に展開した例

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と変換例を示します。

{"name": "太郎", "age": 20, "updated_ts": "2006-01-02T15:04:05-07:00", "score": 3.14}

変換例1

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

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型はこのように

STRUCT(
  値1 AS フィールド名1,
  値2 AS フィールド名2
)

もしくは

STRUCT<フィールド名1 型1, フィールド名2 型2>(
  値1,
  値2
)

のように表現します。
構造体型 - BigQueryリファレンス

JSON型をプリミティブな型に変換する

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

STRING,INT64,FLOAT64,BOOLの各関数を用いて、JSON型をプリミティブな型に変換します。次のクエリの各列は全てtrueになります。

SELECT
  STRING(JSON'"Alice"') = "Alice", 
  INT64(JSON'64') = 64, 
  FLOAT64(JSON'3.14') = 3.14, 
  BOOL(JSON'true') = true

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

STRING関数 - BigQuery リファレンス

それ以外のDATEやTIMESTAMPといった型を使う際は、一旦STRING型に変換してからめあての型に変換することになります。

SELECT
  TIMESTAMP(STRING(JSON'"2006-01-02T15:04:05-07"'))

二階層以上のJSON

JSONと変換例を示します。

{"name": "太郎", "address": {"country": "日本", "prefecture": "東京都", "city": "渋谷区"}}

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を入れればよいだけです。

ただ、型を前置しようとすると省略できず大変なことになります。

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と変換例を示します。

{"request_id":9876, "users": [123,456,7890]}

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

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

JSONと変換例を示します。

{ "users": [
    {"id": 123, "name": "太郎"},
    {"id": 124, "name": "二郎"}
  ]
}

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

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 リファレンス

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を想定します。

{
  "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": "二郎"}
  ]
}

このテーブルに対して次のようなクエリを機械的に作成します。

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などを用いて用意しておきます。

[
  {
    "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の場合は配列となります。

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型をそれぞれの型に変換する関数を用意しておきます。

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})`,
  // 他にも型があれば足す
}

一階層

一階層に対応した実装は次のようになります。

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

二階層以上

二階層以上に対応した実装は次のようになります。再帰的な構造になっているので再帰で実装します。

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も付けてしまいます。

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.'
  )
);

出力は以下のようになります。

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