グローバルナビゲーションへ

本文へ

フッターへ

お役立ち情報Blog

MySQLのJSON型に含まれる値でソートをかける方法 (数値・文字列・Goのtime.Time型)

2021.08.02

WEB

システム開発

MySQLのJSON型に含まれている中の値で、ソートをかける方法についてまとめたいと思います。 JSON型に変換したGoのtime.Time型をソートする方法も後半でご紹介します。

準備

まずはMySQLのコンテナとサンプルデータを準備していきます。
DockerでMySQLのコンテナをたてます。(imageはを以下のものを使用します。)

DockerHub/mysql
https://hub.docker.com/_/mysql
$ docker pull mysql
$ docker run -it --name test -e MYSQL_ROOT_PASSWORD=mysql -d mysql:latest

コンテナの準備ができたらコンテナでMySQLに接続します。
今回使用するMySQLのバージョンは以下の通りです。

$ mysql --version
mysql  Ver 8.0.25 for Linux on x86_64 (MySQL Community Server - GPL)

ソート対象のサンプルデータを用意します。
timeに指定されている時間は、Goのtime.Time型をMarshalして算出した値です。
また、0001-01-01T00:00:00Zの値はtime.Time型のゼロ値です。

mysql> CREATE TABLE data (content JSON);
mysql> INSERT INTO data VALUES
('{"type": 1, "name": "aaa", "available": true, "time": "2021-03-25T00:00:00+09:00"}'),
('{"type": 2, "name": "bbb", "available": true, "time": "2021-04-25T00:00:00+09:00"}'),
('{"type": 3, "name": "ccc", "available": true, "time": "2021-05-25T00:00:00+09:00"}'),
('{"type": 4, "name": "ddd", "available": true, "time": "2021-03-01T00:00:00+09:00"}'),
('{"type": 1, "name": "ccc", "available": false, "time": "2021-03-02T00:00:00+09:00"}'),
('{"type": 2, "name": "ddd", "available": false, "time": "2021-03-02T09:25:00+09:00"}'),
('{"type": 3, "name": "bbb", "available": false, "time": "2021-03-02T09:25:01+09:00"}'),
('{"type": 4, "name": "aaa", "available": false, "time": "0001-01-01T00:00:00Z"}'),
('{"type": 1, "name": "eee", "available": true, "time": "0001-01-01T00:00:00Z"}'),
('{"type": 2, "name": "eee", "available": false, "time": "2021-06-25T00:00:00+09:00"}');

永続化した値を確認してみます。

mysql> SELECT * FROM data;
+-------------------------------------------------------------------------------------+
| content                                                                             |
+-------------------------------------------------------------------------------------+
| {"name": "aaa", "time": "2021-03-25T00:00:00+09:00", "type": 1, "available": true}  |
| {"name": "bbb", "time": "2021-04-25T00:00:00+09:00", "type": 2, "available": true}  |
| {"name": "ccc", "time": "2021-05-25T00:00:00+09:00", "type": 3, "available": true}  |
| {"name": "ddd", "time": "2021-03-01T00:00:00+09:00", "type": 4, "available": true}  |
| {"name": "ccc", "time": "2021-03-02T00:00:00+09:00", "type": 1, "available": false} |
| {"name": "ddd", "time": "2021-03-02T09:25:00+09:00", "type": 2, "available": false} |
| {"name": "bbb", "time": "2021-03-02T09:25:01+09:00", "type": 3, "available": false} |
| {"name": "aaa", "time": "0001-01-01T00:00:00Z", "type": 4, "available": false}      |
| {"name": "eee", "time": "0001-01-01T00:00:00Z", "type": 1, "available": true}       |
| {"name": "eee", "time": "2021-06-25T00:00:00+09:00", "type": 2, "available": false} |
+-------------------------------------------------------------------------------------+

JSON_EXTRACTを使用する方法

JSON_EXTRACTを使用してソートをかけてみたいと思います。
使い方の詳細についてはMySQLリファレンスマニュアルを参考にしてください。

“type”を昇順に並べ替えてみます。

mysql> SELECT * FROM data ORDER BY JSON_EXTRACT(content, "$.type") ASC;
+-------------------------------------------------------------------------------------+
| content                                                                             |
+-------------------------------------------------------------------------------------+
| {"name": "aaa", "time": "2021-03-25T00:00:00+09:00", "type": 1, "available": true}  |
| {"name": "ccc", "time": "2021-03-02T00:00:00+09:00", "type": 1, "available": false} |
| {"name": "eee", "time": "0001-01-01T00:00:00Z", "type": 1, "available": true}       |
| {"name": "bbb", "time": "2021-04-25T00:00:00+09:00", "type": 2, "available": true}  |
| {"name": "ddd", "time": "2021-03-02T09:25:00+09:00", "type": 2, "available": false} |
| {"name": "eee", "time": "2021-06-25T00:00:00+09:00", "type": 2, "available": false} |
| {"name": "ccc", "time": "2021-05-25T00:00:00+09:00", "type": 3, "available": true}  |
| {"name": "bbb", "time": "2021-03-02T09:25:01+09:00", "type": 3, "available": false} |
| {"name": "ddd", "time": "2021-03-01T00:00:00+09:00", "type": 4, "available": true}  |
| {"name": "aaa", "time": "0001-01-01T00:00:00Z", "type": 4, "available": false}      |
+-------------------------------------------------------------------------------------+

“type”が1〜4で昇順にソートされたことが確認できました。
あわせて”available”にもソートをかけてみます。

mysql> SELECT * FROM data ORDER BY JSON_EXTRACT(content, "$.type") ASC, JSON_EXTRACT(content, "$.available") DESC;
+-------------------------------------------------------------------------------------+
| content                                                                             |
+-------------------------------------------------------------------------------------+
| {"name": "aaa", "time": "2021-03-25T00:00:00+09:00", "type": 1, "available": true}  |
| {"name": "eee", "time": "0001-01-01T00:00:00Z", "type": 1, "available": true}       |
| {"name": "ccc", "time": "2021-03-02T00:00:00+09:00", "type": 1, "available": false} |
| {"name": "bbb", "time": "2021-04-25T00:00:00+09:00", "type": 2, "available": true}  |
| {"name": "ddd", "time": "2021-03-02T09:25:00+09:00", "type": 2, "available": false} |
| {"name": "eee", "time": "2021-06-25T00:00:00+09:00", "type": 2, "available": false} |
| {"name": "ccc", "time": "2021-05-25T00:00:00+09:00", "type": 3, "available": true}  |
| {"name": "bbb", "time": "2021-03-02T09:25:01+09:00", "type": 3, "available": false} |
| {"name": "ddd", "time": "2021-03-01T00:00:00+09:00", "type": 4, "available": true}  |
| {"name": "aaa", "time": "0001-01-01T00:00:00Z", "type": 4, "available": false}      |
+-------------------------------------------------------------------------------------+

同じ”type”の中で、true>falseの順番になっているので、”available”にもソートがかけられたことが確認できました。

「->」演算子を使用する方法

先ほどはJSON_EXTRACTを使用しましたが、「->」演算子を使用することもできます。 JSON_EXTRACTの省略記法なので、同様の機能を果たします。

リファレンスにも以下のような記述があります。

-> 演算子は、左側のカラム識別子とJSON ドキュメント(カラム値)に対して評価される右側のJSONパスの2つの引数で使用されるJSON_EXTRACT()関数のエイリアスとして機能します。 このような式は、SQL ステートメントのどこにあるかにかかわらず、カラム識別子のかわりに使用できます。MySQLリファレンスマニュアル
mysql> SELECT * FROM data ORDER BY content->"$.type" ASC, content->"$.available" DESC;
+-------------------------------------------------------------------------------------+
| content                                                                             |
+-------------------------------------------------------------------------------------+
| {"name": "aaa", "time": "2021-03-25T00:00:00+09:00", "type": 1, "available": true}  |
| {"name": "eee", "time": "0001-01-01T00:00:00Z", "type": 1, "available": true}       |
| {"name": "ccc", "time": "2021-03-02T00:00:00+09:00", "type": 1, "available": false} |
| {"name": "bbb", "time": "2021-04-25T00:00:00+09:00", "type": 2, "available": true}  |
| {"name": "ddd", "time": "2021-03-02T09:25:00+09:00", "type": 2, "available": false} |
| {"name": "eee", "time": "2021-06-25T00:00:00+09:00", "type": 2, "available": false} |
| {"name": "ccc", "time": "2021-05-25T00:00:00+09:00", "type": 3, "available": true}  |
| {"name": "bbb", "time": "2021-03-02T09:25:01+09:00", "type": 3, "available": false} |
| {"name": "ddd", "time": "2021-03-01T00:00:00+09:00", "type": 4, "available": true}  |
| {"name": "aaa", "time": "0001-01-01T00:00:00Z", "type": 4, "available": false}      |
+-------------------------------------------------------------------------------------+

先程の、JSON_EXTRACTと同様の結果を得ることができました。 JSON_EXTRACTに比べて、「->」演算子の方がSQL文の記述量が少なくなるので、可読性が上がると思います。

Goのtime.Time型をMySQLのDATETIME型にキャストしてソートする方法

timeも先ほど同様に記述すればソートはできますが、あくまでもJSON内の”文字列”での比較となってしまいます。

※正確には、公式リファレンス に以下のように記述されています。

文字列は、比較される2つの文字列のutf8mb4表現の最初のNバイトで字句的に順序付けされます(N は短い文字列の長さです)。 2つの文字列の最初のNバイトが同一の場合、短い文字列は長い文字列より小さいとみなされます。MySQLリファレンスマニュアル

正しく比較するにはMySQLのDATETIME型にキャストすることが安全かと思いますので、実装してみます。
 -> は値を抽出するだけの演算子ですが、 ->> は抽出された結果を引用符で囲まない演算子です。

mysql> SELECT * FROM data ORDER BY CAST(content->>"$.time" AS DATETIME) ASC;
+-------------------------------------------------------------------------------------+
| content                                                                             |
+-------------------------------------------------------------------------------------+
| {"name": "aaa", "time": "0001-01-01T00:00:00Z", "type": 4, "available": false}      |
| {"name": "eee", "time": "0001-01-01T00:00:00Z", "type": 1, "available": true}       |
| {"name": "ddd", "time": "2021-03-01T00:00:00+09:00", "type": 4, "available": true}  |
| {"name": "ccc", "time": "2021-03-02T00:00:00+09:00", "type": 1, "available": false} |
| {"name": "ddd", "time": "2021-03-02T09:25:00+09:00", "type": 2, "available": false} |
| {"name": "bbb", "time": "2021-03-02T09:25:01+09:00", "type": 3, "available": false} |
| {"name": "aaa", "time": "2021-03-25T00:00:00+09:00", "type": 1, "available": true}  |
| {"name": "bbb", "time": "2021-04-25T00:00:00+09:00", "type": 2, "available": true}  |
| {"name": "ccc", "time": "2021-05-25T00:00:00+09:00", "type": 3, "available": true}  |
| {"name": "eee", "time": "2021-06-25T00:00:00+09:00", "type": 2, "available": false} |
+-------------------------------------------------------------------------------------+

日付でソートして出力することができました。

まとめ

MySQLにJSON型で永続化している場合は、指定した値の取得であっても一手間必要になります。 値の抽出やソートについて、公式のリファレンスには様々なケースが記載されているので、より良い書き方があれば取り入れていきたいと思います。

MySQL 8.0 リファレンスマニュアル
https://dev.mysql.com/doc/refman/8.0/ja/json-functions.html

この記事を書いた人

アーティス
アーティス
創造性を最大限に発揮するとともに、インターネットに代表されるITを活用し、みんなの生活が便利で、豊かで、楽しいものになるようなサービスやコンテンツを考え、創り出し提供しています。
この記事のカテゴリ
WEB システム開発
RECOMMENDED

関連記事

【Go】ベンチマーク機能を使って高速でメモリ使用量の少ないプログラムを書こう

【Go】jsonパッケージのMarshalとUnmarshalはなんとなくわかったが、Encode, Decodeって何？

【Go】最近のリリースで追加された新しい機能について調べてみた（min関数,max関数、clear関数、rangeを使ったforループの新機能）

【Go】embedで作成した文字列を利用する機能のテストで改行に苦しめられた件

Go1.21で追加された「mapsパッケージ」とは？実際にパッケージ内の関数を使うコードを書いてみた。

Go1.21で追加された「slicesパッケージ」とは？実際にパッケージ内の関数を使うコードを書いてみた。
NEW POSTS

最新記事

CodeIgniter4でAPIを使用してランダムユーザーを生成する方法

【Photoshop】アニメーション画像を作ってみよう！（APNG・WebP）【前編】

旅行の楽しさをちょっとプラス！全国津々浦々のアプリをご紹介！　～東日本編～

【jQuery】ページ内のどのコンテンツにいるのか分かるエレベーターメニューを実装してみた

パワポで簡単！画像にモザイクをかけてプライバシーを守りましょう！

【Go】ベンチマーク機能を使って高速でメモリ使用量の少ないプログラムを書こう

FOLLOW US

最新の情報をお届けします

RECOMMENDED

【Go】ベンチマーク機能を使って高速でメモリ使用量の少ないプログラムを書こう

【Go】jsonパッケージのMarshalとUnmarshalはなんとなくわかったが、Encode, Decodeって何？

【Go】最近のリリースで追加された新しい機能について調べてみた（min関数,max関数、clear関数、rangeを使ったforループの新機能）

【Go】embedで作成した文字列を利用する機能のテストで改行に苦しめられた件

Go1.21で追加された「mapsパッケージ」とは？実際にパッケージ内の関数を使うコードを書いてみた。

Go1.21で追加された「slicesパッケージ」とは？実際にパッケージ内の関数を使うコードを書いてみた。
FOLLOW US

資料ダウンロード

お問い合わせ

page top