こんにちは、DCです。

今回はMySQLのJSON型を紹介したいと思います。

JSON型とは

MySQL5.7.8から利用できるようになったデータ型でJSONドキュメントを格納することができます。
自分が調べた限りですと、JSON型が登場する以前はBLOB型やTEXT型に格納していたようです。

公式ドキュメントにもありますように、JSON型の利点は以下です。

・JSON カラムに格納されている JSON ドキュメントの自動検証。 無効なドキュメントではエラーが発生します。

・最適化された記憶域形式。 JSON カラムに格納された JSON ドキュメントは、ドキュメント要素へのクイック読取りアクセスを許可する内部形式に変換されます。 サーバーが後でこのバイナリ形式で格納された JSON 値を読み取る必要がある場合、テキスト表現から値を解析する必要はありません。 バイナリ形式は、サーバーがドキュメント内のサブオブジェクトまたはネストされた値の前後のすべての値を読み取ることなく、キーまたは配列インデックスによって直接サブオブジェクトまたはネストされた値を検索できるように構造化されています。

JSONドキュメントに関連した関数も豊富にあります。

https://dev.mysql.com/doc/refman/8.0/ja/json.html
https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-8.html#mysqld-5-7-8-json
https://dev.mysql.com/doc/refman/8.0/ja/json-function-reference.html

気になった特徴

DEFAULT CHARACTER SETは関係ない

CREATE TABLE Test(id int(10) NOT NULL AUTO_INCREMENT, jcol JSON, PRIMARY KEY (id)) DEFAULT CHARACTER SET utf8mb3;
INSERT INTO Test(jcol) values('{"string":"🍣"}');
SELECT * FROM Test;

+----+-------------------+
| id | jcol              |
+----+-------------------+
|  1 | {"string": "🍣"}   |
+----+-------------------+

DEFAULT CHARACTER SETがutf8mb3のテーブルでもエラーなくutf8mb4の🍣をINSERTとSELECTすることができました。

JSONドキュメントの任意のフィールドでORDER BYできる

CREATE TABLE Test(id int(10) NOT NULL AUTO_INCREMENT, jcol JSON,PRIMARY KEY (id)) DEFAULT CHARACTER SET utf8mb3;
INSERT INTO Test(jcol) values('{"string":"🍣"}');
INSERT INTO Test(jcol) values('{"string":"🍺"}');
INSERT INTO Test(jcol) values('{"string":"a"}');
INSERT INTO Test(jcol) values('{"string":"A"}');
INSERT INTO Test(jcol) values('{"string":"b"}');
INSERT INTO Test(jcol) values('{"string":"B"}');
INSERT INTO Test(jcol) values('{"string":"あ"}');
INSERT INTO Test(jcol) values('{"string":"ア"}');
INSERT INTO Test(jcol) values('{"string":"ｱ"}');
INSERT INTO Test(jcol) values('{"string":"パア"}');
INSERT INTO Test(jcol) values('{"string":"バイ"}');
INSERT INTO Test(jcol) values('{"integer":1}');
SELECT * FROM Test ORDER BY jcol->>'$.string' ASC;
SELECT * FROM Test ORDER BY JSON_EXTRACT(jcol,'$.string') ASC;

※私の場合、複雑なJSON操作の場合はJSON_EXTRACT()、シンプルなJSON操作の場合は矢印演算子を使っています。

+----+---------------------+
| id | jcol                |
+----+---------------------+
| 12 | {"integer": 1}      |
|  4 | {"string": "A"}     |
|  6 | {"string": "B"}     |
|  3 | {"string": "a"}     |
|  5 | {"string": "b"}     |
|  7 | {"string": "あ"}    |
|  8 | {"string": "ア"}    |
| 11 | {"string": "バイ"}  |
| 10 | {"string": "パア"}  |
|  9 | {"string": "ｱ"}    |
|  1 | {"string": "🍣"}    |
|  2 | {"string": "🍺"}    |
+----+---------------------+

COLLATEを使えば照合順序も指定可能です。
また{“integer”: 1}はstringキーは存在しませんがエラーは発生しません。このあたりは自由さがあり、私は好きです。

SELECT * FROM Test ORDER BY jcol->>'$.string' COLLATE utf8mb4_0900_ai_ci ASC;
SELECT * FROM Test ORDER BY jcol->>'$.string' COLLATE utf8mb4_bin ASC;

SELECT * FROM Test ORDER BY jcol->>'$.string' ASC;
SELECT * FROM Test ORDER BY jcol->>'$.string' COLLATE utf8mb4_bin ASC;

COLLATEを指定しなかった場合とutf8mb4_binを指定しなかった結果は同じでした。何も指定しないとutf8mb4_binになるようです。
公式ドキュメントにも以下の記述がありました。

MySQL は、utf8mb4 文字セットおよび utf8mb4_bin 照合順序を使用して、JSON コンテキストで使用される文字列を処理します。 他の文字セットの文字列は、必要に応じて utf8mb4 に変換されます。

JSON型の特定フィールドの検索

CREATE TABLE Test(id int(10) NOT NULL AUTO_INCREMENT, jcol JSON,PRIMARY KEY (id)) DEFAULT CHARACTER SET utf8mb3;
INSERT INTO Test(jcol) values('{"string":"🍣"}');
INSERT INTO Test(jcol) values('{"string":"🍺"}');
SELECT * FROM Test WHERE jcol->>'$.string' = '🍣';
SELECT * FROM Test WHERE JSON_EXTRACT(jcol,'$.string') = '🍣';

+----+-------------------+
| id | jcol              |
+----+-------------------+
|  1 | {"string": "🍣"}  |
+----+-------------------+

CREATE TABLE Test(id int(10) NOT NULL AUTO_INCREMENT, jcol JSON,PRIMARY KEY (id)) DEFAULT CHARACTER SET utf8mb3;
INSERT INTO Test(jcol) values('{"string":"🍣"}');
INSERT INTO Test(jcol) values('{"string":"🍺"}');
SELECT * FROM Test WHERE jcol->>'$.string' COLLATE utf8mb4_unicode_ci = '🍣';

+----+-------------------+
| id | jcol              |
+----+-------------------+
|  1 | {"string": "🍣"}  |
|  2 | {"string": "🍺"}  |
+----+-------------------+

これもまた、COLLATEを使えばお好みの照合順序にすることができます。

JSON型のINDEX利用

JSON型のカラムにINDEXを直接張ることはできません。しかしJSONドキュメントの特定のフィールドに対してインデックスを張ることはできます。

• 仮想列を利用した方法

CREATE TABLE Test(id int(10) NOT NULL AUTO_INCREMENT, jcol JSON,PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb3;
INSERT INTO Test(jcol) values('{"string":"🍣"}');
INSERT INTO Test(jcol) values('{"string":"🍺"}');
ALTER TABLE Test ADD COLUMN jcol_string VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin GENERATED ALWAYS AS (jcol->>'$.string') VIRTUAL;
ALTER TABLE Test ADD INDEX gen_jcol_string(jcol_string);
EXPLAIN SELECT * FROM Test WHERE jcol->>'$.string' = '🍣';
EXPLAIN SELECT * FROM Test WHERE jcol_string = '🍣';

+----+-------------+-------+-----------+------+---------------+----------------+---------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key            | key_len | ref  | rows     | filtered | Extra |
+----+-------------+-------+-----------+------+---------------+----------------+---------+------+----------+-------+
|  1 | SIMPLE      | Test  | NULL      | ref  | gen_gen_string | gen_jcol_string | 1023   | const | 1        | 100.00   | NULL  |
+----+-------------+-------+-----------+------+---------------+----------------+---------+------+----------+-------+

どちらのパターンのWHEREでもこちらのインデックスを使用してくれています。

• 関数インデックスを利用した方法（MySQL8.0.13から）

CREATE TABLE Test(id int(10) NOT NULL AUTO_INCREMENT, jcol JSON,PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb3;
INSERT INTO Test(jcol) values('{"string":"🍣"}');
INSERT INTO Test(jcol) values('{"string":"🍺"}');
ALTER TABLE Test ADD INDEX func_jcol_string((CAST(jcol->>"$.string" as CHAR(255))COLLATE utf8mb4_bin));
EXPLAIN SELECT * FROM Test WHERE JSON_EXTRACT(jcol,'$.string') = '🍣';

+----+-------------+-------+-----------+------+---------------+----------------+---------+------+----------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key            | key_len | ref  | rows     | filtered | Extra |
+----+-------------+-------+-----------+------+---------------+----------------+---------+------+----------+----------+-------+
|  1 | SIMPLE      | Test  | NULL      | ref  | func_jcol_string | func_jcol_string | 1023   | const | 1        | 100.00   | NULL  |
+----+-------------+-------+-----------+------+---------------+----------------+---------+------+----------+----------+-------+

https://blogs.oracle.com/mysql-jp/post/indexing-json-data-in-mysql-jp

仮想列を利用した方法ですとカラムがあれてしまうので私は関数インデックスを利用した方法が好みです。

私的MySQLのJSON型おすすめの利用シーン

・MongoDBなどのNoSQLを利用を検討した場合
・検索条件として常用されない場合
・データベースの正規化の実行コストが高い場合

私が特におすすめする利用シーンはログの保存です。JSON型ですとテーブル設計やカラムの更新を伴わないため、柔軟に利用することができ、
いざ検索したいとなった場合もSQLを書けば、求める結果を取得することができます。実際の現場では物理削除したレコードのログを残したいという要求に対してのログ置き場として利用してみました。物理削除元テーブルでカラム更新があったとしても、JSON型のテーブルである故にカラムの更新を必要としません。ログを記録するためにカラム不一致によるお腹の痛いエラーが起こらないので安心することができます。

MySQLのJSON型いかがでしたでしょうか？取り合えていない便利機能があるかと思いますが、今回はこの辺りで〆させていただきます。
NoSQLに対抗するMySQLの一手、今後も要チェックですね！