mysql8.0以降に追加されたJSON型

# はじめに
`MySQL` は `MySQL8.0` になってからめちゃくちゃ進化したらしいな

JSON型ってなんだよ
NoSQL の非構造化データをカラムごとに扱えるとか言われても難しいよ


## JSON型ってなに
* nosql と同じ使用感で利用可能
* カラムごとに使用でき、CREATE文でデータ型をJSONと記述するだけ

***

### CREATE文
```sql
CREATE TABLE `test` (
  `id` int(11) NOT NULL COMMENT '謎ID' AUTO_INCREMENT PRIMARY KEY,
  `pattern` char(3) NOT NULL COMMENT 'pattern:問題パターン(A,B,C,D)',
  `format_or_hint` JSON NOT NULL COMMENT '[format:回答形式(手入力), hint:解答群]',
  `images` JSON NOT NULL COMMENT '問題画像[ラベル, ファイルパス]',
  `answer` JSON NOT NULL COMMENT '[こたえ群]'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '謎';
```


### INSERT文
##### JSON_OBJECT関数
key-value のデータを格納
* 引数の個数は必ず偶数個
* 引数の奇数番目と偶数番目で JSON object となる
```sql
JSON_OBJECT("key1", "val1") == {"key1", "val1"}
```

##### JSON_ARRAY関数
配列データを格納
```sql
JSON_ARRAY("apple", "orange") == ["apple", "orange"]
```

```sql
insert into test values(1, 'A', JSON_OBJECT('format','ひらがな'), JSON_ARRAY('q001.png'), JSON_ARRAY('とけい'));
insert into test values(2, 'A', JSON_OBJECT('format','カタカナ'), JSON_ARRAY('q002-1.png', 'q002-1.png'), JSON_ARRAY('カキ','ｶｷ'));
insert into test (pattern, format_or_hint, images, answer)
  values('B', JSON_OBJECT("format","半角英数字"), JSON_OBJECT("A","B1-1.jpg","B","B1-2.jpg","LAST","B1-3.jpg"), JSON_ARRAY("ジャマイカ"));
insert into test (pattern, format_or_hint, images, answer)
  values('D', JSON_ARRAY('ひかり','こだま','つばさ','あさひ'), JSON_ARRAY('DDD.gif'), JSON_ARRAY('こだま'));
```

##### 関数を使用しない場合
```sql
insert into test (pattern, format_or_hint, images, answer)
  values('A', '{"format":"漢字"}', '["abc.png"]', '["東京都"]');
insert into test (pattern, format_or_hint, images, answer)
  values('C', '{"format":"数字"}', '["def.png","gg.png","hh.png"]', '["59","５９"]');
```


### SELECT文
```sql
-- SELECT JSON_OBJECT(Q_answer, '') from test;
-- SELECT JSON_EXTRACT(Q_answer, '') from test;
SELECT * FROM test;
SELECT Q_answer FROM test;
-- 結局文字列で取得なので、いつも通りでよい。
```

#### JSON 内部の検索
###### JSON_CONTAINS(target, candidate[, path])
指定値を含むか判定
###### JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...)
指定パスを含むか判定

##### JSON_EXTRACT(json_doc, path[, path] ...)
指定パスのデータ抽出
```sql
select images, json_extract(images, '$') from test;
select images, json_extract(images, '$[0]') from test;
select format_or_hint, json_extract(format_or_hint, '$.format') from test;
select * from test where json_extract(format_or_hint, '$.format') = '漢字';
select * from test where json_extract(format_or_hint, '$.format') like '%字%';
```

###### column->path
JSON_EXTRACT(column, path) と同等
###### column->>path
JSON_UNQUOTE(JSON_EXTRACT(column, path)) と同等
JSON_UNQUOTE(column -> path) と同等

##### JSON_KEYS(json_doc[, path])
キーだけを抽出
```sql
select format_or_hint, json_keys(format_or_hint) from test;
```

###### JSON_OVERLAPS(json_doc1, json_doc2)
2つの JSON ドキュメントを比較

##### JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])
指定値が含まれるパスを検索
```sql
select format_or_hint, json_search(format_or_hint, 'all', '漢字') from test;
```

##### JSON_VALUE(json_doc, path)
値だけを抽出
```sql
select format_or_hint, json_value(format_or_hint, '$.format') from test;
```


### UPDATE文

<!-- 
##### key-valueを追加する場合
JSON_MERGE()関数を使用する
```sql
UPDATE test SET pattern = JSON_MERGE('key3', 'val3') WHERE id=4;
UPDATE test SET pattern = JSON_MERGE_PRESERVE('key3', 'val3') WHERE id=4;
```
 -->

#### 配列を追加する
##### JSON_ARRAY_APPEND(json_doc, path, val[, path, val] ...)
* json_doc : 追加する元データの列名
* path : $を起点に配置したい階層等を指定 (\$, \$[1], \$[1][0])
* val : 追加するデータ
```sql
UPDATE test SET images = JSON_ARRAY_APPEND(images, '$', 'hyoo.jpg') WHERE id=7;
-- ["abc.png"] → ["abc.png", "hyoo.jpg"]
UPDATE test SET images = JSON_ARRAY_APPEND(images, '$[1]', 'hyoo.jpg') WHERE id=7;
-- ["abc.png", "hyoo.jpg"] → ["abc.png", ["hyoo.jpg", "hyoo.jpg"]]
UPDATE test SET images = JSON_ARRAY_APPEND(images, '$[0]', 'hyoo.jpg') WHERE id=7;
-- ["abc.png"] → [["abc.png", "hyoo.jpg"]]
```

#### 配列に要素を追加する
##### JSON_ARRAY_INSERT(json_doc, path, val[, path, val] ...)
* json_doc : 追加する元データの列名
* path : $を起点に配置したい階層等を指定 ```(\$[0], \$[99], \$[1][0])```
* val : 追加するデータ
```sql
UPDATE test SET images = JSON_ARRAY_INSERT(images, '$[1]', 'hyoo.jpg') WHERE id=7;
-- ["abc.png"] → ["abc.png", "hyoo.jpg"]
```

#### json要素を追加する
##### JSON_INSERT(json_doc, path, val[, path, val] ...)
* json_doc : 追加する元データの列名
* path : key
* val : value
```sql
UPDATE test SET format_or_hint = JSON_INSERT(format_or_hint, '$.test', 'kyu') WHERE id=7;
-- {"format": "漢字"} → {"format": "漢字", "test": "kyu"}
```

#### json要素を結合する
##### JSON_MERGE(json_doc, json_doc[, json_doc] ...)
```sql
SELECT JSON_MERGE('[1,2]', '["i", "ro"]');
-- [1, 2, "i", "ro"]
UPDATE test SET answer = JSON_MERGE(answer, '["169"]') WHERE id=8;
-- ["59", "５９"] → ["59", "５９", "169"]
SELECT JSON_MERGE('{"name": "x"}', '{"id": 47}');
-- {"name": "x", "id": 47}
```

#### 頭パンクしますよ
JSON_MERGE_PATCH()
JSON_MERGE_PRESERVE()


#### json要素を削除する
##### JSON_REMOVE(json_doc, path[, path] ...)
```sql
SELECT JSON_REMOVE('["a", ["b", "c"], "d"]', '$[1]');
-- ["a", "d"]
```

#### json要素を置換する
##### JSON_REPLACE(json_doc, path, val[, path, val] ...)
```sql
SELECT JSON_REPLACE('{ "a": 1, "b": [2, 3]}', '$.a', 10 '$.c', '[true, false]');
-- {"a": 10, "b": [2, 3]}
```

#### json要素を設置する
##### JSON_SET(json_doc, path, val[, path, val] ...)
```sql
SELECT JSON_SET('{ "a": 1, "b": [2, 3]}', '$.a', 10 '$.c', '[true, false]');
-- {"a": 10, "b": [2, 3], "c": "[true, false]"}
```

#### さっぱり意味不明
JSON_UNQUOTE()


### 参考文献
[NoSQLどんなデータが向いている？](https://academy.gmocloud.com/keywords/20170222/4142)
[NoSQLとは？RDBMSと比較しながら解説](https://academy.gmocloud.com/qa/20160509/2284)
[MySQLでNoSQLを使ってみる](https://qiita.com/ming_hentech/items/dd5b90fa7dd32f24d743)
[JSON を扱う - MariaDB Server](https://mariadb.com/ja/resources/blog/using-json-with-mariadb-server/)
[mysqlのjson型のupdate、insert](https://qiita.com/tentatsu/items/116f061ca2be88f24b48)
[MySQL 8.0 リファレンスマニュアル JSON 値を変更する関数](https://dev.mysql.com/doc/refman/8.0/ja/json-modification-functions.html)
[JSON型の使い方(検索, 抽出, 挿入, 更新, 置換)](https://www.wakuwakubank.com/posts/507-mysql-json/)
[MySQL 8.0 リファレンスマニュアル JSON 値を検索する関数](https://dev.mysql.com/doc/refman/8.0/ja/json-search-functions.html)