BigQueryでJsonカラム内の配列を集計するSQL

SQLでは扱いJSON型の文字列ですが、BQではJSON関数、UNNEST関数、UDFを組み合わせて使うことでクエリ一本で配列内の集計も出来ます。

テーブル例

テーブル名 : order_table

order_id	order_detail
1	{"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"}]}

json型のカラム

json型のカラム

{
    "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"
}

SQL

全体

CREATE TEMPORARY FUNCTION str_to_array(s STRING)
RETURNS ARRAY<STRUCT<item_id INT64, name STRING, price INT64, quantity INT64>>
LANGUAGE js 
AS "return JSON.parse(s);";

WITH flat_order_detail
AS(
SELECT
  str_to_array(JSON_EXTRACT(order_detail, "$.order_items")) as order_items
from
  `order_table`
)

select
    fod.item_id
  , sum(fod.price) * sum(fod.quantity)
from
  order_table o
  , unnest(flat_order_detail) fod
group by 1
order by 1
;

簡単な説明

SQLではSTRING型からARRAY型への変換はできなかったため、UDFを使う必要があります。

UDFの定義

CREATE TEMPORARY FUNCTION str_to_array(s STRING)
RETURNS ARRAY<STRUCT<item_id INT64, name STRING, price INT64, quantity INT64>>
LANGUAGE js 
AS "return JSON.parse(s);";

1. CREATE TEMPORARY FUNCTION str_to_array(s STRING)

セッション内でのみ有効なUDFを作成します。

2. RETURNS ARRAY<STRUCT<item_id INT64, name STRING, price INT64, quantity INT64>>

UDFの戻り値ですが、STRUCT<カラム名 型>で取り出したい配列内のオブジェクトのデータ型を定義します。

3. LANGUAGE js

UDFは今のところjsのみ対応のようです。

4. AS "return JSON.parse(s);";

関数の実体です。シンプルな内容だったので１行で書いてしまいましたが、"""で囲めば複数行に渡って複雑な関数を書くことも可能です。

SQL（with句）

配列をフラット化する

WITH flat_order_detail
AS(
SELECT
  str_to_array(JSON_EXTRACT(order_detail, "$.order_items")) as order_items
from
  `order_table`
)

読みにくくなりやすいのでWITH句を使うことを推奨します。

ポイントは

str_to_array(JSON_EXTRACT(order_detail, "$.order_items")) as order_items

この部分ですが'JSON_EXTRACT関数'で文字列としてorder_itemsの配列部分を取り出しています。
取得した文字列を先述のUDFを使ってARRAY型に変換します。
CAST(JSON_EXTRACT(order_detail, "$.order_items"), ARRAY<>)は出来ませんでした。

SQL（集計部分)

集計

select
    fod.item_id
  , sum(fod.price) * sum(fod.quantity)
from
  order_table o
  , unnest(flat_order_detail) fod
group by 1
order by 1
;

UNNEST関数を使ってARRAYを展開することでGROUP BYによる集計が可能になります。