唯物是真 @Scaled_Wurm

プログラミング(主にPython2.7)とか機械学習とか

BigQueryのWebコンソールでJSONを整形して出力(pretty print)

BigQueryにはJSON形式の文字列から値を取り出す関数があるのでスキーマレスな感じで使うこともできます
ただしJSONを入れるとWebのコンソール上で見たときに改行などが無視されて、JSONの内容を確認しづらいので適当に整形して表示確認する方法を書いておきます(実用性は……?)

↓JSONが入ったカラムの雑な例、本来はもっと大量に属性があるイメージ
f:id:sucrose:20170617234920p:plain

JSONを整形して表示する上での問題は以下の2点になります

  1. 改行が無視される
  2. 文字列の先頭の空白が無視される

1点目の問題はsplitしてARRAY形式に変換してしまえば見た目上は解決です
2点目は先頭に適当な文字(「.」など)を入れて無理矢理インデントの部分を表示させます

ちなみにJavaScriptではJSON.stringify()という関数を使えて、JSONをある程度整形した文字列に変換できます
なので以下ではUDFでJSON.stringify()を使ってみます(元から整形された状態の文字列ならJSON.stringify()を使って整形し直す必要はありません)
JSON.stringify(JSONの文字列, null, インデントに使う文字)という形で使うとそれっぽい形で整形されます
developer.mozilla.org
ただし、配列やオブジェクトがネストしているとあまり見た目がよくないので自分で整形用の関数を書いた方がよいかもしれません

結果

というわけでJSON.stringify()してからsplitして無理矢理表示します

f:id:sucrose:20170617234427p:plain

#StandardSQL

CREATE TEMPORARY FUNCTION json_pretty_print(x STRING)
RETURNS ARRAY<STRING>
LANGUAGE js AS """
  return JSON.stringify(JSON.parse(x), null, '  ').split('\\n').map((x) => '.' + x);
""";

SELECT json_pretty_print(x)
FROM (SELECT '{"a": 1, "b": [2, 3, 4]}' AS x UNION ALL SELECT '{"a": 1, "b": 2}' AS x)

中に配列などがあると、インデントのレベルが少しわかりづらくなってしまいます
f:id:sucrose:20170617235634p:plain