MySQLのJSON関係の関数を勉強してみた

注意
私は初心者です。MySQLのJSON関連の関数を勉強してみただけの投稿です。詳しく知りたい方は参考のリンクをみた方が良いです。
間違いなどありましたら、教えていただけるとありがたいです。:bow:

---

参考

- [MySQL :: MySQL 8.0 Reference Manual :: 12.17.1 JSON Function Reference](https://dev.mysql.com/doc/refman/8.0/en/json-function-reference.html)
- [MySQL :: MySQL 8.0 Reference Manual :: 11.5 The JSON Data Type](https://dev.mysql.com/doc/refman/8.0/en/json.html)
- [漢(オトコ)のコンピュータ道: MySQL 8.0登場 立ち止まることを知らない進化はこれからも続く](http://nippondanji.blogspot.com/2018/05/mysql-80.html)

---

MySQLのバージョンは全て8.0.19で試しました

```sql
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.19    |
+-----------+
1 row in set (0.00 sec)
```

# `JSON_ARRAY([val[, val] ...])`

https://dev.mysql.com/doc/refman/8.0/en/json-creation-functions.html#function_json-array

引数の値からJSONの配列を返す

```sql
mysql> select json_array(1, null, "aaa");
+----------------------------+
| json_array(1, null, "aaa") |
+----------------------------+
| [1, null, "aaa"]           |
+----------------------------+
```

# `JSON_ARRAY_APPEND(json_doc, path, val[, path, val] ...)`

https://dev.mysql.com/doc/refman/8.0/en/json-modification-functions.html#function_json-array-append

JSONドキュメント内の指定された配列の最後に値を追加し、結果を返す

```sql
mysql> select json_array_append('[1,2,3]', '$', 555);
+----------------------------------------+
| json_array_append('[1,2,3]', '$', 555) |
+----------------------------------------+
| [1, 2, 3, 555]                         |
+----------------------------------------+
1 row in set (0.00 sec)

mysql> select json_array_append('[1,["a","b"]]', '$[1]', 'ccc');
+---------------------------------------------------+
| json_array_append('[1,["a","b"]]', '$[1]', 'ccc') |
+---------------------------------------------------+
| [1, ["a", "b", "ccc"]]                            |
+---------------------------------------------------+
1 row in set (0.00 sec)

mysql> select json_array_append('[1,{"z":100}]', '$[0]', 99, '$[1].z', 200);
+---------------------------------------------------------------+
| json_array_append('[1,{"z":100}]', '$[0]', 99, '$[1].z', 200) |
+---------------------------------------------------------------+
| [[1, 99], {"z": [100, 200]}]                                  |
+---------------------------------------------------------------+
1 row in set (0.00 sec)
```

# `JSON_ARRAY_INSERT(json_doc, path, val[, path, val] ...)`

https://dev.mysql.com/doc/refman/8.0/en/json-modification-functions.html#function_json-array-insert

JSONドキュメント内の配列に挿入して、変更されたドキュメントを返す

```sql
mysql> select json_array_insert('[1,["a","b"]]', '$[1]', 9);
+-----------------------------------------------+
| json_array_insert('[1,["a","b"]]', '$[1]', 9) |
+-----------------------------------------------+
| [1, 9, ["a", "b"]]                            |
+-----------------------------------------------+
1 row in set (0.00 sec)

mysql> select json_array_insert('[{"x":["a","b"]},10]', '$[0].x[1]', "ccc", '$[100]', "zzz");
+--------------------------------------------------------------------------------+
| json_array_insert('[{"x":["a","b"]},10]', '$[0].x[1]', "ccc", '$[100]', "zzz") |
+--------------------------------------------------------------------------------+
| [{"x": ["a", "ccc", "b"]}, 10, "zzz"]                                          |
+--------------------------------------------------------------------------------+
1 row in set (0.00 sec)
```

# `JSON_CONTAINS(target, candidate[, path])`

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

JSONの中に指定のJSONが含まれているか、pathが指定されている場合はpath内のJSONに含まれているかを返す

```sql
mysql> select json_contains('{ "a":1, "b":{ "x": 10, "y": 20 } }', '1', '$.a');
+------------------------------------------------------------------+
| json_contains('{ "a":1, "b":{ "x": 10, "y": 20 } }', '1', '$.a') |
+------------------------------------------------------------------+
|                                                                1 |
+------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select json_contains('{ "a":1, "b":{ "x": 10, "y": 20 } }', '{ "x": 10, "y": 20 }');
+------------------------------------------------------------------------------+
| json_contains('{ "a":1, "b":{ "x": 10, "y": 20 } }', '{ "x": 10, "y": 20 }') |
+------------------------------------------------------------------------------+
|                                                                            0 |
+------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select json_contains('{ "a":1, "b":{ "x": 10, "y": 20 } }', '{ "x": 10, "y": 20 }', '$.b');
+-------------------------------------------------------------------------------------+
| json_contains('{ "a":1, "b":{ "x": 10, "y": 20 } }', '{ "x": 10, "y": 20 }', '$.b') |
+-------------------------------------------------------------------------------------+
|                                                                                   1 |
+-------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
```

# `JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...)`

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

JSONに指定のパスのデータが含まれているかどうか

2つ目の引数には`'one'`か`'all'`を指定でき、`'one'`は1つでもパスが存在したら1、それ以外は0、`'all'`は全てのパスが存在したら1になる

```sql
mysql> select json_contains_path('{ "x":1, "y":2 }', 'one', '$.x', '$.z');
+-------------------------------------------------------------+
| json_contains_path('{ "x":1, "y":2 }', 'one', '$.x', '$.z') |
+-------------------------------------------------------------+
|                                                           1 |
+-------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select json_contains_path('{ "x":1, "y":2 }', 'all', '$.x', '$.z');
+-------------------------------------------------------------+
| json_contains_path('{ "x":1, "y":2 }', 'all', '$.x', '$.z') |
+-------------------------------------------------------------+
|                                                           0 |
+-------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select json_contains_path('{ "x":1, "y":{ "z":2 } }', 'all', '$.x', '$.y.z');
+-----------------------------------------------------------------------+
| json_contains_path('{ "x":1, "y":{ "z":2 } }', 'all', '$.x', '$.y.z') |
+-----------------------------------------------------------------------+
|                                                                     1 |
+-----------------------------------------------------------------------+
1 row in set (0.00 sec)
```

# `JSON_DEPTH(json_doc)`

https://dev.mysql.com/doc/refman/8.0/en/json-attribute-functions.html#function_json-depth

JSONの深さの最大を返す

空の配列、オブジェクトは深さ1、空ではない配列、オブジェクトは深さ2、そこからネストするたびに深さが足されていくようでした　

```sql
mysql> select 
    ->      json_depth('"aaa"') a
    ->     ,json_depth('{}') b
    ->     ,json_depth('[]') c
    ->     ,json_depth('{"a":1}') d
    ->     ,json_depth('[10]') e
    ->     ,json_depth('[10, {"a":1}]') f;
+---+---+---+---+---+---+
| a | b | c | d | e | f |
+---+---+---+---+---+---+
| 1 | 1 | 1 | 2 | 2 | 3 |
+---+---+---+---+---+---+
1 row in set (0.00 sec)
```

# `JSON_EXTRACT(json_doc, path[, path] ...)`

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

パス引数で一致したJSONの部分を返す

`->`の省略記法でも書ける

```sql
mysql> select json_extract('["a","b","c"]', '$[2]');
+---------------------------------------+
| json_extract('["a","b","c"]', '$[2]') |
+---------------------------------------+
| "c"                                   |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> select json_extract('["a","b",{"c":100}]', '$[2].c', '$[0]');
+-------------------------------------------------------+
| json_extract('["a","b",{"c":100}]', '$[2].c', '$[0]') |
+-------------------------------------------------------+
| [100, "a"]                                            |
+-------------------------------------------------------+
1 row in set (0.01 sec)

mysql> select json_extract('["a",["x","y","z"]]', '$[1][*]');
+------------------------------------------------+
| json_extract('["a",["x","y","z"]]', '$[1][*]') |
+------------------------------------------------+
| ["x", "y", "z"]                                |
+------------------------------------------------+
1 row in set (0.00 sec)
```

# `->`

https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#operator_json-column-path

`JSON_EXTRACT()` の省略記法

```sql
mysql> create table t1 (a json);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values('[1,3,5]'), ('{"x":10, "y": 20}'), ('[2, {"z": 100}]');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select 
    ->       json_extract(a, '$[0]')
    ->      ,a->'$[0]'
    ->  ,json_extract(a, '$.y')
    ->      ,a->'$.y'
    -> from t1;
+-------------------------+--------------------+------------------------+----------+
| json_extract(a, '$[0]') | a->'$[0]'          | json_extract(a, '$.y') | a->'$.y' |
+-------------------------+--------------------+------------------------+----------+
| 1                       | 1                  | NULL                   | NULL     |
| {"x": 10, "y": 20}      | {"x": 10, "y": 20} | 20                     | 20       |
| 2                       | 2                  | NULL                   | NULL     |
+-------------------------+--------------------+------------------------+----------+
3 rows in set (0.00 sec)
```

# `->>`

https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#operator_json-inline-path

`JSON_UNQUOTE(JSON_EXTRACT())` の省略記法

```sql
mysql> create table t2 (a json);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t2 values('{"x": "abcde"}');
Query OK, 1 row affected (0.00 sec)

mysql> select
    ->       json_extract(a, '$.x') a -- json_unquote実行しないと、ダブルクォートが残っている
    ->      ,json_unquote(json_extract(a, '$.x')) b
    ->      ,json_unquote(a->'$.x') c
    ->      ,a->>'$.x' d
    -> from t2;
+---------+-------+-------+-------+
| a       | b     | c     | d     |
+---------+-------+-------+-------+
| "abcde" | abcde | abcde | abcde |
+---------+-------+-------+-------+
1 row in set (0.00 sec)
```

# `JSON_SET` `JSON_INSERT` `JSON_REPLACE`

### `JSON_SET(json_doc, path, val[, path, val] ...)`

https://dev.mysql.com/doc/refman/8.0/en/json-modification-functions.html#function_json-set

既存の値を置き換え、存在しない値を追加する

### `JSON_INSERT(json_doc, path, val[, path, val] ...)`

https://dev.mysql.com/doc/refman/8.0/en/json-modification-functions.html#function_json-insert

既存の値を置き換えずに値を挿入する

### `JSON_REPLACE(json_doc, path, val[, path, val] ...)`

https://dev.mysql.com/doc/refman/8.0/en/json-modification-functions.html#function_json-replace

既存の値のみを置き換える

```sql
mysql> SELECT 
    ->       -- あった場合は更新する、ない場合は追加する
    ->       JSON_SET('[0,1]', '$[1]', 5, '$[2]', 100) a
    ->       -- あった場合は更新しない、ない場合は追加する
    ->      ,JSON_INSERT('[0,1]', '$[1]', 5, '$[2]', 100) b
    ->       -- あった場合は更新する、ない場合は無視する
    ->      ,JSON_REPLACE('[0,1]', '$[1]', 5, '$[2]', 100) c;
+-------------+-------------+--------+
| a           | b           | c      |
+-------------+-------------+--------+
| [0, 5, 100] | [0, 1, 100] | [0, 5] |
+-------------+-------------+--------+
1 row in set (0.00 sec)
```

# `JSON_KEYS(json_doc[, path])`

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

オブジェクトの添え字を配列にして返す

```sql
mysql> select json_keys('{"a":1, "b":2, "c":{"x":100, "y":200}}');
+-----------------------------------------------------+
| json_keys('{"a":1, "b":2, "c":{"x":100, "y":200}}') |
+-----------------------------------------------------+
| ["a", "b", "c"]                                     |
+-----------------------------------------------------+
1 row in set (0.00 sec)

mysql> select json_keys('{"a":1, "b":2, "c":{"x":100, "y":200}}', '$.c');
+------------------------------------------------------------+
| json_keys('{"a":1, "b":2, "c":{"x":100, "y":200}}', '$.c') |
+------------------------------------------------------------+
| ["x", "y"]                                                 |
+------------------------------------------------------------+
1 row in set (0.00 sec)
```

# `JSON_LENGTH(json_doc[, path])`

https://dev.mysql.com/doc/refman/8.0/en/json-attribute-functions.html#function_json-length

JSONの長さを返す

```sql
mysql> select json_length('{"a":1, "b":2, "c": [1,2,3,4,5]}');
+-------------------------------------------------+
| json_length('{"a":1, "b":2, "c": [1,2,3,4,5]}') |
+-------------------------------------------------+
|                                               3 |
+-------------------------------------------------+
1 row in set (0.00 sec)

mysql> select json_length('{"a":1, "b":2, "c": [1,2,3,4,5]}', '$.c');
+--------------------------------------------------------+
| json_length('{"a":1, "b":2, "c": [1,2,3,4,5]}', '$.c') |
+--------------------------------------------------------+
|                                                      5 |
+--------------------------------------------------------+
1 row in set (0.00 sec)
```

# `JSON_MERGE_PATCH()` `JSON_MERGE_PRESERVE()`

### `JSON_MERGE_PATCH(json_doc, json_doc[, json_doc] ...)`

https://dev.mysql.com/doc/refman/8.0/en/json-modification-functions.html#function_json-merge-patch

JSONをマージし、重複キーの値は置き換わる

### `JSON_MERGE_PRESERVE(json_doc, json_doc[, json_doc] ...)`

https://dev.mysql.com/doc/refman/8.0/en/json-modification-functions.html#function_json-merge-preserve

JSONをマージし、重複キーは保持しながらマージされる

---

ドキュメントの通りの`JSON_MERGE_PATCH`のマージの動きを確認してみます。間違っていたら教えてください:bow:

```sql
mysql> select
    ->      -- 最初の引数がオブジェクトでない場合、マージの結果は、空のオブジェクトが2番目の引数とマージさ れた場合と同じ
    ->      json_merge_patch('1', '[true]') a
    ->      -- 2番目の引数がオブジェクトでない場合、マージの結果は2番目の引数
    ->      ,json_merge_patch('{"a":1}', '[false, 1]') b
    ->      -- 両方の引数がオブジェクトの場合、マージされる
    ->      ,json_merge_patch('{"a":1}', '{"b":2}') c
    ->      -- 添え字が重複していた場合は後の値で上書きされる
    ->      ,json_merge_patch('{"a":1, "b": 1}', '{"b":2}') d
    ->      -- 添え字が重複していた場合は後の値で上書きされる
    ->      ,json_merge_patch('{"a":1, "b": 1}', '{"b":2}', '{"a":5, "c":3}') e
    ->      -- 値がnullだと削除される
    ->      ,json_merge_patch('{"a":1, "b":2}', '{"b":null}') f
    ->      -- ネストしててもマージできる
    ->      ,json_merge_patch('{"a":{"x":1}}', '{"a":{"y":2}}') g;
+--------+------------+------------------+------------------+--------------------------+----------+-------------------------+
| a      | b          | c                | d                | e                        | f        | g                       |
+--------+------------+------------------+------------------+--------------------------+----------+-------------------------+
| [true] | [false, 1] | {"a": 1, "b": 2} | {"a": 1, "b": 2} | {"a": 5, "b": 2, "c": 3} | {"a": 1} | {"a": {"x": 1, "y": 2}} |
+--------+------------+------------------+------------------+--------------------------+----------+-------------------------+
1 row in set (0.00 sec)
```

`JSON_MERGE_PRESERVE`もドキュメントに確認しつつ確認してみました

```sql
mysql> select 
    ->     -- 配列同士は単一の配列にマージする
    ->     json_merge_preserve('[10, 20]', '["a","b"]') a
    ->     -- オブジェクト同士は単一のオブジェクトにマージ
    ->    ,json_merge_preserve('{"a":1}', '{"z":10}') b
    ->     -- スカラー値の場合は配列にラップし、配列としてマージ
    ->    ,json_merge_preserve('1', '"a"', "false") c
    ->     -- 配列とオブジェクトの場合は、オブジェクトを配列にラップし、2つの配列をマージ
    ->    ,json_merge_preserve('[1,3,5]', '{"z":10}') d
    ->     -- 添え字が重複していた場合
    ->    ,json_merge_preserve('{"a":1, "b": 2}', '{"b": 5, "z":10}') e
    ->     -- 3つでマージ
    ->    ,json_merge_preserve('1', '[20, 30]', '{"a": false}') f;
+--------------------+-------------------+-----------------+----------------------+--------------------------------+---------------------------+
| a                  | b                 | c               | d                    | e                              | f                         |
+--------------------+-------------------+-----------------+----------------------+--------------------------------+---------------------------+
| [10, 20, "a", "b"] | {"a": 1, "z": 10} | [1, "a", false] | [1, 3, 5, {"z": 10}] | {"a": 1, "b": [2, 5], "z": 10} | [1, 20, 30, {"a": false}] |
+--------------------+-------------------+-----------------+----------------------+--------------------------------+---------------------------+
1 row in set (0.00 sec)
```

# `JSON_OBJECT([key, val[, key, val] ...])`

https://dev.mysql.com/doc/refman/8.0/en/json-creation-functions.html#function_json-object

指定したキーと値のペアからJSONオブジェクトを返す

```sql
mysql> select json_object("a", 1, "b", 3);
+-----------------------------+
| json_object("a", 1, "b", 3) |
+-----------------------------+
| {"a": 1, "b": 3}            |
+-----------------------------+
1 row in set (0.00 sec)
```

# `JSON_OVERLAPS(json_doc1, json_doc2)`

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

2つのJSONを比較し、共通のキーと値のペアまたは配列要素がある場合にtrue(1)を返す

```sql
mysql> select
    ->      json_overlaps('[1, 5, 7]', '[3, 5, 9]') a
    ->     ,json_overlaps('[1, 5, 7]', '[3, 6, 9]') b
    ->     ,json_overlaps('{"a":1, "b": 2}', '{"b":2, "c":3}') c
    ->     ,json_overlaps('{"a":1, "b": 5}', '{"b":2, "c":3}') d
    ->     ,json_overlaps('[[1, 2], 3]', '[3, [1, 2]]') b
    ->     ,json_overlaps('[[1, 2], 3]', '[2, [1, 3]]') e;
+---+---+---+---+---+---+
| a | b | c | d | b | e |
+---+---+---+---+---+---+
| 1 | 0 | 1 | 0 | 1 | 0 |
+---+---+---+---+---+---+
1 row in set (0.00 sec)
```

# `JSON_PRETTY(json_val)`

https://dev.mysql.com/doc/refman/8.0/en/json-utility-functions.html#function_json-pretty

JSONを見やすく整形

```sql
mysql> select json_pretty('{"a":1, "b":[1,2,{"c":true}]}');
+----------------------------------------------------------------------+
| json_pretty('{"a":1, "b":[1,2,{"c":true}]}')                         |
+----------------------------------------------------------------------+
| {
  "a": 1,
  "b": [
    1,
    2,
    {
      "c": true
    }
  ]
} |
+----------------------------------------------------------------------+
1 row in set (0.00 sec)
```

# `JSON_QUOTE(string)`

https://dev.mysql.com/doc/refman/8.0/en/json-creation-functions.html#function_json-quote

文字列を二重引用符で囲み、エスケープを行なって、JSONの有効な文字列にする

```sql
mysql> select json_quote('null');
+--------------------+
| json_quote('null') |
+--------------------+
| "null"             |
+--------------------+
1 row in set (0.01 sec)

mysql> select json_quote('"null"');
+----------------------+
| json_quote('"null"') |
+----------------------+
| "\"null\""           |
+----------------------+
1 row in set (0.00 sec)
```

# `JSON_REMOVE(json_doc, path[, path] ...)`

https://dev.mysql.com/doc/refman/8.0/en/json-modification-functions.html#function_json-remove

JSONから指定したデータを削除

```sql
mysql> select json_remove('[1,2,3,4,5]', '$[1]', '$[2]');
+--------------------------------------------+
| json_remove('[1,2,3,4,5]', '$[1]', '$[2]') |
+--------------------------------------------+
| [1, 3, 5]                                  |
+--------------------------------------------+
1 row in set (0.00 sec)
```

# `JSON_SCHEMA_VALID(schema,document)`

https://dev.mysql.com/doc/refman/8.0/en/json-validation-functions.html#function_json-schema-valid

JSON Schemaに対して有効なJSONの場合はtrue(1)、そうでない場合はfalse(0)

```sql
mysql> set @schema = '{
         "$schema": "http://json-schema.org/draft-07/schema#",
         "definitions": {
             "todo": {
                 "definitions": {
                     "id": {
                         "description": "TODOのID",
                         "type": "integer"
                     },
                     "naiyou": {
                         "description": "TODOの内容",
                         "type": "string"
                     }
                 }
             }
         },
         "type": "object",
         "properties": {
             "todos": {
                 "type": "array",
                 "items": {
                     "type": "object",
                     "properties": {
                         "id": {
                             "$ref": "#/definitions/todo/definitions/id"
                         },
                         "naiyou": {
                             "$ref": "#/definitions/todo/definitions/naiyou"
                         }
                     }
                 }
             }
         }
     }';
Query OK, 0 rows affected (0.01 sec)

mysql> select json_schema_valid(@schema, '{"todos":[{"id":1, "naiyou":"買い物"},{"id":2, "naiyou":"映画"}]}') a;
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.01 sec)

mysql> select json_schema_valid(@schema, '{"todos":{}') a;
+------+
| a    |
+------+
|    0 |
+------+
1 row in set (0.00 sec)
```

# `JSON_SCHEMA_VALIDATION_REPORT(schema,document)`

https://dev.mysql.com/doc/refman/8.0/en/json-validation-functions.html#function_json-schema-validation-report

JSON Schemaに対して無効なJSONだった場合はエラーをJSONで表示してくれる

```sql
mysql> select json_pretty(json_schema_validation_report(@schema, '{"todos":[{"id":1, "naiyou":"買い物"},{"id":2, "naiyou":"映画"}]}')) a\G
*************************** 1. row ***************************
a: {
  "valid": true
}
1 row in set (0.00 sec)

mysql> select json_pretty(json_schema_validation_report(@schema, '{"todos":1')) a\G
*************************** 1. row ***************************
a: {
  "valid": false,
  "reason": "The JSON document location '#/todos' failed requirement 'type' at JSON Schema location '#/properties/todos'",
  "schema-location": "#/properties/todos",
  "document-location": "#/todos",
  "schema-failed-keyword": "type"
}
1 row in set (0.01 sec)
```
# `JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])`

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

JSONへの指定の文字列が見つかったパスを返す

2つ目の引数には`'one'`か`'all'`を指定でき、`'one'`は最初に見つかったパス返す、`'all'`は全てのパスを返す(複数ある場合は配列の形でパスを返す)

```sql
mysql> select
    ->  json_search('["10", "20", "30"]', 'one', '20') a
    -> ,json_search('["10", "20", "30", "20", "30"]', 'one', '30') b
    -> ,json_search('["10", "20", { "x": "10", "y": ["5", "10"] }]', 'all', '10') c
    -> ,json_search('["abcde", "xyz", "acz"]', 'all', '%c%') d
    -> ,json_search('["abcde", "xyz", "acz"]', 'all', '__z') e;
+--------+--------+---------------------------------+------------------+------------------+
| a      | b      | c                               | d                | e                |
+--------+--------+---------------------------------+------------------+------------------+
| "$[1]" | "$[2]" | ["$[0]", "$[2].x", "$[2].y[1]"] | ["$[0]", "$[2]"] | ["$[1]", "$[2]"] |
+--------+--------+---------------------------------+------------------+------------------+
1 row in set (0.00 sec)
```

# `JSON_STORAGE_FREE(json_val)`

https://dev.mysql.com/doc/refman/8.0/en/json-utility-functions.html#function_json-storage-free

> ↓↓↓↓↓ドキュメントのgoogle翻訳↓↓↓↓↓
> JSON列値の場合、この関数は、JSON_SET()、JSON_REPLACE()、またはJSON_REMOVE()を使用して所定の場所に更新された後、バイナリ表現で解放されたストレージ容量を示します。 引数は、有効なJSONドキュメント、またはリテラル値またはユーザー変数の値として1つとして解析できる文字列にすることもできます。

jsonのカラムを更新して、解放が容量が表示されるのを確認してみました:bow:

```sql
mysql> create table t3 (jcol JSON);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t3 values('{"a": "abc", "x": "xyzabcdefgh"}');
Query OK, 1 row affected (0.01 sec)

mysql> update t3 set jcol = json_set(jcol, "$.x", "x");
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select json_storage_free(jcol) from t3;
+-------------------------+
| json_storage_free(jcol) |
+-------------------------+
|                      10 |
+-------------------------+
1 row in set (0.00 sec)
```

# `JSON_STORAGE_SIZE(json_val)`

https://dev.mysql.com/doc/refman/8.0/en/json-utility-functions.html#function_json-storage-size

> ↓↓↓↓↓ドキュメントのgoogle翻訳↓↓↓↓↓
> この関数は、JSONドキュメントのバイナリ表現を格納するために使用されるバイト数を返します。

jsonをインサートしてみて値を確認してみました:bow:

```sql
mysql> create table t4 (jcol JSON);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t4 values('[1,2,3,4,5,{"a":1, "b":2}]');
Query OK, 1 row affected (0.01 sec)

mysql> select json_storage_size(jcol) from t4;
+-------------------------+
| json_storage_size(jcol) |
+-------------------------+
|                      43 |
+-------------------------+
1 row in set (0.00 sec)
```

# `JSON_TABLE(expr, path COLUMNS (column_list) [AS] alias)`

https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html#function_json-table

JSONを表として返す

ドキュメントのまんまですが、JSONを表として扱えました

```sql
mysql> SELECT *
    ->     FROM
    ->        JSON_TABLE(
    ->          '[{"a":"3"},{"a":2},{"b":1},{"a":0},{"a":[1,2]}]',
    ->          "$[*]"
    ->          COLUMNS(
    ->            rowid FOR ORDINALITY,
    ->            ac VARCHAR(100) PATH "$.a" DEFAULT '111' ON EMPTY DEFAULT '999' ON ERROR, 
    ->            aj JSON PATH "$.a" DEFAULT '{"x": 333}' ON EMPTY,
    ->            bx INT EXISTS PATH "$.b"
    ->          )
    ->        ) AS tt;
+-------+------+------------+------+
| rowid | ac   | aj         | bx   |
+-------+------+------------+------+
|     1 | 3    | "3"        |    0 |
|     2 | 2    | 2          |    0 |
|     3 | 111  | {"x": 333} |    1 |
|     4 | 0    | 0          |    0 |
|     5 | 999  | [1, 2]     |    0 |
+-------+------+------------+------+
5 rows in set (0.00 sec)
```

# `JSON_TYPE(json_val)`

https://dev.mysql.com/doc/refman/8.0/en/json-attribute-functions.html#function_json-type

JSONの値のタイプを示す文字列を返す

```sql
mysql> select 
    ->    json_type('[1, 2, 3]') a
    ->   ,json_type('{"a": 1}') b
    ->   ,json_type('1') c
    ->   ,json_type('"abc"') d
    ->   ,json_type('null') e
    ->   ,json_type(json_extract('{"a": 1}', '$.a')) f;
+-------+--------+---------+--------+------+---------+
| a     | b      | c       | d      | e    | f       |
+-------+--------+---------+--------+------+---------+
| ARRAY | OBJECT | INTEGER | STRING | NULL | INTEGER |
+-------+--------+---------+--------+------+---------+
1 row in set (0.00 sec)
```

# `JSON_UNQUOTE(json_val)`

https://dev.mysql.com/doc/refman/8.0/en/json-modification-functions.html#function_json-unquote

jsonの二重引用符などを削除する

```sql
mysql> select json_unquote('"abc"');
+-----------------------+
| json_unquote('"abc"') |
+-----------------------+
| abc                   |
+-----------------------+
1 row in set (0.00 sec)
```

# `JSON_VALID(val)`

https://dev.mysql.com/doc/refman/8.0/en/json-attribute-functions.html#function_json-valid

指定の文字列が有効なJSONであればtrue(1)、そうでなければfalse(0)

```sql
mysql> select json_valid('[1,2,3]'), json_valid('[1,2,');
+-----------------------+---------------------+
| json_valid('[1,2,3]') | json_valid('[1,2,') |
+-----------------------+---------------------+
|                     1 |                   0 |
+-----------------------+---------------------+
1 row in set (0.00 sec)
```

# `JSON_ARRAYAGG(col_or_expr) [over_clause]`

https://dev.mysql.com/doc/refman/8.0/en/group-by-functions.html#function_json-arrayagg

集計結果をJSONの配列にする　

```sql
mysql> create table t5 (category int, color varchar(255));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t5 values(1, 'red'),(2, 'blue'),(1, 'green'),(2, 'yellow'),(1, 'pink');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select category, json_arrayagg(color)
    -> from t5
    -> group by category;
+----------+--------------------------+
| category | json_arrayagg(color)     |
+----------+--------------------------+
|        1 | ["red", "green", "pink"] |
|        2 | ["blue", "yellow"]       |
+----------+--------------------------+
2 rows in set (0.00 sec)
```

# `JSON_OBJECTAGG(key, value) [over_clause]`

https://dev.mysql.com/doc/refman/8.0/en/group-by-functions.html#function_json-objectagg

集計結果をJSONのオブジェクトにする、1つめの引数がkeyで2つめがvalue

```sql
mysql> create table t6 (category int, color varchar(255), num int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t6 values(1, 'red', 3),(2, 'blue', 1),(1, 'green', 10),(2, 'yellow', 2),(1, 'pink', 5);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select category, json_objectagg(color, num)
    -> from t6
    -> group by category;
+----------+------------------------------------+
| category | json_objectagg(color, num)         |
+----------+------------------------------------+
|        1 | {"red": 3, "pink": 5, "green": 10} |
|        2 | {"blue": 1, "yellow": 2}           |
+----------+------------------------------------+
2 rows in set (0.00 sec)
```