Mysql 8.0 之后的Json 类型玩法
首先 json 类型有两种：
// 数组
var array = [...];
// 对象
var object = {...}
MySQL8.0开始支持JSON格式，可以对JSON格式的数据进行高效的访问。
和原来JSON格式的字符串相比，JSON格式有以下的优点：
1，自动验证。错误的JSON格式会报错。
2，存储格式优化。数据保存为二进制格式，文件存储很紧凑，读取速度快。
3，MySQL可以通过键或数组索引查询和修改对应的值，不用把整个字符串都读出来。
一些其他的介绍：
1，JSON格式需要的磁盘空间和longblob或longtext差不多。
2，JSON格式的默认值只能是null。
3，JSON格式的列不能直接建立索引，可以建立JSON索引。
4，JSON格式的key必须是字符串格式。value可以是字符串，数字，布尔型。
5，JSON格式默认使用utf8mb4字符集，utf8mb4-bin排序，其他字符集使用JSON格式需要做字符集转换。ascii或utf8不用转换，他们是utf8mb4的子集。
6，大小写敏感，而且true，false，null这些关键字在JSON格式里都必须小写。说白了就是：null，Null，NULL，都是null，但是"Null"无法转成null，只有"null"才能转成null。
7，JSON格式中包含单引号或双引号时，需要用一条反斜线来转义。
8，JSON格式会丢弃一些额外的空格，并且会把键值对排序。
JSON的路径表达式
MySQL用路径表达式对JSON格式的数据进行查询。
路径表达式中用$代表JSON值。
用key值代表该key对应的元素。
用[N]代表JSON数组中的第N个元素。序号从0开始。
用[M to N]代表JSON数组中第M个至第N个元素。序号从0开始。
用.[*]通配符代表JSON对象的所有子元素。
用[*]通配符代表JSON数组的所有元素。
用**通配符代表用某字符开头或结尾的元素。
在JSON数组中查询
如果用$代表以下JSON：
[3, {"a": [5, 6], "b": 10}, [99, 100]]
那么：
$[0] 指向 3。
$[1] 指向 {"a": [5, 6], "b": 10}。
$[2] 指向 [99, 100]。
$[3] 指向 NULL。因为没有第四个元素。
$[1].a 指向 [5, 6]。
$[1].a[1] 指向 6。
$[1].b 指向 10。
$[2][0] 指向 99。
注意到$[1].a中的a可以不写引号。而如果key名中有空格，则必须加引号，见后面的例子。
在JSON对象中查询
如果用$代表以下JSON：
{"a fish": "shark", "a bird": "sparrow"}
那么：
$."a fish" 指向 shark。
$."a bird" 指向 sparrow。
注意，"a fish"这个key值中有空格，所以引号是必须加的。
*通配符的查询：
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": 1, "b": 2, "c": [3, 4, 5]}', '$.c[*]');
+------------------------------------------------------------+
| JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.c[*]') |
+------------------------------------------------------------+
| [3, 4, 5] |
+------------------------------------------------------------+
**通配符的查询：
mysql> SELECT JSON_EXTRACT('{"a": {"b": 1}, "c": {"b": 2}}', '$**.b');
+---------------------------------------------------------+
| JSON_EXTRACT('{"a": {"b": 1}, "c": {"b": 2}}', '$**.b') |
+---------------------------------------------------------+
| [1, 2] |
+---------------------------------------------------------+
创建
1，JSON_ARRAY([val[, val] ...])
根据参数值创建JSON数组，每个参数都是数组中一个元素。
举例：
mysql> SELECT JSON_ARRAY(1, "abc", NULL, TRUE, CURTIME());
+---------------------------------------------+
| JSON_ARRAY(1, "abc", NULL, TRUE, CURTIME()) |
+---------------------------------------------+
| [1, "abc", null, true, "10:27:46.000000"] |
+---------------------------------------------+
1 row in set (0.00 sec)
2，JSON_OBJECT([key, val[, key, val] ...])
根据参数列表创建JSON对象。
按照参数的排列，第一个是key，第二个是value，第三个是key，以此类推。参数个数必须是双数。
参数列表可以为空，得到空JSON：{}。
key不能是null。否则报错。value可以是null。
数据类型错误时会报错。
举例：
mysql> SELECT JSON_OBJECT('id', 87, 'name', 'carrot');
+-----------------------------------------+
| JSON_OBJECT('id', 87, 'name', 'carrot') |
+-----------------------------------------+
| {"id": 87, "name": "carrot"} |
+-----------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_OBJECT('id', 87, null, 'carrot');
ERROR 3158 (22032): JSON documents may not contain NULL member names.
mysql> SELECT JSON_OBJECT('id', 87, 'name', NULL);
+-------------------------------------+
| JSON_OBJECT('id', 87, 'name', NULL) |
+-------------------------------------+
| {"id": 87, "name": null} |
+-------------------------------------+
1 row in set (0.00 sec)
3，JSON_QUOTE(string)
用双引号把字符串括起来，把结果转为utf8mb4并返回。其中会经过JSON的转义。目的是得到JSON的字符串形式。
mysql> SELECT JSON_QUOTE('null'), JSON_QUOTE('"null"');
+--------------------+----------------------+
| JSON_QUOTE('null') | JSON_QUOTE('"null"') |
+--------------------+----------------------+
| "null" | "\"null\"" |
+--------------------+----------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_QUOTE('[1, 2, 3]');
+-------------------------+
| JSON_QUOTE('[1, 2, 3]') |
+-------------------------+
| "[1, 2, 3]" |
+-------------------------+
1 row in set (0.01 sec)
查询
1，JSON_CONTAINS(target, candidate[, path])
检查候选JSON是否包含在目标JSON中，1表示包含，0表示不包含。
target是目标元素，candidate是候选元素，path是路径表达式，如果path有值，则目标元素需要先经过路径表达式的处理再参与判断。
此函数可以使用多值索引。
另有如下规则：
1）参数可比时才能使用此函数。json_type()相同时才能使用此函数，另外integer和decimal类型可以直接比。
2）数组包含在数组中。只有目标数组的每个元素都包含在候选数组中，才算包含。
3）非数组包含在数组中。只有候选JSON对象包含在目标数组的某个元素中，才算包含。
4）非数组包含在非数组中。当且仅当候选JSON对象的每个key，在目标JSON对象中都有同名的key和同值的value，才算包含。
比如：
mysql> SELECT JSON_CONTAINS('{"a": 1, "b": 2, "c": {"d": 4}}', '1', '$.a');
+--------------------------------------------------------------+
| JSON_CONTAINS('{"a": 1, "b": 2, "c": {"d": 4}}', '1', '$.a') |
+--------------------------------------------------------------+
| 1 |
+--------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_CONTAINS('{"a": 1, "b": 2, "c": {"d": 4}}', '1', '$.b');
+--------------------------------------------------------------+
| JSON_CONTAINS('{"a": 1, "b": 2, "c": {"d": 4}}', '1', '$.b') |
+--------------------------------------------------------------+
| 0 |
+--------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_CONTAINS('{"a": 1, "b": 2, "c": {"d": 4}}', '{"d":4}', '$.b');
+--------------------------------------------------------------------+
| JSON_CONTAINS('{"a": 1, "b": 2, "c": {"d": 4}}', '{"d":4}', '$.b') |
+--------------------------------------------------------------------+
| 0 |
+--------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_CONTAINS('{"a": 1, "b": 2, "c": {"d": 4}}', '{"d":4}', '$.c');
+--------------------------------------------------------------------+
| JSON_CONTAINS('{"a": 1, "b": 2, "c": {"d": 4}}', '{"d":4}', '$.c') |
+--------------------------------------------------------------------+
| 1 |
+--------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_CONTAINS('{"a": 1, "b": 2, "c": {"d": 4}}', "{\"d\":4}", '$.c');
+----------------------------------------------------------------------+
| JSON_CONTAINS('{"a": 1, "b": 2, "c": {"d": 4}}', "{\"d\":4}", '$.c') |
+----------------------------------------------------------------------+
| 1 |
+----------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_CONTAINS('{"a": 1, "b": 2, "c": {"d": 4}}', "{\"d\":\"4\"}", '$.c');
+--------------------------------------------------------------------------+
| JSON_CONTAINS('{"a": 1, "b": 2, "c": {"d": 4}}', "{\"d\":\"4\"}", '$.c') |
+--------------------------------------------------------------------------+
| 0 |
+--------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_CONTAINS('["abc","def"]','"abc"');
+----------------------------------------+
| JSON_CONTAINS('["abc","def"]','"abc"') |
+----------------------------------------+
| 1 |
+----------------------------------------+
注意，候选JSON和目标JSON都得是JSON格式的字符串，单个元素也得加引号，数字要写成这样："1"，字符串要引号外面再套引号，写成这样：'"abc"'。
2，JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...)
判断目标JSON中是否存在指定的路径或路径列表。
第一个参数是目标JSON。
第二个参数可以选择one或all。如果选择one，那么只要其中一条路径是存在的就返回1，否则返回0。如果选择all，那么必须所有路径都存在才返回1，否则返回0。
举例：
mysql> SELECT JSON_CONTAINS_PATH('{"a": 1, "b": 2, "c": {"d": 4}}', 'one', '$.a', '$.e');
+----------------------------------------------------------------------------+
| JSON_CONTAINS_PATH('{"a": 1, "b": 2, "c": {"d": 4}}', 'one', '$.a', '$.e') |
+----------------------------------------------------------------------------+
| 1 |
+----------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_CONTAINS_PATH('{"a": 1, "b": 2, "c": {"d": 4}}', 'all', '$.a', '$.e');
+----------------------------------------------------------------------------+
| JSON_CONTAINS_PATH('{"a": 1, "b": 2, "c": {"d": 4}}', 'all', '$.a', '$.e') |
+----------------------------------------------------------------------------+
| 0 |
+----------------------------------------------------------------------------+
1 row in set (0.00 sec)
3，JSON_EXTRACT(json_doc, path[, path] ...)
从目标JSON中返回对应路径下的元素。如果匹配到多个元素则封装成数组。
比如：
mysql> SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]');
+--------------------------------------------+
| JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]') |
+--------------------------------------------+
| 20 |
+--------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]', '$[0]');
+----------------------------------------------------+
| JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]', '$[0]') |
+----------------------------------------------------+
| [20, 10] |
+----------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[2][*]');
+-----------------------------------------------+
| JSON_EXTRACT('[10, 20, [30, 40]]', '$[2][*]') |
+-----------------------------------------------+
| [30, 40] |
+-----------------------------------------------+
1 row in set (0.00 sec)
4，->运算符
此运算符是JSON_EXTRACT()函数的简写，单个path的场景。
这个运算符几乎可以出现在sql的所有位置，而且在select，update等语句中都能用，比如：
mysql> SELECT c, JSON_EXTRACT(c, "$.id"), g
> FROM jemp
> WHERE JSON_EXTRACT(c, "$.id") > 1
> ORDER BY JSON_EXTRACT(c, "$.name");
这个语句可以替换为：
mysql> SELECT c, c->"$.id", g
> FROM jemp
> WHERE c->"$.id" > 1
> ORDER BY c->"$.name";
另外，在EXPLAIN结果的WARING中，->表达式被展开成json_extract()函数：
mysql> explain select a->'$.name' from test_j;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | test_j | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | NULL |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> SHOW WARNINGS;
+-------+------+------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+------------------------------------------------------------------------------------------------------+
| Note | 1003 | /* select#1 */ select json_extract(`dev`.`test_j`.`a`,'$.name') AS `a->'$.name'` from `dev`.`test_j` |
+-------+------+------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
5，->>运算符
->>运算符是加强版的->运算符，他把->运算符得到的结果去掉了引号，就像JSON_UNQUOTE ()函数一样，所以，以下三个表达式所代表的含义是一样的：
- JSON_UNQUOTE( JSON_EXTRACT(column, path) )
- JSON_UNQUOTE(column -> path)
- column->>path
和->>运算符一样，->>运算符可以被用到sql中的很多位置。
举例：
mysql> select * from test_j;
+-------------------------------+
| a |
+-------------------------------+
| {"id": "3", "name": "Barney"} |
| {"id": "4", "name": "Betty"} |
+-------------------------------+
2 rows in set (0.00 sec)
mysql> select a->'$.name' from test_j;
+-------------+
| a->'$.name' |
+-------------+
| "Barney" |
| "Betty" |
+-------------+
2 rows in set (0.00 sec)
mysql> select a->>'$.name' from test_j;
+--------------+
| a->>'$.name' |
+--------------+
| Barney |
| Betty |
+--------------+
2 rows in set (0.00 sec)
另外，和->运算符一样，在EXPLAIN语句结果中，->>运算符会被展开：
mysql> explain select a->>'$.name' from test_j;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | test_j | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | NULL |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> SHOW WARNINGS;
+-------+------+---------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+---------------------------------------------------------------------------------------------------------------------+
| Note | 1003 | /* select#1 */ select json_unquote(json_extract(`dev`.`test_j`.`a`,'$.name')) AS `a->>'$.name'` from `dev`.`test_j` |
+-------+------+---------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
6，JSON_KEYS(json_doc[, path])
返回JSON中一级键值对中key的列表。如果写了path字段，则先进行路径表达式计算，然后的返回第一级键值对中key的列表。
也就是说，不会返回子元素的key。
mysql> SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}');
+---------------------------------------+
| JSON_KEYS('{"a": 1, "b": {"c": 30}}') |
+---------------------------------------+
| ["a", "b"] |
+---------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}', '$.b');
+----------------------------------------------+
| JSON_KEYS('{"a": 1, "b": {"c": 30}}', '$.b') |
+----------------------------------------------+
| ["c"] |
+----------------------------------------------+
1 row in set (0.00 sec)
7，JSON_OVERLAPS(json_doc1, json_doc2)
比较两个JSON是否有相同元素。也就是是否有交集。有则返回1，没有返回0。
部分匹配的情况不能算有相同元素。
两个JSON对象比较时，两者至少有一个相同name的key和相同对应value，则返回1。
两个标量比较时，则比较值是否相等。
标量和数组比较时，则判断标量是否和数组某元素相等。数据类型不同时不算相等。
比如：
mysql> SELECT JSON_OVERLAPS("[1,3,5,7]", "[2,5,7]");
+---------------------------------------+
| JSON_OVERLAPS("[1,3,5,7]", "[2,5,7]") |
+---------------------------------------+
| 1 |
+---------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_OVERLAPS("[1,3,5,7]", "[2,6,8]");
+---------------------------------------+
| JSON_OVERLAPS("[1,3,5,7]", "[2,6,8]") |
+---------------------------------------+
| 0 |
+---------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_OVERLAPS('[[1,2],[3,4],5]', '[1,[2,3],[4,5]]');
+-----------------------------------------------------+
| JSON_OVERLAPS('[[1,2],[3,4],5]', '[1,[2,3],[4,5]]') |
+-----------------------------------------------------+
| 0 |
+-----------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_OVERLAPS('{"a":1,"b":10,"d":10}', '{"a":5,"e":10,"f":1,"d":20}');
+-----------------------------------------------------------------------+
| JSON_OVERLAPS('{"a":1,"b":10,"d":10}', '{"a":5,"e":10,"f":1,"d":20}') |
+-----------------------------------------------------------------------+
| 0 |
+-----------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_OVERLAPS('[4,5,"6",7]', '6');
+-----------------------------------+
| JSON_OVERLAPS('[4,5,"6",7]', '6') |
+-----------------------------------+
| 0 |
+-----------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_OVERLAPS('[4,5,6,7]', '"6"');
+-----------------------------------+
| JSON_OVERLAPS('[4,5,6,7]', '"6"') |
+-----------------------------------+
| 0 |
+-----------------------------------+
1 row in set (0.00 sec)
8，JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])
根据某字符串，返回在目标JSON中匹配的value的路径表达式，search_str和JSON中的value相等时算匹配。
此函数能查询子元素信息。
第一个参数json_doc是目标JSON。
第二个参数one_or_all可以选择one或者all。one表示返回一个匹配的值的路径。all表示返回所有路径。
参数search_str是要搜索的字符串。其中可以用%(百分号)代表任意多个字符，_(下划线)代表任意一个字符。
参数escape_char是转义字符。默认是\。写成空字符串或NULL时，也默认为\。
参数path是路径表达式，如果写了path，匹配结果需在路径表达式下进行。
比如：
mysql> SELECT JSON_SEARCH('["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]', 'one', 'abc');
+--------------------------------------------------------------------------------------+
| JSON_SEARCH('["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]', 'one', 'abc') |
+--------------------------------------------------------------------------------------+
| "$[0]" |
+--------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_SEARCH('["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]', 'all', 'abc');
+--------------------------------------------------------------------------------------+
| JSON_SEARCH('["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]', 'all', 'abc') |
+--------------------------------------------------------------------------------------+
| ["$[0]", "$[2].x"] |
+--------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_SEARCH('["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]', 'all', 'abcdefg');
+------------------------------------------------------------------------------------------+
| JSON_SEARCH('["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]', 'all', 'abcdefg') |
+------------------------------------------------------------------------------------------+
| NULL |
+------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_SEARCH('["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]', 'all', '10', NULL, '$[1][0].k');
+--------------------------------------------------------------------------------------------------------+
| JSON_SEARCH('["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]', 'all', '10', NULL, '$[1][0].k') |
+--------------------------------------------------------------------------------------------------------+
| "$[1][0].k" |
+--------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_SEARCH('["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]', 'all', '%b%', NULL, '$[3]');
+----------------------------------------------------------------------------------------------------+
| JSON_SEARCH('["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]', 'all', '%b%', NULL, '$[3]') |
+----------------------------------------------------------------------------------------------------+
| "$[3].y" |
+----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
9，value MEMBER OF(json_array)
判断value是否被包含在某JSON数组中。包含则返回1，否则返回0。
数据格式不同时不算包含。
value可以用其他表达式替代。
JSON格式的字符串不能直接和数组中的JSON对象比较，会返回0，此时需要把value转成JSON类型才能返回1。
mysql> SELECT 17 MEMBER OF('[23, "abc", 17, "ab", 10]');
+-------------------------------------------+
| 17 MEMBER OF('[23, "abc", 17, "ab", 10]') |
+-------------------------------------------+
| 1 |
+-------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT '17' MEMBER OF('[23, "abc", 17, "ab", 10]');
+---------------------------------------------+
| '17' MEMBER OF('[23, "abc", 17, "ab", 10]') |
+---------------------------------------------+
| 0 |
+---------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_ARRAY(4,5) MEMBER OF('[[3,4],[4,5]]');
+--------------------------------------------+
| JSON_ARRAY(4,5) MEMBER OF('[[3,4],[4,5]]') |
+--------------------------------------------+
| 1 |
+--------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT '{"a":1}' MEMBER OF(JSON_ARRAY(17, CAST('{"a":1}' AS JSON), "abc", 23));
+-------------------------------------------------------------------------+
| '{"a":1}' MEMBER OF(JSON_ARRAY(17, CAST('{"a":1}' AS JSON), "abc", 23)) |
+-------------------------------------------------------------------------+
| 0 |
+-------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT CAST('{"a":1}' AS JSON) MEMBER OF(JSON_ARRAY(17, CAST('{"a":1}' AS JSON), "abc", 23));
+---------------------------------------------------------------------------------------+
| CAST('{"a":1}' AS JSON) MEMBER OF(JSON_ARRAY(17, CAST('{"a":1}' AS JSON), "abc", 23)) |
+---------------------------------------------------------------------------------------+
| 1 |
+---------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
更新
1，JSON_ARRAY_APPEND(json_doc, path, val[, path, val] ...)
向JSON数组中追加元素，如果对应位置是单个元素，则和新元素一起封装成数组。
另外，在MySQL8.0中，原来的JSON_APPEND()函数不再使用。
mysql> SELECT JSON_ARRAY_APPEND('["a", ["b", "c"], "d"]', '$[1]', 1);
+--------------------------------------------------------+
| JSON_ARRAY_APPEND('["a", ["b", "c"], "d"]', '$[1]', 1) |
+--------------------------------------------------------+
| ["a", ["b", "c", 1], "d"] |
+--------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_ARRAY_APPEND('["a", ["b", "c"], "d"]', '$[1][0]', 3);
+-----------------------------------------------------------+
| JSON_ARRAY_APPEND('["a", ["b", "c"], "d"]', '$[1][0]', 3) |
+-----------------------------------------------------------+
| ["a", [["b", 3], "c"], "d"] |
+-----------------------------------------------------------+
1 row in set (0.01 sec)
mysql> SELECT JSON_ARRAY_APPEND('{"a": 1}', '$', 'z');
+-----------------------------------------+
| JSON_ARRAY_APPEND('{"a": 1}', '$', 'z') |
+-----------------------------------------+
| [{"a": 1}, "z"] |
+-----------------------------------------+
1 row in set (0.00 sec)
2，JSON_ARRAY_INSERT(json_doc, path, val[, path, val] ...)
在数组的指定位置追加元素，原位置的元素和后面的元素依次向后移一位。
指定位置超过数组上限，则添加在数组最后位置。
注意：在多个位置添加多个元素时，添加是有顺序的，后面添加的元素需要等前面的元素添加成功后重新确认具体位置。
举例：
mysql> SELECT JSON_ARRAY_INSERT('["a", {"b": [1, 2]}, [3, 4]]', '$[1]', 'x');
+----------------------------------------------------------------+
| JSON_ARRAY_INSERT('["a", {"b": [1, 2]}, [3, 4]]', '$[1]', 'x') |
+----------------------------------------------------------------+
| ["a", "x", {"b": [1, 2]}, [3, 4]] |
+----------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_ARRAY_INSERT('["a", {"b": [1, 2]}, [3, 4]]', '$[100]', 'x');
+------------------------------------------------------------------+
| JSON_ARRAY_INSERT('["a", {"b": [1, 2]}, [3, 4]]', '$[100]', 'x') |
+------------------------------------------------------------------+
| ["a", {"b": [1, 2]}, [3, 4], "x"] |
+------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_ARRAY_INSERT('["a", {"b": [1, 2]}, [3, 4]]', '$[1].b[0]', 'x');
+---------------------------------------------------------------------+
| JSON_ARRAY_INSERT('["a", {"b": [1, 2]}, [3, 4]]', '$[1].b[0]', 'x') |
+---------------------------------------------------------------------+
| ["a", {"b": ["x", 1, 2]}, [3, 4]] |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_ARRAY_INSERT('["a", {"b": [1, 2]}, [3, 4]]', '$[0]', 'x', '$[2][1]', 'y');
+--------------------------------------------------------------------------------+
| JSON_ARRAY_INSERT('["a", {"b": [1, 2]}, [3, 4]]', '$[0]', 'x', '$[2][1]', 'y') |
+--------------------------------------------------------------------------------+
| ["x", "a", {"b": [1, 2]}, [3, 4]] |
+--------------------------------------------------------------------------------+
1 row in set (0.00 sec)
注意最后一个语句，在$[0]处添加x，在$[2][1]处添加y，但是最后y没有添加成功，本来$[2]代表的应该是[3, 4]，但是前面添加了x后，JSON变成了：
["x", "a", {"b": [1, 2]}, [3, 4]]
于是此时的$[2]变成了{"b": [1, 2]}，是个JSON对象，不是数组，所以无法使用此函数。
以下sql可以验证这个场景：
mysql> SELECT JSON_ARRAY_INSERT('["a", {"b": [1, 2]}, [3, 4]]', '$[0]', 'x', '$[2].b[1]', 'y');
+----------------------------------------------------------------------------------+
| JSON_ARRAY_INSERT('["a", {"b": [1, 2]}, [3, 4]]', '$[0]', 'x', '$[2].b[1]', 'y') |
+----------------------------------------------------------------------------------+
| ["x", "a", {"b": [1, "y", 2]}, [3, 4]] |
+----------------------------------------------------------------------------------+
1 row in set (0.00 sec)
可见添加y的时候$[2]已经是{"b": [1, 2]}了。
3，JSON_INSERT(json_doc, path, val[, path, val] ...)
向JSON添加键值对。
如果添加的key已经存在，则忽略此键值对，不再添加。
mysql> SELECT JSON_INSERT('{ "a": 1, "b": [2, 3]}', '$.a', 10, '$.c', '[true, false]');
+--------------------------------------------------------------------------+
| JSON_INSERT('{ "a": 1, "b": [2, 3]}', '$.a', 10, '$.c', '[true, false]') |
+--------------------------------------------------------------------------+
| {"a": 1, "b": [2, 3], "c": "[true, false]"} |
+--------------------------------------------------------------------------+
1 row in set (0.00 sec)
从此结果可以看到，此函数不支持数据类型转换，'[true, false]'参数被当做字符串添加了，如果需要作为JSON数组添加，需要手动转换：
mysql> SELECT JSON_INSERT('{ "a": 1, "b": [2, 3]}', '$.a', 10, '$.c', CAST('[true, false]' AS JSON));
+----------------------------------------------------------------------------------------+
| JSON_INSERT('{ "a": 1, "b": [2, 3]}', '$.a', 10, '$.c', CAST('[true, false]' AS JSON)) |
+----------------------------------------------------------------------------------------+
| {"a": 1, "b": [2, 3], "c": [true, false]} |
+----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
4，JSON_MERGE(json_doc, json_doc[, json_doc] ...)
合并多个JSON，MySQL8.0.3版本已废弃，建议使用JSON_MERGE_PRESERVE()函数。
举例：
mysql> SELECT JSON_MERGE('[1, 2]', '[true, false]');
+---------------------------------------+
| JSON_MERGE('[1, 2]', '[true, false]') |
+---------------------------------------+
| [1, 2, true, false] |
+---------------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> SHOW WARNINGS;
+---------+------+-----------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | 'JSON_MERGE' is deprecated and will be removed in a future release. Please use JSON_MERGE_PRESERVE/JSON_MERGE_PATCH instead |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
5，JSON_MERGE_PATCH(json_doc, json_doc[, json_doc] ...)
合并多个JSON对象。会去重的合并。
合并规则：
1，如果两JSON合并，第一个JSON不是对象（比如标量或数组），则合并的结果是第二个JSON，即使第二个JSON也不是对象。
2，如果两JSON合并，第二个JSON不是对象（比如标量或数组），则合并的结果还是第二个JSON。为什么？这什么道理？
3，如果两个JSON中有相同的key，且value中有非对象，则value保留后面JSON的value。
4，如果两个JSON中有相同的key，且对应的value中都是JSON对象，则两个value递归合并。
5，合并完成后，value是null的键值对会被删掉。可以用这个特性来删除空值键值对，比如用一个标量和想删null值的JSON合并，记得标量放第一位，JSON放第二位。
mysql> SELECT JSON_MERGE_PATCH('[1, 2]', '[true, false]');
+---------------------------------------------+
| JSON_MERGE_PATCH('[1, 2]', '[true, false]') |
+---------------------------------------------+
| [true, false] |
+---------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_MERGE_PATCH('{"a":1, "b":2, "c":null}', '[]');
+----------------------------------------------------+
| JSON_MERGE_PATCH('{"a":1, "b":2, "c":null}', '[]') |
+----------------------------------------------------+
| [] |
+----------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_MERGE_PATCH('{ "a": 1, "b":2 }','{ "a": 3, "c":4 }','{ "a": 5, "d":6 }');
+-------------------------------------------------------------------------------+
| JSON_MERGE_PATCH('{ "a": 1, "b":2 }','{ "a": 3, "c":4 }','{ "a": 5, "d":6 }') |
+-------------------------------------------------------------------------------+
| {"a": 5, "b": 2, "c": 4, "d": 6} |
+-------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_MERGE_PATCH('{"a":{"x":1}}', '{"a":{"y":2}}');
+----------------------------------------------------+
| JSON_MERGE_PATCH('{"a":{"x":1}}', '{"a":{"y":2}}') |
+----------------------------------------------------+
| {"a": {"x": 1, "y": 2}} |
+----------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_MERGE_PATCH('1','{"a":1, "b":2, "c":null}');
+--------------------------------------------------+
| JSON_MERGE_PATCH('1','{"a":1, "b":2, "c":null}') |
+--------------------------------------------------+
| {"a": 1, "b": 2} |
+--------------------------------------------------+
1 row in set (0.00 sec)
6，JSON_MERGE_PRESERVE(json_doc, json_doc[, json_doc] ...)
合并多个JSON。不去重，保留所有值的合并。
合并规则：
1，两数组或两标量合并，合成一个新数组。
2，两对象合并，递归合并成一个新对象。如有同key键值对，则几个value合并成最终的value。
3，对象和数组合并，对象转成单个元素的数组然后合并成一个新数组。
举例：
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('{"name": "x"}', '{"id": 47}');
+----------------------------------------------------+
| JSON_MERGE_PRESERVE('{"name": "x"}', '{"id": 47}') |
+----------------------------------------------------+
| {"id": 47, "name": "x"} |
+----------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_MERGE_PRESERVE('[1, 2]', '{"id": 47}');
+---------------------------------------------+
| JSON_MERGE_PRESERVE('[1, 2]', '{"id": 47}') |
+---------------------------------------------+
| [1, 2, {"id": 47}] |
+---------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_MERGE_PRESERVE('{ "a": 1, "b": 2 }','{ "a": 3, "c": 4 }','{ "a": 5, "d": 6 }');
+-------------------------------------------------------------------------------------+
| JSON_MERGE_PRESERVE('{ "a": 1, "b": 2 }','{ "a": 3, "c": 4 }','{ "a": 5, "d": 6 }') |
+-------------------------------------------------------------------------------------+
| {"a": [1, 3, 5], "b": 2, "c": 4, "d": 6} |
+-------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
感觉这个JSON_MERGE_PRESERVE()函数才是真正的合并，不漏元素。相比之下JSON_MERGE_PATCH()函数的作用更像是去重。
7，JSON_REMOVE(json_doc, path[, path] ...)
从JSON中删除对应路径下的元素。
指定路径不存在也不会报错。
mysql> SELECT JSON_REMOVE('["a", ["b", "c"], "d"]', '$[1]');
+-----------------------------------------------+
| JSON_REMOVE('["a", ["b", "c"], "d"]', '$[1]') |
+-----------------------------------------------+
| ["a", "d"] |
+-----------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_REMOVE('["a", ["b", "c"], "d"]', '$[9]');
+-----------------------------------------------+
| JSON_REMOVE('["a", ["b", "c"], "d"]', '$[9]') |
+-----------------------------------------------+
| ["a", ["b", "c"], "d"] |
+-----------------------------------------------+
1 row in set (0.00 sec)
8，JSON_REPLACE(json_doc, path, val[, path, val] ...)
替换JSON中的值。
如果路径不存在，则忽略。
mysql> SELECT JSON_REPLACE('{ "a": 1, "b": [2, 3]}', '$.a', 10, '$.c', '[true, false]');
+---------------------------------------------------------------------------+
| JSON_REPLACE('{ "a": 1, "b": [2, 3]}', '$.a', 10, '$.c', '[true, false]') |
+---------------------------------------------------------------------------+
| {"a": 10, "b": [2, 3]} |
+---------------------------------------------------------------------------+
1 row in set (0.00 sec)
9，JSON_SET(json_doc, path, val[, path, val] ...)
替换JSON中的值。
如果路径不存在，则添加该值。
如果修改的是数组，路径超过了数组上限，则把元素添加到数组末尾。
mysql> SELECT JSON_SET('{ "a": 1, "b": [2, 3]}', '$.a', 10, '$.c', '[true, false]');
+-----------------------------------------------------------------------+
| JSON_SET('{ "a": 1, "b": [2, 3]}', '$.a', 10, '$.c', '[true, false]') |
+-----------------------------------------------------------------------+
| {"a": 10, "b": [2, 3], "c": "[true, false]"} |
+-----------------------------------------------------------------------+
1 row in set (0.00 sec)
所以，JSON_SET()，JSON_INSERT()，和JSON_REPLACE()三者的区别在于：
JSON_SET()：路径存在则替换值，路径不存在则新增。
JSON_INSERT()：只负责新增。
JSON_REPLACE()：只负责替换已存在的值。
10，JSON_UNQUOTE(json_val)
去掉JSON值的双引号，并返回utf8mb4格式的字符串。
此函数可以识别转义字符。
mysql> SELECT '"abc"', JSON_UNQUOTE('"abc"');
+-------+-----------------------+
| "abc" | JSON_UNQUOTE('"abc"') |
+-------+-----------------------+
| "abc" | abc |
+-------+-----------------------+
1 row in set (0.00 sec)
mysql> SELECT '[1, 2, 3]', JSON_UNQUOTE('[1, 2, 3]');
+-----------+---------------------------+
| [1, 2, 3] | JSON_UNQUOTE('[1, 2, 3]') |
+-----------+---------------------------+
| [1, 2, 3] | [1, 2, 3] |
+-----------+---------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_UNQUOTE('"\\t\\u0032"');
+------------------------------+
| JSON_UNQUOTE('"\\t\\u0032"') |
+------------------------------+
| 2 |
+------------------------------+
四，JSON值的属性
1，JSON_DEPTH(json_doc)
返回JSON值的最大深度。
空数组，空对象，标量的深度为1。
仅包含深度为1的元素的数组或对象的深度为2。
其他情况以此类推。
举例：
mysql> SELECT JSON_DEPTH('{}'), JSON_DEPTH('[]'), JSON_DEPTH('true');
+------------------+------------------+--------------------+
| JSON_DEPTH('{}') | JSON_DEPTH('[]') | JSON_DEPTH('true') |
+------------------+------------------+--------------------+
| 1 | 1 | 1 |
+------------------+------------------+--------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_DEPTH('[10, 20]'), JSON_DEPTH('[[], {}]');
+------------------------+------------------------+
| JSON_DEPTH('[10, 20]') | JSON_DEPTH('[[], {}]') |
+------------------------+------------------------+
| 2 | 2 |
+------------------------+------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_DEPTH('[10, {"a": 20}]');
+-------------------------------+
| JSON_DEPTH('[10, {"a": 20}]') |
+-------------------------------+
| 3 |
+-------------------------------+
1 row in set (0.00 sec)
2，JSON_LENGTH(json_doc[, path])
返回JSON值的长度。如果写了path，返回对应路径下的JSON长度。
标量的长度是1。
数组的长度是元素的数量。
对象的长度是成员数量。
嵌套的数组不会被计算长度。
举例：
mysql> SELECT JSON_LENGTH('[1, 2, {"a": 3}]');
+---------------------------------+
| JSON_LENGTH('[1, 2, {"a": 3}]') |
+---------------------------------+
| 3 |
+---------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_LENGTH('{"a": 1, "b": {"c": 30}}');
+-----------------------------------------+
| JSON_LENGTH('{"a": 1, "b": {"c": 30}}') |
+-----------------------------------------+
| 2 |
+-----------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_LENGTH('{"a": 1, "b": {"c": 30}}', '$.b');
+------------------------------------------------+
| JSON_LENGTH('{"a": 1, "b": {"c": 30}}', '$.b') |
+------------------------------------------------+
| 1 |
+------------------------------------------------+
1 row in set (0.00 sec)
3，JSON_TYPE(json_val)
返回JSON类型。返回值是一个utf8mb4字符串。
可返回的JSON类型有：
OBJECT。JSON对象。
ARRAY。JSON数组。
BOOLEAN。JSON的true或false。
NULL。JSON的null值。这个返回值是大写的。
INTEGER。TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT类型标量。
DOUBLE。DOUBLE和FLOAT类型标量。
DECIMAL。DECIMAL和NUMERIC类型标量。
DATETIME。DATETIME和TIMESTAMP类型标量。
DATE。DATE类型标量。
TIME。TIME类型标量。
STRING。MySQL中utf8格式标量，比如：CHAR，VARCHAR，TEXT，ENUM，SET。
BLOB。MySQL中二进制格式标量，比如：BINARY，VARBINARY，BLOB，BIT。
OPAQUE。raw bits格式。
举例：
mysql> SELECT JSON_TYPE('{"a": [10, true]}');
+--------------------------------+
| JSON_TYPE('{"a": [10, true]}') |
+--------------------------------+
| OBJECT |
+--------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_TYPE(JSON_EXTRACT('{"a": [10, true]}', '$.a[1]'));
+--------------------------------------------------------+
| JSON_TYPE(JSON_EXTRACT('{"a": [10, true]}', '$.a[1]')) |
+--------------------------------------------------------+
| BOOLEAN |
+--------------------------------------------------------+
1 row in set (0.00 sec)
4，JSON_VALID(val)
判断JSON值是否符合JSON规范。符合返回1，不符合返回0。
mysql> SELECT JSON_VALID('{"a": 1}');
+------------------------+
| JSON_VALID('{"a": 1}') |
+------------------------+
| 1 |
+------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_VALID('hello'), JSON_VALID('"hello"');
+---------------------+-----------------------+
| JSON_VALID('hello') | JSON_VALID('"hello"') |
+---------------------+-----------------------+
| 0 | 1 |
+---------------------+-----------------------+
1 row in set (0.00 sec)
五，JSON临时表函数
JSON临时表函数可以把一个JSON值变成一个临时表，并返回表格的一些信息。
建立临时表的函数如下：
JSON_TABLE(
expr,
path COLUMNS (column_list)
) [AS] alias
解析一下其中的各个表达式：
1，expr是JSON值。可以是个常量，比如'{"a":1}'。可以是一列值，比如t1.json_data，t1是之前定义的JSON临时表。可以是返回JSON值的函数，比如：JSON_EXTRACT(t1,jsn_data,'$.post.comments')。
2，path是路径表达式，JSON值要先经过路径表达式的筛选。
3，alias是临时表的表名。AS可以不写。
4，COLUMNS是临时表的列。当列中用到path路径时，$从父级路径表达式继承，属于相对路径。
COLUMNS的单个元素都可以有以下选择：
1），name FOR ORDINALITY
此列代表行号。name是自定义列名。此列内容是一个自增的计数器，类型是unsigned int，初始为1。类似MySQL的自增id。如果列中包含NESTED PATH语句，会把一行拆成多行，此时这些行的行号都是一样的，也就是第一级行的行号。
2），name type PATH string_path [on_empty] [on_error]
此列表示按照路径表达式查询JSON中的值。name是列名。type是要求返回的数据格式。string_path是路径表达式。
[on_empty]用于路径表达式值不存在的时候的默认值。
[on_empty]可选的写法有：
NULL ON EMPTY。显示为NULL，此为默认方式。
ERROR ON EMPTY。显示为ERROR。
DEFAULT json_string ON EMPTY。显示一个默认的JSON值。
[on_error]用于路径表达式下报错的时候的默认值。报错的场景比如保存时的精度错误，格式错误等。
[on_error]可选的写法有：
NULL ON ERROR。显示为NULL，此为默认方式。
ERROR ON ERROR。显示为ERROR。
DEFAULT json_string ON ERROR。显示一个默认的JSON值。
下面是一个使用ON EMPTY和ON ERROR的例子：
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
-> )
-> ) AS tt;
+-------+------+
| rowid | ac |
+-------+------+
| 1 | 3 |
| 2 | 2 |
| 3 | 111 |
| 4 | 0 |
| 5 | 999 |
+-------+------+
5 rows in set (0.01 sec)
可见，第3行从{"b":1}获取"$.a"时不存在，显示的是ON EMPTY的默认值111。
第5行从{"a":[1,2]}获取"$.a"时得到了数组，而不是列中定义的VARCHAR(100)，类型错误，显示的是ON ERROR默认的999。
3），name type EXISTS PATH path
指定路径下的值是否存在。存在返回1，不存在返回0。
4），NESTED [PATH] path COLUMNS (column_list)
将JSON中嵌套结构的JSON对象或数组元素拆成单独的行。
[PATH]关键字可以不写。
column_list可以写多个，可写的内容和临时表函数可用的column_list一样，name FOR ORDINALITY之类的都可以写。
对于同一元素中的PATH和NESTED PATH，得到的结果类似一个内连接。
如果存在多个NESTED PATH，则会对每个NESTED PATH单独生成一组记录，不会用连接的形式展示。
举例：
mysql> SELECT *
-> FROM
-> JSON_TABLE(
-> '[ {"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3}]',
-> '$[*]' COLUMNS(
-> id FOR ORDINALITY,
-> a INT PATH '$.a',
-> NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$')
-> )
-> ) AS jt
-> WHERE b IS NOT NULL;
+------+------+------+
| id | a | b |
+------+------+------+
| 1 | 1 | 11 |
| 1 | 1 | 111 |
| 2 | 2 | 22 |
| 2 | 2 | 222 |
+------+------+------+
4 rows in set (0.00 sec)
可以看到，前两行id都是1，他们都是从数组的第一个元素{"a": 1, "b": [11,111]}得来的，这两行的a列和b列就类似'$.a'和'$.b[*]'的一个内连接。
再比如：
mysql> SELECT *
-> FROM
-> JSON_TABLE(
-> '[{"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222,2222]}]',
-> '$[*]' COLUMNS(
-> a INT PATH '$.a',
-> NESTED PATH '$.b[*]' COLUMNS (b1 INT PATH '$'),
-> NESTED PATH '$.b[*]' COLUMNS (b2 INT PATH '$')
-> )
-> ) AS jt;
+------+------+------+
| a | b1 | b2 |
+------+------+------+
| 1 | 11 | NULL |
| 1 | 111 | NULL |
| 1 | NULL | 11 |
| 1 | NULL | 111 |
| 2 | 22 | NULL |
| 2 | 222 | NULL |
| 2 | 2222 | NULL |
| 2 | NULL | 22 |
| 2 | NULL | 222 |
| 2 | NULL | 2222 |
+------+------+------+
10 rows in set (0.00 sec)
可以看到，前4条数据是由{"a": 1, "b": [11,111]}得来的，b1和b2列理论上能获得11和111两个值，但是查询结果给b1和b2分别生成了2条记录。后6条数据是由{"a": 2, "b": [22,222,2222]}得来的，b1和b2列能获得22，222，2222三个值，但是查询结果给b1和b2分别生成了3条记录。
稍微复杂一点的情况：
mysql> SELECT *
-> FROM
-> JSON_TABLE(
-> '[{"a": "a_val",
'> "b": [{"c": "c_val", "l": [1,2]}]},
'> {"a": "a_val",
'> "b": [{"c": "c_val","l": [11]}, {"c": "c_val", "l": [22]}]}]',
-> '$[*]' COLUMNS(
-> top_ord FOR ORDINALITY,
-> apath VARCHAR(10) PATH '$.a',
-> NESTED PATH '$.b[*]' COLUMNS (
-> bpath VARCHAR(10) PATH '$.c',
-> ord FOR ORDINALITY,
-> NESTED PATH '$.l[*]' COLUMNS (lpath varchar(10) PATH '$')
-> )
-> )
-> ) as jt;
+---------+-------+-------+------+-------+
| top_ord | apath | bpath | ord | lpath |
+---------+-------+-------+------+-------+
| 1 | a_val | c_val | 1 | 1 |
| 1 | a_val | c_val | 1 | 2 |
| 2 | a_val | c_val | 1 | 11 |
| 2 | a_val | c_val | 2 | 22 |
+---------+-------+-------+------+-------+
4 rows in set (0.00 sec)
六，JSON格式校验
1，JSON_SCHEMA_VALID(schema,document)
校验document文档是否符合schema定义的JSON规则。
document和schema必须都是标准的JSON格式。
schema中的require参数表示必须要有的key。
假如定义一个schema：
mysql> SET @schema = '{
'> "id": "http://json-schema.org/geo",
'> "$schema": "http://json-schema.org/draft-04/schema#",
'> "description": "A geographical coordinate",
'> "type": "object",
'> "properties": {
'> "latitude": {
'> "type": "number",
'> "minimum": -90,
'> "maximum": 90
'> },
'> "longitude": {
'> "type": "number",
'> "minimum": -180,
'> "maximum": 180
'> }
'> },
'> "required": ["latitude", "longitude"]
'> }';
Query OK, 0 rows affected (0.00 sec)
然后定义document：
mysql> SET @document = '{
'> "latitude": 63.444697,
'> "longitude": 10.445118
'> }';
Query OK, 0 rows affected (0.00 sec)
最后用JSON_SCHEMA_VALID(schema,document)函数来校验：
mysql> SELECT JSON_SCHEMA_VALID(@schema, @document);
+---------------------------------------+
| JSON_SCHEMA_VALID(@schema, @document) |
+---------------------------------------+
| 1 |
+---------------------------------------+
1 row in set (0.00 sec)
返回1即为符合schema定义的规范。
下面把document改成空的：
mysql> SET @document = '{}';
Query OK, 0 rows affected (0.00 sec)
再校验一下：
mysql> SELECT JSON_SCHEMA_VALID(@schema, @document);
+---------------------------------------+
| JSON_SCHEMA_VALID(@schema, @document) |
+---------------------------------------+
| 0 |
+---------------------------------------+
1 row in set (0.00 sec)
返回0表示校验失败。
2，JSON_SCHEMA_VALIDATION_REPORT(schema,document)
校验document文档是否符合schema定义的JSON规则，并返回JSON格式的校验报告。
如果校验成功，则返回：{"valid": true}。
如果校验失败，则返回的校验报告中会包含以下内容：
valid。校验失败则为false。
reason。失败原因。
schema-location。校验失败的节点在schema中的位置。
document-location。校验失败的节点在document中的位置。
schema-failed-keyword。包含校验失败节点的一段schema的关键字。
一个校验失败的场景：
mysql> SET @schema = '{
'> "id": "http://json-schema.org/geo",
'> "$schema": "http://json-schema.org/draft-04/schema#",
'> "description": "A geographical coordinate",
'> "type": "object",
'> "properties": {
'> "latitude": {
'> "type": "number",
'> "minimum": -90,
'> "maximum": 90
'> },
'> "longitude": {
'> "type": "number",
'> "minimum": -180,
'> "maximum": 180
'> }
'> },
'> "required": ["latitude", "longitude"]
'> }';
Query OK, 0 rows affected (0.00 sec)
mysql> SET @document = '{
'> "latitude": 63.444697,
'> "longitude": 310.445118
'> }';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT JSON_PRETTY(JSON_SCHEMA_VALIDATION_REPORT(@schema, @document));
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| JSON_PRETTY(JSON_SCHEMA_VALIDATION_REPORT(@schema, @document)) |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {
"valid": false,
"reason": "The JSON document location '#/longitude' failed requirement 'maximum' at JSON Schema location '#/properties/longitude'",
"schema-location": "#/properties/longitude",
"document-location": "#/longitude",
"schema-failed-keyword": "maximum"
} |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
JSON_PRETTY()函数的作用是让返回的结果格式更好看一些，否则是一行显示。
七，JSON辅助函数
1，JSON_PRETTY(json_val)
格式化JSON值，让输出更好看一些。
参数必须是JSON值或者是符合JSON格式的字符串。
举例：
mysql> SELECT JSON_PRETTY('["a",1,{"key1":
'> "value1"},"5", "77" ,
'> {"key2":["value3","valueX",
'> "valueY"]},"j", "2" ]');
+-------------------------------------------------------------------------------------------------------------------------------------------------------+
| JSON_PRETTY('["a",1,{"key1":
"value1"},"5", "77" ,
{"key2":["value3","valueX",
"valueY"]},"j", "2" ]') |
+-------------------------------------------------------------------------------------------------------------------------------------------------------+
| [
"a",
1,
{
"key1": "value1"
},
"5",
"77",
{
"key2": [
"value3",
"valueX",
"valueY"
]
},
"j",
"2"
] |
+-------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
2，JSON_STORAGE_FREE(json_val)
此函数的功能是，在使用JSON_SET()，JSON_REPLACE(),JSON_REMOVE()函数修改了列值之后，JSON释放了多少空间。
多次使用这三个函数修改列值，此函数返回值会累加。
不使用这三个函数修改列值时，比如直接用set关键字修改列值，则此函数返回0。
只对修改表中存储JSON值的场景有效，如果用三个函数修改用户变量，此函数返回值依然是0。
参数是JSON字符串，返回值会是0。
比如：
mysql> select * from test_j;
+----------------------------------------------+
| a |
+----------------------------------------------+
| {"a": 10, "b": "wxyz", "c": "[true, false]"} |
+----------------------------------------------+
1 row in set (0.00 sec)
mysql> update test_j SET a = JSON_SET(a, "$.a", 10, "$.b", "wxyz", "$.c", 1);
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from test_j;
+--------------------------------+
| a |
+--------------------------------+
| {"a": 10, "b": "wxyz", "c": 1} |
+--------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_STORAGE_FREE(a) FROM test_j;
+----------------------+
| JSON_STORAGE_FREE(a) |
+----------------------+
| 14 |
+----------------------+
1 row in set (0.00 sec)
mysql> update test_j SET a = JSON_SET(a, "$.a", 10, "$.b", "wx", "$.c", 1);
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT JSON_STORAGE_FREE(a) FROM test_j;
+----------------------+
| JSON_STORAGE_FREE(a) |
+----------------------+
| 16 |
+----------------------+
1 row in set (0.00 sec)
mysql> update test_j set a = '{"a": 10, "b": "wx"}';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT JSON_STORAGE_FREE(a) FROM test_j;
+----------------------+
| JSON_STORAGE_FREE(a) |
+----------------------+
| 0 |
+----------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_STORAGE_FREE('{"a": 10, "b": "wxyz", "c": "1"}');
+-------------------------------------------------------+
| JSON_STORAGE_FREE('{"a": 10, "b": "wxyz", "c": "1"}') |
+-------------------------------------------------------+
| 0 |
+-------------------------------------------------------+
1 row in set (0.00 sec)
3，JSON_STORAGE_SIZE(json_val)
此函数返回的是一个JSON值转换成二进制后占用的磁盘空间。
如果参数是一个JSON格式的列，则表示此列的JSON值转换成二进制后占用的磁盘空间。
如果参数是一个JSON格式的字符串，则表示此字符串代表的JSON值转换成二进制后会占用的磁盘空间。
JSON_SET()，JSON_REPLACE(),JSON_REMOVE()三个函数会部分更新JSON值，不会导致此函数的返回值发生变化。直接用set关键字修改列值会导致此函数返回值发生变化。
因为用户变量不能部分修改，所以当修改用户变量时，此函数的返回值会立刻变化。
举例：
mysql> select * from test_j;
+-----------------------------------------------+
| a |
+-----------------------------------------------+
| {"a": 1000, "b": "wxyz", "c": "[1, 3, 5, 7]"} |
+-----------------------------------------------+
1 row in set (0.00 sec)
mysql> select
-> a,
-> JSON_STORAGE_SIZE(a) AS Size,
-> JSON_STORAGE_FREE(a) AS Free
-> from test_j;
+-----------------------------------------------+------+------+
| a | Size | Free |
+-----------------------------------------------+------+------+
| {"a": 1000, "b": "wxyz", "c": "[1, 3, 5, 7]"} | 47 | 0 |
+-----------------------------------------------+------+------+
1 row in set (0.00 sec)
mysql> UPDATE test_j SET a = JSON_SET(a, "$.b", "a");
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select
-> a,
-> JSON_STORAGE_SIZE(a) AS Size,
-> JSON_STORAGE_FREE(a) AS Free
-> from test_j;
+--------------------------------------------+------+------+
| a | Size | Free |
+--------------------------------------------+------+------+
| {"a": 1000, "b": "a", "c": "[1, 3, 5, 7]"} | 47 | 3 |
+--------------------------------------------+------+------+
1 row in set (0.00 sec)
mysql> update test_j set a = '{"a": 4.55, "b": "wxyz", "c": "[true, false]"}';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select
-> a,
-> JSON_STORAGE_SIZE(a) AS Size,
-> JSON_STORAGE_FREE(a) AS Free
-> from test_j;
+------------------------------------------------+------+------+
| a | Size | Free |
+------------------------------------------------+------+------+
| {"a": 4.55, "b": "wxyz", "c": "[true, false]"} | 56 | 0 |
+------------------------------------------------+------+------+
1 row in set (0.00 sec)
mysql> SET @j = '[100, "sakila", [1, 3, 5], 425.05]';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @j, JSON_STORAGE_SIZE(@j) AS Size;
+------------------------------------+------+
| @j | Size |
+------------------------------------+------+
| [100, "sakila", [1, 3, 5], 425.05] | 45 |
+------------------------------------+------+
1 row in set (0.01 sec)
mysql> SET @j = JSON_SET(@j, '$[1]', "json");
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @j, JSON_STORAGE_SIZE(@j) AS Size;
+----------------------------------+------+
| @j | Size |
+----------------------------------+------+
| [100, "json", [1, 3, 5], 425.05] | 43 |
+----------------------------------+------+
1 row in set (0.00 sec)
mysql> SELECT
-> JSON_STORAGE_SIZE('[100, "sakila", [1, 3, 5], 425.05]') AS A,
-> JSON_STORAGE_SIZE('{"a": 1000, "b": "a", "c": "[1, 3, 5, 7]"}') AS B,
-> JSON_STORAGE_SIZE('{"a": 1000, "b": "wxyz", "c": "[1, 3, 5, 7]"}') AS C,
-> JSON_STORAGE_SIZE('[100, "json", [[10, 20, 30], 3, 5], 425.05]') AS D;
+----+----+----+----+
| A | B | C | D |
+----+----+----+----+
| 45 | 44 | 47 | 56 |
+----+----+----+----+
1 row in set (0.00 sec)
标题：Mysql 8.0 之后的Json 类型玩法
作者：llilei
地址：http://solo.llilei.work/articles/2021/10/30/1635594438237.html