[datahub]クエリで配列になっているJSONデータをレコードに変換したい

Open Q&A
1

以下のようなJSONデータをテーブルのカラムに持っています。このデータをPKのcustomer_idのカラムと一緒にレコードに変換したいのですが、JavascriptUDFが使用不可であるためJSON.parse(s)でSTRUCTに変換できませんでした。JSONpathとして「…」「*」で配列として取得して処理しようと思いましたがこちらも「Unsupported operator」とエラーになるため使用できませんでした。何か方法をご存じの方がおられましたらご教授頂けないでしょうか？
{
“customer_id”: 1,
“order_items”:[
{
“item_id”: “A”,
“name”: “あああ”,
“price”: “80”,
“quantity”: 2,
},
{
“item_id”: “B”,
“name”: “いいい”,
“price”: “100”,
“quantity”: 1,
},
{
“item_id”: “C”,
“name”: “ううう”,
“price”: “300”,
“quantity”: 2,
}
],
“order_date”: “2017-01-01”
}

2

面倒なところですよね・・
クエリコレクション内のクエリにも似たような事をやっているクエリを見つけました

あまりかっこいい実装ではありませんが
以下のような形で、正規表現でとってきて配列化して利用しているみたいです。

REGEXP_EXTRACT_ALL(JSON_EXTRACT(values, '$.event_name.event_filed'), r'"item_id":"([^"]+)"') AS item_ids
3

akibaさん

回答ありがとうございます。教えて頂いた方法でできました。非常に助かりました。

ちょっとJSONの値は追加しましたが、同じような形式でKARTEのクエリエディタに張り付ければ動くものを投稿しておきます。

WITH TEST_JSON AS (
select 1 AS key_data,
‘{“user_id”:1,“order_items”:[{“item_id”:“A”,“name”:“あああ”,“price”:“80”,“quantity”:2,“order_date”:“2017-01-01”},{“item_id”:“B”,“name”:“いいい”,“price”:“100”,“quantity”:1,“order_date”:“2017-02-01”},{“item_id”:“C”,“name”:“ううう”,“price”:“300”,“quantity”:2,“order_date”:“2017-04-01”}]}’ AS value
)
, array_data AS (
select
key_data, /* value, */
REGEXP_EXTRACT_ALL(JSON_EXTRACT(value, ‘.order_items'), r'"item_id":"([^"]+)"') AS item_ids, REGEXP_EXTRACT_ALL(JSON_EXTRACT(value, '.order_items’), r’“name”:"([^"]+)"’) AS names,
REGEXP_EXTRACT_ALL(JSON_EXTRACT(value, ‘.order_items'), r'"price":"([^"]+)"') AS prices, REGEXP_EXTRACT_ALL(JSON_EXTRACT(value, '.order_items’), r’“quantity”:([0-9]+)’) AS quantities,
REGEXP_EXTRACT_ALL(JSON_EXTRACT(value, ‘$.order_items’), r’“order_date”:"([^"]+)"’) AS order_dates
from TEST_JSON
)
select
key_data,
item,
names[SAFE_OFFSET(offset)] AS name,
prices[SAFE_OFFSET(offset)] AS price,
quantities[SAFE_OFFSET(offset)] AS quantity,
order_dates[SAFE_OFFSET(offset)] AS order_date
from array_data,
UNNEST(array_data.item_ids) AS item WITH OFFSET AS offset
;

最終的に完了した後で、個人的に思うところとしてはなんだか実現するための書き方としても、
成果物の可読性という意味でもやりたいことに対して複雑すぎるような気がしました。
将来的にはJavascriptUDFとか使えるようにならないかと期待したいところです。

最後にご相談に乗って頂きありがとうございました。

「いいね！」 2
4

とても良くわかります・・

BigQueryのセル内でのJSONデータの取り扱いは、KARTEにおいてめちゃくちゃたくさんあるので
UDFでもいいですし、KARTE側でそれ用の関数を用意するでもいいですが
何かしら簡便化されてほしいものです