MySQLでJSON体験(その3:JSONを生成する)

 今回は MySQLを使ってJSON形式のデータを作ることにトライしてみました。正直よくわからんけど、まぁやりたいことは一応実現可能なのかな、といったレベルの理解です。

目標とするデータ形式

 Cocoaのデータっぽい形式を目指すことにします。細かい部分で違ったり端折ったりするので、まったく同じものを目指すわけではありません。

基本的なJSON要素の生成

 MySQLでのJSONデータの生成では、「JSON配列の生成」と「JSONオブジェクトの生成」があります。今回目標とする形式の大枠は、名前と値がセットになった「JSONオブジェクト」なので、JSON_OBJECT()関数を使います。
 引数にはひたすら、名前,値,名前,値,名前,値,....の繰り返しを記述します。

mysql> SELECT JSON_OBJECT('platform', 'iOS', 'model', 'iPhone13,2', 'app_version', '2.0.1') j;
+--------------------------------------------------------------------+
| j                                                                  |
+--------------------------------------------------------------------+
| {"model": "iPhone13,2", "platform": "iOS", "app_version": "2.0.1"} |
+--------------------------------------------------------------------+

platform/model/app_version の3つの要素を持つJSONデータができました。

見やすくするJSON関数

 上で試した出力結果は、JSONデータが1行のデータとして(改行抜きで)表示されています。JSON_PRETTY()関数を使うと、良い感じに改行とインデントを入れて見やすくしてくれるので、今後はこれを使うことにします。併せて、文末を ; ではなく \G で見やすく出力することにします。

mysql> SELECT JSON_PRETTY(JSON_OBJECT('platform', 'iOS', 'model', 'iPhone13,2', 'app_version', '2.0.1')) j\G
*************************** 1. row ***************************
j: {
  "model": "iPhone13,2",
  "platform": "iOS",
  "app_version": "2.0.1"
}

配列要素の追加

 次に daily_summaries のブロックの生成を試みます。 daily_summaries はJSONオブジェクトを複数持つ配列なので、入れ子にする形で JSON_ARRAY()関数を使います。その各要素オブジェクトの中には更にJSONオブジェクトを持つのですが、一段階ずつトライしていきたいので、一旦「ObjTodo」という文字列を入れておくことにします。

mysql> SELECT JSON_PRETTY(
    ->   JSON_OBJECT('platform', 'iOS', 
    ->               'model', 'iPhone13,2',
    ->               'app_version', '2.0.1',
    ->               'daily_summaries', 
    ->                   JSON_ARRAY(
    ->                     JSON_OBJECT('DateMillisSinceEpoch',1650931200000,
    ->                                 'DaySummary','ObjTodo', 
    ->                                 'RecursiveSummary',null),
    ->                     JSON_OBJECT('DateMillisSinceEpoch',1651017600000,'DaySummary','ObjTodo','RecursiveSummary',null),
    ->                     JSON_OBJECT('DateMillisSinceEpoch',1651363200000,'DaySummary','ObjTodo','RecursiveSummary',null)
    ->                   )
    ->               )
    ->               ) j\G
*************************** 1. row ***************************
j: {
  "model": "iPhone13,2",
  "platform": "iOS",
  "app_version": "2.0.1",
  "daily_summaries": [
    {
      "DaySummary": "ObjTodo",
      "RecursiveSummary": null,
      "DateMillisSinceEpoch": 1650931200000
    },
    {
      "DaySummary": "ObjTodo",
      "RecursiveSummary": null,
      "DateMillisSinceEpoch": 1651017600000
    },
    {
      "DaySummary": "ObjTodo",
      "RecursiveSummary": null,
      "DateMillisSinceEpoch": 1651363200000
    }
  ]
}

先延ばしにしていたオブジェクト内オブジェクトを記述

 先ほど ObjTodo としていたdaily_summaries内のDaySummaryも記述してみます。記述は面倒くさいですが、素直に入れ子にして書いていくだけですね。

mysql> SELECT JSON_PRETTY(
    ->   JSON_OBJECT('platform', 'iOS', 
    ->               'model', 'iPhone13,2',
    ->               'app_version', '2.0.1',
    ->               'daily_summaries', 
    ->                   JSON_ARRAY(
    ->                     JSON_OBJECT('DateMillisSinceEpoch',1650931200000,
    ->                                 'DaySummary',JSON_OBJECT('MaximumScore', 60.0, 
    ->                                                          'ScoreSum', 60.0,
    ->                                                          'WeightedDurationSum',60.0),
    ->                                 'RecursiveSummary',null),
    ->                     JSON_OBJECT('DateMillisSinceEpoch',1651017600000,
    ->                                 'DaySummary',JSON_OBJECT('MaximumScore', 60.0, 
    ->                                                          'ScoreSum', 60.0,
    ->                                                          'WeightedDurationSum',60.0),
    ->                                 'RecursiveSummary',null),
    ->                     JSON_OBJECT('DateMillisSinceEpoch',1651363200000,
    ->                                 'DaySummary',JSON_OBJECT('MaximumScore', 60.0, 
    ->                                                          'ScoreSum', 60.0,
    ->                                                          'WeightedDurationSum',60.0),
    ->                                 'RecursiveSummary',null)
    ->                   )
    ->               )
    ->               ) j\G
*************************** 1. row ***************************
j: {
  "model": "iPhone13,2",
  "platform": "iOS",
  "app_version": "2.0.1",
  "daily_summaries": [
    {
      "DaySummary": {
        "ScoreSum": 60.0,
        "MaximumScore": 60.0,
        "WeightedDurationSum": 60.0
      },
      "RecursiveSummary": null,
      "DateMillisSinceEpoch": 1650931200000
    },
    {
      "DaySummary": {
        "ScoreSum": 60.0,
        "MaximumScore": 60.0,
        "WeightedDurationSum": 60.0
      },
      "RecursiveSummary": null,
      "DateMillisSinceEpoch": 1651017600000
    },
    {
      "DaySummary": {
        "ScoreSum": 60.0,
        "MaximumScore": 60.0,
        "WeightedDurationSum": 60.0
      },
      "RecursiveSummary": null,
      "DateMillisSinceEpoch": 1651363200000
    }
  ]
}

数値の桁についてちょっと寄り道

 先ほど生成したJSONデータ。 小数点以下ゼロの値を与えたときに、そのまま生成されていました。内部で数値として解釈されて小数点以下のゼロは省略して出力されることを予想していたので、やや意外でした。
 ちょっとした追試を。

mysql> SELECT JSON_PRETTY(
    ->   JSON_OBJECT('platform', 'iOS', 
    ->               'daily_summaries', 
    ->                   JSON_ARRAY(
    ->                     JSON_OBJECT('DateMillisSinceEpoch',1650931200000,
    ->                                 'DaySummary',JSON_OBJECT('MaximumScore', 60.0, 
    ->                                                          'ScoreSum', 60.00,
    ->                                                          'WeightedDurationSum',60),
    ->                                 'RecursiveSummary',null)
    ->                   )
    ->               )
    ->               ) j\G
*************************** 1. row ***************************
j: {
  "platform": "iOS",
  "daily_summaries": [
    {
      "DaySummary": {
        "ScoreSum": 60.00,
        "MaximumScore": 60.0,
        "WeightedDurationSum": 60
      },
      "RecursiveSummary": null,
      "DateMillisSinceEpoch": 1650931200000
    }
  ]
}

 DaySummaryの中身の数字が、与えたとおりの桁数で生成されましたね。内部でも一貫して文字列として扱われているのかしらん。。(結論なし)

テーブルデータからの生成トライ(テーブルとデータの準備)

 ここまで試してきたJSON生成は、関数の引数に、名前,値,名前,値・・の順に与え続ける必要があるものでした。テーブルからデータを取得して生成したいときはどうしたらいいのか、試みました。
 まずデータを用意します。実は、DaySummary 要素は3つの要素を単にグルーピングするだけのもので、実は daily_summaries 要素と 1対1 なので、テーブルを分ける必要がまったくなかったことに後で気づきましたが、今回はまぁこのままで。

CREATE TABLE cocoa_main (
    id INTEGER PRIMARY KEY, 
    platform VARCHAR(10),
    model VARCHAR(30),
    app_version VARCHAR(10)
);
INSERT INTO cocoa_main VALUES (1, 'iOS', 'iPhone13,2', '2.0.1');
CREATE TABLE cocoa_daily_sum (
    id INTEGER PRIMARY KEY,
    main_id INTEGER,
    DateMillisSinceEpoch BIGINT,
    RecursiveSummary VARCHAR(10)
);
INSERT INTO cocoa_daily_sum VALUES 
  (11, 1, 1650931200000, null),
  (12, 1, 1651017600000, null),
  (13, 1, 1651363200000, null);
CREATE TABLE cocoa_daySummary (
  id INTEGER AUTO_INCREMENT PRIMARY KEY,
  daily_sum_id INTEGER,
  MaximumScore FLOAT,
  ScoreSum  FLOAT,
  WeightedDurationSum  FLOAT
);
INSERT INTO cocoa_daySummary VALUES 
  (null, 11, 60.0, 60.0, 60.0),
  (null, 12, 120.0, 60.0, 180.0),
  (null, 13, 4620.0, 18840.0, 18840.0);
mysql> SELECT * FROM cocoa_main;
+----+----------+------------+-------------+
| id | platform | model      | app_version |
+----+----------+------------+-------------+
|  1 | iOS      | iPhone13,2 | 2.0.1       |
+----+----------+------------+-------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM cocoa_daily_sum;
+----+---------+----------------------+------------------+
| id | main_id | DateMillisSinceEpoch | RecursiveSummary |
+----+---------+----------------------+------------------+
| 11 |       1 |        1650931200000 | NULL             |
| 12 |       1 |        1651017600000 | NULL             |
| 13 |       1 |        1651363200000 | NULL             |
+----+---------+----------------------+------------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM cocoa_daySummary;
+----+--------------+--------------+----------+---------------------+
| id | daily_sum_id | MaximumScore | ScoreSum | WeightedDurationSum |
+----+--------------+--------------+----------+---------------------+
|  1 |           11 |           60 |       60 |                  60 |
|  2 |           12 |          120 |       60 |                 180 |
|  3 |           13 |         4620 |    18840 |               18840 |
+----+--------------+--------------+----------+---------------------+
3 rows in set (0.00 sec)

テーブルからシンプル部分のJSON生成

 冒頭の即値を与えたときと同様に、シンプルな部分だけまず生成してみます。JSON_OBJECT()関数の引数として、名前とカラムを交互に羅列していきます。

mysql> SELECT JSON_PRETTY(
    ->   JSON_OBJECT(
    ->     'platform', platform,
    ->     'model', model,
    ->     'app_version', app_version
    ->   ) 
    -> ) j FROM cocoa_main\G
*************************** 1. row ***************************
j: {
  "model": "iPhone13,2",
  "platform": "iOS",
  "app_version": "2.0.1"
}

入れ子となった daily_summariesを生成

 次に、cocoa_daily_sum テーブルを結合して、結果をJSON配列として含ませるトライです。
結合しっぱなしではレコードが3件に分かれてしまうので、cocoa_main の id列でグルーピングすることと、入れ子部分の配列には JSON_ARRAYAGG() という集約関数を使っているところがポイントです。
 ARRAYAGGで生成する配列の中身には、JSON_OBJECTが入るので、JSON_OBJECT()関数で各要素を作ることになります。(答えにたどり着いてしまえば「そのとおり」な書き方ですが、ここにたどり着くまでに結構な時間をつぶしてしまいました・・・・。JSON_OBJECTAGG()や JSON_ARRAYAGG()にはウィンドウ関数を使うこともできるため、そちらの迷路に迷い込んでしまったりなどなど。 将棋の先生方が「指されてみればなるほどという手です」とよく言いますが、それに近い心境かもしれません)

mysql> SELECT JSON_PRETTY(
    ->   JSON_OBJECT(
    ->     'platform', platform,
    ->     'model', model,
    ->     'app_version', app_version,
    ->     'daily_summaries', JSON_ARRAYAGG( JSON_OBJECT('DateMillisSinceEpoch', ds.DateMillisSinceEpoch, 'RecursiveSummary', ds.RecursiveSummary))
    ->   ) 
    -> ) j FROM cocoa_main m
    -> LEFT OUTER JOIN cocoa_daily_sum ds ON (m.id=ds.main_id)
    -> GROUP BY m.id\G
*************************** 1. row ***************************
j: {
  "model": "iPhone13,2",
  "platform": "iOS",
  "app_version": "2.0.1",
  "daily_summaries": [
    {
      "RecursiveSummary": null,
      "DateMillisSinceEpoch": 1651363200000
    },
    {
      "RecursiveSummary": null,
      "DateMillisSinceEpoch": 1651017600000
    },
    {
      "RecursiveSummary": null,
      "DateMillisSinceEpoch": 1650931200000
    }
  ]
}

テーブルからの生成、最後の仕上げ

 配列の各要素となるJSONオブジェクトの中には、更に入れ子があります。内側にある DaySummaryオブジェクトも出力に追加して、一連のトライを終了としましょう。
 結局やっていることは、JSON_OBJECT中に DaySummaryという要素名を持つJSONオブジェクトをもういっこ追加しているというだけです。本節冒頭で述べたようにこのオブジェクトは実は単に見かけ上のグルーピングをしているだけであり、実態は 1対1なので、AGG系関数を使用することなく記述できています。

mysql>  SELECT JSON_PRETTY(
    ->    JSON_OBJECT(
    ->      'platform', platform,
    ->      'model', model,
    ->      'app_version', app_version,
    ->      'daily_summaries', JSON_ARRAYAGG( 
    ->                             JSON_OBJECT('DateMillisSinceEpoch', ds.DateMillisSinceEpoch, 
    ->                                         'RecursiveSummary', ds.RecursiveSummary,
    ->                                         'DaySummary', JSON_OBJECT('MaximumScore',MaximumScore, 
    ->                                                                   'ScoreSum', ScoreSum, 
    ->                                                                   'WeightedDurationSum', WeightedDurationSum) )
    ->                                       )
    ->    ) 
    ->  ) j FROM cocoa_main m
    ->  LEFT OUTER JOIN cocoa_daily_sum ds ON (m.id=ds.main_id)
    ->  LEFT OUTER JOIN cocoa_daySummary das ON (ds.id=das.daily_sum_id)
    ->  GROUP BY m.id\G
*************************** 1. row ***************************
j: {
  "model": "iPhone13,2",
  "platform": "iOS",
  "app_version": "2.0.1",
  "daily_summaries": [
    {
      "DaySummary": {
        "ScoreSum": 18840.0,
        "MaximumScore": 4620.0,
        "WeightedDurationSum": 18840.0
      },
      "RecursiveSummary": null,
      "DateMillisSinceEpoch": 1651363200000
    },
    {
      "DaySummary": {
        "ScoreSum": 60.0,
        "MaximumScore": 120.0,
        "WeightedDurationSum": 180.0
      },
      "RecursiveSummary": null,
      "DateMillisSinceEpoch": 1651017600000
    },
    {
      "DaySummary": {
        "ScoreSum": 60.0,
        "MaximumScore": 60.0,
        "WeightedDurationSum": 60.0
      },
      "RecursiveSummary": null,
      "DateMillisSinceEpoch": 1650931200000
    }
  ]
}

感想

 JSON生成の入り口から始まり、テーブル上のデータを元にしてJSON形式にまとめて結果を返す、というトライをしてみましたが、実際のプロダクトで「MySQLからJSON形式でデータを取得したい」ということになってこのSQLを書いた場合、正直言ってこのSQLをメンテナンスできる自信はありません。今回は「ようやくとりあえず動くものにはなった」という段階だったので、理解が進むともう少し洗練された書き方ができるようになるのかもしれませんが・・・・。
 あとは、入れ子になっているように見えて、実は単なるグループにしてみたいだけという例もあり(姓と名という要素があるときに、それらをまとめて「氏名」というJSONオブジェクトにするみたな感じ)、入れ子の深さとDB的なデータの持ち方に、直接の関係はないのだなという印象を受けました。このあたりは、様々なデータを触っているうちに、肌感覚のようなもので見えてくることがあるかもしれません。

 こういうJSON生成は、DBMSでやるよりも、ORマッパなどで各テーブルからの結果をオブジェクトに入れてそちらで生成させるほうが、得意かもしれませんね。 一応今回は、MySQLで生成する場合というトライでした。実運用でこういった事をやられている方がいましたら、ぜひブログとかで使用例などを聞かせてください。

Dolphins image by DALL-E