mysql处理惩罚json格式的字段，一文搞懂mysql剖析json数据 ...

一、概述

1、什么是JSON

略。自行百度。
2、MySQL的JSON

JSON 数据范例是 MySQL 5.7.8 开始支持的。在此之前，只能通过字符范例（CHAR，VARCHAR 或 TEXT ）来保存 JSON 文档。
MySQL 8.0版本中增长了对JSON范例的索引支持。可以利用CREATE INDEX语句创建JSON范例的索引，进步JSON范例数据的查询效率。
存储JSON文档所需的空间与存储LONGBLOB或LONGTEXT所需的空间大抵相同。
在MySQL 8.0.13之前，JSON列不能有非空的默认值。
JSON 范例比较得当存储一些列不固定、修改较少、相对静态的数据。MySQL支持JSON格式的数据之后，可以减少对非关系型数据库的依赖。
3、varchar、text、json范例字段的区别

这三种范例的字段，都可以存储json格式，查询起来好像正常的json函数也能用，这三者存储json范例的数据有什么区别吗？
我们接下来测试一下。
二、JSON范例的创建

1、建表指定

1. CREATE TABLE `users` (
2.   `id` int NOT NULL AUTO_INCREMENT COMMENT 'id',
3.   `name` varchar(50) DEFAULT NULL COMMENT '名字',
4.   `json_data` json DEFAULT NULL COMMENT 'json数据',
5.   `info` varchar(2000) DEFAULT NULL COMMENT '普通数据',
6.   `text` text COMMENT 'text数据',
7.   PRIMARY KEY (`id`)
8. ) ENGINE=InnoDB;

复制代码

2、修改字段

1. -- 添加json字段
2. ALTER TABLE users ADD COLUMN `test_json` JSON DEFAULT NULL COMMENT '测试';
3. -- 修改字段类型为json
4. ALTER TABLE users modify test_json JSON DEFAULT NULL COMMENT '测试';
5. -- 删除json字段
6. ALTER TABLE users DROP COLUMN test_json;

复制代码

三、JSON范例的插入

1、字符串直接插入

varchar、text、json格式都支持，也可以插入更复杂的嵌套json：

1. -- 插入数组
2. insert into users(json_data) values('[1, "abc", null, true, "08:45:06.000000"]');
3. insert into users(info) values('[1, "abc", null, true, "08:45:06.000000"]');
4. insert into users(text) values('[1, "abc", null, true, "08:45:06.000000"]');
5. -- 插入对象
6. insert into users(json_data) values('{"id": 87, "name": "carrot"}');
7. insert into users(info) values('{"id": 87, "name": "carrot"}');
8. insert into users(text) values('{"id": 87, "name": "carrot"}');
9. -- 插入嵌套json
10. insert into users(json_data) values('[{"sex": "M"},{"sex":"F", "city":"nanjing"}]');
11. insert into users(info) values('[{"sex": "M"},{"sex":"F", "city":"nanjing"}]');
12. insert into users(text) values('[{"sex": "M"},{"sex":"F", "city":"nanjing"}]');

复制代码

但是json格式的字段，插入时会自动校验格式，如果格式不是json的，会报错：

1. insert into users(json_data) values('{"id", "name": "carrot"}');
2. > 3140 - Invalid JSON text: "Missing a colon after a name of object member." at position 5 in value for column 'users.json_data'.

复制代码

2、JSON_ARRAY()函数插入数组

1. -- 格式：
2. JSON_ARRAY([val[, val] ...])
4. -- 使用JSON_ARRAY()函数创建数组 ： [1, "abc", null, true, "08:09:38.000000"]
5. insert into users(json_data) values(JSON_ARRAY(1, "abc", null, true,curtime()));
6. insert into users(info) values(JSON_ARRAY(1, "abc", null, true,curtime()));
7. insert into users(text) values(JSON_ARRAY(1, "abc", null, true,curtime()));

复制代码

3、JSON_OBJECT()函数插入对象

对于 JSON 文档，KEY 名不能重复。
如果插入的值中存在重复 KEY，在 MySQL 8.0.3 之前，遵照 first duplicate key wins 原则，会保存第一个 KEY，背面的将被抛弃掉。
从 MySQL 8.0.3 开始，遵照的是 last duplicate key wins 原则，只会保存最后一个 KEY。

1. -- 格式：
2. JSON_OBJECT([key, val[, key, val] ...])
4. -- 创建对象，一个key对应一个value ： {"id": 87, "name": "carrot"}
5. insert into users(json_data) values(json_object('id', 87, 'name', 'carrot'));
6. insert into users(info) values(json_object('id', 87, 'name', 'carrot'));
7. insert into users(text) values(json_object('id', 87, 'name', 'carrot'));

复制代码

4、JSON_ARRAYAGG()和JSON_OBJECTAGG()将查询结果封装成json

1. mysql> SELECT o_id, attribute, value FROM t3;
2. +------+-----------+-------+
3. | o_id | attribute | value |
4. +------+-----------+-------+
5. |    2 | color     | red   |
6. |    2 | fabric    | silk  |
7. |    3 | color     | green |
8. |    3 | shape     | square|
9. +------+-----------+-------+
10. 4 rows in set (0.00 sec)
12. mysql> SELECT o_id, JSON_ARRAYAGG(attribute) AS attributes
13.     -> FROM t3 GROUP BY o_id;
14. +------+---------------------+
15. | o_id | attributes          |
16. +------+---------------------+
17. |    2 | ["color", "fabric"] |
18. |    3 | ["color", "shape"]  |
19. +------+---------------------+
20. 2 rows in set (0.00 sec)

复制代码

1. mysql> SELECT o_id, attribute, value FROM t3;
2. +------+-----------+-------+
3. | o_id | attribute | value |
4. +------+-----------+-------+
5. |    2 | color     | red   |
6. |    2 | fabric    | silk  |
7. |    3 | color     | green |
8. |    3 | shape     | square|
9. +------+-----------+-------+
10. 4 rows in set (0.00 sec)
12. mysql> SELECT o_id, JSON_OBJECTAGG(attribute, value)
13.     -> FROM t3 GROUP BY o_id;
14. +------+---------------------------------------+
15. | o_id | JSON_OBJECTAGG(attribute, value)      |
16. +------+---------------------------------------+
17. |    2 | {"color": "red", "fabric": "silk"}    |
18. |    3 | {"color": "green", "shape": "square"} |
19. +------+---------------------------------------+
20. 2 rows in set (0.00 sec)

复制代码

5、CAST()将字符串转成json

如果直接插入的话，插入的是字符串，需要手动转换

1. set @j = '{"a": 1, "b": [2, 3], "a c": 4}';
2. -- {"a": "{"qq": 22, "ww": 33}", "b": [2, 3], "a c": 4}
3. select JSON_SET(@j, '$.a', '{"qq": 22, "ww": 33}');
4. -- {"a": {"qq": 22, "ww": 33}, "b": [2, 3], "a c": 4}
5. select JSON_SET(@j, '$.a', CAST('{"qq": 22, "ww": 33}' as JSON));

复制代码

四、JSON范例的剖析

1、JSON_EXTRACT()剖析json

格式：JSON_EXTRACT(json_doc, path[, path] …)
其中，json_doc 是 JSON 文档，path 是路径。该函数会从 JSON 文档提取指定路径（path）的元素。如果指定 path 不存在，会返回 NULL。可指定多个 path，匹配到的多个值会以数组形式返回。

1. -- 解析数组
2. -- 取下标为1的数组值（数组下标从0开始），结果：20
3. SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]');
4. -- 取多个，结果返回是一个数组，结果：[20, 10]
5. SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]', '$[0]');
6. -- 可以使用*获取全部，结果：[30, 40]
7. SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[2][*]');
9. -- 还可通过 [M to N] 获取数组的子集
10. -- 结果：[10, 20]
11. select json_extract('[10, 20, [30, 40]]', '$[0 to 1]');
12. -- 这里的 last 代表最后一个元素的下标，结果：[20, [30, 40]]
13. select json_extract('[10, 20, [30, 40]]', '$[last-1 to last]');

复制代码

1. -- 解析对象：对象的路径是通过 KEY 来表示的。
2. set @j='{"a": 1, "b": [2, 3], "a c": 4}';
4. -- 如果 KEY 在路径表达式中不合法（譬如存在空格），则在引用这个 KEY 时，需用双引号括起来。
5. -- 结果： 1 4 3
6. select json_extract(@j, '$.a'), json_extract(@j, '$."a c"'), json_extract(@j, '$.b[1]');
7. -- 使用*获取所有元素，结果：[1, [2, 3], 4]
8. select json_extract('{"a": 1, "b": [2, 3], "a c": 4}', '$.*');
9. -- 这里的 $**.b 匹配 $.a.b 和 $.c.b，结果：[1, 2]
10. select json_extract('{"a": {"b": 1}, "c": {"b": 2}}', '$**.b');

复制代码

json_extract剖析出来的数据，可以灵活用于where、order by等等所有地方。
2、-> 箭头函数剖析json

column->path，包括背面讲到的 column->>path，都是语法糖，在现实利用的时间都会在底层自动转化为 JSON_EXTRACT。
column->path 等同于 JSON_EXTRACT(column, path) ，只能指定一个path。

1. -- 同JSON_EXTRACT
2. insert into users(json_data) values('{"empno": 1001, "ename": "jack"}');
3. -- 结果："jack"
4. select json_data, json_data -> '$.ename' from users;

复制代码

3、JSON_QUOTE()引用与JSON_UNQUOTE()取消引用

JSON_QUOTE(string)，生成有效的 JSON 字符串，主要是对一些特殊字符（如双引号）进行转义。

1. -- 结果："null"        ""null""        "[1, 2, 3]"
2. select json_quote('null'), json_quote('"null"'), json_quote('[1, 2, 3]');

复制代码

JSON_UNQUOTE(json_val)，将 JSON 转义成字符串输出。常用于利用JSON_EXTRACT()和->函数剖析完之后，去除引号。
JSON_UNQUOTE()特殊字符转义表：
转义序列由序列表示的字符\"双引号\b退格字符\f换页字符\n换行符\r回车符\t制表符\\反斜杠(\)字符\uXXXXUnicode XXXX 转UTF-8

1. insert into users(json_data) values('{"empno": 1001, "ename": "jack"}');
2. -- 字符串类型转换后会去掉引号，结果："jack"        jack        1        0
3. select json_data->'$.ename',json_unquote(json_data->'$.ename'),json_valid(json_data->'$.ename'),json_valid(json_unquote(json_data->'$.ename')) from users;
4. -- 数字类型转换并没有额外效果，结果：1001        1001        1        1
5. select json_data->'$.empno',json_unquote(json_data->'$.empno'),json_valid(json_data->'$.empno'),json_valid(json_unquote(json_data->'$.empno')) from users;

复制代码

直观地看，没加 JSON_UNQUOTE 字符串会用双引号引起来，加了 JSON_UNQUOTE 就没有。但本质上，前者是 JSON 中的 STRING 范例，后者是 MySQL 中的字符范例，这一点可通过 JSON_VALID 来判断。
4、->>箭头剖析json

同 column->path 类似，只不过其返回的是字符串，相当于将字符串的双引号去掉了，是一个语法糖，本质上是执行了JSON_UNQUOTE( JSON_EXTRACT(column, path) )。
以下三者是等价的：
JSON_UNQUOTE( JSON_EXTRACT(column, path) )
JSON_UNQUOTE(column -> path)
column->>path

1. insert into users(json_data) values('{"empno": 1001, "ename": "jack"}');
2. -- 结果："jack"        jack        jack        jack
3. select json_data->'$.ename',json_unquote(json_data->'$.ename'),json_data->>'$.ename', JSON_UNQUOTE( JSON_EXTRACT(json_data, '$.ename') ) from users;

复制代码

五、JSON范例的查询

1、JSON_CONTAINS()判断是否包含

格式：JSON_CONTAINS(target, candidate[, path])
判断 target 文档是否包含 candidate 文档，包含的话返回1，不包含的话返回0
如果带了path，就判断path中的数据是否等于candidate，等于的话返回1，不等于的话返回0
函数前加not可取反

1. SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}';
2. SET @j2 = '{"a":1}';
3. -- 判断@j中是否包含@j2，结果：1
4. SELECT JSON_CONTAINS(@j, @j2);
6. SET @j2 = '1';
7. -- 判断@j字段中的a是否等于1，结果：1
8. SELECT JSON_CONTAINS(@j, @j2, '$.a');
9. -- 结果：0
10. SELECT JSON_CONTAINS(@j, @j2, '$.b');
12. SET @j2 = '{"d": 4}';
13. -- 结果：0
14. SELECT JSON_CONTAINS(@j, @j2, '$.a');
15. -- 结果：1
16. SELECT JSON_CONTAINS(@j, @j2, '$.c');
18. SET @j = '[1, "a", 1.02]';
19. SET @j2 = '"a"';
20. -- 判断@j数组中是否包含@j2，结果：1
21. SELECT JSON_CONTAINS(@j, @j2);

复制代码

2、JSON_CONTAINS_PATH()判断

格式：JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] …)
判断指定的 path 是否存在，存在，则返回 1，否则是 0。
函数中的 one_or_all 可指定 one 或 all，one 是任意一个路径存在就返回 1，all 是所有路径都存在才返回 1。
函数前加not可取反

1. SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}';
2. -- a或者e 存在一个就返回1，结果：1
3. SELECT JSON_CONTAINS_PATH(@j, 'one', '$.a', '$.e');
4. -- a和e都存在返回1，结果：0
5. SELECT JSON_CONTAINS_PATH(@j, 'all', '$.a', '$.e');
6. -- c中的d存在返回1，结果：1
7. SELECT JSON_CONTAINS_PATH(@j, 'one', '$.c.d');
9. SET @j = '[1, 4, "a", "c"]';
10. -- @j是一个数组，$[1]判断第二个数据是否存在，结果为1
11. select JSON_CONTAINS_PATH(@j, 'one', '$[1]');
12. -- $[11]判断第11个数据不存在，结果为0
13. select JSON_CONTAINS_PATH(@j, 'one', '$[11]');

复制代码

3、JSON_KEYS()获取keys

返回 JSON 文档最外层的 key，如果指定了 path，则返回该 path 对应元素最外层的 key。

1. -- 结果：["a", "b"]
2. SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}');
3. -- 结果：["c"]
4. SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}', '$.b');

复制代码

4、JSON_OVERLAPS()比较两个json

MySQL 8.0.17 引入的，用来比较两个 JSON 文档是否有相同的键值对或数组元素，如果有，则返回 1，否则是 0。 如果两个参数都是标量，则判断这两个标量是否相等。
函数前加not可取反

1. -- 结果： 1        0
2. select json_overlaps('[1,3,5,7]', '[2,5,7]'),json_overlaps('[1,3,5,7]', '[2,6,8]');
4. -- 部分匹配被视为不匹配，结果：0
5. SELECT JSON_OVERLAPS('[[1,2],[3,4],5]', '[1,[2,3],[4,5]]');
7. -- 比较对象时，如果它们至少有一个共同的键值对，则结果为真。
8. -- 结果：1
9. SELECT JSON_OVERLAPS('{"a":1,"b":10,"d":10}', '{"c":1,"e":10,"f":1,"d":10}');
10. -- 结果：0
11. SELECT JSON_OVERLAPS('{"a":1,"b":10,"d":10}', '{"a":5,"e":10,"f":1,"d":20}');
13. -- 如果两个标量用作函数的参数，JSON_OVERLAPS()会执行一个简单的相等测试:
14. -- 结果：1
15. SELECT JSON_OVERLAPS('5', '5');
16. -- 结果：0
17. SELECT JSON_OVERLAPS('5', '6');
19. -- 当比较标量和数组时，JSON_OVERLAPS()试图将标量视为数组元素。在此示例中，第二个参数6被解释为[6]，如下所示:结果：1
20. SELECT JSON_OVERLAPS('[4,5,6,7]', '6');
22. -- 该函数不执行类型转换:
23. -- 结果：0
24. SELECT JSON_OVERLAPS('[4,5,"6",7]', '6');
25. -- 结果：0
26. SELECT JSON_OVERLAPS('[4,5,6,7]', '"6"');

复制代码

5、JSON_SEARCH()返回字符串的位置

格式：JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] …])
返回某个字符串（search_str）在 JSON 文档中的位置，其中，
one_or_all：匹配的次数，one 是只匹配一次，all 是匹配所有。如果匹配到多个，结果会以数组的形式返回。
search_str：子串，支持模糊匹配：% 和 _ 。
escape_char：转义符，如果该参数不填或为 NULL，则取默认转义符\。
path：查找路径。

1. SET @j = '["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]';
2. -- 结果："$[0]"
3. SELECT JSON_SEARCH(@j, 'one', 'abc');
4. -- 结果：["$[0]", "$[2].x"]
5. SELECT JSON_SEARCH(@j, 'all', 'abc');
6. -- 结果：null
7. SELECT JSON_SEARCH(@j, 'all', 'ghi');
8. -- 结果："$[1][0].k"
9. SELECT JSON_SEARCH(@j, 'all', '10');
10. -- 结果："$[1][0].k"
11. SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$');
12. -- 结果："$[1][0].k"
13. SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[*]');
14. -- 结果："$[1][0].k"
15. SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$**.k');
16. -- 结果："$[1][0].k"
17. SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[*][0].k');
18. -- 结果："$[1][0].k"
19. SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[1]');
20. -- 结果："$[1][0].k"
21. SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[1][0]');
22. -- 结果："$[2].x"
23. SELECT JSON_SEARCH(@j, 'all', 'abc', NULL, '$[2]');
24. -- 结果：["$[0]", "$[2].x"]
25. SELECT JSON_SEARCH(@j, 'all', '%a%');
26. -- 结果：["$[0]", "$[2].x", "$[3].y"]
27. SELECT JSON_SEARCH(@j, 'all', '%b%');
28. -- 结果："$[0]"
29. SELECT JSON_SEARCH(@j, 'all', '%b%', NULL, '$[0]');
30. -- 结果："$[2].x"
31. SELECT JSON_SEARCH(@j, 'all', '%b%', NULL, '$[2]');
32. -- 结果：null
33. SELECT JSON_SEARCH(@j, 'all', '%b%', NULL, '$[1]');
34. -- 结果：null
35. SELECT JSON_SEARCH(@j, 'all', '%b%', '', '$[1]');
36. -- 结果："$[3].y"
37. SELECT JSON_SEARCH(@j, 'all', '%b%', '', '$[3]');

复制代码

6、JSON_VALUE()提取指定路径的元素

格式：JSON_VALUE(json_doc, path)
8.0.21 引入的，从 JSON 文档提取指定路径（path）的元素。
完备的语法如下所示：

1. JSON_VALUE(json_doc, path [RETURNING type] [on_empty] [on_error])
3. on_empty:
4.     {NULL | ERROR | DEFAULT value} ON EMPTY
6. on_error:
7.     {NULL | ERROR | DEFAULT value} ON ERROR

复制代码

其中：
RETURNING type：返回值的范例，不指定，则默认是 VARCHAR(512)。不指定字符集，则默认是 utf8mb4，且区分大小写。
on_empty：如果指定路径没有值，会触发 on_empty 子句， 默认是返回 NULL，也可指定 ERROR 抛堕落误，或者通过 DEFAULT value 返回默认值。
on_error：三种环境下会触发 on_error 子句：从数组或对象中提取元素时，会剖析到多个值；范例转换错误，譬如将 “abc” 转换为 unsigned 范例；值被 truncate 了。默认是返回 NULL。

1. -- 查找fname的值，结果为：Joe
2. SELECT JSON_VALUE('{"fname": "Joe", "lname": "Palmer"}', '$.fname');
3. -- 结果：49.95
4. SELECT JSON_VALUE('{"item": "shoes", "price": "49.95"}', '$.price' RETURNING DECIMAL(4,2)) AS price;
5. -- 结果：50.0
6. SELECT JSON_VALUE('{"item": "shoes", "price": "49.95"}', '$.price' RETURNING DECIMAL(4,1)) AS price;
7. -- 使用RETURNING定义返回数据类型，等效于以下sql：
8. SELECT CAST(
9.     JSON_UNQUOTE( JSON_EXTRACT(json_doc, path) )
10.     AS type
11. );
14. mysql> select json_value('{"item": "shoes", "price": "49.95"}', '$.price1' error on empty);
15. ERROR 3966 (22035): No value was found by 'json_value' on the specified path.
17. mysql> select json_value('[1, 2, 3]', '$[1 to 2]' error on error);
18. ERROR 3967 (22034): More than one value was found by 'json_value' on the specified path.
20. mysql> select json_value('{"item": "shoes", "price": "49.95"}', '$.item' returning unsigned error on error) as price;
21. ERROR 1690 (22003): UNSIGNED value is out of range in 'json_value'

复制代码

7、MEMBER OF()判断是否是json数组中的元素

格式：value MEMBER OF(json_array)
在 MySQL 8.0.17引入了MEMBER OF()函数。判断 value 是否是 JSON 数组的一个元素，如果是，则返回 1，否则是 0。
函数前加not可取反

1. -- 结果：1
2. SELECT 17 MEMBER OF('[23, "abc", 17, "ab", 10]');
3. -- 结果：1
4. SELECT 'ab' MEMBER OF('[23, "abc", 17, "ab", 10]');
5. -- 部分匹配不代表匹配
6. -- 结果：0
7. SELECT 7 MEMBER OF('[23, "abc", 17, "ab", 10]');
8. -- 结果：0
9. SELECT 'a' MEMBER OF('[23, "abc", 17, "ab", 10]');
10. -- 不执行字符串类型之间的相互转换:结果：0·0
11. SELECT 17 MEMBER OF('[23, "abc", "17", "ab", 10]'), "17" MEMBER OF('[23, "abc", 17, "ab", 10]')
12. -- 要将该操作符与本身是数组的值一起使用，必须将其显式转换为JSON数组。结果：1
13. SELECT CAST('[4,5]' AS JSON) MEMBER OF('[[3,4],[4,5]]');
14. -- 还可以使用JSON_ARRAY()函数执行必要的强制转换，如下所示: 结果：1
15. SELECT JSON_ARRAY(4,5) MEMBER OF('[[3,4],[4,5]]');
17. --转换，结果：1        1
18. SET @a = CAST('{"a":1}' AS JSON);
19. SET @b = JSON_OBJECT("b", 2);
20. SET @c = JSON_ARRAY(17, @b, "abc", @a, 23);
21. SELECT @a MEMBER OF(@c), @b MEMBER OF(@c);

复制代码

8、JSON_DEPTH()获取JSON最大深度

语法：JSON_DEPTH(json_doc)
返回JSON文档的最大深度。如果参数为NULL，则返回NULL。如果参数不是有效的JSON文档，则会出现错误。
对于空数组，空对象，标量值，其深度为 1。

1. -- 结果：1        1        1
2. SELECT JSON_DEPTH('{}'), JSON_DEPTH('[]'), JSON_DEPTH('true');
3. -- 结果：2        2
4. SELECT JSON_DEPTH('[10, 20]'), JSON_DEPTH('[[], {}]');
5. -- 结果：3
6. SELECT JSON_DEPTH('[10, {"a": 20}]');

复制代码

9、JSON_LENGTH()获取文档长度

语法：JSON_LENGTH(json_doc[, path])
返回 JSON 文档的长度，其计算规则如下：
1、如果是标量值，其长度为 1。
2、如果是数组，其长度为数组元素的个数。
3、如果是对象，其长度为对象元素的个数。
4、不包括嵌套数据和嵌套对象的长度。

1. -- 结果：3
2. SELECT JSON_LENGTH('[1, 2, {"a": 3}]');
3. -- 结果：2
4. SELECT JSON_LENGTH('{"a": 1, "b": {"c": 30}}');
5. -- 结果：1
6. SELECT JSON_LENGTH('{"a": 1, "b": {"c": 30}}', '$.b');

复制代码

10、JSON_TYPE()获取JSON范例

语法：JSON_TYPE(json_val)
返回 JSON 值的范例。
如果参数不是有效的JSON值，则会出现错误。

1. SET @j = '{"a": [10, true]}';
2. -- 结果：OBJECT
3. SELECT JSON_TYPE(@j);
4. -- 结果：ARRAY
5. SELECT JSON_TYPE(JSON_EXTRACT(@j, '$.a'));
6. -- 结果：INTEGER
7. SELECT JSON_TYPE(JSON_EXTRACT(@j, '$.a[0]'));
8. -- 结果：BOOLEAN
9. SELECT JSON_TYPE(JSON_EXTRACT(@j, '$.a[1]'));
10. -- 结果：NULL
11. SELECT JSON_TYPE(NULL);
12. -- 结果：STRING
13. select json_type('"abc"');
14. -- 结果：DATETIME
15. select json_type(cast(now() as json));

复制代码

JSON范例：OBJECT（对象）、ARRAY（数组）、BOOLEAN（布尔范例）、NULL
数字范例：INTEGER（TINYINT、SMALLINT、MEDIUMINT以及INT和BIGINT标量）、DOUBLE（DOUBLE、FLOAT）、DECIMAL（MySQL、DECIMAL）
时间范例：DATETIME（DATETIME、TIMESTAMP）、DATE、TIME
字符串范例：STRING（CHAR, VARCHAR, TEXT, ENUM, SET）
二进制范例：BLOB（ BINARY, VARBINARY, BLOB, BIT）
其他范例：OPAQUE
11、JSON_VALID()校验JSON格式

语法：JSON_VALID(val)
判断给定值是否是有效的 JSON 文档。
函数前加not可取反

1. -- 结果：1
2. SELECT JSON_VALID('{"a": 1}');
3. -- 结果：0        1
4. SELECT JSON_VALID('hello'), JSON_VALID('"hello"');

复制代码

六、JSON范例的修改

1、全量修改

直接利用update语句，将json数据字段全部更换。

1. update users set json_data = '{"a":1}';

复制代码

2、JSON_ARRAY_APPEND()向数组追加元素

格式：JSON_ARRAY_APPEND(json_doc, path, val[, path, val] …)
向数组指定位置追加元素。如果指定 path 不存在，则不添加。
在MySQL 5.7中，这个函数被定名为JSON_APPEND()。MySQL 8.0不再支持该名称。

1. SET @j = '["a", ["b", "c"], "d"]';
2. -- 在数组第二个元素的数组中追加1，结果：["a", ["b", "c", 1], "d"]
3. SELECT JSON_ARRAY_APPEND(@j, '$[1]', 1);
4. -- 结果：[["a", 2], ["b", "c"], "d"]
5. SELECT JSON_ARRAY_APPEND(@j, '$[0]', 2);
6. -- 结果：["a", [["b", 3], "c"], "d"]
7. SELECT JSON_ARRAY_APPEND(@j, '$[1][0]', 3);
8. -- 多个参数，结果：[["a", 1], [["b", 2], "c"], "d"]
9. select json_array_append(@j, '$[0]', 1, '$[1][0]', 2, '$[3]', 3);
11. SET @j = '{"a": 1, "b": [2, 3], "c": 4}';
12. -- 往b中追加，结果：{"a": 1, "b": [2, 3, "x"], "c": 4}
13. SELECT JSON_ARRAY_APPEND(@j, '$.b', 'x');
14. -- 结果：{"a": 1, "b": [2, 3], "c": [4, "y"]}
15. SELECT JSON_ARRAY_APPEND(@j, '$.c', 'y');
17. SET @j = '{"a": 1}';
18. -- 结果：[{"a": 1}, "z"]
19. SELECT JSON_ARRAY_APPEND(@j, '$', 'z');

复制代码

3、JSON_ARRAY_INSERT()向数组指定位置插入元素

格式：JSON_ARRAY_INSERT(json_doc, path, val[, path, val] …)
向数组指定位置插入元素。

1. SET @j = '["a", {"b": [1, 2]}, [3, 4]]';
2. -- 在下标1处添加元素x，结果：["a", "x", {"b": [1, 2]}, [3, 4]]
3. SELECT JSON_ARRAY_INSERT(@j, '$[1]', 'x');
4. -- 没有100个元素，在最后插入，结果： ["a", {"b": [1, 2]}, [3, 4], "x"]
5. SELECT JSON_ARRAY_INSERT(@j, '$[100]', 'x');
6. -- 结果：["a", {"b": ["x", 1, 2]}, [3, 4]]
7. SELECT JSON_ARRAY_INSERT(@j, '$[1].b[0]', 'x');
8. -- 结果：["a", {"b": [1, 2]}, [3, "y", 4]]
9. SELECT JSON_ARRAY_INSERT(@j, '$[2][1]', 'y');
11. -- 早期的修改会影响数组中后续元素的位置，因此同一个JSON_ARRAY_INSERT()调用中的后续路径应该考虑这一点。在最后一个示例中，第二个路径没有插入任何内容，因为在第一次插入之后，该路径不再匹配任何内容。
12. -- 结果：["x", "a", {"b": [1, 2]}, [3, 4]]
13. SELECT JSON_ARRAY_INSERT(@j, '$[0]', 'x', '$[2][1]', 'y');

复制代码

4、JSON_INSERT()插入新值

格式：JSON_INSERT(json_doc, path, val[, path, val] …)
插入不存在的key的值，已经存在的不修改。
仅当指定位置或指定 KEY 的值不存在时，才执行插入操作。别的，如果指定的 path 是数组下标，且 json_doc 不是数组，该函数起首会将 json_doc 转化为数组，然后再插入新值。

1. SET @j = '{ "a": 1, "b": [2, 3]}';
2. -- a已经存在则忽略，c不存在则添加，结果：{"a": 1, "b": [2, 3], "c": "[true, false]"}
3. SELECT JSON_INSERT(@j, '$.a', 10, '$.c', '[true, false]');
4. -- 上面插入的c是一个带引号的字符串，想要插入一个数组，必须进行转换，结果：{"a": 1, "b": [2, 3], "c": [true, false]}
5. SELECT JSON_INSERT(@j, '$.a', 10, '$.c', CAST('[true, false]' AS JSON));
7. -- 下标0位置已经有值了，不会插入，结果：1
8. select json_insert('1','$[0]',"10");
9. -- 结果：[1, "10"]
10. select json_insert('1','$[1]',"10");
11. -- 结果：["1", "2", "10"]
12. select json_insert('["1","2"]','$[2]',"10");

复制代码

5、JSON_MERGE()归并json

格式：JSON_MERGE(json_doc, json_doc[, json_doc] …)
归并两个或多个JSON文档。JSON_MERGE_PRESERVE()的同义词；在MySQL 8.0.3中已弃用，在未来版本中大概会被删除。
保举利用JSON_MERGE_PRESERVE()

1. -- 结果：[1, 2, true, false]
2. SELECT JSON_MERGE('[1, 2]', '[true, false]');

复制代码

6、JSON_MERGE_PATCH()归并json

MySQL 8.0.3 引入的，用来归并多个 JSON 文档。其归并规则如下：
1、如果两个文档不满是 JSON 对象，则归并后的结果是第二个文档。
2、如果两个文档都是 JSON 对象，且不存在着同名 KEY，则归并后的文档包括两个文档的所有元素，如果存在着同名 KEY，则第二个文档的值会覆盖第一个。

1. -- 不是对象，结果：[true, false]
2. SELECT JSON_MERGE_PATCH('[1, 2]', '[true, false]');
3. -- 都是对象，结果：{"id": 47, "name": "x"}
4. SELECT JSON_MERGE_PATCH('{"name": "x"}', '{"id": 47}');
5. -- 都不是对象，取第二个，结果：true
6. SELECT JSON_MERGE_PATCH('1', 'true');
7. -- 第一个不是对象，取第二个 ，结果：{"id": 47}
8. SELECT JSON_MERGE_PATCH('[1, 2]', '{"id": 47}');
9. -- 第二个覆盖第一个，结果：{"a": 3, "b": 2, "c": 4}
10. SELECT JSON_MERGE_PATCH('{ "a": 1, "b":2 }','{ "a": 3, "c":4 }');
11. -- 结果：{"a": 5, "b": 2, "c": 4, "d": 6}
12. SELECT JSON_MERGE_PATCH('{ "a": 1, "b":2 }','{ "a": 3, "c":4 }', '{ "a": 5, "d":6 }');
13. -- 第二个有null，会删除该key，结果：{"a": 1}
14. SELECT JSON_MERGE_PATCH('{"a":1, "b":2}', '{"b":null}');
15. -- 嵌套json也可以合并，结果：{"a": {"x": 1, "y": 2}}
16. SELECT JSON_MERGE_PATCH('{"a":{"x":1}}', '{"a":{"y":2}}');

复制代码

注意区别于JSON_MERGE_PRESERVE
7、JSON_MERGE_PRESERVE()归并json

MySQL 8.0.3 引入的，用来代替 JSON_MERGE。也是用来归并文档，但归并规则与 JSON_MERGE_PATCH 有所差别。
1、两个文档中，只要有一个文档是数组，则别的一个文档会归并到该数组中。
2、两个文档都是 JSON 对象，若存在着同名 KEY ，第二个文档并不会覆盖第一个，而是会将值 append 到第一个文档中。

1. -- 数组合并，结果：[1, 2, true, false]
2. SELECT JSON_MERGE_PRESERVE('[1, 2]', '[true, false]');
3. -- 对象合并，结果：{"id": 47, "name": "x"}
4. SELECT JSON_MERGE_PRESERVE('{"name": "x"}', '{"id": 47}');
5. -- 两个常量，合并为一个数组，结果：[1, true]
6. SELECT JSON_MERGE_PRESERVE('1', 'true');
7. -- 对象合并到数组中，结果：[1, 2, {"id": 47}]
8. SELECT JSON_MERGE_PRESERVE('[1, 2]', '{"id": 47}');
9. -- 相同的key合并到一个数组，结果：{"a": [1, 3], "b": 2, "c": 4}
10. SELECT JSON_MERGE_PRESERVE('{ "a": 1, "b": 2 }', '{ "a": 3, "c": 4 }');
11. -- 结果：{"a": [1, 3, 5], "b": 2, "c": 4, "d": 6}
12. SELECT JSON_MERGE_PRESERVE('{ "a": 1, "b": 2 }','{ "a": 3, "c": 4 }', '{ "a": 5, "d": 6 }');

复制代码

注意区别于JSON_MERGE_PATCH()
8、JSON_REMOVE()删除元素

格式：JSON_REMOVE(json_doc, path[, path] …)
删除 JSON 文档指定位置的元素。

1. SET @j = '["a", ["b", "c"], "d"]';
2. -- 删除下标为1的元素，结果：["a", "d"]
3. SELECT JSON_REMOVE(@j, '$[1]');
5. set @j = '{ "a": 1, "b": [2, 3]}';
6. -- 删除a元素，结果：{"b": [2, 3]}
7. select json_remove(@j, '$.a');
9. set @j = '["a", ["b", "c"], "d", "e"]';
10. -- 删除多个元素，删除1下标之后，下标移动结果之后再删除下标2位置，结果：["a", "d"]
11. select json_remove(@j, '$[1]','$[2]');
12. -- 结果：["a", "e"]
13. select json_remove(@j, '$[1]','$[1]');

复制代码

9、JSON_REPLACE()更换元素

语法：JSON_REPLACE(json_doc, path, val[, path, val] …)
更换已经存在的值。不存在的值不做影响。

1. SET @j = '{ "a": 1, "b": [2, 3]}';
2. -- 对象替换，结果：{"a": 10, "b": [2, 3]}
3. SELECT JSON_REPLACE(@j, '$.a', 10, '$.c', '[true, false]');
5. -- 数组替换，结果：[1, "a", 4, "b"]
6. select json_replace('[1, "a", 3, "b"]', '$[2]', 4, '$[8]', 8);

复制代码

10、JSON_SET()插入并更换

格式：JSON_SET(json_doc, path, val[, path, val] …)
插入新值，并更换已经存在的值。
换言之，如果指定位置或指定 KEY 的值不存在，会执行插入操作，如果存在，则执行更新操作。
注意JSON_SET、JSON_INSERT、JSON_REPLACE的区别。

1. SET @j = '{ "a": 1, "b": [2, 3]}';
2. -- 结果：{"a": 10, "b": [2, 3], "c": "[true, false]"}
3. SELECT JSON_SET(@j, '$.a', 10, '$.c', '[true, false]');
4. -- 结果：{"a": 1, "b": [2, 3], "c": "[true, false]"}
5. SELECT JSON_INSERT(@j, '$.a', 10, '$.c', '[true, false]');
6. -- 结果：{"a": 10, "b": [2, 3]}
7. SELECT JSON_REPLACE(@j, '$.a', 10, '$.c', '[true, false]');

复制代码

七、其他JSON函数

1、JSON_TABLE()列转行

语法：JSON_TABLE(expr, path COLUMNS (column_list) [AS] alias)
MySQL 8.0支持如许一个函数，JSON_TABLE()，从 JSON 文档中提取数据并以表格的形式返回。
完备语法如下：

1. JSON_TABLE(
2.     expr,
3.     path COLUMNS (column_list)
4. )   [AS] alias
6. column_list:
7.     column[, column][, ...]
9. column:
10.     name FOR ORDINALITY
11.     |  name type PATH string path [on_empty] [on_error]
12.     |  name type EXISTS PATH string path
13.     |  NESTED [PATH] path COLUMNS (column_list)
15. on_empty:
16.     {NULL | DEFAULT json_string | ERROR} ON EMPTY
18. on_error:
19.     {NULL | DEFAULT json_string | ERROR} ON ERROR

复制代码

1. mysql> SELECT *
2.     ->   FROM
3.     ->     JSON_TABLE(
4.     ->       '[ {"c1": null} ]',
5.     ->       '$[*]' COLUMNS( c1 INT PATH '$.c1' ERROR ON ERROR )
6.     ->     ) as jt;
7. +------+
8. | c1   |
9. +------+
10. | NULL |
11. +------+
12. 1 row in set (0.00 sec)

复制代码

1. select *
2. from
3.    json_table(
4.      '[{"x":2, "y":"8", "z":9, "b":[1,2,3]}, {"x":"3", "y":"7"}, {"x":"4", "y":6, "z":10}]',
5.      "$[*]" columns(
6.        id for ordinality,
7.        xval varchar(100) path "$.x",
8.        yval varchar(100) path "$.y",
9.        z_exist int exists path "$.z",
10.        nested path '$.b[*]' columns (b INT PATH '$')
11.      )
12.    ) as t;
13. +------+------+------+---------+------+
14. | id   | xval | yval | z_exist | b    |
15. +------+------+------+---------+------+
16. |    1 | 2    | 8    |       1 |    1 |
17. |    1 | 2    | 8    |       1 |    2 |
18. |    1 | 2    | 8    |       1 |    3 |
19. |    2 | 3    | 7    |       0 | NULL |
20. |    3 | 4    | 6    |       1 | NULL |
21. +------+------+------+---------+------+
22. 5 rows in set (0.00 sec)

复制代码

1. mysql> SELECT *
2.     -> FROM
3.     ->   JSON_TABLE(
4.     ->     '[{"a":"3"},{"a":2},{"b":1},{"a":0},{"a":[1,2]}]',
5.     ->     "$[*]"
6.     ->     COLUMNS(
7.     ->       rowid FOR ORDINALITY,
8.     ->       ac VARCHAR(100) PATH "$.a" DEFAULT '111' ON EMPTY DEFAULT '999' ON ERROR,
9.     ->       aj JSON PATH "$.a" DEFAULT '{"x": 333}' ON EMPTY,
10.     ->       bx INT EXISTS PATH "$.b"
11.     ->     )
12.     ->   ) AS tt;
14. +-------+------+------------+------+
15. | rowid | ac   | aj         | bx   |
16. +-------+------+------------+------+
17. |     1 | 3    | "3"        |    0 |
18. |     2 | 2    | 2          |    0 |
19. |     3 | 111  | {"x": 333} |    1 |
20. |     4 | 0    | 0          |    0 |
21. |     5 | 999  | [1, 2]     |    0 |
22. +-------+------+------------+------+
23. 5 rows in set (0.00 sec)

复制代码

1. mysql> SELECT *
2.     -> FROM
3.     ->   JSON_TABLE(
4.     ->     '[{"x":2,"y":"8"},{"x":"3","y":"7"},{"x":"4","y":6}]',
5.     ->     "$[*]" COLUMNS(
6.     ->       xval VARCHAR(100) PATH "$.x",
7.     ->       yval VARCHAR(100) PATH "$.y"
8.     ->     )
9.     ->   ) AS  jt1;
11. +------+------+
12. | xval | yval |
13. +------+------+
14. | 2    | 8    |
15. | 3    | 7    |
16. | 4    | 6    |
17. +------+------+

复制代码

1. -- 指定path
2. mysql> SELECT *
3.     -> FROM
4.     ->   JSON_TABLE(
5.     ->     '[{"x":2,"y":"8"},{"x":"3","y":"7"},{"x":"4","y":6}]',
6.     ->     "$[1]" COLUMNS(
7.     ->       xval VARCHAR(100) PATH "$.x",
8.     ->       yval VARCHAR(100) PATH "$.y"
9.     ->     )
10.     ->   ) AS  jt1;
12. +------+------+
13. | xval | yval |
14. +------+------+
15. | 3    | 7    |
16. +------+------+

复制代码

1. mysql> SELECT *
2.     -> FROM
3.     ->   JSON_TABLE(
4.     ->     '[ {"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3}]',
5.     ->     '$[*]' COLUMNS(
6.     ->             a INT PATH '$.a',
7.     ->             NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$')
8.     ->            )
9.     ->    ) AS jt
10.     -> WHERE b IS NOT NULL;
12. +------+------+
13. | a    | b    |
14. +------+------+
15. |    1 |   11 |
16. |    1 |  111 |
17. |    2 |   22 |
18. |    2 |  222 |
19. +------+------+

复制代码

1. mysql> SELECT *
2.     -> FROM
3.     ->   JSON_TABLE(
4.     ->     '[{"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}]',
5.     ->     '$[*]' COLUMNS(
6.     ->         a INT PATH '$.a',
7.     ->         NESTED PATH '$.b[*]' COLUMNS (b1 INT PATH '$'),
8.     ->         NESTED PATH '$.b[*]' COLUMNS (b2 INT PATH '$')
9.     ->     )
10.     -> ) AS jt;
12. +------+------+------+
13. | a    | b1   | b2   |
14. +------+------+------+
15. |    1 |   11 | NULL |
16. |    1 |  111 | NULL |
17. |    1 | NULL |   11 |
18. |    1 | NULL |  111 |
19. |    2 |   22 | NULL |
20. |    2 |  222 | NULL |
21. |    2 | NULL |   22 |
22. |    2 | NULL |  222 |
23. +------+------+------+

复制代码

1. mysql> SELECT *
2.     -> FROM
3.     ->   JSON_TABLE(
4.     ->     '[{"a": "a_val",
5.     '>       "b": [{"c": "c_val", "l": [1,2]}]},
6.     '>     {"a": "a_val",
7.     '>       "b": [{"c": "c_val","l": [11]}, {"c": "c_val", "l": [22]}]}]',
8.     ->     '$[*]' COLUMNS(
9.     ->       top_ord FOR ORDINALITY,
10.     ->       apath VARCHAR(10) PATH '$.a',
11.     ->       NESTED PATH '$.b[*]' COLUMNS (
12.     ->         bpath VARCHAR(10) PATH '$.c',
13.     ->         ord FOR ORDINALITY,
14.     ->         NESTED PATH '$.l[*]' COLUMNS (lpath varchar(10) PATH '$')
15.     ->         )
16.     ->     )
17.     -> ) as jt;
19. +---------+---------+---------+------+-------+
20. | top_ord | apath   | bpath   | ord  | lpath |
21. +---------+---------+---------+------+-------+
22. |       1 |  a_val  |  c_val  |    1 | 1     |
23. |       1 |  a_val  |  c_val  |    1 | 2     |
24. |       2 |  a_val  |  c_val  |    1 | 11    |
25. |       2 |  a_val  |  c_val  |    2 | 22    |
26. +---------+---------+---------+------+-------+

复制代码

与表关联查询：

1. CREATE TABLE t1 (c1 INT, c2 CHAR(1), c3 JSON);
3. INSERT INTO t1 () VALUES
4.         ROW(1, 'z', JSON_OBJECT('a', 23, 'b', 27, 'c', 1)),
5.         ROW(1, 'y', JSON_OBJECT('a', 44, 'b', 22, 'c', 11)),
6.         ROW(2, 'x', JSON_OBJECT('b', 1, 'c', 15)),
7.         ROW(3, 'w', JSON_OBJECT('a', 5, 'b', 6, 'c', 7)),
8.         ROW(5, 'v', JSON_OBJECT('a', 123, 'c', 1111))
9. ;
12. SELECT c1, c2, JSON_EXTRACT(c3, '$.*')
13. FROM t1 AS m
14. JOIN
15. JSON_TABLE(
16.   m.c3,
17.   '$.*'
18.   COLUMNS(
19.     at VARCHAR(10) PATH '$.a' DEFAULT '1' ON EMPTY,
20.     bt VARCHAR(10) PATH '$.b' DEFAULT '2' ON EMPTY,
21.     ct VARCHAR(10) PATH '$.c' DEFAULT '3' ON EMPTY
22.   )
23. ) AS tt
24. ON m.c1 > tt.at;

复制代码

结果：

与表关联查询：

1. CREATE TABLE employees (
2.   id INT,
3.   details JSON
4. );
6. INSERT INTO employees VALUES (1, '{"name": "John Doe", "position": "Manager"}');
7. INSERT INTO employees VALUES (2, '{"name": "Jane Smith", "position": "Developer"}');
9. SELECT name, position
10. FROM employees,
11. JSON_TABLE(details, '$' COLUMNS(
12.   name VARCHAR(255) PATH '$.name',
13.   position VARCHAR(255) PATH '$.position'
14. )) AS emp;

复制代码

2、JSON_SCHEMA_VALID()验证json

语法：JSON_SCHEMA_VALID(schema,document)
判断 document （ JSON 文档 ）是否满足 schema （ JSON 对象）界说的规范要求。完备的规范要求可参考 Draft 4 of the JSON Schema specification （https://json-schema.org/specification-links.html#draft-4）。如果不满足，可通过 JSON_SCHEMA_VALIDATION_REPORT() 获取具体的原因。
它的要求如下：
1、document 必须是 JSON 对象。
2、JSON 对象必须的两个属性是 latitude 和 longitude。
3、latitude 和 longitude 必须是数值范例，且两者的大小分别在 -90 ～ 90，-180 ～ 180 之间。

1. mysql> SET @schema = '{
2.     '>  "id": "http://json-schema.org/geo",
3.     '> "$schema": "http://json-schema.org/draft-04/schema#",
4.     '> "description": "A geographical coordinate",
5.     '> "type": "object",
6.     '> "properties": {
7.     '>   "latitude": {
8.     '>     "type": "number",
9.     '>     "minimum": -90,
10.     '>     "maximum": 90
11.     '>   },
12.     '>   "longitude": {
13.     '>     "type": "number",
14.     '>     "minimum": -180,
15.     '>     "maximum": 180
16.     '>   }
17.     '> },
18.     '> "required": ["latitude", "longitude"]
19.     '>}';
20. Query OK, 0 rows affected (0.01 sec)
22. mysql> SET @document = '{
23.     '> "latitude": 63.444697,
24.     '> "longitude": 10.445118
25.     '>}';
26. Query OK, 0 rows affected (0.00 sec)
28. mysql> SELECT JSON_SCHEMA_VALID(@schema, @document);
29. +---------------------------------------+
30. | JSON_SCHEMA_VALID(@schema, @document) |
31. +---------------------------------------+
32. |                                     1 |
33. +---------------------------------------+
34. 1 row in set (0.00 sec)

复制代码

1. mysql> SET @document = '{}';
2. mysql> SET @schema = '{
3.     '> "id": "http://json-schema.org/geo",
4.     '> "$schema": "http://json-schema.org/draft-04/schema#",
5.     '> "description": "A geographical coordinate",
6.     '> "type": "object",
7.     '> "properties": {
8.     '>   "latitude": {
9.     '>     "type": "number",
10.     '>     "minimum": -90,
11.     '>     "maximum": 90
12.     '>   },
13.     '>   "longitude": {
14.     '>     "type": "number",
15.     '>     "minimum": -180,
16.     '>     "maximum": 180
17.     '>   }
18.     '> }
19.     '>}';
20. Query OK, 0 rows affected (0.00 sec)
23. mysql> SELECT JSON_SCHEMA_VALID(@schema, @document);
24. +---------------------------------------+
25. | JSON_SCHEMA_VALID(@schema, @document) |
26. +---------------------------------------+
27. |                                     1 |
28. +---------------------------------------+
29. 1 row in set (0.00 sec)

复制代码

1. -- 建表指定check
2. mysql> CREATE TABLE geo (
3.     ->     coordinate JSON,
4.     ->     CHECK(
5.     ->         JSON_SCHEMA_VALID(
6.     ->             '{
7.     '>                 "type":"object",
8.     '>                 "properties":{
9.     '>                       "latitude":{"type":"number", "minimum":-90, "maximum":90},
10.     '>                       "longitude":{"type":"number", "minimum":-180, "maximum":180}
11.     '>                 },
12.     '>                 "required": ["latitude", "longitude"]
13.     '>             }',
14.     ->             coordinate
15.     ->         )
16.     ->     )
17.     -> );
18. Query OK, 0 rows affected (0.45 sec)
20. mysql> SET @point1 = '{"latitude":59, "longitude":18}';
21. Query OK, 0 rows affected (0.00 sec)
23. mysql> SET @point2 = '{"latitude":91, "longitude":0}';
24. Query OK, 0 rows affected (0.00 sec)
26. mysql> SET @point3 = '{"longitude":120}';
27. Query OK, 0 rows affected (0.00 sec)
29. mysql> INSERT INTO geo VALUES(@point1);
30. Query OK, 1 row affected (0.05 sec)
32. mysql> INSERT INTO geo VALUES(@point2);
33. ERROR 3819 (HY000): Check constraint 'geo_chk_1' is violated.
35. -- 查看原因
36. mysql> SHOW WARNINGS\G
37. *************************** 1. row ***************************
38.   Level: Error
39.    Code: 3934
40. Message: The JSON document location '#/latitude' failed requirement 'maximum' at
41. JSON Schema location '#/properties/latitude'.
42. *************************** 2. row ***************************
43.   Level: Error
44.    Code: 3819
45. Message: Check constraint 'geo_chk_1' is violated.
46. 2 rows in set (0.00 sec)
49. mysql> INSERT INTO geo VALUES(@point3);
50. ERROR 3819 (HY000): Check constraint 'geo_chk_1' is violated.
51. mysql> SHOW WARNINGS\G
52. *************************** 1. row ***************************
53.   Level: Error
54.    Code: 3934
55. Message: The JSON document location '#' failed requirement 'required' at JSON
56. Schema location '#'.
57. *************************** 2. row ***************************
58.   Level: Error
59.    Code: 3819
60. Message: Check constraint 'geo_chk_1' is violated.
61. 2 rows in set (0.00 sec)

复制代码

3、JSON_SCHEMA_VALIDATION_REPORT()检察验证陈诉

语法：JSON_SCHEMA_VALIDATION_REPORT(schema,document)
该函数会以JSON文档的形式返回一个关于验证结果的陈诉。如果验证成功，返回{"valid": true}。如果JSON文档验证失败，该函数将返回一个JSON对象，该对象包含下面列出的属性:
valid：false
reason：失败原因
schema-location：校验失败的位置
document-location：失败位置
schema-failed-keyword：关键字或属性名

1. mysql> SET @schema = '{
2.     '>  "id": "http://json-schema.org/geo",
3.     '> "$schema": "http://json-schema.org/draft-04/schema#",
4.     '> "description": "A geographical coordinate",
5.     '> "type": "object",
6.     '> "properties": {
7.     '>   "latitude": {
8.     '>     "type": "number",
9.     '>     "minimum": -90,
10.     '>     "maximum": 90
11.     '>   },
12.     '>   "longitude": {
13.     '>     "type": "number",
14.     '>     "minimum": -180,
15.     '>     "maximum": 180
16.     '>   }
17.     '> },
18.     '> "required": ["latitude", "longitude"]
19.     '>}';
20. Query OK, 0 rows affected (0.01 sec)
22. mysql> SET @document = '{
23.     '> "latitude": 63.444697,
24.     '> "longitude": 10.445118
25.     '>}';
26. Query OK, 0 rows affected (0.00 sec)
28. mysql> SELECT JSON_SCHEMA_VALIDATION_REPORT(@schema, @document);
29. +---------------------------------------------------+
30. | JSON_SCHEMA_VALIDATION_REPORT(@schema, @document) |
31. +---------------------------------------------------+
32. | {"valid": true}                                   |
33. +---------------------------------------------------+
34. 1 row in set (0.00 sec)

复制代码

1. mysql> SET @document = '{
2.     '> "latitude": 63.444697,
3.     '> "longitude": 310.445118
4.     '> }';
6. mysql> SELECT JSON_PRETTY(JSON_SCHEMA_VALIDATION_REPORT(@schema, @document))\G
7. *************************** 1. row ***************************
8. JSON_PRETTY(JSON_SCHEMA_VALIDATION_REPORT(@schema, @document)): {
9.   "valid": false,
10.   "reason": "The JSON document location '#/longitude' failed requirement 'maximum' at JSON Schema location '#/properties/longitude'",
11.   "schema-location": "#/properties/longitude",
12.   "document-location": "#/longitude",
13.   "schema-failed-keyword": "maximum"
14. }
15. 1 row in set (0.00 sec)
17. mysql> SET @document = '{}';
18. Query OK, 0 rows affected (0.00 sec)
20. mysql> SELECT JSON_PRETTY(JSON_SCHEMA_VALIDATION_REPORT(@schema, @document))\G
21. *************************** 1. row ***************************
22. JSON_PRETTY(JSON_SCHEMA_VALIDATION_REPORT(@schema, @document)): {
23.   "valid": false,
24.   "reason": "The JSON document location '#' failed requirement 'required' at JSON Schema location '#'",
25.   "schema-location": "#",
26.   "document-location": "#",
27.   "schema-failed-keyword": "required"
28. }
29. 1 row in set (0.00 sec)
31. mysql> SET @schema = '{
32.     '> "id": "http://json-schema.org/geo",
33.     '> "$schema": "http://json-schema.org/draft-04/schema#",
34.     '> "description": "A geographical coordinate",
35.     '> "type": "object",
36.     '> "properties": {
37.     '>   "latitude": {
38.     '>     "type": "number",
39.     '>     "minimum": -90,
40.     '>     "maximum": 90
41.     '>   },
42.     '>   "longitude": {
43.     '>     "type": "number",
44.     '>     "minimum": -180,
45.     '>     "maximum": 180
46.     '>   }
47.     '> }
48.     '>}';
49. Query OK, 0 rows affected (0.00 sec)
51. mysql> SELECT JSON_SCHEMA_VALIDATION_REPORT(@schema, @document);
52. +---------------------------------------------------+
53. | JSON_SCHEMA_VALIDATION_REPORT(@schema, @document) |
54. +---------------------------------------------------+
55. | {"valid": true}                                   |
56. +---------------------------------------------------+
57. 1 row in set (0.00 sec)

复制代码

4、JSON_PRETTY()格式化输出

语法：JSON_PRETTY(json_val)
将 JSON 格式化输出。

1. SELECT JSON_PRETTY('123'); # scalar
2. +--------------------+
3. | JSON_PRETTY('123') |
4. +--------------------+
5. | 123                |
6. +--------------------+
8. SELECT JSON_PRETTY("[1,3,5]"); # array
9. +------------------------+
10. | JSON_PRETTY("[1,3,5]") |
11. +------------------------+
12. | [
13.   1,
14.   3,
15.   5
16. ]      |
17. +------------------------+
19. SELECT JSON_PRETTY('{"a":"10","b":"15","x":"25"}'); # object
20. +---------------------------------------------+
21. | JSON_PRETTY('{"a":"10","b":"15","x":"25"}') |
22. +---------------------------------------------+
23. | {
24.   "a": "10",
25.   "b": "15",
26.   "x": "25"
27. }   |
28. +---------------------------------------------+
30. SELECT JSON_PRETTY('["a",1,{"key1":
31.     "value1"},"5",     "77" ,
32.          {"key2":["value3","valueX",
33.     "valueY"]},"j", "2"   ]')\G  # nested arrays and objects
34. *************************** 1. row ***************************
35. JSON_PRETTY('["a",1,{"key1":
36.              "value1"},"5",     "77" ,
37.                 {"key2":["value3","valuex",
38.           "valuey"]},"j", "2"   ]'): [
39.   "a",
40.   1,
41.   {
42.     "key1": "value1"
43.   },
44.   "5",
45.   "77",
46.   {
47.     "key2": [
48.       "value3",
49.       "valuex",
50.       "valuey"
51.     ]
52.   },
53.   "j",
54.   "2"
55. ]

复制代码

5、JSON_STORAGE_FREE()计算空间

MySQL 8.0 新增的，与 Partial Updates 有关，用于计算 JSON 文档在进行部分更新后的剩余空间。

1. CREATE TABLE jtable (jcol JSON);
2. INSERT INTO jtable VALUES ('{"a": 10, "b": "wxyz", "c": "[true, false]"}');
3. -- 更新，结果：{"a": 10, "b": "wxyz", "c": 1}
4. UPDATE jtable SET jcol = JSON_SET(jcol, "$.a", 10, "$.b", "wxyz", "$.c", 1);
5. -- 结果：14
6. SELECT JSON_STORAGE_FREE(jcol) FROM jtable;
8. -- 连续的部分更新对这个空闲空间的影响是累积的，如下例所示，使用JSON_SET()来减少具有键b的值所占用的空间(并且不做任何其他更改):
9. UPDATE jtable SET jcol = JSON_SET(jcol, "$.a", 10, "$.b", "wx", "$.c", 1);
10. -- 结果：16
11. SELECT JSON_STORAGE_FREE(jcol) FROM jtable;
13. -- 不使用JSON_SET()、JSON_REPLACE()或JSON_REMOVE()更新列意味着优化器不能就地执行更新；在这种情况下，JSON_STORAGE_FREE()返回0，如下所示:
14. UPDATE jtable SET jcol = '{"a": 10, "b": 1}';
15. -- 结果：0
16. SELECT JSON_STORAGE_FREE(jcol) FROM jtable;
18. -- JSON文档的部分更新只能在列值上执行。对于存储JSON值的用户变量，该值总是被完全替换，即使使用JSON_SET()执行更新也是如此:
19. SET @j = '{"a": 10, "b": "wxyz", "c": "[true, false]"}';
20. SET @j = JSON_SET(@j, '$.a', 10, '$.b', 'wxyz', '$.c', '1');
21. SELECT @j, JSON_STORAGE_FREE(@j) AS Free; -- 结果：0
23. -- 对于JSON文本，该函数总是返回0:
24. SELECT JSON_STORAGE_FREE('{"a": 10, "b": "wxyz", "c": "1"}') AS Free; -- 结果：0

复制代码

6、JSON_STORAGE_SIZE()计算空间

语法：JSON_STORAGE_SIZE(json_val)
MySQL 5.7.22 引入的，用于计算 JSON 文档的空间利用环境。

1. CREATE TABLE jtable (jcol JSON);
2. INSERT INTO jtable VALUES ('{"a": 1000, "b": "wxyz", "c": "[1, 3, 5, 7]"}');
3. SELECT jcol, JSON_STORAGE_SIZE(jcol) AS Size, JSON_STORAGE_FREE(jcol) AS Free FROM jtable;
4. +-----------------------------------------------+------+------+
5. | jcol                                          | Size | Free |
6. +-----------------------------------------------+------+------+
7. | {"a": 1000, "b": "wxyz", "c": "[1, 3, 5, 7]"} |   47 |    0 |
8. +-----------------------------------------------+------+------+
9. 1 row in set (0.00 sec)
11. UPDATE jtable SET jcol = '{"a": 4.55, "b": "wxyz", "c": "[true, false]"}';
12. SELECT jcol, JSON_STORAGE_SIZE(jcol) AS Size, JSON_STORAGE_FREE(jcol) AS Free FROM jtable;
13. +------------------------------------------------+------+------+
14. | jcol                                           | Size | Free |
15. +------------------------------------------------+------+------+
16. | {"a": 4.55, "b": "wxyz", "c": "[true, false]"} |   56 |    0 |
17. +------------------------------------------------+------+------+
18. 1 row in set (0.00 sec)
20. -- json文本显示占用存储空间
21. SELECT JSON_STORAGE_SIZE('[100, "sakila", [1, 3, 5], 425.05]') AS A,
22.     JSON_STORAGE_SIZE('{"a": 1000, "b": "a", "c": "[1, 3, 5, 7]"}') AS B,
23.      JSON_STORAGE_SIZE('{"a": 1000, "b": "wxyz", "c": "[1, 3, 5, 7]"}') AS C,
24.      JSON_STORAGE_SIZE('[100, "json", [[10, 20, 30], 3, 5], 425.05]') AS D;
25. +----+----+----+----+
26. | A  | B  | C  | D  |
27. +----+----+----+----+
28. | 45 | 44 | 47 | 56 |
29. +----+----+----+----+
30. 1 row in set (0.00 sec)

复制代码

八、JSON字段创建索引

同 TEXT，BLOB 字段一样，JSON 字段不允许直接创建索引。
即使支持，现实意义也不大，因为我们一般是基于文档中的元素进行查询，很少会基于整个 JSON 文档。
对文档中的元素进行查询，就需要用到 MySQL 5.7 引入的虚拟列及函数索引。

1. # C2 即虚拟列
2. # index (c2) 对虚拟列添加索引。
3. create table t ( c1 json, c2 varchar(10) as (JSON_UNQUOTE(c1 -> "$.name")), index (c2) );
5. insert into t (c1) values  ('{"id": 1, "name": "a"}'), ('{"id": 2, "name": "b"}'), ('{"id": 3, "name": "c"}'), ('{"id": 4, "name": "d"}');
7. mysql> explain select * from t where c2 = 'a';
8. +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
9. | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
10. +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
11. |  1 | SIMPLE      | t     | NULL       | ref  | c2            | c2   | 43      | const |    1 |   100.00 | NULL  |
12. +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
13. 1 row in set, 1 warning (0.00 sec)
15. mysql> explain select * from t where c1->'$.name' = 'a';
16. +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
17. | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
18. +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
19. |  1 | SIMPLE      | t     | NULL       | ref  | c2            | c2   | 43      | const |    1 |   100.00 | NULL  |
20. +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
21. 1 row in set, 1 warning (0.00 sec)

复制代码

可以看到，无论是利用虚拟列，还是文档中的元素来查询，都可以利用上索引。
注意，在创建虚拟列时需指定 JSON_UNQUOTE，将 c1 -> “$.name” 的返回值转换为字符串。
参考文档

https://dev.mysql.com/doc/refman/8.0/en/json.html
https://blog.csdn.net/java_faep/article/details/125206014
https://zhuanlan.zhihu.com/p/514819634?utm_id=0
https://blog.csdn.net/sinat_20938225/article/details/129471550
GeoJSON：https://dev.mysql.com/doc/refman/8.0/en/spatial-geojson-functions.html
json方法：https://dev.mysql.com/doc/refman/8.0/en/json-functions.html
json索引：https://dev.mysql.com/doc/refman/8.0/en/create-table-secondary-indexes.html#json-column-indirect-index
json多值索引：https://dev.mysql.com/doc/refman/8.0/en/create-index.html#create-index-multi-valued

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。