BigQueryでドットを含むJSON Path文字をエスケープする方法まとめ【Sexy Tech for You #13】

データサイエンティストの阿部です。今年は大河ドラマを毎週見ています。三谷幸喜の代表作として長く語り継がれるのではないかと思っています。

さて、明日から使いたくなるデータハンドリング術を紹介する "Sexy Tech for You" の第13話を配信しました。BigQueryでは、JSONを扱う関数がレガシー版とスタンダード版の2種類あります。どちらを使っているかで、ドットを含む場合のJSON Path文字をエスケープする方法が異なるのでまとめました。

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

Youtube

関数別のエスケープ方法まとめ

新旧	関数	全体の引用符	ドットを含むJSON Path文字の指定方法
レガシー	JSON_EXTRACT_SCALAR	" "	[‘target.path’]
スタンダード	JSON_VALUE	' '	“target.path”

BigQuery クエリ

JSON Path文字をエスケープせずNULLになってしまう例

WITH 

back_screen AS (SELECT 
  JSON_QUERY(json_text, '$') AS json_text_string
FROM UNNEST([
  '{"team.tigers" : {"players" : {"name" : "Bass"}}}',
  '{"team.tigers" : {"players" : {"name" : "Kakefu"}}}',
  '{"team.tigers" : {"players" : {"name" : "Okada"}}}'
  ]) AS json_text
)

SELECT 
  JSON_EXTRACT_SCALAR(json_text_string, "$.team.tigers.players.name") AS legacy_func,
  JSON_VALUE(json_text_string, '$.team.tigers.players.name') AS standard_func
FROM
  back_screen

JSON Path文字をエスケープして値を取得する例

WITH 

back_screen AS (SELECT 
  JSON_QUERY(json_text, '$') AS json_text_string
FROM UNNEST([
  '{"team.tigers" : {"players" : {"name" : "Bass"}}}',
  '{"team.tigers" : {"players" : {"name" : "Kakefu"}}}',
  '{"team.tigers" : {"players" : {"name" : "Okada"}}}'
  ]) AS json_text
)

SELECT 
  JSON_EXTRACT_SCALAR(json_text_string, "$['team.tigers'].players.name") AS legacy_func,
  JSON_VALUE(json_text_string, '$."team.tigers".players.name') AS standard_func
FROM
  back_screen

補足

JSON Path文字のエスケープ方法が異なることで、少しはまったので、今回記事にまとめてみました。

エスケープする必要がなければ、JSON Pathを扱う部分の全体引用符は一重でも二重でも値を取得できます。そのため、どちらで書くか気にしていなかったのですが、これからはエスケープする場合に備えて、JSON_VALUE関数は、一重引用符でJSON Pathを括ることにします。

少し脱線しますが、とあるプラットフォームの中の人に聞いた時、関数や機能が新旧ある場合について、

• Classicと記載されている＝しばらく現役の見込みが高い
• Legacyと記載されている＝近い将来使えなくなりうる

と聞きました。BigQueryがどうなるかはわかりませんが、いずれJSON_EXTRACT_SCALAR関数が稼働しなくなることを見据えて、見つけたらJSON_VALUE関数に置き換えるようにしています。進取の意気でBigQueryと付き合って参りましょう。

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