Yappli Tech Blog

株式会社ヤプリの開発メンバーによるブログです。最新の技術情報からチーム・働き方に関するテーマまで、日々の熱い想いを持って発信していきます。

BigQueryのJSONデータで、同一のJSONキー名がある場合に全ての値を取得する【Sexy Tech for You #10】

BigQuery

データサイエンティストの阿部です。今さらながら『劇場版 少女☆歌劇 レヴュースタァライト』を映画館で観たのですが衝撃的でした。

さて、明日から使いたくなるデータハンドリング術を紹介する "Sexy Tech for You" の第10話を配信しました。僕は「とりあえずJSON形式で突っ込んでおいてくれたらあとはよしなにやります」と言いがちなタイプですが、最近便利さに感心したBigQueryのJSON_QUERY_ARRAY関数について紹介しました。

動画中に記載したクエリや補足事項を本ブログに掲載しますので、少しでも皆様のお役に立てたら幸いです。

Youtube


BigQuery クエリ

同一のJSONキーの数がレコードごとに異なる場合に対応できない例：オフセット値を指定する

WITH 

input AS (
  SELECT PARSE_JSON('{"id":1,"name":"Sexy","info":[{"key":"point","value":"100"},{"key":"stamp","value":"2"}]}') AS jd
  UNION ALL
  SELECT PARSE_JSON('{"id":2,"name":"Tech","info":[{"key":"point","value":"500"},{"key":"stamp","value":"5"},{"key":"coupon","value":"3"}]}') 
)

SELECT
  JSON_VALUE(jd, '$.id') AS id,
  JSON_VALUE(jd, '$.name') AS name,
  JSON_VALUE(jd, '$.info[0].key') AS key0,
  JSON_VALUE(jd, '$.info[0].value') AS value0,
  JSON_VALUE(jd, '$.info[1].key') AS key1,
  JSON_VALUE(jd, '$.info[1].value') AS value1
FROM
  input

クエリが冗長な例：JSON_EXTRACT_ARRAY関数を用いる

WITH 

input AS (
  SELECT PARSE_JSON('{"id":1,"name":"Sexy","info":[{"key":"point","value":"100"},{"key":"stamp","value":"2"}]}') AS jd
  UNION ALL
  SELECT PARSE_JSON('{"id":2,"name":"Tech","info":[{"key":"point","value":"500"},{"key":"stamp","value":"5"},{"key":"coupon","value":"3"}]}') 
),

tmp AS (
SELECT 
  JSON_EXTRACT_SCALAR(jd, '$.id') AS id,
  JSON_EXTRACT_SCALAR(jd, '$.name') AS name,
  ARRAY(
      SELECT 
        JSON_EXTRACT_SCALAR(x, '$.key')
      FROM UNNEST(JSON_EXTRACT_ARRAY(jd, "$.info")) x
  ) AS key,
  ARRAY(
      SELECT JSON_EXTRACT_SCALAR(x, '$.value')
      FROM UNNEST(JSON_EXTRACT_ARRAY(jd, "$.info")) x
  ) AS value
FROM
  input
)

SELECT 
  id,
  name,
  key,
  value[SAFE_OFFSET(offset)] AS value
FROM
  tmp, UNNEST(key) AS key WITH OFFSET AS offset

おすすめ例：JSON_QUERY_ARRAY関数を用いる

WITH 

input AS (
  SELECT PARSE_JSON('{"id":1,"name":"Sexy","info":[{"key":"point","value":"100"},{"key":"stamp","value":"2"}]}') AS jd
  UNION ALL
  SELECT PARSE_JSON('{"id":2,"name":"Tech","info":[{"key":"point","value":"500"},{"key":"stamp","value":"5"},{"key":"coupon","value":"3"}]}') 
)

SELECT 
  JSON_VALUE(input.jd, '$.id') AS id,
  JSON_VALUE(input.jd, '$.name') AS name,
  JSON_VALUE(info.key) AS key,
  JSON_VALUE(info.value) AS value
FROM
  input, UNNEST(JSON_QUERY_ARRAY(jd.info)) AS info

補足

ヤプリには、各アプリの構成情報をJSON形式で保持しているテーブルがあって、マスタとして活用しています。そして表現力の高いサービスを実現するために、そのテーブルでは、キーの個数が可変だったり、新たなキーが追加されたりするケースがあります。そうしたケースにBigQueryで対応しているうちに、上記のような知見を得ました。

また本稿をまとめるにあたって、末尾のブログを参照させて頂きました。どうもありがとうございました。

以上、Moblie Tech for All のヤプリがお送りする Sexy Tech for You でした！次回またお会いしましょう。

参考