目次
事例
下記のようにサーバーで発行されたログがDWHに蓄えられているとします。
| id | timestamp | log |
| 1 | 1234 | {"sum_price": 400, "items": [{"item": "りんご", "category": "果物", "num": "3", "price": 100}, {"item": "鉛筆", "category": "文房具", "num": 2, "price": 50}]} |
| 2 | 1345 | {"sum_price": 1500, "items": [{"item": "りんご", "category": "果物", "num": "2", "price": 100}, {"item": "みかん", "category": "果物", "num": 6, "price": 50}, {"item": "洗剤", "category": "日用品", "num": 1, "price": 1000}]} |
このときに、次のように下記のように1つの買い物に対してカテゴリごとに売り上げを集計できるようにしたいとします。
つまり、JSONのitemsのバリューである配列の要素としてあるJSONのcategoryごとにまとめたいとします。
| id | timestamp | category | items | sum_price |
| 1 | 1234 | 果物 | [{"item": "りんご", "category": "果物", "num": "3", "price": 100}] | 300 |
| 1 | 1234 | 文房具 | [{"item": "鉛筆", "category": "文房具", "num": 2, "price": 50}] | 100 |
| 2 | 1345 | 果物 | [{"item": "りんご", "category": "果物", "num": "2", "price": 100}, {"item": "みかん", "category": "果物", "num": 6, "price": 50}] | 500 |
| 2 | 1345 | 日用品 | [{"item": "洗剤", "category": "日用品", "num": 1, "price": 1000}] | 1000 |
環境の用意
MySQLのコンテナを立ち上げ
docker run --platform linux/x86_64 -p 3307:3306 -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:latest
MySQLクライアントから接続
mysql -uroot -pmy-secret-pw -h127.0.0.1 -P3307
mysqlクライアントがない場合には
sudo apt install mysql-client-core-8.0
データの準備
データベース作成
mysql> CREATE DATABASE sample; Query OK, 1 row affected (0.01 sec) mysql> use sample; Database changed
テーブルの設計
mysql> CREATE TABLE `logs` ( `id` INT unsigned NOT NULL AUTO_INCREMENT, `log` JSON, `created_at` TIMESTAMP NOT NULL, UNIQUE KEY `id` (`id`)); Query OK, 0 rows affected (0.02 sec) mysql> show tables; +------------------+ | Tables_in_sample | +------------------+ | logs | +------------------+ 1 row in set (0.00 sec) mysql> show columns in logs; +------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+----------------+ | id | int unsigned | NO | PRI | NULL | auto_increment | | log | json | YES | | NULL | | | created_at | timestamp | NO | | NULL | | +------------+--------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec)
データの挿入
mysql> INSERT INTO logs (log, created_at) VALUES ('{"sum_price": 400, "items": [{"item": "りんご", "category": "果物", "num": "3", "price": 100}, {"item": "鉛筆", "category": "文房具", "num": 2, "price": 50}]}', '2023-01-01 11:11:11'), ('{"sum_price": 1500, "items": [{"item": "りんご", "category": "果物", "num": "2", "price": 100}, {"item": "みかん", "category": "果物", "num": 6, "price": 50}, {"item": "洗剤", "category": "日用品", "num": 1, "price": 1000}]}', '2023-01-02 13:02:10'); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0
JSON型及びTIMESTAMP型のデータの挿入はこの記事を参考に書きました。
dev.mysql.com
dev.mysql.com
データの確認
mysql> SELECT id, created_at, log FROM logs; +----+---------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | id | created_at | log | +----+---------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 1 | 2023-01-01 11:11:11 | {"items": [{"num": "3", "item": "りんご", "price": 100, "category": "果物"}, {"num": 2, "item": "鉛筆", "price": 50, "category": "文房具"}], "sum_price": 400} | | 2 | 2023-01-02 13:02:10 | {"items": [{"num": "2", "item": "りんご", "price": 100, "category": "果物"}, {"num": 6, "item": "みかん", "price": 50, "category": "果物"}, {"num": 1, "item": "洗剤", "price": 1000, "category": "日用品"}], "sum_price": 1500} | +----+---------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
JSONに対する基本操作
JSONのあるキーの値を取得
JSONからキーを指定して値を抽出するのはJSON_EXTRACT(カラム名, '$.キー名')でいけます。
今回はitemsの要素である配列に注目したいためJSON_EXTRACT(log, '$.items')で抽出できます。
mysql> SELECT id, created_at, JSON_EXTRACT(`log`, '$.items') FROM logs; +----+---------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | id | created_at | JSON_EXTRACT(`log`, '$.items') | +----+---------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 1 | 2023-01-01 11:11:11 | [{"num": "3", "item": "りんご", "price": 100, "category": "果物"}, {"num": 2, "item": "鉛筆", "price": 50, "category": "文房具"}] | | 2 | 2023-01-02 13:02:10 | [{"num": "2", "item": "りんご", "price": 100, "category": "果物"}, {"num": 6, "item": "みかん", "price": 50, "category": "果物"}, {"num": 1, "item": "洗剤", "price": 1000, "category": "日用品"}] | +----+---------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
配列を平坦化
このままでは「商品」を表すJSONが「1買い物」を表す配列の中に入っておりカテゴリごとにグループ化ができません。
そこでグループ化できるように、「商品」を表すJSONごとにレコードを作ってみます。
mysql> SELECT logs.id, logs.created_at, t.* FROM logs, JSON_TABLE(log, '$.items[*]' COLUMNS (item varchar(10) PATH '$.item', num int PATH '$.num', price int PATH '$.price', category varchar(10) PATH '$.category')) t; +----+---------------------+-----------+------+-------+-----------+ | id | created_at | item | num | price | category | +----+---------------------+-----------+------+-------+-----------+ | 1 | 2023-01-01 11:11:11 | りんご | 3 | 100 | 果物 | | 1 | 2023-01-01 11:11:11 | 鉛筆 | 2 | 50 | 文房具 | | 2 | 2023-01-02 13:02:10 | りんご | 2 | 100 | 果物 | | 2 | 2023-01-02 13:02:10 | みかん | 6 | 50 | 果物 | | 2 | 2023-01-02 13:02:10 | 洗剤 | 1 | 1000 | 日用品 | +----+---------------------+-----------+------+-------+-----------+ 5 rows in set (0.00 sec)
平坦化の考え方はもとのJSONのレコードと、配列の各要素に分解した値を部分クロス結合するというものです。
分かりやすいように全カラムを表示してみます。
元テーブルの「1買い物」を表す配列に対して配列の要素である「商品」のJSONを部分クロス結合しているに過ぎません。
mysql> SELECT logs.id, logs.created_at, logs.log, t.* FROM logs, JSON_TABLE(log, '$.items[*]' COLUMNS (num varchar(10) PATH '$.num', price int PATH '$.price', category varchar(10) PATH '$.category')) t; +----+---------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+-------+-----------+ | id | created_at | log | num | price | category | +----+---------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+-------+-----------+ | 1 | 2023-01-01 11:11:11 | {"items": [{"num": "3", "item": "りんご", "price": 100, "category": "果物"}, {"num": 2, "item": "鉛筆", "price": 50, "category": "文房具"}], "sum_price": 400} | 3 | 100 | 果物 | | 1 | 2023-01-01 11:11:11 | {"items": [{"num": "3", "item": "りんご", "price": 100, "category": "果物"}, {"num": 2, "item": "鉛筆", "price": 50, "category": "文房具"}], "sum_price": 400} | 2 | 50 | 文房具 | | 2 | 2023-01-02 13:02:10 | {"items": [{"num": "2", "item": "りんご", "price": 100, "category": "果物"}, {"num": 6, "item": "みかん", "price": 50, "category": "果物"}, {"num": 1, "item": "洗剤", "price": 1000, "category": "日用品"}], "sum_price": 1500} | 2 | 100 | 果物 | | 2 | 2023-01-02 13:02:10 | {"items": [{"num": "2", "item": "りんご", "price": 100, "category": "果物"}, {"num": 6, "item": "みかん", "price": 50, "category": "果物"}, {"num": 1, "item": "洗剤", "price": 1000, "category": "日用品"}], "sum_price": 1500} | 6 | 50 | 果物 | | 2 | 2023-01-02 13:02:10 | {"items": [{"num": "2", "item": "りんご", "price": 100, "category": "果物"}, {"num": 6, "item": "みかん", "price": 50, "category": "果物"}, {"num": 1, "item": "洗剤", "price": 1000, "category": "日用品"}], "sum_price": 1500} | 1 | 1000 | 日用品 | +----+---------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+-------+-----------+ 5 rows in set (0.00 sec)
MySQLで配列を平坦にするのは下記の構文で得られます。
JSON_TABLE(JSON型のカラム, 配列となるようなクエリ COLUMNS (tのカラム tのカラムの型 PATH クエリによって得られた1要素に対するクエリ)) AS t
このStackOverflowの例がドキュメントよりも分かりやすかったです。
stackoverflow.com
この操作に関してはAthenaやBigQueryのほうが100倍分かりやすいです。
docs.aws.amazon.com
cloud.google.com
商品のカテゴリごとにグループ化
現在この状態のviewが得られているので、次は問題であった（「1買い物」,「商品のカテゴリ」）ごとにグループ化してみましょう。
まず、品ごとの合計値を求めます。
mysql> SELECT logs.id, logs.created_at, t.*, t.num * t.price AS sum_price FROM logs, JSON_TABLE(log, '$.items[*]' COLUMNS (item varchar(10) PATH '$.item', num int PATH '$.num', price int PATH '$.price', category varchar(10) PA TH '$.category')) t; +----+---------------------+-----------+------+-------+-----------+-----------+ | id | created_at | item | num | price | category | sum_price | +----+---------------------+-----------+------+-------+-----------+-----------+ | 1 | 2023-01-01 11:11:11 | りんご | 3 | 100 | 果物 | 300 | | 1 | 2023-01-01 11:11:11 | 鉛筆 | 2 | 50 | 文房具 | 100 | | 2 | 2023-01-02 13:02:10 | りんご | 2 | 100 | 果物 | 200 | | 2 | 2023-01-02 13:02:10 | みかん | 6 | 50 | 果物 | 300 | | 2 | 2023-01-02 13:02:10 | 洗剤 | 1 | 1000 | 日用品 | 1000 | +----+---------------------+-----------+------+-------+-----------+-----------+ 5 rows in set (0.00 sec)
上記のテーブルをpとしてp.idとp.categoryでグループ化をしp.sum_priceの和をとれば完成です。
mysql> SELECT p.id, p.category, SUM(p.sum_price) FROM (SELECT logs.id, logs.created_at, t.*, t.num * t.price AS sum_price FROM logs, JSON_TABLE(log, '$.items[*]' COLUMNS (item varchar(10) PATH '$.item', num int PATH '$.num', p rice int PATH '$.price', category varchar(10) PATH '$.category')) t) p GROUP BY p.id, p.category; +----+-----------+------------------+ | id | category | SUM(p.sum_price) | +----+-----------+------------------+ | 1 | 果物 | 300 | | 1 | 文房具 | 100 | | 2 | 果物 | 500 | | 2 | 日用品 | 1000 | +----+-----------+------------------+ 4 rows in set (0.00 sec)
