参考文档：https://dev.mysql.com/doc/

一.创建数据库表

CREATE TABLE `json_tb` (
  `id` int NOT NULL AUTO_INCREMENT,
  `variation_data` json DEFAULT NULL COMMENT 'json数据',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=55777 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='json表';
1
2
3
4
5

二.JSON创建函数

1.JSON_ARRAY([val[, val] …])

返回包含这些值的 JSON 数组。

mysql> SELECT JSON_ARRAY(1, "abc", NULL, TRUE, CURTIME());
--> [1, "abc", null, true, "11:30:24.000000"]   
1
2

2.JSON_OBJECT([key, val[, key, val] …])

返回一个JSON对象

mysql> SELECT JSON_OBJECT('id', 87, 'name', 'carrot');
--> {"id": 87, "name": "carrot"}            
1
2

3.JSON_QUOTE(string)

返回一个特殊字符转义的字符串

mysql> SELECT JSON_QUOTE('null'), JSON_QUOTE('"null"');
+--------------------+----------------------+
| JSON_QUOTE('null') | JSON_QUOTE('"null"') |
+--------------------+----------------------+
| "null"             | "\"null\""           |
+--------------------+----------------------+
1
2
3
4
5
6

4.例子-插入一条JSON数据到表中

SET @j = JSON_ARRAY( JSON_OBJECT('entryName','养老保险', 'date',NOW()),
JSON_OBJECT('entryName','医疗保险', 'date','2022-06-22 16:51:14'))

SELECT @j;

INSERT INTO json_tb(`id`,`variation_data`) 
VALUES (NULL,@j)
--> id:55777

INSERT INTO json_tb(`id`,`variation_data`) 
VALUES (NULL,JSON_OBJECT('entryName', '"医疗保险"' ,'date',NOW()))
--> id:55780

SET @j = JSON_ARRAY( 
JSON_OBJECT('entryName','养老保险', 'date',NOW()),
JSON_OBJECT('entryName','医疗保险', 'date','2022-06-22 16:51:14'),
JSON_OBJECT('entryName','大病医疗', 'child',JSON_OBJECT('entryName','大病1','date',NOW()))
);
INSERT INTO json_tb(`id`,`variation_data`) 
VALUES (NULL,@j);
--> id:55781
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

三.JSON元素符

1.$

表示整个json对象

{"a fish": "shark", "a bird": "sparrow"}
1

有空格的key必须用引号括起来：

• $.“a fish” 计算结果为：shark

2.column->path

-> 运算符用作 JSON_EXTRACT() 函数的别名，不会去除转义符号

SELECT variation_data->'$[0].entryName' FROM json_tb WHERE id=55777;
--> "养老保险"                              	  

SELECT variation_data->'$.entryName' FROM json_tb WHERE id=55780;
--> "\"医疗保险\""                            	  
1
2
3
4
5

->>会去除转义符

SELECT variation_data->'$.entryName' FROM json_tb WHERE id=55780;
--> "医疗保险"                           	      
1
2

3.[ ]

[3, {"a": [5, 6], "b": 10}, [99, 100]]
1

表示的json数组：

• $[0] 结果为：3
• $[1].a 结果为：[5, 6]

**4.通配符 **

[*] 返回JSON数组中所有JSON对象

SELECT variation_data->'$[*]' FROM json_tb WHERE id=55781;
--> [{"date": "2022-06-30 00:00:40.000000", "entryName": "养老保险"},
 {"date": "2022-06-22 16:51:14", "entryName": "医疗保险"}, 
 {"child": {"date": "2022-06-30 00:00:40.000000", "entryName": "大病1"}, "entryName": "大病医疗"}]

SELECT variation_data->'$[*].entryName' FROM json_tb WHERE id=55781;
--> ["养老保险", "医疗保险", "大病医疗"]
1
2
3
4
5
6
7

.* 返回JSON对象中顶层所有key的value

SELECT variation_data->'$.*' FROM json_tb WHERE id=55780;
--> ["2022-06-30 00:12:04.000000", "\"医疗保险\""]

SELECT variation_data->'$[2].*' FROM json_tb WHERE id=55781;
--> [{"date": "2022-06-30 00:00:40.000000", "entryName": "大病1"}, "大病医疗"]
1
2
3
4
5

prefix**suffix 计算结果为以命名前缀开头并以命名后缀结尾的所有路径。前缀是非必须的，后缀是必须的。

SELECT JSON_EXTRACT('{"a": {"b": 1}, "c": {"b": 2}}', '$**.b');
--> [1, 2]

SELECT JSON_EXTRACT(' {"a": {"b": 1}, "c": {"b": 2}, "d":{"b":{"b":3}}} ', '$**.b');
--> [1, 2, {"b": 3}, 3]

SELECT JSON_EXTRACT(' {"a": {"b": 1}, "c": {"b": 2}, "d":{"b":{"b":3}}} ', '$.a**.b');
--> [1]
1
2
3
4
5
6
7
8

5.[M to N]指定数组值的子集或范围，从位置 M 的值开始，到位置 N 的值结束。

SELECT JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[1 to 3]');
--> [2, 3, 4]

SELECT JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[last-3 to last-1]');
--> [2, 3, 4]
1
2
3
4
5

四.JSON搜索函数

1.JSON_CONTAINS(target, candidate[, path])

通过0和1表达，搜索的json是否在目标json中

mysql> SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}';

mysql> SET @j2 = '1';
mysql> SELECT JSON_CONTAINS(@j, @j2, '$.a');
--> 1

mysql> SET @j2 = '{"d": 4}';
mysql> SELECT JSON_CONTAINS(@j, @j2, '$.c');
--> 1
1
2
3
4
5
6
7
8
9

2.JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] …)

‘one’：如果文档中至少存在一个路径，则为 1，否则为 0。

‘all’：如果文档中存在所有路径，则为 1，否则为 0。

mysql> SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}';

mysql> SELECT JSON_CONTAINS_PATH(@j, 'one', '$.a', '$.e');
--> 1

mysql> SELECT JSON_CONTAINS_PATH(@j, 'all', '$.a', '$.e');
--> 0

mysql> SELECT JSON_CONTAINS_PATH(@j, 'one', '$.c.d');
--> 1

mysql> SELECT JSON_CONTAINS_PATH(@j, 'one', '$.a.d');
-->0
1
2
3
4
5
6
7
8
9
10
11
12
13

3.JSON_EXTRACT(json_doc, path[, path] …)

返回所有有效路径中的值，多个返回值按顺序包装为数组

mysql> SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]');
--> 20 
                                        
mysql> SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]', '$[0]');
--> [20, 10] 
                                          
mysql> SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[2][*]');
--> [30, 40]                                      

SELECT JSON_EXTRACT(variation_data,'$[0].entryName') FROM json_tb WHERE id=55777;
--> "养老保险" 	  
                              
SELECT JSON_EXTRACT(variation_data,'$.entryName') FROM json_tb WHERE id=55780;
--> "\"医疗保险\"" 	                            
1
2
3
4
5
6
7
8
9
10
11
12
13
14

4.JSON_UNQUOTE ( json_val )

会去除转义符

SELECT JSON_UNQUOTE("你好啊");
--> 你好啊                           	          

SELECT JSON_UNQUOTE(variation_data->'$.entryName') FROM json_tb WHERE id=55780;
--> "医疗保险"                           	      
1
2
3
4
5

5.JSON_KEYS(json_doc[, path])

返回json对象中对最顶层的key作为数组返回，如果设置了path路径，则返回路径中的顶级key

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

6.JSON_OVERLAPS(json_doc1, json_doc2)

比较两个json，如果有共同的键值对或数组元素，则返回1，否则0

mysql> SELECT JSON_OVERLAPS("[1,3,5,7]", "[2,5,7]");
--> 1

mysql> SELECT JSON_OVERLAPS("[1,3,5,7]", "[2,6,8]");
--> 0

mysql> SELECT JSON_OVERLAPS('[4,5,6,7]', '6');
--> 1

mysql> SELECT JSON_OVERLAPS('[4,5,"6",7]', '6');
--> 0

mysql> SELECT JSON_OVERLAPS('{"a": 1, "b": {"c": 30}}'  ,  '{"a": 1, "b": {"c": 28}}');
--> 1

mysql> SELECT JSON_OVERLAPS('{"a1": 1, "b1": {"c": 30}}', '{"a2": 1, "b2": {"c": 30}}');
--> 0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

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

返回 JSON 文档中给定字符串的路径。

‘one’：搜索在第一个匹配后终止，并返回一个路径字符串。未定义首先考虑哪个匹配项。

‘all’：搜索将返回所有匹配的路径字符串，以便不包含重复的路径。如果有多个字符串，它们将自动包装为数组。数组元素的顺序未定义。

mysql> SET @j = '["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]';

mysql> SELECT JSON_SEARCH(@j, 'one', 'abc');
--> "$[0]"

mysql> SELECT JSON_SEARCH(@j, 'all', 'abc');
--> ["$[0]", "$[2].x"]

mysql> SELECT JSON_SEARCH(@j, 'all', '10');
--> "$[1][0].k"

mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$');
--> "$[1][0].k"

mysql> SELECT JSON_SEARCH(@j, 'all', '%a%');
--> ["$[0]", "$[2].x"]  


mysql> SELECT JSON_SEARCH(@j, 'all', '%cd%');
--> "$[3].y"
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

8.JSON_VALUE(json_doc, path)

可以将json中的值强转为一个类型

mysql> SELECT JSON_VALUE('{"item": "shoes", "price": "49.95"}', '$.price' RETURNING DECIMAL(4,2)) AS price;
--> 49.95
1
2

9.value MEMBER OF(json_array)

如果value是json数组中的元素，返回1，否则0，区分字符串与数字

SELECT 17 MEMBER OF('[23, "abc", 17, "ab", 10]');
--> 1

SELECT 2 MEMBER OF('[23, "abc", 17, "ab", 10]');
--> 0
1
2
3
4
5