MySQLでJSONを扱う入門として 前回の日記 では比較的シンプルな関数群を試してみました。今回は、JSON 内の配列部分をテーブルにして返す JSON_TABLE 関数を試してみます。
JSON_TABLE() の基本的な使い方。
JSON_TABLE()は、JSON内の配列部分を引数に与えて、その配列内の要素をテーブル形式にして返してくれる関数です*1。「1個のJSONデータ」から複数の行が生成されます。
以下は、JSON_TABLE()関数の第一引数に生のJSON配列をそのまま与えてみた例です。
mysql> SELECT * FROM JSON_TABLE('[ '> { '> "DateMillisSinceEpoch": 1650931200000, '> "ConfirmedClinicalDiagnosisSummary": "sample01", '> "RecursiveSummary": null, '> "SelfReportedSummary": null '> }, '> { '> "DateMillisSinceEpoch": 1651017600000, '> "ConfirmedClinicalDiagnosisSummary": null, '> "RecursiveSummary": null, '> "SelfReportedSummary": null '> }, '> { '> "DateMillisSinceEpoch": 1651363200000, '> "ConfirmedClinicalDiagnosisSummary": "sample03", '> "RecursiveSummary": null, '> "SelfReportedSummary": null '> } '> ]', '$[*]' COLUMNS ( -> daily_sum BIGINT PATH '$.DateMillisSinceEpoch', -> confd_diag_sum VARCHAR(20) PATH '$.ConfirmedClinicalDiagnosisSummary' -> ) -> ) t; +---------------+----------------+ | daily_sum | confd_diag_sum | +---------------+----------------+ | 1650931200000 | sample01 | | 1651017600000 | NULL | | 1651363200000 | sample03 | +---------------+----------------+
JSON_TABLE()は、このように、第1引数にJSON配列を、第2引数にはどのJSONパスの内容を何という名前のカラムとして生成するのかを定義する情報を与える使い方ができます。
即値を与えるのじゃ使いにくい
関数の引数にJSONの値をそのまま記述するのは使いにくいので、一歩進めて変数を使うことにした方法です。JSON_TABLE()関数を使用する部分は、少しスッキリしました。
mysql> SET @myjson='[ '> { '> "DateMillisSinceEpoch": 1650931200000, '> "ConfirmedClinicalDiagnosisSummary": "sample01", '> "RecursiveSummary": null, '> "SelfReportedSummary": null '> }, '> { '> "DateMillisSinceEpoch": 1651017600000, '> "ConfirmedClinicalDiagnosisSummary": null, '> "RecursiveSummary": null, '> "SelfReportedSummary": null '> }, '> { '> "DateMillisSinceEpoch": 1651363200000, '> "ConfirmedClinicalDiagnosisSummary": "sample03", '> "RecursiveSummary": null, '> "SelfReportedSummary": null '> } '> ]'; Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM JSON_TABLE(@myjson, '$[*]' COLUMNS ( -> daily_sum BIGINT PATH '$.DateMillisSinceEpoch', -> confd_diag_sum VARCHAR(20) PATH '$.ConfirmedClinicalDiagnosisSummary' -> ) -> ) t; +---------------+----------------+ | daily_sum | confd_diag_sum | +---------------+----------------+ | 1650931200000 | sample01 | | 1651017600000 | NULL | | 1651363200000 | sample03 | +---------------+----------------+ 3 rows in set (0.00 sec)
テーブル上のJSONデータを使用する（トライ1：失敗）
前回の日記で作成した j1 テーブルのデータから配列部分を取ってきて、第1引数に与えることを試みます。配列部分は
j->"$.daily_summaries"
で示されるパスにあるので、これを使ってみたのが以下の例。
mysql> SELECT * FROM JSON_TABLE((SELECT j->"$.daily_summaries" FROM j1), -> '$[*]' COLUMNS ( -> daily_sum BIGINT PATH '$.DateMillisSinceEpoch' -> ) -> ) t; ERROR 1210 (HY000): Incorrect arguments to JSON_TABLE
あらら。エラーです。少しずつ記述法を変えるなどして試したのですが、どうやら第1引数にはサブクエリは（まだ？）採れないようです。
テーブル上のJSONデータを使用する（トライ2：一応成功、ただし格好悪い）
そこで、先ほど試した「一旦変数に入れる」ワザを使います。
mysql> SET @myj:= (SELECT j->"$.daily_summaries" FROM j1); '$[*]' COLUMNS ( daily_sum BIGINT PATH '$.DateMillisSinceEpoch' ) ) t; Query OK, 0 rows affected (0.01 sec)
mysql> SELECT * FROM JSON_TABLE(@myj, -> '$[*]' COLUMNS ( -> daily_sum BIGINT PATH '$.DateMillisSinceEpoch' -> ) -> ) t; +---------------+ | daily_sum | +---------------+ | 1650931200000 | | 1651017600000 | | 1651363200000 | | 1651449600000 | （略） | 1657756800000 | | 1657843200000 | | 1658102400000 | +---------------+ 32 rows in set (0.00 sec)
一応成功しました。
テーブル上のJSONデータを使用する（トライ3：一応成功、ただし納得してない）
先ほどの例では、やりたいことは実現できましたが、一旦変数に入れるあたりがRDBMSっぽくなくて格好悪いです。べつにカッコつける必要もないのだけど。
ネットを見ていると、JSONデータを格納しているテーブルそのものと、JSON_TABLE()によって作られたテーブルとをJOINすれば良いらいしいということでやってみたら、それっぽい結果に。
mysql> SELECT j1b.* -> FROM j1 -> JOIN JSON_TABLE(j1.j->"$.daily_summaries", -> '$[*]' COLUMNS ( -> daily_sum BIGINT PATH '$.DateMillisSinceEpoch', -> score_sum INTEGER PATH '$.ConfirmedTestSummary.ScoreSum' -> ) -> ) j1b; +---------------+-----------+ | daily_sum | score_sum | +---------------+-----------+ | 1650931200000 | 60 | | 1651017600000 | 60 | | 1651363200000 | 60 | | 1657670400000 | 600 | （略） | 1658102400000 | 60 | +---------------+-----------+ 32 rows in set (0.00 sec)
ただし、これ、結合条件を書いていないところが気持ち悪くて、たぶん今回レコードが1件しかないおかげでまともに動作しているように見えるだけなのかも、、、という気もしています。必要なときにもう少しツッコんで調べることにします。
最終目的地へ
もともとは、「COCOAアプリが吐き出したJSONデータを、MySQLを使って見てみたい」ということで、今回の「お勉強」が始まったのでした。ということで、最終目的地のひとつに到着します。先ほど得たデータは、日ごとの陽性者との接触時間を表したデータでした。daily_sum として取得した列は、unix timestamp のミリ秒なので、演算を加えて日付にしてみました。ということで、GO！
mysql> SELECT DATE(FROM_UNIXTIME(j1b.daily_sum/1000)) dt, j1b.score_sum -> FROM j1 -> JOIN JSON_TABLE(j1.j->"$.daily_summaries", -> '$[*]' COLUMNS ( -> daily_sum BIGINT PATH '$.DateMillisSinceEpoch', -> score_sum INTEGER PATH '$.ConfirmedTestSummary.ScoreSum' -> ) -> ) j1b -> ; +------------+-----------+ | dt | score_sum | +------------+-----------+ | 2022-04-26 | 60 | | 2022-04-27 | 60 | | 2022-05-01 | 60 | | 2022-05-02 | 840 | | 2022-05-05 | 60 | | 2022-05-06 | 60 | | 2022-05-08 | 60 | | 2022-05-10 | 60 | | 2022-05-25 | 60 | | 2022-06-05 | 60 | | 2022-06-07 | 300 | | 2022-06-08 | 240 | | 2022-06-09 | 60 | | 2022-06-12 | 60 | | 2022-06-13 | 60 | | 2022-06-14 | 60 | | 2022-06-16 | 60 | | 2022-06-20 | 60 | | 2022-06-24 | 60 | | 2022-06-30 | 60 | | 2022-07-01 | 60 | | 2022-07-03 | 60 | | 2022-07-04 | 60 | | 2022-07-06 | 60 | | 2022-07-07 | 60 | | 2022-07-08 | 60 | | 2022-07-11 | 18840 | | 2022-07-12 | 60 | | 2022-07-13 | 600 | | 2022-07-14 | 60 | | 2022-07-15 | 60 | | 2022-07-18 | 60 | +------------+-----------+ 32 rows in set (0.00 sec)
7/11 に、大変たくさんの時間、陽性の方と過ごしたようです。 18840 という数字については、もう少し COCOAファイルの定義書を読む必要がありますが、スマホの画面に表示された値を見比べると、この値を 24 で割ると、接触していた分数（の合計）になるようです。
（ちなみに、幸いにもこの接触による私への感染は、なかったようです（あれから2週間以上経過））
おわりに
こんな感じで、JSON_TABLE()を使うと、ひとつのJSONデータから、テーブルの形式に変換して値を抽出することができるようになることがわかりました。 今回は1件のデータだけを持つテーブルを元にして試したので、今後、複数レコードあるケースなど、もうすこし試してみるべきかもしれません。（個人的にはここで、取得に関しては一旦おしまいにします）
せっかく触り始めたので、生成系の関数も試してみたくなってきました。そのうち試してみたいと思います。
*1:実際はもうちょっといろいろなものを与えられます。詳しくはマニュアル参照
