今回は第197回 MySQLで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.
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の値を一部変更・
- JSON_
REPLACE関数 - JSON_
INSERT関数 - JSON_
SET関数
それぞれ用途が異なるため、しっかりと違いを理解して使用しましょう。
JSON_REPLACE関数
JSON_
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.
一旦まずは、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_
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_
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_
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_
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_
JSON_SET関数
ここまで読んでいただいた方は、JSON_
では、UPSERTの処理をしたい場合はどうしたらよいのかなと思ったことでしょう。JSON_
実際に、今までJSON_
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_
まとめ
今回はMySQLでJSON型のデータを更新する方法を紹介しました。今回紹介した3個の関数はできることが非常によく似ていますが、動作微妙に異なるため、使用する際にはどういった違いがあるのか理解して使用するようにしましょう。簡単に機能の違いをまとめると、以下の表のようになります。
関数名 | 既存の値 | 存在しない値 |
---|---|---|
JSON_ |
変更する | 追加しない |
JSON_ |
変更しない | 追加する |
JSON_ |
変更する | 追加する |
また、アプリケーションからJSONを触っている場合には、JSON型のサポートはフレームワークが行うこともあるので、直接恩恵を受けられる機会が少ないかもしれませんが、もし万が一、中に入っている関数をSQLで適切に操作したい場合は非常に強力なツールとなりますので、活用していきましょう。