今回は第197回　MySQLでJSONを活用してみる［その1］に引き続き、MySQL 5.7からサポートが強化されているJSONを活用する方法を紹介していきたいと思います。

検証環境

今回はdockerで建てたMySQLを使用します。以下のコマンドでdockerを建てて、ローカルからアクセスをします。

% docker run --platform linux/x86_64 -p 3307:3306 -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:latest

アクセス方法は以下の通りになります。

% mysql -uroot -pmy-secret-pw -h127.0.0.1 -P3307

執筆時点では以下の通り、MySQL 8.0.33を使用しております。

mysql>  select version();
+-----------+
| version() |
+-----------+
| 8.0.33    |
+-----------+
1 row in set (0.01 sec)

今回はtestデータベースとdataテーブルを作成します。

mysql> create database test;
mysql> use test
mysql> CREATE TABLE `json_data` (id SERIAL, doc json);

カラムにはSERIAL型のidとjson型のdocカラムを用意しています。

前回と同様に以下のデータを保存しておきましょう。

mysql> INSERT INTO json_data(doc) VALUES ('[1, [2, 3], {"4": "5" }]');
mysql> INSERT INTO json_data(doc) VALUES ('{"a": {"b": ["c", "d", null]}, "e": "f"}');

JSONを更新してみる

ここでは、挿入されているJSONの値を一部変更・更新したい場合に使用する関数を紹介します。今回は、以下の3個の関数に関して紹介していきたいと思います。

• JSON_REPLACE関数
• JSON_INSERT関数
• JSON_SET関数

それぞれ用途が異なるため、しっかりと違いを理解して使用しましょう。

JSON_REPLACE関数

JSON_REPLACEは、文字通り現在存在するJSONの値を変更するための関数です。現在の1件目のデータは以下の通りです。

mysql> select * from json_data where id = 1;
+----+------------------------------------------+
| id | doc                                      |
+----+------------------------------------------+
|  1 | [1, [2, 3], {"4": "5"}]                  |
+----+------------------------------------------+
1 rows in set (0.00 sec)

このうち、idが1のdocカラムにある先頭の1を10に変更してみたいと思います。5.6以前では、MySQL外で有効なJSONを作成して文字列としてINSERTする必要がありました。JSON_REPLACE関数を使って、まずは先頭の数字を10に変更したJSONを作ってみましょう。JSON_REPLACEの使い方は、第1引数にJSONを入れて第2引数にパスを指定します。第3引数には変更後の値を入れます。

一旦まずは、SELECTで取得したデータに対して行ってみましょう。

mysql> SELECT JSON_REPLACE(doc, '$[0]', CAST('$[0]' as INT) * 10) from json_data where id = 1;

結果は以下のようになります。

+-------------------------------+
| JSON_REPLACE(doc, '$[0]', 10) |
+-------------------------------+
| [10, [2, 3], {"4": "5"}]      |
+-------------------------------+
1 row in set (0.00 sec)

配列の最初の要素が、1から10に変更されていることがわかります。また、この時点ではUPDATEが実行されていないため、元々の値が変更されていないことには注意しましょう。

mysql> select * from json_data where id = 1;
+----+-------------------------+
| id | doc                     |
+----+-------------------------+
|  1 | [1, [2, 3], {"4": "5"}] |
+----+-------------------------+
1 row in set (0.00 sec)

上記のように取得しなおすと、変わっていないことがわかります。

では、続いて実際にデータベース内の情報を変更してみましょう。といっても、普段のUPDATE文とあまり変わりはありません。UPDATEでSETする値に、JSON_REPLACEの結果を入れてあげることでUPDATEできます。

mysql> UPDATE json_data SET doc = JSON_REPLACE(doc, '$[0]', 10)  where id = 1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

結果を確認してみましょう。

mysql> select * from json_data where id = 1;
+----+--------------------------+
| id | doc                      |
+----+--------------------------+
|  1 | [10, [2, 3], {"4": "5"}] |
+----+--------------------------+
1 row in set (0.00 sec)

上記のように変更されていることが確認できました。

また、JSON_REPLACE関数は第2引数と第3引数は繰り返し指定することもでき、いっぺんに値を変更することも可能です。

mysql> SELECT JSON_REPLACE(doc, '$[0]', 100, '$[1]', 101, '$[2]', 102) from json_data where id = 1;
+----------------------------------------------------------+
| JSON_REPLACE(doc, '$[0]', 100, '$[1]', 101, '$[2]', 102) |
+----------------------------------------------------------+
| [100, 101, 102]                                          |
+----------------------------------------------------------+
1 row in set (0.00 sec)

配列の要素がそれぞれ100, 101, 102で置き換えられていることがわかります。では続けて、元々の配列に存在しない4番目の値を指定してみたらどうでしょうか？

mysql> SELECT JSON_REPLACE(doc, '$[3]',103) from json_data where id = 1;
+-------------------------------+
| JSON_REPLACE(doc, '$[3]',103) |
+-------------------------------+
| [10, [2, 3], {"4": "5"}]      |
+-------------------------------+
1 row in set (0.00 sec)

103の値が消えてしまいました。というわけで、JSON_REPLACEは存在する値を変更するための関数なので、存在しないものに対しては操作ができないことがわかります。とあるキーが存在する場合だけ更新したい場合に、活用できると思います。

JSON_INSERT関数

現在のデータは以下の通りです。

mysql> select * from json_data where id = 1;
+----+--------------------------+
| id | doc                      |
+----+--------------------------+
|  1 | [10, [2, 3], {"4": "5"}] |
+----+--------------------------+
1 row in set (0.01 sec)

先ほどのように存在しない4番目に値を挿入したい場合に使用できるのが、JSON_INSERT関数になります。ではさっそく使ってみましょう。

mysql> SELECT JSON_INSERT(doc, '$[3]',103) from json_data where id = 1;
+-------------------------------+
| JSON_INSERT(doc, '$[3]',103)  |
+-------------------------------+
| [10, [2, 3], {"4": "5"}, 103] |
+-------------------------------+
1 row in set (0.00 sec)

このように挿入されていることがわかります。では、それ以外の元々ある値を変更したらどうなるでしょうか？

mysql> SELECT JSON_INSERT(doc, '$[0]',100) from json_data where id = 1;
+------------------------------+
| JSON_INSERT(doc, '$[0]',100) |
+------------------------------+
| [10, [2, 3], {"4": "5"}]     |
+------------------------------+
1 row in set (0.01 sec)

上記のように、変更されていないことがわかります。

というわけで、JSON_INSERT関数は値が無い場合に追加したい時にだけ使用できます。たとえば、後からキーが増えた場合などに、デフォルト値を付与したいといった際に便利だと思います。

JSON_SET関数

ここまで読んでいただいた方は、JSON_REPLACEは存在しないものには更新ができなくて、JSON_INSERTは存在するものは変更ができない、ということは理解していただいたと思います。

では、UPSERTの処理をしたい場合はどうしたらよいのかなと思ったことでしょう。JSON_REPLACEやJSON_INSERTを組み合わせればできなくも無いですが、ちょっと大変です。そんな時に便利なのがJSON_SET関数です。

実際に、今までJSON_REPLACEやJSON_INSERTでやっていたことを試してみましょう。一番最初の値を100に、最後に103を追加するという処理をいっぺんにやってみます。

mysql> SELECT JSON_SET(doc, '$[0]',100, '$[4]', 103) from json_data where id = 1;
+----------------------------------------+
| JSON_SET(doc, '$[0]',100, '$[4]', 103) |
+----------------------------------------+
| [100, [2, 3], {"4": "5"}, 103]         |
+----------------------------------------+
1 row in set (0.00 sec)

上記のように、変更されていることがわかります。おそらく大多数のケースでは、JSON_SETが役に立つのではないかと思います。

まとめ

今回はMySQLでJSON型のデータを更新する方法を紹介しました。今回紹介した3個の関数はできることが非常によく似ていますが、動作微妙に異なるため、使用する際にはどういった違いがあるのか理解して使用するようにしましょう。簡単に機能の違いをまとめると、以下の表のようになります。

また、アプリケーションからJSONを触っている場合には、JSON型のサポートはフレームワークが行うこともあるので、直接恩恵を受けられる機会が少ないかもしれませんが、もし万が一、中に入っている関数をSQLで適切に操作したい場合は非常に強力なツールとなりますので、活用していきましょう。