mysql の json 型について
mysql の json 型について
はじめに
こちらは、ポート株式会社 サービス開発部 Advent Calendar 2023 3日目の記事となります。
はじめまして、ポート株式会社でバックエンドエンジニアをしている沢田です。
今回は、mysql の json 型について基本的な使い方（保存、検索、更新、インデックス）を調べてみたのでまとめていきたいと思います！
テーブル作成
では、早速テーブルの作成から行っていきます。
今回は、json 型のカラムを１つ持つテーブルを作成していきます。
sql は、以下の通りです。
CREATE TABLE `t1` (
`data` JSON NULL
);
JSON型を指定したカラムには、配列および、オブジェクトのデータの格納が設定が可能となります。
["str", 1, true, null]
{"key1": "str", "key2": 123}
保存
続いてデータの保存を行っていきます。
json 文字列を直接設定する他、JSON_ARRAY および、JSON_OBJECT 関数を使用し作成することも可能です。
INSERT INTO t1 VALUE('{"key": "val"}');
INSERT INTO t1 VALUE(JSON_ARRAY('a', 1, now()));
INSERT INTO t1 VALUE(JSON_OBJECT('key', 1, 'key2', now()));
INSERT INTO t1 VALUE(JSON_OBJECT('key', JSON_ARRAY('val')));
結果は以下の通りとなります。
mysql> SELECT * FROM t1;
+--------------------------------------------------+
| data |
+--------------------------------------------------+
| {"key": "val"} |
| ["a", 1, "2023-12-04 04:42:17.000000"] |
| {"key": 1, "key2": "2023-12-04 04:42:45.000000"} |
| {"key": 1, "key2": ["val"]} |
+--------------------------------------------------+
検索
続いて json 型の検索を行っていきます。
今回はjson データ全体を比較する方法と特定のキーの値で検索する方法を行っていきます。
jsonオブジェクト全体での比較
json データ全体を比較する場合、JSON_OBJECT, JSON_ARRAY を使用します。
mysql> SELECT * FROM t1 WHERE data = JSON_OBJECT("key", "val");
+----------------+
| data |
+----------------+
| {"key": "val"} |
+----------------+
なお、この比較の場合大文字小文字の比較も行われますので、以下のケースでは取得できません。
mysql> SELECT * FROM t1 WHERE data = JSON_OBJECT("Key", "val");
Empty set (0.01 sec)
jsonオブジェクト内のキーで検索するとき
column->"$.key" といった記法で検索可能です。
mysql> SELECT * FROM t1 WHERE data->"$.key" = "val";
+------------------+
| data |
+------------------+
| {"key": "val"} |
+------------------+
なお、-> の記法は、JSON_EXTRACT の省力記法となります。
mysql> SELECT * FROM t1 WHERE JSON_EXTRACT(data, '$.key') = "val";
+------------------+
| data |
+------------------+
| {"key":"val"} |
+------------------+
更新
続いて、更新を行っていきます。
json型の更新では、jsonデータ全体を更新するだけでなく、部分更新といった効率的な更新を行うことも可能です。
部分更新について
json 型の部分更新が行われるには様々な条件が必要になります。
条件の１つとして、update 文内で JSON_SET()、JSON_REPLACE() または JSON_REMOVE() を利用する、といったものがありますので、
今回はこちらの関数を利用して更新処理を行っていきます。
部分更新の詳細については以下をご確認下さい。
JSON_SET
JSON_SET は、指定のpath の値を更新します。
存在しないpath の場合は、値の新規追加が行われます。
定義は、JSON_SET(json_doc, path, val[, path, val] ...) となります。
更新前のデータは以下の通りです。
mysql> SELECT * FROM t1 WHERE data->'$.key' = 'val';
+--------------------------------------------------+
| data |
+--------------------------------------------------+
| {"key": "val"} |
+--------------------------------------------------+
以下のように、第二引数でpath を指定し、第三引数の値に置き換えます。
mysql> UPDATE T1 SET data = JSON_SET(data, '$.key', 'val_updated') where data->'$.key' = 'val';
mysql> SELECT * FROM t1;
+--------------------------------------------------+
| data |
+--------------------------------------------------+
| {"key": "val_updated"} |
+--------------------------------------------------+
第4引数以降はオプショナルとなっており、値へのパス、値を指定することで複数の値を更新することも可能です。
mysql> UPDATE t1 SET data = JSON_SET(data, '$.key', 'val', '$.key2', 'val2', '$.key3', 'val3') where data->'$.key' = 'val_updated';
mysql> SELECT * FROM t1 WHERE data->'$.key' = 'val';
+--------------------------------------------------------+
| data |
+--------------------------------------------------------+
| {"key": "val", "key2": "val2", "key3": "val3"} |
+--------------------------------------------------------+
JSON_REPLACE
JSON_REPLACE も JSON_SET と同様に、指定のpath の値の更新可能が可能となります。
JSON_SET との違いは、存在しない path の場合はデータの追加が発生しないということです。
定義も JSON_SET と同様で JSON_REPLACE(json_doc, path, val[, path, val] ...) となります。
更新前のデータを以下の通りです。
mysql> INSERT INTO t1 VALUE(JSON_OBJECT('key', JSON_ARRAY('val')));
mysql> SELECT * FROM t1 WHERE data->'$.key' = 'val';
+--------------------------------------------------+
| data |
+--------------------------------------------------+
| {"key": "val"} |
+--------------------------------------------------+
こちらも同様に、第二引数でpath を指定し、第三引数の値に置き換えます。
mysql> UPDATE t1 SET data = JSON_REPLACE(data, '$.key', 'val_updated') WHERE data->'$.key[0]' = 'val';
mysql> SELECT * FROM t1;
+----------------------------+
| data |
+----------------------------+
| {"key": "val_updated"} |
+----------------------------+
前述の通り、存在しない path を指定した場合データの追加は行われません。
mysql> UPDATE t1 SET data = JSON_REPLACE(data, '$.key', 'val_updated', '$.undefined_key', 'val2') WHERE data->'$.key' = 'val';
mysql> SELECT * FROM t1;
+------------------------+
| data |
+------------------------+
| {"key": "val_updated"} |
+------------------------+
JSON_REMOVE
続いて、JSON_REMOVE です。
名前の通り、対象のパスの要素を削除します。
削除する要素が存在しない場合は、影響を与えません。（エラーも発生しません。）
定義は、前述のJSON_SET, JSON_REPLACE と同じで、JSON_REMOVE(json_doc, path[, path] ...)となります。
更新前のデータを以下の通りです。
mysql> INSERT INTO t1 VALUE(JSON_OBJECT('key', JSON_ARRAY('val')));
mysql> SELECT * FROM t1 WHERE data->'$.key.0' = 'val';
+--------------------+
| data |
+--------------------+
| {"key": ["val"]} |
+--------------------+
今回は、以下の構文でkeyの値の配列の0番目の要素を削除します。
mysql> UPDATE t1 SET data = JSON_REMOVE(data, '$.key[0]') WHERE data->'$.key[0] = 'val';
mysql> SELECT * FROM t1;
+------------------+
| data |
+------------------+
| {"key": []} |
+------------------+
インデックス
json 型のカラムに直接インデックスを貼ることはできませんが、以下の2つの手法を利用することでパフォーマンスを向上させることが可能です。
- Generated Column を利用する方法
- 関数インデックスを利用する方法
今回は、Generated Column を利用する方法について詳しく見ていきます。
Generated Column を利用する方法
Generated Column ですがザックリ言うと、定義した式から自動で値を生成し、所定のカラムに値を設定する方法です。
定義は、テーブル作成時か、カラム追加時に行うことが可能です。
なお、注意点として、generated column に設定したカラムの値を更新しようとするとエラーが発生してしまいます。
今回は、 data カラムのjson の key の値を generated column で設定し、それに対して index を貼っていきます。
注意として、generated カラムに値を設定する際に -> ではなく、 ->> を利用しないと、" 等を含めてカラムに保存されてしまう為注意が必要です。
カラム追加、インデックス作成のsql 配下の通りです。
ALTER TABLE t1 ADD COLUMN data_key varchar(255) GENERATED ALWAYS AS(data->>"$.key")
ALTER TABLE t1 ADD INDEX data_key_index (name);
結果、テーブルはこのような状態となります。
mysql> SELECT * FROM t1;
+------------------------+-------------+
| data | data_key|
+------------------------+-------------+
| {"key": "val_updated"} | val_updated |
| {"key": "val"} | val |
+------------------------+-------------+
続いて、インデックスが効いているか確認していきます。
data_key カラムに対して条件を付け、explain で実行計画を確認します。
mysql> EXPLAIN SELECT * FROM t1 WHERE data_key = 'val';
+----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | t1 | NULL | ref | data_key_index | data_key_index | 1023 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
possible_keys に作成した index が出てきました。
まとめ
いかがでしたでしょうか？
今回は mysql の json 型について調査しました。
json 型を上手く利用できればデータを柔軟に利用できたり、過剰なリレーションを回避したり、など様々なところで活躍します。
また、イベントソーシングの実装でも利用することとなるため詳しく知っておくといいでしょう。
参考文献
Discussion