注意
私は初心者です。MySQLのJSON関連の関数を勉強してみただけの投稿です。詳しく知りたい方は参考のリンクをみた方が良いです。
間違いなどありましたら、教えていただけるとありがたいです。
参考
- MySQL :: MySQL 8.0 Reference Manual :: 12.17.1 JSON Function Reference
- MySQL :: MySQL 8.0 Reference Manual :: 11.5 The JSON Data Type
- 漢(オトコ)のコンピュータ道: MySQL 8.0登場 立ち止まることを知らない進化はこれからも続く
MySQLのバージョンは全て8.0.19で試しました
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.19 |
+-----------+
1 row in set (0.00 sec)
JSON_ARRAY([val[, val] ...])
引数の値からJSONの配列を返す
mysql> select json_array(1, null, "aaa");
+----------------------------+
| json_array(1, null, "aaa") |
+----------------------------+
| [1, null, "aaa"] |
+----------------------------+
JSON_ARRAY_APPEND(json_doc, path, val[, path, val] ...)
JSONドキュメント内の指定された配列の最後に値を追加し、結果を返す
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] ...)
JSONドキュメント内の配列に挿入して、変更されたドキュメントを返す
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])
JSONの中に指定のJSONが含まれているか、pathが指定されている場合はpath内のJSONに含まれているかを返す
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] ...)
JSONに指定のパスのデータが含まれているかどうか
2つ目の引数には'one'か'all'を指定でき、'one'は1つでもパスが存在したら1、それ以外は0、'all'は全てのパスが存在したら1になる
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)
JSONの深さの最大を返す
空の配列、オブジェクトは深さ1、空ではない配列、オブジェクトは深さ2、そこからネストするたびに深さが足されていくようでした
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] ...)
パス引数で一致したJSONの部分を返す
->の省略記法でも書ける
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)
->
JSON_EXTRACT() の省略記法
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)
->>
JSON_UNQUOTE(JSON_EXTRACT()) の省略記法
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] ...)
既存の値を置き換え、存在しない値を追加する
JSON_INSERT(json_doc, path, val[, path, val] ...)
既存の値を置き換えずに値を挿入する
JSON_REPLACE(json_doc, path, val[, path, val] ...)
既存の値のみを置き換える
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])
オブジェクトの添え字を配列にして返す
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])
JSONの長さを返す
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] ...)
JSONをマージし、重複キーの値は置き換わる
JSON_MERGE_PRESERVE(json_doc, json_doc[, json_doc] ...)
JSONをマージし、重複キーは保持しながらマージされる
ドキュメントの通りのJSON_MERGE_PATCHのマージの動きを確認してみます。間違っていたら教えてください
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もドキュメントに確認しつつ確認してみました
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] ...])
指定したキーと値のペアからJSONオブジェクトを返す
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)
2つのJSONを比較し、共通のキーと値のペアまたは配列要素がある場合にtrue(1)を返す
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)
JSONを見やすく整形
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)
文字列を二重引用符で囲み、エスケープを行なって、JSONの有効な文字列にする
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] ...)
JSONから指定したデータを削除
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)
JSON Schemaに対して有効なJSONの場合はtrue(1)、そうでない場合はfalse(0)
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)
JSON Schemaに対して無効なJSONだった場合はエラーをJSONで表示してくれる
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] ...])
JSONへの指定の文字列が見つかったパスを返す
2つ目の引数には'one'か'all'を指定でき、'one'は最初に見つかったパス返す、'all'は全てのパスを返す(複数ある場合は配列の形でパスを返す)
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)
↓↓↓↓↓ドキュメントのgoogle翻訳↓↓↓↓↓
JSON列値の場合、この関数は、JSON_SET()、JSON_REPLACE()、またはJSON_REMOVE()を使用して所定の場所に更新された後、バイナリ表現で解放されたストレージ容量を示します。 引数は、有効なJSONドキュメント、またはリテラル値またはユーザー変数の値として1つとして解析できる文字列にすることもできます。
jsonのカラムを更新して、解放が容量が表示されるのを確認してみました
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)
↓↓↓↓↓ドキュメントのgoogle翻訳↓↓↓↓↓
この関数は、JSONドキュメントのバイナリ表現を格納するために使用されるバイト数を返します。
jsonをインサートしてみて値を確認してみました
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)
JSONを表として返す
ドキュメントのまんまですが、JSONを表として扱えました
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)
JSONの値のタイプを示す文字列を返す
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)
jsonの二重引用符などを削除する
mysql> select json_unquote('"abc"');
+-----------------------+
| json_unquote('"abc"') |
+-----------------------+
| abc |
+-----------------------+
1 row in set (0.00 sec)
JSON_VALID(val)
指定の文字列が有効なJSONであればtrue(1)、そうでなければfalse(0)
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]
集計結果をJSONの配列にする
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]
集計結果をJSONのオブジェクトにする、1つめの引数がkeyで2つめがvalue
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)