Zenn
🔥

BigQueryのネイティブJSON型がサポートされたことでどう変わったのか

2022/01/10に公開
BigQuery
tech

Google Cloud Next 2021で発表され、Private PreviewだったBigQueryのネイティブJSON型が先日Public Previewになったとのことです。

BigQuery now natively supports semi-structured data | Google Cloud Blog

https://twitter.com/yutah_3/status/1479225222587125762?s=20

発表された当初は「あれ、すでに出ているJSON_EXTRACT()とかのJSON関数と何が違うのだろう」と思っていたのですが、実際に触ってみるとネイティブJSON型の柔軟性に感動したので紹介したいと思います。

サンプルJSON

以下のようなJSONデータをパースしたいとします。

{
    "id": 12345,
    "name": "ohsawa0515",
    "person_info": [
        {
            "key": "height",
            "value": "178"
        },
        {
            "key": "weight",
            "value": "65"
        },
        {
            "key": "address",
            "value": "Tokyo"
        }
    ]
}

UDFを利用する方法

今までだとUDF（ユーザー定義関数）を使うか、頑張ってパースする方法が取られていたと思います。以下はJavaScript UDFを使った方法です。

CREATE TEMPORARY FUNCTION parse_json_data(json_data STRING)
RETURNS STRUCT<id INT64, name STRING,
  person_info ARRAY<STRUCT<key STRING, value STRING
>>>
LANGUAGE js
AS "return JSON.parse(json_data);";

WITH input_data AS (
  SELECT '{"id":12345,"name":"ohsawa0515","person_info":[{"key":"height","value":"178"},{"key":"weight","value":"65"},{"key":"address","value":"Tokyo"}]}' AS json_data
), parsed AS (
  SELECT
    parse_json_data(json_data).*
  FROM
    input_data
)
SELECT
  id,
  name,
  person_info.key AS key,
  person_info.value AS value
FROM parsed
LEFT JOIN UNNEST(parsed.person_info) AS person_info;

実行結果は以下のようになります。

id name key value
1 12345 ohsawa0515 height 178
2 12345 ohsawa0515 weight 65
3 12345 ohsawa0515 address Tokyo

ネイティブJSON型をつかった方法

これが新しいJSON型でやると以下のようになります。実行結果は先ほどと同じです。

UDFを使った方法と比べて、あらかじめ戻り値となる型を決める必要がないため、とてもシンプルになっていることがわかります。

WITH input_data AS (
  SELECT SAFE.PARSE_JSON('{"id":12345,"name":"ohsawa0515","person_info":[{"key":"height","value":"178"},{"key":"weight","value":"65"},{"key":"address","value":"Tokyo"}]}') AS json_data
)
SELECT
  json_data.id,
  JSON_VALUE(json_data.name) AS name,
  JSON_VALUE(person_info.key) AS key,
  JSON_VALUE(person_info.value) AS value
FROM input_data
LEFT JOIN UNNEST(JSON_QUERY_ARRAY(json_data.person_info)) AS person_info
;

いくつかポイントを見ていきます。

JSON型への変換

WITH input_data AS (
  SELECT SAFE.PARSE_JSON('{"id":12345,"name":"ohsawa0515","person_info":[{"key":"height","value":"178"},{"key":"weight","value":"65"},{"key":"address","value":"Tokyo"}]}') AS json_data
)

PARSE_JSON 関数で文字列型からJSON型に変換します。SAFE.PARSE_JSONにすることでJSONにパースできない場合はNULLを返します。

SELECT SAFE.PARSE_JSON('{"id":12345,"name":"ohsawa0515"}') AS json_data
UNION ALL
SELECT SAFE.PARSE_JSON('{"invalid"}')  AS json_data

+----------------------------------+
| json_data                        |
+----------------------------------+
| {"id":12345,"name":"ohsawa0515"} |
| NULL                             |
+----------------------------------+

ドット表記

JSON型ではドット表記でアクセスできます。以下の2つのクエリ結果は同じになります。

-- 文字列型
WITH input_data AS (
  SELECT '{"id":12345,"name":"ohsawa0515"}' AS json_data
)
SELECT
  JSON_QUERY(json_data, '$.id') AS id,
  JSON_QUERY(json_data, '$.name') AS name
FROM input_data;

-- JSON型
WITH input_data AS (
  SELECT SAFE.PARSE_JSON('{"id":12345,"name":"ohsawa0515"}') AS json_data
)
SELECT
  json_data.id,
  json_data.name
FROM input_data;

実行結果。

id name
1 12345 "ohsawa0515"

JSON型から配列の抽出

JSON_QUERY_ARRAY関数でJSON型の中にある配列を抽出できます。

LEFT JOIN UNNEST(JSON_QUERY_ARRAY(json_data.person_info)) AS person_info

まとめ

ネイティブJSON型が登場したことで複雑なJSON文字列への抽出がとてもやりやすくなりました。つかってみるとパースに時間を要するのか、やや実行時間が延びたように感じました（気のせいかもしれません）。とはいえとても便利だったのでぜひ使ってみてほしいと思います。

参考

Discussion