【MySQL入門】MySQLを使ったJSON形式の保存とデータ取得について

## 目的
・MySQLでJSON形式での保存など操作する際の備忘録
・主にテーブル作成、データ保存、更新、削除、置換の記述例を掲載

## 環境
・Ubuntu 19.04
・MySQL 5.7.27

## 作業
1. テスト用テーブル作成
2. INSERTを使ったデータ登録例
3. UPDATEを使ったデータ更新例
4. JSON_REMOVEを使ったJSONデータの操作
5. JSON_REPLACEを使ったJSONデータの操作


## 内容
### 1. テスト用テーブル作成
- 以降、各種データを保存する為のテスト用テーブル作成
- カラムは３つ（id, title, tags）
- tags をJSON形式で保存していく
 

▼テーブルイメージ（テーブル名：test_book）

| カラム名    |  タイプ  |  サイズ  |  その他  |
|:----------:|:-------:|:-------:|:--------------:|
| id         |   INT   |  11     | NOT NULL / AUTO INCREMENT / PRIMARY KEY|
| title      | VARCHAR |  200    | NOT NULL |
| tags       |  JSON   |         | NULL     |


▼テーブル生成例

```SQL
> CREATE TABLE `test_book` (
  ->  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  ->  `title` VARCHAR(200) NOT NULL,
  ->  `tags` JSON DEFAULT NULL,
  ->  PRIMARY KEY (`id`)
  ->  ) ENGINE=InnoDB;
```

### 2. INSERTを使ったデータ登録例
- INSERTのみを使ったJSON形式の保存方法
- JSON_OBJECTを使った保存方法
- JSON_ARRAYとJSON_OBJECTを使った保存方法

▼登録方法１

```SQL
> INSERT INTO `test_book` ( `title`, `tags` ) VALUES (
  -> 'This is Test1',
  -> '[ "Javascript", "ES2015", "JSON" ]',
  -> );
```

結果：配列として保存される

| tags    |
|:----------|
|  ["Javascript", "ES2015", "JSON"]  |



▼登録方法２

```SQL
> INSERT INTO `test_book` ( `title`, `tags` ) VALUES (
  -> 'This is Test2',
  -> JSON_OBJECT( 'name', 'MySQL', 'version', '5.7', 'os', 'Linux' )
  -> );
```

結果：連想配列として保存される

| tags    |
|:----------|
|  {"os" : "Linux", "name" : "MySQL", "version" : "5.7"}  |

▼登録方法３

```SQL
> INSERT INTO `test_book` ( `title`, `tags` ) VALUES (
  -> 'This is Test3',
  -> JSON_ARRAY (
  ->   JSON_OBJECT( 'name', '火鼻', 'price', 2000 ),
  ->   JSON_OBJECT( 'name', 'IQ60', 'price', 1000 )
  ->  )
  -> );
```

結果：配列の中に連想配列を２つ保存

| tags | 
|:------|
| [{"name" : "火鼻", "price" : 2000 }, {"name" : "IQ60", "price" : 1000 }] |


### 3. UPDATEを使ったデータ更新例
- JSON_MERGEを使った更新方法
- JSON_MERGEとJSON_OBJECTを使った更新方法
- JSON_MERGEとJSON_ARRAYとJSON_OBJECTを使った更新方法


#### JSON_MERGEを使った更新方法
▼更新方法１※登録方法１レコード利用

```SQL
> UPDATE `test_book` SET tags = JSON_MERGE( tags, '[ "technical"]' ) WHERE id = 1;
```

結果：配列の末尾に値が挿入され更新

| tags（更新前） | 
|:------|
| ["JavaScript", "ES2015", "JSON"] |

| tags（更新後） | 
|:------|
| ["JavaScript", "ES2015", "JSON", "technical"] |


▼更新方法２※登録方法２レコード利用

```SQL
> UPDATE `test_book` SET tags = JSON_MERGE( tags, '[ "technical"]' ) WHERE id = 2;
```

結果：配列の末尾に値が挿入され更新

| tags（更新前） | 
|:------|
|  {"os": "Linux", "name": "MySQL", "version": "5.7"} |

| tags（更新後） | 
|:------|
|  [{"os": "Linux", "name": "MySQL", "version": "5.7"}, "technical"] |


▼更新方法３※登録方法３レコード利用

```SQL
> UPDATE `test_book` SET tags = JSON_MERGE( tags, '["technical"]' ) WHERE id = 3;
```

結果：配列の末尾に値が挿入され更新

| tags（更新前） | 
|:------|
|  [{"name": "火鼻", "price": 2000}, {"name": "IQ60", "price": 1000}] |

| tags（更新後） | 
|:------|
|  [{"name": "火鼻", "price": 2000}, {"name": "IQ60", "price": 1000}, "technical"] |


#### 所感
* JSON_MERGEの操作は、配列の末尾に値を追加する結果となる

---------------------------------------------------------

#### JSON_MERGEとJSON_OBJECTを使った更新方法
▼更新方法１※登録方法１レコード利用

```SQL
> UPDATE `test_book` SET tags = JSON_MERGE( tags, JSON_OBJECT( "name", "初変" ) ) WHERE id = 1;
```

結果：配列の末尾に連想配列が挿入され更新

| tags（更新前） | 
|:------|
| ["JavaScript", "ES2015", "JSON"] |

| tags（更新後） | 
|:------|
| ["JavaScript", "ES2015", "JSON", {"name": "初変"}] |


▼更新方法２※登録方法２レコード利用

```SQL
> UPDATE `test_book` SET tags = JSON_MERGE( tags, JSON_OBJECT( "name", "初変" ) ) WHERE id = 2;
```

結果：配列の末尾に連想配列が挿入され更新

| tags（更新前） | 
|:------|
| [{"os": "Linux", "name": "MySQL", "version": "5.7"}] |

| tags（更新後） | 
|:------|
| [{"os": "Linux", "name": "MySQL", "version": "5.7"}, {"name": "初変"}] |


▼更新方法３※登録方法３レコード利用

```SQL
> UPDATE `test_book` SET tags = JSON_MERGE( tags, JSON_OBJECT( "name", "初変" ) ) WHERE id = 3;
```

結果：配列の末尾に連想配列が挿入され更新

| tags（更新前） | 
|:------|
|  [{"name": "火鼻", "price": 2000}, {"name": "IQ60", "price": 1000}] |

| tags（更新後） | 
|:------|
|  [{"name": "火鼻", "price": 2000}, {"name": "IQ60", "price": 1000}, {"name": "初変"}"] |

#### 所感
* JSON_MERGEとJSON_OBJECTの操作は、配列の末尾に連想配列を追加する結果となる

---------------------------------------------------------

#### JSON_MERGEとJSON_ARRAYとJSON_OBJECTを使った更新方法
▼更新方法１※登録方法１レコード利用

```SQL
> UPDATE `test_book` SET tags = JSON_MERGE( tags, JSON_ARRAY( JSON_OBJECT( "name", "初変" ) ) ) WHERE id = 1;
```

結果：配列の末尾に連想配列が挿入され更新

| tags（更新前） | 
|:------|
| ["JavaScript", "ES2015", "JSON"] |

| tags（更新後） | 
|:------|
| ["JavaScript", "ES2015", "JSON", {"name": "初変"}] |

▼更新方法２※登録方法２レコード利用

```SQL
> UPDATE `test_book` SET tags = JSON_MERGE( tags, JSON_ARRAY( JSON_OBJECT( "name", "初変" ) ) ) WHERE id = 2;
```

結果：配列の末尾に連想配列が挿入され更新

| tags（更新前） | 
|:------|
| [{"os": "Linux", "name": "MySQL", "version": "5.7"}] |

| tags（更新後） | 
|:------|
| [{"os": "Linux", "name": "MySQL", "version": "5.7"}, {"name": "初変"}] |


▼更新方法３※登録方法３レコード利用

```SQL
> UPDATE `test_book` SET tags = JSON_MERGE( tags, JSON_ARRAY( JSON_OBJECT( "name", "kameda" ) ) ) WHERE id = 3;
```

結果：配列の末尾に連想配列が挿入され更新

| tags（更新前） | 
|:------|
|  [{"name": "火鼻", "price": 2000}, {"name": "IQ60", "price": 1000}] |

| tags（更新後） | 
|:------|
|  [{"name": "火鼻", "price": 2000}, {"name": "IQ60", "price": 1000}, {"name": "初変"}"] |


#### 所感
* JSON_MERGEとJSON_OBJECTで検証した結果と出力される結果は変わらない
* 文字数を考えれば、JSON_MERGEとJSON_OBJECTの組み合わせで十分と思われる

---------------------------------------------------------

### 4. JSON_REMOVEを使ったJSONデータの操作
* UPDATE操作時に作成したレコードを対象して検証する
* DELETEではなくJSON_REMOVEを利用して検証する


▼REMOVE操作１※JSON_MERGEを使った更新方法１のレコードを利用

```SQL
> UPDATE `test_book` SET tags = JSON_REMOVE( tags, '$[3]' ) WHERE id = 1;
```

結果：tagsカラムの配列[3]の値を削除 ( [0, 1, 2, 3] )

| tags（更新前） | 
|:------|
| ["JavaScript", "ES2015", "JSON", "technical"] |

| tags（更新後） | 
|:------|
| ["JavaScript", "ES2015", "JSON"] |



▼REMOVE操作２※JSON_MERGEを使った更新方法２のレコードを利用

```SQL
> UPDATE `test_book` SET tags = JSON_REMOVE( tags, '$[1]' ) WHERE id = 2;
```

結果：tagsカラムの配列[1]の値を削除 ( [0, 1] )


| tags（更新前） | 
|:------|
|  [{"os": "Linux", "name": "MySQL", "version": "5.7"}, "technical"] |

| tags（更新後） | 
|:------|
|  [{"os": "Linux", "name": "MySQL", "version": "5.7"}] |


▼REMOVE操作３※JSON_MERGEを使った更新方法３のレコードを利用

```SQL
> UPDATE `test_book` SET tags = JSON_REMOVE( tags, '$[2]' ) WHERE id = 3;
```


結果：tagsカラムの配列[2]の値を削除 ( [0, 1, 2] )

| tags（更新前） | 
|:------|
|  [{"name": "火鼻", "price": 2000}, {"name": "IQ60", "price": 1000}, "technical"] |

| tags（更新後） | 
|:------|
|  [{"name": "火鼻", "price": 2000}, {"name": "IQ60", "price": 1000}] |



▼REMOVE操作１※JSON_MERGEとJSON_OBJECTを使った更新方法１のレコードを利用

```SQL
> UPDATE `test_book` SET tags = JSON_REMOVE( tags, '$[3].name' ) WHERE id = 1;
```

結果：tagsカラムの配列[3]の要素（name）の値を削除 ( [0, 1, 2, 3] )

| tags（更新前） | 
|:------|
| ["JavaScript", "ES2015", "JSON", {“name” : “初変”}] |

| tags（更新後） | 
|:------|
| ["JavaScript", "ES2015", "JSON", {}] |


▼REMOVE操作３※JSON_MERGEとJSON_OBJECTを使った更新方法３のレコードを利用

```SQL
> UPDATE `test_book` SET tags = JSON_REMOVE( tags, '$[2].name' ) WHERE id = 3;
```

結果：tagsカラムの配列[2]の要素（name）の値を削除 ( [0, 1, 2] )

| tags（更新前） | 
|:------|
| [{"name": "火鼻", "price": 2000}, {"name": "IQ60", "price": 1000}, {"name": "初変"}"] |

| tags（更新後） | 
|:------|
| [{"name": "火鼻", "price": 2000}, {"name": "IQ60", "price": 1000}, {}"] |



### 5. JSON_REPLACEを使ったJSONデータの操作
* JSON型カラムの値の任意の要素値を変更する
* JSON_REPLACEを使用

▼REPLACE操作２※JSON_MERGEとJSON_OBJECTを使った更新方法２のレコードを利用

```SQL
> UPDATE `test_book` SET tags = JSON_REPLACE( tags, '$[0].os', 'windows10', '$[0].version', '10.10' ) WHERE id = 2;
```

結果：tagsカラムの配列[0]の要素（os, version）の値を置換 ( [0, 1] )

| tags（更新前） | 
|:------|
| [{"os": "Linux", "name": "MySQL", "version": "5.7"}, {"name": "初変"}] |

| tags（更新後） | 
|:------|
| [{"os": "windows10", "name": "MySQL", "version": "10.10"}, {"name": "初変"}] |

## References
『[12.17.4 Functions That Modify JSON Values](https://dev.mysql.com/doc/refman/5.7/en/json-modification-functions.html#function_json-append)』
『[MySQLでJSON型を使う（基本編）](https://www.s-style.co.jp/blog/2017/05/301/)』
『[JSON型の使い方（検索、抽出、挿入、更新、置換）](https://www.wakuwakubank.com/posts/507-mysql-json/)』
『[もう知ってた？MySQL5.7でNoSQLっぽくJSONデータを扱う方法](https://www.webprofessional.jp/use-json-data-fields-mysql-databases/)』
『[MySQLでJSON](http://wakatta.xyz/i29/)』