[MySQL] JSON操作入門

はじめに

この記事では、MySQLのJSON操作関数の使い方を初心者向けに解説します。
環境: MySQL:8.0.40

json型のカラムuser_dataをもつusersテーブルをもとに解説します。

mysql> desc users;
+-----------+------+------+-----+---------+-------+
| Field     | Type | Null | Key | Default | Extra |
+-----------+------+------+-----+---------+-------+
| user_data | json | YES  |     | NULL    |       |
+-----------+------+------+-----+---------+-------+

user_dataには以下のデータが格納されています。

mysql> select * from users;
+-----------------------+
| user_data             |
+-----------------------+
| {"name": "test_name"} |
+-----------------------+

以下解説では、上記name部分をパス、test_name部分を値と呼ぶこととします。

{ パス: 値 }

検索

パスの検索

• json_contains_path(json, one_or_all, path1, path2…)
  • one…json内に、pathが一つでもあれば1、なければ0を返す
  • all…json内に、pathが全て存在すれば1、なければ0を返す

# user_dataの中に、パスnameがあるか確認

mysql> select json_contains_path(user_data, 'one', '$.name') from users;
+------------------------------------------------+
| json_contains_path(user_data, 'one', '$.name') |
+------------------------------------------------+
|                                              1 |
+------------------------------------------------+

値の検索

• json_contains(json, 検索したい値, path)
  • json内のpathに、検索したい値が存在すれば1、なければ0を返す

# パスnameの値が、test_nameか確認

mysql> select json_contains(user_data, '"test_name"', '$.name') from users;
+---------------------------------------------------+
| json_contains(user_data, '"test_name"', '$.name') |
+---------------------------------------------------+
|                                                 1 |
+---------------------------------------------------+

値の抽出

• json_extract(json, path)
  • pathの値を出力する

# パスnameの値を抽出

mysql> select json_extract(user_data, '$.name') from users;
+-----------------------------------+
| json_extract(user_data, '$.name') |
+-----------------------------------+
| "test_name"                       |
+-----------------------------------+

変更

値のセット&上書き

• json_set(json, path, 値)
  • json内に、path: 値を生成する
  • すでに同名のpathが存在していた場合は、そのpathの値を上書きする(json_insertとの違い)

# nameを追加

mysql> update users set user_data=json_set(user_data, '$.age', '25');
mysql> select * from users;
+------------------------------------+
| user_data                          |
+------------------------------------+
| {"age": "25", "name": "test_name"} |
+------------------------------------+

# すでに存在するパスageを指定すると、値が上書きされる

mysql> update users set user_data=json_set(user_data, '$.age', '50');
mysql> select * from users;
+------------------------------------+
| user_data                          |
+------------------------------------+
| {"age": "50", "name": "test_name"} |
+------------------------------------+

値の挿入

• json_insert(json, path, 値)
  • json内に、path: 値を生成する
  • すでに同名のpathが存在していた場合は、処理をスキップする(json_setとの違い)

# emailの追加

mysql> update users set user_data=json_insert(user_data, '$.email', '[e-mail]');
mysql> select * from users;
+----------------------------------------------------------------+
| user_data                                                      |
+----------------------------------------------------------------+
| {"age": "50", "name": "test_name", "email": "[e-mail]"} |
+----------------------------------------------------------------+

# すでに存在するパスemailを指定すると、処理がスキップされる

mysql> update users set user_data=json_insert(user_data, '$.email', '[e-mail]');
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> select * from users;
+----------------------------------------------------------------+
| user_data                                                      |
+----------------------------------------------------------------+
| {"age": "50", "name": "test_name", "email": "[e-mail]"} |
+----------------------------------------------------------------+

値の書き換え

• json_replace(json, path, 書き換えたい値)
  • json内のpathが持っている値を書き換える

# ageを100に書き換える

mysql> update users set user_data=json_replace(user_data, '$.age', '100');
mysql> select * from users;
+-----------------------------------------------------------------+
| user_data                                                       |
+-----------------------------------------------------------------+
| {"age": "100", "name": "test_name", "email": "[e-mail]"} |
+-----------------------------------------------------------------+

削除

パスと値を丸ごと削除

• json_remove(json, path)
  • json内の、指定したpathとその値を削除する

# ageを削除してみる

mysql> update users set user_data=json_remove(user_data, '$.age');
mysql> select * from users;
+---------------------------------------------------+
| user_data                                         |
+---------------------------------------------------+
| {"name": "test_name", "email": "[e-mail]"} |
+---------------------------------------------------+

パスの変更

値を変更するJSON関数は、json_set, json_insert, json_replaceなどがありますが、
パスを直接変更するJSON関数は存在しないので、以下JSON関数を組み合わせることで、パスの変更を行います。

• json_extract
• json_set or insert
• json_remove

# パスnameを、user_nameに書き換える

mysql> update users set user_data=json_remove(json_set(user_data, '$.user_name', json_extract(user_data, '$.name')), '$.name');
mysql> select * from users;
+--------------------------------------------------------+
| user_data                                              |
+--------------------------------------------------------+
| {"email": "[e-mail]", "user_name": "test_name"} |
+--------------------------------------------------------+

上記の処理は、以下のように分解できます。

1. json_extractで、パスnameの値を取得
2. json_set or insertで、新たに{ user_name: パスnameの値 }を生成
3. json_removeで、json_set or insertで返されたjsonから、パスnameとその値を削除する

参考