【BigQuery】JSONテクニック。キーが動的な場合はダミーに置換

先日、BigQueryのJSON型データを扱う際に、キーがバラバラで値の抽出処理ができなくて困った。

こちらの記事を参考にして、キーをダミーの文字列に置換して処理する方法でなんとか解決できた。

maxhalford.github.io

例えば下記のようなキーにユーザ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型のデータを文字列にして、その中のキーをダミーの文字列に変換するという処理は無理矢理感がスゴイけど、こうする他ないこともまあまあるので、知っておくと役に立つかも。