この記事は公開されてから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結果は次の通りで、値がエスケープされてパースができるようになり、値を取得することができます。
最後に
今回はBigQueryで、keyが予め決まっている/決まっていないといったケース別に列データからJSON文字列を作成してみました。
参考になりましたら幸いです。
3
