MySQLでJSON型を使う（基本編）

MySQLではバージョン 5.7.8 以降でJSON型がサポートされるようになりました。
このシリーズでは基本編、パフォーマンス編、論理設計編と、JSON型のデータ操作方法やどのような場合に使用を検討するべきかをベンチマーク結果も踏まえて探っていきたいと思います。

• MySQLでJSON型を使う（基本編）
• MySQLでJSON型を使う（パフォーマンス編）
• MySQLでJSON型を使う（論理設計編）

今回はJSON型の定義やデータ操作方法についてです。

JSON型の定義

型名をJSONと指定するだけです。

データ型に対して必要なディスクスペースはLONGBLOBやLONGTEXTと同じです。
また、max_allowed_packet （デフォルト4M）の制限を受けるので場合によっては値を変更するなど注意が必要です。

参考　：　 MySQL :: MySQL 5.7 Reference Manual :: 12.6 The JSON Data Type

INSERT

JSONの文字列をそのまま値として渡すこともできます。
[mysql]
mysql> INSERT INTO t1 \
-> VALUES (‘{“key1”: “value1”, “key2”: “value2”}’);
Query OK, 1 row affected (0.03 sec)
[/mysql]

JSONが間違ってるとちゃんとエラーを返してくれます。
[mysql]
mysql> INSERT INTO t1 \
-> VALUES (‘{“key1”: “value1”, “key2”: “value2”}}’);
ERROR 3140 (22032): Invalid JSON text: “The document root must not follow by other values.” at position 36 in value for column ‘t1.items’.
[/mysql]

JSON_OBJECT関数を使ってJSON文字列を生成することもできます。
[mysql]
mysql> INSERT INTO t1 \
-> VALUES (JSON_OBJECT(‘key1’, ‘value1’, ‘key2’, ‘value2’, ‘key3’, JSON_OBJECT(‘key4’, ‘value4’)));
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM t1;
+——————————————————————+
| items |
+——————————————————————+
| {“key1”: “value1”, “key2”: “value2”, “key3”: {“key4”: “value4”}} |
+——————————————————————+
1 row in set (0.01 sec)
[/mysql]

SELECT

JSONの中身を検索条件に加えたい場合はJSON_EXTRACTを使います。
[mysql]
mysql> SELECT items \
-> FROM t1 \
-> WHERE JSON_EXTRACT(items, “$.key1”) = “value1”;
+————————————–+
| items |
+————————————–+
| {“key1”: “value1”, “key2”: “value2”} |
+————————————–+
1 row in set (0.02 sec)
[/mysql]

MySQL 5.7.9以降なら column->path による記法も使えます。
[mysql]
mysql> SELECT * \
-> FROM t1 \
-> WHERE items->”$.key1″ = “value1”;
+————————————–+
| items |
+————————————–+
| {“key1”: “value1”, “key2”: “value2”} |
+————————————–+
1 row in set (0.00 sec)
[/mysql]

SELECT句でもJSON_EXTACTやcolumn->pathは使えます。
[mysql]
mysql> SELECT JSON_EXTRACT(items, “$.key1”) AS key1 FROM t1;
+———-+
| key1 |
+———-+
| “value1″ |
+———-+
1 row in set (0.00 sec)

mysql> SELECT items->”$.key1” AS key1 FROM t1;
+———-+
| key1 |
+———-+
| “value1” |
+———-+
1 row in set (0.00 sec)
[/mysql]

UPDATE

JSONの文字列をそのまま渡して更新することもできます。
[mysql]
mysql> UPDATE t1
-> SET items = ‘{“key3”: “value3”, “key4”: “value4”}’;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT * FROM t1;
+————————————–+
| items |
+————————————–+
| {“key3”: “value3”, “key4”: “value4”} |
+————————————–+
1 row in set (0.00 sec)
[/mysql]

JSONの一部のデータだけ更新したい場合はJSON_SET関数を使います。
[mysql]
mysql> UPDATE t1 \
-> SET items = JSON_SET(items, ‘$.key3’, ‘replace’);
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT * FROM t1;
+—————————————+
| items |
+—————————————+
| {“key3”: “replace”, “key4”: “value4”} |
+—————————————+
1 row in set (0.00 sec)
[/mysql]

上記に上げた関数以外にも末尾にデータを追加するJSON_APPENDや
データの一部だけ削除するJSON_REMOVEなどがさまざまな関数が存在します。

参考　：　MySQL :: MySQL 5.7 Reference Manual :: 13.16.1 JSON Function Reference

まとめ

• JSON型にはJSONフォーマットの文字列をINSERTすることもできます。
• JSON_EXTRACT関数などを使えばパス指定した値で検索も可能です。
• JSON_SET関数などを使えばカラム内のデータの一部だけ変更することも可能です。

次回は「MySQLでJSON型を使う」のパフォーマンス編です。

• MySQLでJSON型を使う（基本編）
• MySQLでJSON型を使う（パフォーマンス編）
• MySQLでJSON型を使う（論理設計編）