MySQLでJSONを扱う

mysql8上でJSON Data Typeを試してみる。

[JSON Data Type](https://dev.mysql.com/doc/refman/8.0/en/json.html)は5.7からサポートされた機能、のはず。

mysql6, 7の経緯は[こちら](http://nippondanji.blogspot.com/2018/05/mysql-80.html)を参照すると良いかも。

# dockerでmysqlを準備する


## データ移行を特に考えない場合

とりあえずすぐに使ってみたい場合は淡々と下記のコマンドを打ち込んでいけばよい。

```
# docker pull mysql:8.0.12
# docker run --name mysql8 -e MYSQL_ROOT_PASSWORD=mysqlpass --restart always -d mysql:8.0.12
# docker exec -it mysql8 bash
root@061095cb1b39:/# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.12 MySQL Community Server - GPL

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 
```

docker execしないでmysqlサーバーにアクセスする場合は下記を参考。

3306ポートをdockerのmysqlコンテナに向けて上げる方法もある。

```
# docker inspect mysql8 | jq .[0].NetworkSettings.IPAddress
"172.17.0.2"

$ mysql -u root -p -h 172.17.0.2
  Enter password: 
  Welcome to the MySQL monitor.  Commands end with ; or \g.
  Your MySQL connection id is 15
  Server version: 8.0.12 MySQL Community Server - GPL
  
  Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
  
  Oracle is a registered trademark of Oracle Corporation and/or its
  affiliates. Other names may be trademarks of their respective
  owners.
  
  Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  
```

## データ移行を考える場合。

先のデータ移行を考えない場合でもすでに下記の通り、 `/var/lib/mysql` だけをコンテナをホストしているシステムで保存してはいる。

```
# docker inspect mysql8 | jq .[0].Mounts
[
  {
    "Type": "volume",
    "Name": "d5dbc5f20b14c40405bb43dd29c10419f7ff8b9e834ae0408a7ff587496ab9e5",
    "Source": "/var/lib/docker/volumes/d5dbc5f20b14c40405bb43dd29c10419f7ff8b9e834ae0408a7ff587496ab9e5/_data",
    "Destination": "/var/lib/mysql",
    "Driver": "local",
    "Mode": "",
    "RW": true,
    "Propagation": ""
  }
]
# docker volume ls
DRIVER              VOLUME NAME
local               d5dbc5f20b14c40405bb43dd29c10419f7ff8b9e834ae0408a7ff587496ab9e5
```

まずはデータ移行を考えてボリュームを作成する。

```
# docker volume create mysql-data
mysql-data
# docker volume ls
DRIVER              VOLUME NAME
local               mysql-data
```

作成したボリュームを使ってmysqlコンテナを起動する。

```
# docker run -v mysql-data:/var/lib/mysql --name mysql8 -e MYSQL_ROOT_PASSWORD=mysqlp -d -p 3306:3306 mysql:8.0.12
$ mysql -u root -p -h 127.0.0.1
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.12 MySQL Community Server - GPL

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
mysql> create database json_sample;
mysql> use json_sample;
mysql> create table jtable (jcol JSON);
mysql> insert into jtable values('{"a": 100}');
mysql> select * from jtable;
+------------+
| jcol       |
+------------+
| {"a": 100} |
+------------+
mysql> select jcol->>"$.a" from jtable;
+--------------+
| jcol->>"$.a" |
+--------------+
| 100          |
+--------------+

# docker stop mysql8
mysql8
```

他のコンテナ(今回はubuntu)を使ってvolumeをマウントし、ローカルのカレントディレクトリにtar.gzとしてmysqlのデータを出力する。

mysql-data2というボリュームを新たに作成し、そこにリストアする。

mysqlコンテナの2回目の起動時はパスワードを設定する必要はない。

```
# docker run -v mysql-data:/target -v `pwd`:/export -w /target ubuntu bash -c 'tar zcvf /export/mysql-data.YYYYMMDD.tar.gz .'
# docker volume create mysql-data2
mysql-data2
# docker volume ls
DRIVER              VOLUME NAME
local               mysql-data
local               mysql-data2
# docker run -v mysql-data2:/target -v `pwd`:/import -w /target ubuntu bash -c 'tar zxvf /import/mysql-data.YYYYMMDD.tar.gz'
# docker run -v mysql-data2:/var/lib/mysql --name mysql8_2 -d -p 3306:3306 mysql:8.0.12
# docker exec -it mysql8_2 bash
もしくは
# mysql -u root -p -h 127.0.0.1
mysql> use json_sample;
mysql> select * from jtable;
+------------+
| jcol       |
+------------+
| {"a": 100} |
+------------+
```

こうすることでテストに使うデータセット別のmysqlサーバーを用意して素早く利用することなどができる。

参考にした記事
- https://qiita.com/mopemope/items/b05ff7f603a5ad74bf55

# JSON Data Type

JSONの検証は下記から。

こんな感じでカラムに適用できる。

```
mysql> show create table book\G
*************************** 1. row ***************************
       Table: book
Create Table: CREATE TABLE `book` (
  `id` mediumint(8) NOT NULL AUTO_INCREMENT,
  `title` varchar(200) NOT NULL,
  `tags` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)
```

- jsonデータの自動validationが有効になる
- ストレージに最適化されバイナリ形式で保存される。
- jsonカラムに格納されるサイズは `max_allowed_packet` で定義される
- jsonカラムにはデフォルト値を設定できない
- json関数群を使えるようになる。
- GeoJSONも使えるようになる。
- jsonカラムに対しては直接インデックスを作成できない。ただし、jsonデータのスカラー値に対してはインデックスを作成できる。

## 基本的な使い方

```
mysql> create table users (data JSON);
mysql> insert into users values (json_object('name', 'taro', 'age', 20));
mysql> insert into users values (json_object('name', 'jiro', 'age', 15));
mysql> insert into users values (json_object('name', 'saburo', 'age', 10));
mysql> select data->>"$.name" from users;
+-----------------+
| data->>"$.name" |
+-----------------+
| taro            |
| jiro            |
| saburo          |
+-----------------+
mysql> select * from users where data->>"$.age" > 14;
+-----------------------------+
| data                        |
+-----------------------------+
| {"age": 20, "name": "taro"} |
| {"age": 15, "name": "jiro"} |
+-----------------------------+
mysql> select * from users where data->>"$.age" > 9;
+-------------------------------+
| data                          |
+-------------------------------+
| {"age": 20, "name": "taro"}   |
| {"age": 15, "name": "jiro"}   |
| {"age": 10, "name": "saburo"} |
+-------------------------------+
mysql> update users set data = json_set(data, '$.age', json_extract(data, '$.age') + 10);
mysql> select * from users;
+---------------------------------+
| data                            |
+---------------------------------+
| {"age": 30.0, "name": "taro"}   |
| {"age": 25.0, "name": "jiro"}   |
| {"age": 20.0, "name": "saburo"} |
+---------------------------------+
mysql> update users set data = json_set(data, '$.alcohol', true) where data->>"$.age" > 25;
mysql> select * from users;
+------------------------------------------------+
| data                                           |
+------------------------------------------------+
| {"age": 30.0, "name": "taro", "alcohol": true} |
| {"age": 25.0, "name": "jiro"}                  |
| {"age": 20.0, "name": "saburo"}                |
+------------------------------------------------+
mysql> select * from users where data->"$.alcohol" = true;
+------------------------------------------------+
| data                                           |
+------------------------------------------------+
| {"age": 30.0, "name": "taro", "alcohol": true} |
+------------------------------------------------+
```

`$` はある特定の状況を指し示す。 ワイルドカードを使ったアクセスも可能だ。

```
mysql> select json_extract('[3, {"a": [5, 6], "b": 10}, [99, 100]]', '$[1]') as json;
+------------------------+
| json                   |
+------------------------+
| {"a": [5, 6], "b": 10} |
+------------------------+
mysql> select json_extract('[3, {"a": [5, 6], "b": 10}, [99, 100]]', '$[1].a[0]') as json;
+------+
| json |
+------+
| 5    |
+------+
mysql> select json_extract('{"a":1, "b":2, "c":[3,4,5]}', '$.*');
+----------------------------------------------------+
| json_extract('{"a":1, "b":2, "c":[3,4,5]}', '$.*') |
+----------------------------------------------------+
| [1, 2, [3, 4, 5]]                                  |
+----------------------------------------------------+
mysql> select json_extract('{"a": {"b": 1}, "c": {"b": 2}, "d":{"e": {"b": 3}}}', '$**.b');
+------------------------------------------------------------------------------+
| json_extract('{"a": {"b": 1}, "c": {"b": 2}, "d":{"e": {"b": 3}}}', '$**.b') |
+------------------------------------------------------------------------------+
| [1, 2, 3]                                                                    |
+------------------------------------------------------------------------------+
mysql> select json_extract('{"a": {"b": 1}, "c": {"b": 2}, "d":{"e": {"b": 3}}}', '$.*.b');
+------------------------------------------------------------------------------+
| json_extract('{"a": {"b": 1}, "c": {"b": 2}, "d":{"e": {"b": 3}}}', '$.*.b') |
+------------------------------------------------------------------------------+
| [1, 2]                                                                       |
+------------------------------------------------------------------------------+
mysql> select json_extract('[1, 2, {"foo":"bar"}, 3, {"foo":"hoge"}, 5]', '$[0 to 2].foo');
+------------------------------------------------------------------------------+
| json_extract('[1, 2, {"foo":"bar"}, 3, {"foo":"hoge"}, 5]', '$[0 to 2].foo') |
+------------------------------------------------------------------------------+
| ["bar"]                                                                      |
+------------------------------------------------------------------------------+
```

functions


```
mysql> SET @j = '["a", {"b": [true, false]}, [10, 20]]';
mysql> select json_insert(@j, '$[2]', 100, '$[3]', 1, '$[2][2]', 10);
+--------------------------------------------------------+
| json_insert(@j, '$[2]', 100, '$[3]', 1, '$[2][2]', 10) |
+--------------------------------------------------------+
| ["a", {"b": [true, false]}, [10, 20, 10], 1]           |
+--------------------------------------------------------+
mysql> select json_replace(@j, '$[1].b[0]', 100, '$[3]', 777);
+-------------------------------------------------+
| json_replace(@j, '$[1].b[0]', 100, '$[3]', 777) |
+-------------------------------------------------+
| ["a", {"b": [100, false]}, [10, 20]]            |
+-------------------------------------------------+
mysql> select json_remove(@j, '$[2]', '$[1].b[1]', '$[1].b[1]');
+---------------------------------------------------+
| json_remove(@j, '$[2]', '$[1].b[1]', '$[1].b[1]') |
+---------------------------------------------------+
| ["a", {"b": [true]}]                              |
+---------------------------------------------------+
```


# JSON Functions
公式のドキュメントは[こちら](https://dev.mysql.com/doc/refman/8.0/en/json-functions.html)

計28個

|Name|Description|
| :---- | :---- |
|JSON_ARRAY()|Create JSON array|
|JSON_ARRAY_APPEND()|Append data to JSON document|
|JSON_ARRAY_INSERT()|Insert into JSON array|
|->|Return value from JSON column after evaluating path; equivalent to JSON_EXTRACT().|
|JSON_CONTAINS()|Whether JSON document contains specific object at path|
|JSON_CONTAINS_PATH()|Whether JSON document contains any data at path|
|JSON_DEPTH()|Maximum depth of JSON document|
|JSON_EXTRACT()|Return data from JSON document|
|->>|Return value from JSON column after evaluating path and unquoting the result; equivalent to JSON_UNQUOTE(JSON_EXTRACT()).|
|JSON_INSERT()|Insert data into JSON document|
|JSON_KEYS()|Array of keys from JSON document|
|JSON_LENGTH()|Number of elements in JSON document|
|JSON_MERGE() (deprecated 8.0.3)|Merge JSON documents, preserving duplicate keys. Deprecated synonym for JSON_MERGE_PRESERVE()|
|JSON_MERGE_PATCH()|Merge JSON documents, replacing values of duplicate keys|
|JSON_MERGE_PRESERVE()|Merge JSON documents, preserving duplicate keys|
|JSON_OBJECT()|Create JSON object|
|JSON_PRETTY()|Prints a JSON document in human-readable format, with each array element or object member printed on a new line, indented two spaces with respect to its parent.|
|JSON_QUOTE()|Quote JSON document|
|JSON_REMOVE()|Remove data from JSON document|
|JSON_REPLACE()|Replace values in JSON document|
|JSON_SEARCH()|Path to value within JSON document|
|JSON_SET()|Insert data into JSON document|
|JSON_STORAGE_FREE()|Freed space within binary representation of a JSON column value following a partial update|
|JSON_STORAGE_SIZE()|Space used for storage of binary representation of a JSON document; for a JSON column, the space used when the document was inserted, prior to any partial updates|
|JSON_TABLE()|Returns data from a JSON expression as a relational table|
|JSON_TYPE()|Type of JSON value|
|JSON_UNQUOTE()|Unquote JSON value|
|JSON_VALID()|Whether JSON value is valid|

以降はここに挙がっている関数群を公式ドキュメントに載っているサンプルを参考に試していく。(長い)

## Functions That Create JSON Values
計3個
- JSON_ARRAY
- JSON_OBJECT
- JSON_QUOTE

## Functions That Search JSON Values
計7個
- JSON_CONTAINS
- JSON_CONTAINS_PATH
- JSON_EXTRACT
- column->path
- column->>path
- JSON_KEYS
- JSON_SEARCH

## Functions That Modify JSON Values
計10個
- JSON_ARRAY_APPEND
- JSON_ARRAY_INSERT
- JSON_INSERT
- JSON_MERGE
- JSON_MERGE_PATCH
- JSON_MERGE_PRESERVE
- JSON_REMOVE
- JSON_REPLACE
- JSON_SET
- JSON_UNQUOTE

## Functions That Return JSON Value Attributes
計4個
- JSON_DEPTH
- JSON_LENGTH
- JSON_TYPE
- JSON_VALID

## JSON Table Functions
計1個
- JSON_TABLE

## JSON Utility Functions
計3個
- JSON_PRETTY
- JSON_STORAGE_FREE
- JSON_STORAGE_SIZE

# JSON Functions Sample

## JSON_ARRAY
json配列を返す

```
mysql> select json_array(1, "abc", null, true, curtime());
+---------------------------------------------+
| json_array(1, "abc", null, true, curtime()) |
+---------------------------------------------+
| [1, "abc", null, true, "23:30:04.000000"]   |
+---------------------------------------------+
```

## JSON_OBJECT
key valueのペアリストを評価し、オブジェクトを返す。

keyがnullの場合や引数が奇数の場合はエラー

```
mysql> select json_object('id', 87, 'name', 'taro');
+---------------------------------------+
| json_object('id', 87, 'name', 'taro') |
+---------------------------------------+
| {"id": 87, "name": "taro"}            |
+---------------------------------------+
mysql> select json_object('id', 87, 'name', 'taro', 'foo');
ERROR 1582 (42000): Incorrect parameter count in the call to native function 'json_object'
mysql> select json_object('id', 87, 'name', 'taro', null, 'yes');
ERROR 3158 (22032): JSON documents may not contain NULL member names.
```

## JSON_QUOTE
文字列をダブルクォートで囲みその他の文字をエスケープしてくれる。

引数がnullの場合はNULL

```
mysql> select json_quote(null), json_quote('null'), json_quote('"null"');
+------------------+--------------------+----------------------+
| json_quote(null) | json_quote('null') | json_quote('"null"') |
+------------------+--------------------+----------------------+
| NULL             | "null"             | "\"null\""           |
+------------------+--------------------+----------------------+
```

## JSON_CONTAINS
特定の候補がターゲットに含まれているかをチェックし1か0を返す

pathを指定することもできる。

```
mysql> SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}';
mysql> select json_contains(@j, '{"a":1}');
+------------------------------+
| json_contains(@j, '{"a":1}') |
+------------------------------+
|                            1 |
+------------------------------+
mysql> select json_contains(@j, '1', "$.a");
+-------------------------------+
| json_contains(@j, '1', "$.a") |
+-------------------------------+
|                             1 |
+-------------------------------+
mysql> select json_contains(@j, '{"d":4}', "$.c");
+-------------------------------------+
| json_contains(@j, '{"d":4}', "$.c") |
+-------------------------------------+
|                                   1 |
+-------------------------------------+
mysql> select json_contains(@j, '4', "$.c.d");
+---------------------------------+
| json_contains(@j, '4', "$.c.d") |
+---------------------------------+
|                               1 |
+---------------------------------+
```

## JSON_CONTAINS_PATH
JSONドキュメントに引数に与えられたパスもしくは複数のパスが含まれているかを確認し0か1を返す。

引数がNULLの場合はNULLを返す。

`one` もしくは `all` という引数を与えて、パスが一つでも含まれている場合と全て含まれている場合の条件を指定することができる。

```
mysql> SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}';
mysql> select json_contains_path(@j, 'one', '$.foo', '$.bar');
+-------------------------------------------------+
| json_contains_path(@j, 'one', '$.foo', '$.bar') |
+-------------------------------------------------+
|                                               0 |
+-------------------------------------------------+
mysql> select json_contains_path(@j, 'one', '$.foo', '$.bar', '$.a');
+--------------------------------------------------------+
| json_contains_path(@j, 'one', '$.foo', '$.bar', '$.a') |
+--------------------------------------------------------+
|                                                      1 |
+--------------------------------------------------------+
mysql> select json_contains_path(@j, 'all', '$.foo', '$.bar', '$.a');
+--------------------------------------------------------+
| json_contains_path(@j, 'all', '$.foo', '$.bar', '$.a') |
+--------------------------------------------------------+
|                                                      0 |
+--------------------------------------------------------+
mysql> select json_contains_path(@j, 'all', '$.a', '$.b');
+---------------------------------------------+
| json_contains_path(@j, 'all', '$.a', '$.b') |
+---------------------------------------------+
|                                           1 |
+---------------------------------------------+
mysql> select json_contains_path(@j, 'one', '$.c.d');
+----------------------------------------+
| json_contains_path(@j, 'one', '$.c.d') |
+----------------------------------------+
|                                      1 |
+----------------------------------------+
mysql> select json_contains_path(@j, 'one', '$.a.d');
+----------------------------------------+
| json_contains_path(@j, 'one', '$.a.d') |
+----------------------------------------+
|                                      0 |
+----------------------------------------+
```

## JSON_EXTRACT

JSONドキュメントからpathにマッチしたデータを返す。pathがマッチしない場合はNULLを返す。

複数マッチするバスの場合は配列データとして返される。

```
mysql> select json_extract('[10, 20, [30, 40]]', '$[1]');
+--------------------------------------------+
| json_extract('[10, 20, [30, 40]]', '$[1]') |
+--------------------------------------------+
| 20                                         |
+--------------------------------------------+
mysql> select json_extract('[10, 20, [30, 40]]', '$[2][1]');
+-----------------------------------------------+
| json_extract('[10, 20, [30, 40]]', '$[2][1]') |
+-----------------------------------------------+
| 40                                            |
+-----------------------------------------------+
mysql> select json_extract('[10, 20, [30, 40]]', '$[0]', '$[2][1]');
+-------------------------------------------------------+
| json_extract('[10, 20, [30, 40]]', '$[0]', '$[2][1]') |
+-------------------------------------------------------+
| [10, 40]                                              |
+-------------------------------------------------------+
mysql> select json_extract('[10, 20, [30, 40]]', '$[0]', '$[2][1]', '$[2][*]');
+------------------------------------------------------------------+
| json_extract('[10, 20, [30, 40]]', '$[0]', '$[2][1]', '$[2][*]') |
+------------------------------------------------------------------+
| [10, 40, 30, 40]                                                 |
+------------------------------------------------------------------+
```

## column->path

`->` オペレーターは `JSON_EXTRACT` のエイリアスとなっている。


データを作成する

```
mysql> create table json_samples ( data json);
mysql> insert into json_samples (data) values (json_object("id", 2, "name", "Wilma"));
mysql> insert into json_samples (data) values (json_object("id", 3, "name", "Barney"));
mysql> insert into json_samples (data) values (json_object("id", 4, "name", "Betty"));
mysql> select * from json_samples;
+-----------------------------+
| data                        |
+-----------------------------+
| {"id": 3, "name": "Barney"} |
| {"id": 4, "name": "Betty"}  |
| {"id": 2, "name": "Wilma"}  |
+-----------------------------+
```

オペレーターを試す。

```
mysql> select data, json_extract(data, "$.id"), json_extract(data, "$.name") from json_samples where json_extract(data, "$.id") > 2 order by json_extract(data, "$.name") desc;
+-----------------------------+----------------------------+------------------------------+
| data                        | json_extract(data, "$.id") | json_extract(data, "$.name") |
+-----------------------------+----------------------------+------------------------------+
| {"id": 4, "name": "Betty"}  | 4                          | "Betty"                      |
| {"id": 3, "name": "Barney"} | 3                          | "Barney"                     |
+-----------------------------+----------------------------+------------------------------+

mysql> select data, data->"$.id", data->"$.name" from json_samples where data->"$.id" > 2 order by data->"$.name" desc;
+-----------------------------+--------------+----------------+
| data                        | data->"$.id" | data->"$.name" |
+-----------------------------+--------------+----------------+
| {"id": 4, "name": "Betty"}  | 4            | "Betty"        |
| {"id": 3, "name": "Barney"} | 3            | "Barney"       |
+-----------------------------+--------------+----------------+

```

## column->>path

`column->path` が抽出したデータをunquoteしてくれなかった部分を改良してquoteしてくれるようになっている。

`JSON_UNQUOTE(JSON_EXTRACT())` しているのと同じこと。

```
mysql> select data, data->>"$.id", data->>"$.name" from json_samples where data->"$.id" > 2 order by data->"$.name" desc;
+-----------------------------+---------------+-----------------+
| data                        | data->>"$.id" | data->>"$.name" |
+-----------------------------+---------------+-----------------+
| {"id": 4, "name": "Betty"}  | 4             | Betty           |
| {"id": 3, "name": "Barney"} | 3             | Barney          |
+-----------------------------+---------------+-----------------+
mysql> select data, json_extract(data, "$.id"), json_unquote(json_extract(data, "$.name")) from json_samples where json_extract(data, "$.id") > 2 order by json_extract(data, "$.name") desc;
+-----------------------------+----------------------------+--------------------------------------------+
| data                        | json_extract(data, "$.id") | json_unquote(json_extract(data, "$.name")) |
+-----------------------------+----------------------------+--------------------------------------------+
| {"id": 4, "name": "Betty"}  | 4                          | Betty                                      |
| {"id": 3, "name": "Barney"} | 3                          | Barney                                     |
+-----------------------------+----------------------------+--------------------------------------------+
```

## JSON_KEYS
JSONオブジェクトからtop-levelのkeyを配列で返す。

pathが指定された場合は選択されたpathのtop-levelのkeyの配列をを返す。pathにワイルドカード指定はできない。

```
mysql> select json_keys('{"a":1,"b":{"c":30}}');
+-----------------------------------+
| json_keys('{"a":1,"b":{"c":30}}') |
+-----------------------------------+
| ["a", "b"]                        |
+-----------------------------------+
mysql> select json_keys('{"a":1,"b":{"c":30}}', '$.b');
+------------------------------------------+
| json_keys('{"a":1,"b":{"c":30}}', '$.b') |
+------------------------------------------+
| ["c"]                                    |
+------------------------------------------+
```

## JSON_SEARCH

`JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])`

与えられた文字列を含む場合にそのpathを返す。含まない場合はNULLを返す。

`one` は最初にマッチしたpathを一つ返す。 `all` はマッチするパス全てを返す。

`%` と `_` はLIKE検索の時に使うオペレータと同等に機能する。 `%` は複数の文字にマッチし `_` は一つの文字にマッチする。

`escape_char` でエスケープ文字列を指定することができる。指定しない場合は何も記述しないかNULLをパラメータとして渡す。



```
mysql> set @j='["abc", [{"k":"10"}, "def"], {"x":"abc"}, {"y":"bcd"}]';
mysql> select json_search(@j, 'one', 'abc');
+-------------------------------+
| json_search(@j, 'one', 'abc') |
+-------------------------------+
| "$[0]"                        |
+-------------------------------+
mysql> select json_search(@j, 'all', 'abc');
+-------------------------------+
| json_search(@j, 'all', 'abc') |
+-------------------------------+
| ["$[0]", "$[2].x"]            |
+-------------------------------+
mysql> select json_search(@j, 'all', 'ghi');
+-------------------------------+
| json_search(@j, 'all', 'ghi') |
+-------------------------------+
| NULL                          |
+-------------------------------+
mysql> select json_search('["hoge%%"]', 'all', 'hoge|%|%', '|');
+---------------------------------------------------+
| json_search('["hoge%%"]', 'all', 'hoge|%|%', '|') |
+---------------------------------------------------+
| "$[0]"                                            |
+---------------------------------------------------+
mysql> select json_search(@j, 'all', 'abc');
+-------------------------------+
| json_search(@j, 'all', 'abc') |
+-------------------------------+
| ["$[0]", "$[2].x"]            |
+-------------------------------+
mysql> select json_search(@j, 'all', 'abc', NULL, '$[2]');
+---------------------------------------------+
| json_search(@j, 'all', 'abc', NULL, '$[2]') |
+---------------------------------------------+
| "$[2].x"                                    |
+---------------------------------------------+
```


## JSON_ARRAY_APPEND

指定のpathに与えられたvalueをappendする

mysql5.7ではJSON_APPENDという名前だったが、mysql8ではサポートされていない。

```
mysql> set @j = '["a", ["b", "c"], "d"]';
mysql> select json_array_append(@j, '$[1]', 1);
+----------------------------------+
| json_array_append(@j, '$[1]', 1) |
+----------------------------------+
| ["a", ["b", "c", 1], "d"]        |
+----------------------------------+
mysql> select json_array_append(@j, '$[2]', 1);
+----------------------------------+
| json_array_append(@j, '$[2]', 1) |
+----------------------------------+
| ["a", ["b", "c"], ["d", 1]]      |
+----------------------------------+
mysql> select json_array_append(@j, '$[0]', 1);
+----------------------------------+
| json_array_append(@j, '$[0]', 1) |
+----------------------------------+
| [["a", 1], ["b", "c"], "d"]      |
+----------------------------------+
mysql> select json_array_append(@j, '$', 1);
+-------------------------------+
| json_array_append(@j, '$', 1) |
+-------------------------------+
| ["a", ["b", "c"], "d", 1]     |
+-------------------------------+
mysql> select json_array_append(@j, '$[1][0]', 1);
+-------------------------------------+
| json_array_append(@j, '$[1][0]', 1) |
+-------------------------------------+
| ["a", [["b", 1], "c"], "d"]         |
+-------------------------------------+
mysql> select json_array_append(@j, '$[1]', json_array(1,2,3));
+--------------------------------------------------+
| json_array_append(@j, '$[1]', json_array(1,2,3)) |
+--------------------------------------------------+
| ["a", ["b", "c", [1, 2, 3]], "d"]                |
+--------------------------------------------------+
mysql> select json_array_append(@j, '$[1]', 1, '$[1]', 2, '$[1]', 3);
+--------------------------------------------------------+
| json_array_append(@j, '$[1]', 1, '$[1]', 2, '$[1]', 3) |
+--------------------------------------------------------+
| ["a", ["b", "c", 1, 2, 3], "d"]                        |
+--------------------------------------------------------+
```

hashで試す

```
mysql> set @j = '{"a":1, "b":[2,3], "c":4}';
mysql> select json_array_append(@j, '$.b', 'x');
+------------------------------------+
| json_array_append(@j, '$.b', 'x')  |
+------------------------------------+
| {"a": 1, "b": [2, 3, "x"], "c": 4} |
+------------------------------------+
mysql> select json_array_append(@j, '$.c', 'x');
+--------------------------------------+
| json_array_append(@j, '$.c', 'x')    |
+--------------------------------------+
| {"a": 1, "b": [2, 3], "c": [4, "x"]} |
+--------------------------------------+
mysql> select json_array_append(@j, '$', 'x');
+--------------------------------------+
| json_array_append(@j, '$', 'x')      |
+--------------------------------------+
| [{"a": 1, "b": [2, 3], "c": 4}, "x"] |
+--------------------------------------+
```

## JSON_ARRAY_INSERT

例の最後にあるように、複数のpathとvalueを指定する場合は、最初の修正は後続のpathに影響を与えるので注意が必要。

```
mysql> set @j = '["a", {"b": [1,2]}, [3,4]]';
mysql> select json_array_insert(@j, '$[1]', 'x');
+------------------------------------+
| json_array_insert(@j, '$[1]', 'x') |
+------------------------------------+
| ["a", "x", {"b": [1, 2]}, [3, 4]]  |
+------------------------------------+
mysql> select json_array_insert(@j, '$[100]', 'x');
+--------------------------------------+
| json_array_insert(@j, '$[100]', 'x') |
+--------------------------------------+
| ["a", {"b": [1, 2]}, [3, 4], "x"]    |
+--------------------------------------+
mysql> select json_array_insert(@j, '$[1].b[0]', 'x');
+-----------------------------------------+
| json_array_insert(@j, '$[1].b[0]', 'x') |
+-----------------------------------------+
| ["a", {"b": ["x", 1, 2]}, [3, 4]]       |
+-----------------------------------------+
mysql> select json_array_insert(@j, '$[2][1]', 'y');
+---------------------------------------+
| json_array_insert(@j, '$[2][1]', 'y') |
+---------------------------------------+
| ["a", {"b": [1, 2]}, [3, "y", 4]]     |
+---------------------------------------+
mysql> select json_array_insert(@j, '$[0]', 'x', '$[2][1]', 'y');
+----------------------------------------------------+
| json_array_insert(@j, '$[0]', 'x', '$[2][1]', 'y') |
+----------------------------------------------------+
| ["x", "a", {"b": [1, 2]}, [3, 4]]                  |
+----------------------------------------------------+
```

## JSON_INSERT

`path-value` は左から右に評価される。

pathが存在する場合、何もインサートされない。

```
mysql> set @j = '{"a":1, "b":[2,3]}';
mysql> select json_insert(@j, '$.a', 10);
+----------------------------+
| json_insert(@j, '$.a', 10) |
+----------------------------+
| {"a": 1, "b": [2, 3]}      |
+----------------------------+
mysql> select json_insert(@j, '$.a', 10, '$.c', '[true, false]');
+----------------------------------------------------+
| json_insert(@j, '$.a', 10, '$.c', '[true, false]') |
+----------------------------------------------------+
| {"a": 1, "b": [2, 3], "c": "[true, false]"}        |
+----------------------------------------------------+
mysql> select json_insert(@j, '$.a', 10, '$.c', cast('[true, false]' as json));+------------------------------------------------------------------+
| json_insert(@j, '$.a', 10, '$.c', cast('[true, false]' as json)) |
+------------------------------------------------------------------+
| {"a": 1, "b": [2, 3], "c": [true, false]}                        |
+------------------------------------------------------------------+
mysql> select json_insert(@j, '$.a', 10, '$.c', json_array(true, false));
+------------------------------------------------------------+
| json_insert(@j, '$.a', 10, '$.c', json_array(true, false)) |
+------------------------------------------------------------+
| {"a": 1, "b": [2, 3], "c": [true, false]}                  |
+------------------------------------------------------------+
```

## JSON_MERGE

2個かそれ以上のJSON documentを連結する。 8.0.3で廃止予定になっている。 `JSON_MERGE_PRESERVE`が代替。

```
mysql> select json_merge('[1,2]', '3', '[true, false]', '{"a":100}');
+--------------------------------------------------------+
| json_merge('[1,2]', '3', '[true, false]', '{"a":100}') |
+--------------------------------------------------------+
| [1, 2, 3, true, false, {"a": 100}]                     |
+--------------------------------------------------------+
mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1287
Message: 'JSON_MERGE' is deprecated and will be removed in a future release. Please use JSON_MERGE_PRESERVE/JSON_MERGE_PATCH instead
```

## JSON_MERGE_PATCH

8.0.3以降でサポートしている関数

最初の引数がobjectでない場合空のobjectつまり '{}' が2番目の引数にマージされるのと同じことになる。

2番めの引数がobjectでない場合、結果は2番目の引数と同じになる。

```
mysql> select json_merge_patch('[1,2]', '[true,false]');
+-------------------------------------------+
| json_merge_patch('[1,2]', '[true,false]') |
+-------------------------------------------+
| [true, false]                             |
+-------------------------------------------+
mysql> select json_merge_patch('{"name":"x"}', '{"id":47}');
+-----------------------------------------------+
| json_merge_patch('{"name":"x"}', '{"id":47}') |
+-----------------------------------------------+
| {"id": 47, "name": "x"}                       |
+-----------------------------------------------+

mysql> select json_merge_patch('{"a":"b"}', '[1,2]');
+----------------------------------------+
| json_merge_patch('{"a":"b"}', '[1,2]') |
+----------------------------------------+
| [1, 2]                                 |
+----------------------------------------+
mysql> select json_merge_patch('1', 'true');
+-------------------------------+
| json_merge_patch('1', 'true') |
+-------------------------------+
| true                          |
+-------------------------------+
mysql> select json_merge_patch('[1,2]', '{"foo":"bar"}');
+--------------------------------------------+
| json_merge_patch('[1,2]', '{"foo":"bar"}') |
+--------------------------------------------+
| {"foo": "bar"}                             |
+--------------------------------------------+
mysql> select json_merge_patch('{"a":1,"b":2}', '{"a":3,"c":4}');
+----------------------------------------------------+
| json_merge_patch('{"a":1,"b":2}', '{"a":3,"c":4}') |
+----------------------------------------------------+
| {"a": 3, "b": 2, "c": 4}                           |
+----------------------------------------------------+
```

nullをvalueとして与えることでkeyを削除できる

```
mysql> select json_merge_patch('{"a":1,"b":2}', '{"b": null}');
+--------------------------------------------------+
| json_merge_patch('{"a":1,"b":2}', '{"b": null}') |
+--------------------------------------------------+
| {"a": 1}                                         |
+--------------------------------------------------+
```

オブジェクトの構造も辿って修正することができる。

```
mysql> select json_merge_patch('{"a": {"b":2}}', '{"a": {"b": [1,2,3]}}');
+-------------------------------------------------------------+
| json_merge_patch('{"a": {"b":2}}', '{"a": {"b": [1,2,3]}}') |
+-------------------------------------------------------------+
| {"a": {"b": [1, 2, 3]}}                                     |
+-------------------------------------------------------------+
```

## JSON_MERGE_PRESERVE

8.0.3以降で追加された関数。それ以前の `JSON_MERGE` は廃止予定

`JSON_MERGE_PATCH` との比較は下記

```
mysql> set @x = '{"a":1, "b":2}',
    ->     @y = '{"a":3, "c":4}',
    ->     @z = '{"a":5, "d":6}';
mysql> select json_merge_patch(@x, @y, @z) as patch,
    ->        json_merge_preserve(@x, @y, @z) as preserve\G
*************************** 1. row ***************************
   patch: {"a": 5, "b": 2, "c": 4, "d": 6}
preserve: {"a": [1, 3, 5], "b": 2, "c": 4, "d": 6}

```

array同士のmergeはひとつのarrayに連結される。

```
mysql> select json_merge_preserve('[1,2]', '[true, false]');
+-----------------------------------------------+
| json_merge_preserve('[1,2]', '[true, false]') |
+-----------------------------------------------+
| [1, 2, true, false]                           |
+-----------------------------------------------+
1 row in set (0.00 sec)

mysql> select json_merge_preserve('[1,2]', '[true, false, [3,4]]');
+------------------------------------------------------+
| json_merge_preserve('[1,2]', '[true, false, [3,4]]') |
+------------------------------------------------------+
| [1, 2, true, false, [3, 4]]                          |
+------------------------------------------------------+
```

object同士のmergeはひとつのobjectに統合される。


```
mysql> select json_merge_preserve('{"name":"x"}', '{"id":47}');
+--------------------------------------------------+
| json_merge_preserve('{"name":"x"}', '{"id":47}') |
+--------------------------------------------------+
| {"id": 47, "name": "x"}                          |
+--------------------------------------------------+
mysql> select json_merge_preserve('{"name":"x"}', '{"name":"x"}');
+-----------------------------------------------------+
| json_merge_preserve('{"name":"x"}', '{"name":"x"}') |
+-----------------------------------------------------+
| {"name": ["x", "x"]}                                |
+-----------------------------------------------------+
mysql> select json_merge_patch('{"name":"x"}', '{"name":"x"}');
+--------------------------------------------------+
| json_merge_patch('{"name":"x"}', '{"name":"x"}') |
+--------------------------------------------------+
| {"name": "x"}                                    |
+--------------------------------------------------+
```

その他

```
mysql> select json_merge_preserve('1', 'true');
+----------------------------------+
| json_merge_preserve('1', 'true') |
+----------------------------------+
| [1, true]                        |
+----------------------------------+

mysql> select json_merge_preserve('[1,2]', '{"id": 100}');
+---------------------------------------------+
| json_merge_preserve('[1,2]', '{"id": 100}') |
+---------------------------------------------+
| [1, 2, {"id": 100}]                         |
+---------------------------------------------+
```


## JSON_REMOVE

JSONドキュメントからデータを削除する。

```
mysql> set @j = '["a", ["b", "c"], "d"]';
mysql> select json_remove(@j, '$[1]');
+-------------------------+
| json_remove(@j, '$[1]') |
+-------------------------+
| ["a", "d"]              |
+-------------------------+
```


## JSON_REPLACE

path-valueが存在しないpathへの置換は無視される。

```
mysql> set @j = '{"a":1, "b":[2,3]}';
mysql> select json_replace(@j, '$.a', 10, '$.c', '[true, false]');
+-----------------------------------------------------+
| json_replace(@j, '$.a', 10, '$.c', '[true, false]') |
+-----------------------------------------------------+
| {"a": 10, "b": [2, 3]}                              |
+-----------------------------------------------------+
```

## JSON_SET

`JSON_SET` と `JSON_INSERT` と `JSON_REPLACE` との比較は下記

JSON_SETは問答無用で既存のvalueを置き換えてないものは追加する。

JSON_INSERTは既存の置き換えはせずにinsertする。

JSON_REPLACEは既存の置き換えのみをしてinsertはしない。

```
mysql> set @j = '{"a":1, "b":[2,3]}';
mysql> select json_set(@j, '$.a', 10, '$.c', json_array(true, false));
+---------------------------------------------------------+
| json_set(@j, '$.a', 10, '$.c', json_array(true, false)) |
+---------------------------------------------------------+
| {"a": 10, "b": [2, 3], "c": [true, false]}              |
+---------------------------------------------------------+
mysql> select @j;
+--------------------+
| @j                 |
+--------------------+
| {"a":1, "b":[2,3]} |
+--------------------+

mysql> select json_insert(@j, '$.a', 10, '$.c', json_array(true, false));
+------------------------------------------------------------+
| json_insert(@j, '$.a', 10, '$.c', json_array(true, false)) |
+------------------------------------------------------------+
| {"a": 1, "b": [2, 3], "c": [true, false]}                  |
+------------------------------------------------------------+
mysql> select @j;
+--------------------+
| @j                 |
+--------------------+
| {"a":1, "b":[2,3]} |
+--------------------+

mysql> select json_replace(@j, '$.a', 10, '$.c', json_array(true, false));
+-------------------------------------------------------------+
| json_replace(@j, '$.a', 10, '$.c', json_array(true, false)) |
+-------------------------------------------------------------+
| {"a": 10, "b": [2, 3]}                                      |
+-------------------------------------------------------------+
mysql> select @j;
+--------------------+
| @j                 |
+--------------------+
| {"a":1, "b":[2,3]} |
+--------------------+
```

## JSON_UNQUOTE

JSON valueをUNQUOTEして utf8mb4 文字列を返す。

```
mysql> set @x = '"abc"',
    ->     @y = '[1,2,3]';
mysql> select json_unquote(@x), 
    ->        json_valid(json_unquote(@x)),
    ->        json_unquote(@y),
    ->        json_valid(json_unquote(@y))\G
*************************** 1. row ***************************
            json_unquote(@x): abc
json_valid(json_unquote(@x)): 0
            json_unquote(@y): [1,2,3]
json_valid(json_unquote(@y)): 1
```

## JSON_DEPTH

JSONドキュメントの最大の深さを返す。

```
mysql> select json_depth('{}'), json_depth('[]'), json_depth('true');
+------------------+------------------+--------------------+
| json_depth('{}') | json_depth('[]') | json_depth('true') |
+------------------+------------------+--------------------+
|                1 |                1 |                  1 |
+------------------+------------------+--------------------+
mysql> select json_depth('{"a":1}'), json_depth('[1]'), json_depth('true');
+-----------------------+-------------------+--------------------+
| json_depth('{"a":1}') | json_depth('[1]') | json_depth('true') |
+-----------------------+-------------------+--------------------+
|                     2 |                 2 |                  1 |
+-----------------------+-------------------+--------------------+
mysql> select json_depth('{"a":[1,2,3]}'), json_depth('[1]'), json_depth('true');
+-----------------------------+-------------------+--------------------+
| json_depth('{"a":[1,2,3]}') | json_depth('[1]') | json_depth('true') |
+-----------------------------+-------------------+--------------------+
|                           3 |                 2 |                  1 |
+-----------------------------+-------------------+--------------------+
```

## JSON_LENGTH

ドキュメントの長さを返す。

```
mysql> select json_length('[1,2,{"a":3}]');
+------------------------------+
| json_length('[1,2,{"a":3}]') |
+------------------------------+
|                            3 |
+------------------------------+
mysql> select json_length('{"a":1, "b":{"c":2}}');
+-------------------------------------+
| json_length('{"a":1, "b":{"c":2}}') |
+-------------------------------------+
|                                   2 |
+-------------------------------------+
mysql> select json_length('{"a":1, "b":{"c":2}}', '$.b');
+--------------------------------------------+
| json_length('{"a":1, "b":{"c":2}}', '$.b') |
+--------------------------------------------+
|                                          1 |
+--------------------------------------------+
```

## JSON_TYPE

JSON valueのTypeを utf8mb4 で返す。

```
mysql> set @j='{"a":[10,true]}';
mysql> select json_type(@j);
+---------------+
| json_type(@j) |
+---------------+
| OBJECT        |
+---------------+
mysql> select json_type(json_extract(@j, '$.a'));
+------------------------------------+
| json_type(json_extract(@j, '$.a')) |
+------------------------------------+
| ARRAY                              |
+------------------------------------+
mysql> select json_type(json_extract(@j, '$.a[0]'));
+---------------------------------------+
| json_type(json_extract(@j, '$.a[0]')) |
+---------------------------------------+
| INTEGER                               |
+---------------------------------------+
mysql> select json_type(json_extract(@j, '$.a[1]'));
+---------------------------------------+
| json_type(json_extract(@j, '$.a[1]')) |
+---------------------------------------+
| BOOLEAN                               |
+---------------------------------------+
mysql> select json_type(NULL);
+-----------------+
| json_type(NULL) |
+-----------------+
| NULL            |
+-----------------+
mysql> select json_quote("1");
+-----------------+
| json_quote("1") |
+-----------------+
| "1"             |
+-----------------+
mysql> select json_valid(json_quote("1"));
+-----------------------------+
| json_valid(json_quote("1")) |
+-----------------------------+
|                           1 |
+-----------------------------+
mysql> select json_type(json_quote("1"));
+----------------------------+
| json_type(json_quote("1")) |
+----------------------------+
| STRING                     |
+----------------------------+
```

## JSON_VALID

validなJSONなら1を返す

```
mysql> select json_valid('{"a": "b}');
+-------------------------+
| json_valid('{"a": "b}') |
+-------------------------+
|                       0 |
+-------------------------+
mysql> select json_valid('{"a": "b"}');
+--------------------------+
| json_valid('{"a": "b"}') |
+--------------------------+
|                        1 |
+--------------------------+
```

## JSON_TABLE

JSONデータを表形式に変換する関数。

現時点ではこの記事では取り上げない。

公式のサンプルは[ここ](https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html#function_json-table)

## JSON_PRETTY

良い感じに整形してくれる。

```
mysql> select json_pretty('{"a": {"b": {"c": [1,2,{"d": [4,5,6]}]}}}')\G
*************************** 1. row ***************************
json_pretty('{"a": {"b": {"c": [1,2,{"d": [4,5,6]}]}}}'): {
  "a": {
    "b": {
      "c": [
        1,
        2,
        {
          "d": [
            4,
            5,
            6
          ]
        }
      ]
    }
  }
}
```

## JSON_STORAGE_FREE

`JSON_SET`, `JSON_REPLACE`, `JSON_REMOVE`を使ってカラムをアップデートした後にどれくらいのストレージが空いたかを確認することができる。

これらの関数を使わないでカラムをアップデートした後はpartial updateにならないのでJSON_STORAGE_FREEの戻り値は0となる。 

```
mysql> create table jtable(jcol JSON);
mysql> insert into jtable values ('{"a":10, "b": "xyz", "c": "[true, false]"}');
mysql> select * from jtable;
+---------------------------------------------+
| jcol                                        |
+---------------------------------------------+
| {"a": 10, "b": "xyz", "c": "[true, false]"} |
+---------------------------------------------+
mysql> select json_storage_free(jcol) from jtable;
+-------------------------+
| json_storage_free(jcol) |
+-------------------------+
|                       0 |
+-------------------------+
mysql> update jtable set jcol=json_set(jcol, "$.a", 10, "$.b", "xyz", "$.c", 1);
mysql> select * from jtable;
+-------------------------------+
| jcol                          |
+-------------------------------+
| {"a": 10, "b": "xyz", "c": 1} |
+-------------------------------+
mysql> select json_storage_free(jcol) from jtable;
+-------------------------+
| json_storage_free(jcol) |
+-------------------------+
|                      14 |
+-------------------------+
```

普通にアップデートすると下記のようになる。

```
mysql> update jtable set jcol='{"a":10,"b":20}';
mysql> select json_storage_free(jcol) from jtable;
+-------------------------+
| json_storage_free(jcol) |
+-------------------------+
|                       0 |
+-------------------------+
```

## JSON_STORAGE_SIZE

JSON documentが使用しているバイト数を返す

```
mysql> create table jtable(jcol JSON);
mysql> insert into jtable values('{"a": 1000, "b": "wxyz", "c": "[1,3,5,7]"}');
mysql> select jcol, json_storage_size(jcol) as size, json_storage_free(jcol) as free from jtable;
+--------------------------------------------+------+------+
| jcol                                       | size | free |
+--------------------------------------------+------+------+
| {"a": 1000, "b": "wxyz", "c": "[1,3,5,7]"} |   44 |    0 |
+--------------------------------------------+------+------+
mysql> update jtable set jcol=json_set(jcol, "$.b", "a");
mysql> select jcol, json_storage_size(jcol) as size, json_storage_free(jcol) as free from jtable;
+-----------------------------------------+------+------+
| jcol                                    | size | free |
+-----------------------------------------+------+------+
| {"a": 1000, "b": "a", "c": "[1,3,5,7]"} |   44 |    3 |
+-----------------------------------------+------+------+
```

originは[こちら](https://abcb2.github.io/blog/2018/08/25/mysql-json)