【BigQuery】列データからJSON文字列を作成する

k.uehara

2023.01.12

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

データアナリティクス事業本部のueharaです。

今回はBigQueryで、列データからJSON文字列として作成してみたいと思います。

keyが予め決まっているケース

「keyが予め決まっているケース」とは、例えば{"name": "foo1"}の"name"など、予めkeyとして扱いたい値が決まっていることを指します。

簡単ですが、以下のテーブルを考えてみます。

name	value
foo1	123
foo2	456
foo3	789

上記テーブルから、{"name":"foo1",　"value":123}といったようなJSON文字列を作成したい時、BigQueryでは以下のように書くことができます。

-- データのため一時テーブルを作成
WITH tmp AS (
  (SELECT 'foo1' as name, 123 as value) UNION ALL
  (SELECT 'foo2' as name, 456 as value) UNION ALL
  (SELECT 'foo3' as name, 789 as value) 
)
--
SELECT TO_JSON_STRING(STRUCT(name, value)) AS json_str
FROM tmp

実行結果は次の通りです。

key部分を別の名前にしたい時は、STRUCT(name, value)の部分をSTRUCT(name as username, value as val)というような形にしてあげることで実現可能です。

テーブル全体をJSON文字列にしたい場合は、以下のように簡潔に書くこともできます。

-- データのため一時テーブルを作成
WITH tmp AS (
  (SELECT 'foo1' as name, 123 as value) UNION ALL
  (SELECT 'foo2' as name, 456 as value) UNION ALL
  (SELECT 'foo3' as name, 789 as value) 
)
SELECT TO_JSON(tmp) AS json_str
FROM tmp

パースの確認

作成したJSON文字列が、きちんとパースできるか（JSONPathにより値が取得できるか）、念のため確認を行います。

先程のjson_strを一時テーブルとして、PARSE_JSON関数でパースを行い、JSON_VALUE関数で値を取得してみます。

WITH tmp1 AS (
  (SELECT 'foo1' as name, 123 as value) UNION ALL
  (SELECT 'foo2' as name, 456 as value) UNION ALL
  (SELECT 'foo3' as name, 789 as value) 
),
tmp2 AS (
  SELECT TO_JSON_STRING(STRUCT(name, value)) AS json_str
  FROM tmp1
)
-- 値が取得できるか確認
SELECT JSON_VALUE(PARSE_JSON(json_str), '$.name') AS name
FROM tmp2

結果を確認すると、しっかり値が取れることが分かります。

keyが予め決まっていないケース

keyが予め決まっていないケースとは、「列データに入ってくる値をkeyとして利用したい」というケースを指します。

例えば、以下のテーブルを考えてみます。

key	value
title1	hoge1
title2	hoge2
title3	hoge3

上記テーブルから、{"title1":"hoge1"}といったように列データに入ってくる値をキーとしてJSON文字列を作成したい時、先程のようにSTRUCTを利用してJSON文字列をつくることはできません。（どんな値をkeyとするか定義できない）

2023年1月時点では専用の関数は無いので、次のように「JSONを手作り」する必要があります。

-- データのため一時テーブルを作成
WITH tmp AS (
  (SELECT 'title1' as key, 'hoge1' as value) UNION ALL
  (SELECT 'title2' as key, 'hoge2' as value) UNION ALL
  (SELECT 'title3' as key, 'hoge3' as value) 
)
--
SELECT CONCAT('{"', TO_JSON_STRING(key), '":', TO_JSON_STRING(value), '}') as json_str
FROM tmp

力技ですが、実行結果は次の通りです。

ここで、CONCATする文字列にTO_JSON_STRINGを適用しているのは、文字列をエスケープするためです。

パースの確認

例えば、先程のvalueをhoge1 "aaa編"のようにして、TO_JSON_STRINGを適用しない以下のSQLを実行してみます。

WITH tmp1 AS (
  (SELECT 'title1' as key, 'hoge1 "aaa編"' as value) UNION ALL
  (SELECT 'title2' as key, 'hoge2 "bbb編"' as value) UNION ALL
  (SELECT 'title3' as key, 'hoge3 "ccc編"' as value) 
),
tmp2 AS (
  SELECT CONCAT('{"', key, '":"', value, '"}') as json_str
  FROM tmp1
)
-- 値が取得できるか確認
SELECT
  COALESCE(
    JSON_VALUE(PARSE_JSON(json_str), '$.title1'),
    JSON_VALUE(PARSE_JSON(json_str), '$.title2'),
    JSON_VALUE(PARSE_JSON(json_str), '$.title3')
  ) AS title
FROM tmp2

実行結果は次の通り、パースができずエラーとなります。

これは、ダブルクオーテーションがエスケープされていないというのが原因です。

先程のSQLについて、CONCATをする際にTO_JSON_STRING関数を適用したものは以下の通りです。

WITH tmp1 AS (
  (SELECT 'title1' as key, 'hoge1 "aaa編"' as value) UNION ALL
  (SELECT 'title2' as key, 'hoge2 "bbb編"' as value) UNION ALL
  (SELECT 'title3' as key, 'hoge3 "ccc編"' as value) 
),
tmp2 AS (
  -- SELECT CONCAT('{"', key, '":"', value, '"}') as json_str
  SELECT CONCAT('{"', TO_JSON_STRING(key), '":', TO_JSON_STRING(value), '}') as json_str
  FROM tmp1
)
-- 値が取得できるか確認
SELECT
  COALESCE(
    JSON_VALUE(PARSE_JSON(json_str), '$.title1'),
    JSON_VALUE(PARSE_JSON(json_str), '$.title2'),
    JSON_VALUE(PARSE_JSON(json_str), '$.title3')
  ) AS title
FROM tmp2

結果は次の通りで、値がエスケープされてパースができるようになり、値を取得することができます。