[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', 'xxx@example.com');
mysql> select * from users;
+----------------------------------------------------------------+
| user_data                                                      |
+----------------------------------------------------------------+
| {"age": "50", "name": "test_name", "email": "xxx@example.com"} |
+----------------------------------------------------------------+

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

mysql> update users set user_data=json_insert(user_data, '$.email', 'testtest@example.com');
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": "xxx@example.com"} |
+----------------------------------------------------------------+
```

#### 値の書き換え
- `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": "xxx@example.com"} |
+-----------------------------------------------------------------+
```

## 削除
#### パスと値を丸ごと削除
- `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": "xxx@example.com"} |
+---------------------------------------------------+
```

## パスの変更
値を変更する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": "xxx@example.com", "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とその値を削除する


## 参考
https://man.plustar.jp/mysql/json-functions.html

https://dev.mysql.com/doc/refman/8.0/ja/json-search-functions.html#function_json-contains

https://dev.mysql.com/doc/refman/8.0/ja/json-modification-functions.html