先日、BigQueryのJSON型データを扱う際に、キーがバラバラで値の抽出処理ができなくて困った。
こちらの記事を参考にして、キーをダミーの文字列に置換して処理する方法でなんとか解決できた。
例えば下記のようなキーにユーザID、値にユーザ名が入っている場合、普通のやり方だとユーザIDを事前に知らないとユーザ名を抽出できない。
ここでユーザIDを一度「KEY」という文字列に置換してから、JSONのパスを「$.KEY」で指定することで、ユーザ名を取り出すことができる。
WITH sample AS (
SELECT JSON '{"8901": "山田 太郎", "8071": "鈴木 次郎"}' AS json_data
)
SELECT
JSON_VALUE(
REGEXP_REPLACE(
TO_JSON_STRING(json_data),
CONCAT('"', key, '"'),
'"__KEY__"'
),
'$.__KEY__'
) AS value
FROM sample,
UNNEST(JSON_KEYS(json_data)) AS key
JSON型のデータを文字列にして、その中のキーをダミーの文字列に変換するという処理は無理矢理感がスゴイけど、こうする他ないこともまあまあるので、知っておくと役に立つかも。
