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

本文へ

フッターへ

お役立ち情報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

関連記事

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

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

【Go】standard libraryのtext/templateを使って、簡単に文字列を作成・加工しよう！

Go1.18から実装されたジェネリクスの基本的な使い方をまとめてみた

GraphQLサーバを構築するためのGoライブラリ「gqlgen」を使ってGraphQLサーバーを構築してみる

Go WebAssemblyでブラウザ上でGoを実行する その3 Javascript側からGoの関数を呼び出す
NEW POSTS

最新記事

Reactでフォーカス可能な要素にフォーカスを当てる方法

デザインセンスが無くても大丈夫！パワーポイントで分かりやすい資料を作成する4つのポイント

【Canva Pro】写真の不要な部分を一瞬で消去！Magic Eraser（消しゴムマジック）機能の使い方

Laravel×Inertia×Vue3でCRUD機能を持つSPAを作ってみた

【Illustratorの生成AI機能】テキストからベクターデータを生成！イラスト・アイコン・パターンを素早く作る！

情報を視覚的に伝える！インフォグラフィックスの使い方・作り方

FOLLOW US

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

RECOMMENDED

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

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

【Go】standard libraryのtext/templateを使って、簡単に文字列を作成・加工しよう！

Go1.18から実装されたジェネリクスの基本的な使い方をまとめてみた

GraphQLサーバを構築するためのGoライブラリ「gqlgen」を使ってGraphQLサーバーを構築してみる

Go WebAssemblyでブラウザ上でGoを実行する その3 Javascript側からGoの関数を呼び出す
FOLLOW US

資料ダウンロード

お問い合わせ

page top