参考文档：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表';
二.JSON创建函数
1.JSON_ARRAY([val[, val] …])
返回包含这些值的 JSON 数组。
mysql> SELECT JSON_ARRAY(1, "abc", NULL, TRUE, CURTIME());
--> [1, "abc", null, true, "11:30:24.000000"]
2.JSON_OBJECT([key, val[, key, val] …])
返回一个JSON对象
mysql> SELECT JSON_OBJECT('id', 87, 'name', 'carrot');
--> {"id": 87, "name": "carrot"}
3.JSON_QUOTE(string)
返回一个特殊字符转义的字符串
mysql> SELECT JSON_QUOTE('null'), JSON_QUOTE('"null"');
+--------------------+----------------------+
| JSON_QUOTE('null') | JSON_QUOTE('"null"') |
+--------------------+----------------------+
| "null" | "\"null\"" |
+--------------------+----------------------+
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
三.JSON元素符
1.$
表示整个json对象
{"a fish": "shark", "a bird": "sparrow"}
有空格的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;
--> "\"医疗保险\""
->>会去除转义符
SELECT variation_data->'$.entryName' FROM json_tb WHERE id=55780;
--> "医疗保险"
3.[ ]
[3, {"a": [5, 6], "b": 10}, [99, 100]]
表示的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;
--> ["养老保险", "医疗保险", "大病医疗"]
.* 返回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"}, "大病医疗"]
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]
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]
四.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
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
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;
--> "\"医疗保险\""
4.JSON_UNQUOTE ( json_val )
会去除转义符
SELECT JSON_UNQUOTE("你好啊");
--> 你好啊
SELECT JSON_UNQUOTE(variation_data->'$.entryName') FROM json_tb WHERE id=55780;
--> "医疗保险"
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"]
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
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"
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
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
10.FIND_IN_SET(str,strlist)
返回参数在目标list中的位置，如果搜不到就返回0
这个函数并不是json函数，但是可以用于搜索JSON_ARRAY，但是要去除其他符合
SELECT
FIND_IN_SET(1, REPLACE(REPLACE(REPLACE(variation_data,' ',''),'[','' ),']',''))
FROM json_tb
WHERE id=55785;
--> 1
SELECT
FIND_IN_SET(1, REGEXP_REPLACE(variation_data, '[\\[\\]]', ''))
FROM json_tb
WHERE id=55785;
--> 1
五.jSON修改函数
1.JSON_ARRAY_APPEND(json_doc, path, val[, path, val] …)
将参数追加到指定位置的末尾
mysql> SET @j = '["a", ["b", "c"], "d"]';
mysql> SELECT JSON_ARRAY_APPEND(@j, '$[1]', 1);
--> ["a", ["b", "c", 1], "d"]
mysql> SELECT JSON_ARRAY_APPEND(@j, '$[1][0]', 3);
--> ["a", [["b", 3], "c"], "d"]
mysql> SET @j = '{"a": 1, "b": [2, 3], "c": 4}';
mysql> SELECT JSON_ARRAY_APPEND(@j, '$.b', 'x');
--> {"a": 1, "b": [2, 3, "x"], "c": 4}
mysql> SET @j = '{"a": 1}';
mysql> SELECT JSON_ARRAY_APPEND(@j, '$', 'z');
--> [{"a": 1}, "z"]
2.JSON_ARRAY_INSERT(json_doc, path, val[, path, val] …)
插入元素到指定位置
mysql> SET @j = '["a", {"b": [1, 2]}, [3, 4]]';
mysql> SELECT JSON_ARRAY_INSERT(@j, '$[1]', 'x');
--> ["a", "x", {"b": [1, 2]}, [3, 4]]
mysql> SELECT JSON_ARRAY_INSERT(@j, '$[0]', 'x', '$[2][1]', 'y');
--> ["x", "a", {"b": [1, 2]}, [3, 4]]
3.JSON_INSERT(json_doc, path, val[, path, val] …)
将元素插入到指定位置，如果该位置元素不存在，则插入，否则不插入
mysql> SET @j = '{ "a": 1, "b": [2, 3]}';
mysql> SELECT JSON_INSERT(@j, '$.a', 10, '$.c', '[true, false]');
--> {"a": 1, "b": [2, 3], "c": "[true, false]"}
4.JSON_MERGE(json_doc, json_doc[, json_doc] …)
合并两个或多个JSON
mysql> SELECT JSON_MERGE('[1, 2]', '[true, false]');
--> [1, 2, true, false]
mysql> SELECT JSON_MERGE('{"bool": 1}', '[true, false]');
--> [{"bool": 1}, true, false]
5.JSON_MERGE_PATCH(json_doc, json_doc[, json_doc] …)
合并JSON对象：
- 如果有一个参数不是JSON对象，结果为最后一个参数。
- 如果都是JSON对象，前后JSON对象相同的键，则后一个JSON对象中键的值覆盖前一个
mysql> SELECT JSON_MERGE_PATCH('[1, 2]', '[true, false]');
--> [true, false]
mysql> SELECT JSON_MERGE_PATCH('{"name": "x"}', '{"id": 47}');
--> {"id": 47, "name": "x"}
SELECT JSON_MERGE_PATCH('{ "a": 1, "b":{"d":2}}','{ "a": 2, "b":{"d":3}}');
--> {"a": 2, "b": {"d": 3}}
6.JSON_MERGE_PRESERVE(json_doc, json_doc[, json_doc] …)
合并多个JSON，对象与对象间合成一个对象，对象与数组间合成一个数组，数组与数组间合成一个数组。如果存在相同的键，将两个值合成一个数组
mysql> SELECT JSON_MERGE_PRESERVE('[1, 2]', '[true, false]');
--> [1, 2, true, false]
mysql> SELECT JSON_MERGE_PRESERVE('{"name": "x"}', '{"id": 47}');
--> {"id": 47, "name": "x"}
mysql> SELECT JSON_MERGE_PRESERVE('[1, 2]', '{"id": 47}');
--> [1, 2, {"id": 47}]
mysql> SELECT JSON_MERGE_PRESERVE('{ "a": 1, "b": 2 }', '{ "a": 3, "c": 4 }');
--> {"a": [1, 3], "b": 2, "c": 4}
7.JSON_REMOVE(json_doc, path[, path] …)
删除指定位置的元素
mysql> SET @j = '["a", ["b", "c"], "d"]';
mysql> SELECT JSON_REMOVE(@j, '$[1]');
--> ["a", "d"]
8.JSON_REPLACE(json_doc, path, val[, path, val] …)
替换指定位置的元素
mysql> SET @j = '{ "a": 1, "b": [2, 3]}';
mysql> SELECT JSON_REPLACE(@j, '$.a', 10, '$.c', '[true, false]');
--> {"a": 10, "b": [2, 3]}
9.JSON_SET(json_doc, path, val[, path, val] …)
给JSON指定位置设置参数，如果该路径存在则更新，否则插入
mysql> SET @j = '{ "a": 1, "b": [2, 3]}';
mysql> SELECT JSON_SET(@j, '$.a', 10, '$.c', '[true, false]');
--> {"a": 10, "b": [2, 3], "c": "[true, false]"}
六. JSON转表函数
1.JSON_TABLE(expr, path COLUMNS (column_list) [AS] alias)
完整语法：
JSON_TABLE(
expr,
path COLUMNS (column_list)
) [AS] alias
column_list:
column[, column][, ...]
column:
name FOR ORDINALITY
| name type PATH string path [on_empty] [on_error]
| name type EXISTS PATH string path
| NESTED [PATH] path COLUMNS (column_list)
on_empty:
{NULL | DEFAULT json_string | ERROR} ON EMPTY
on_error:
{NULL | DEFAULT json_string | ERROR} ON ERROR
JSON元素转型失败可以设置默认值，如DEFAULT ‘0’ ON ERROR
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 '0' ON EMPTY DEFAULT '0' ON ERROR,
aj JSON PATH "$.a" DEFAULT '0' ON EMPTY,
bx INT EXISTS PATH "$.b"
)
) AS tt;
+-------+------+------------+------+
| rowid | ac | aj | bx |
+-------+------+------------+------+
| 1 | 3 | "3" | 0 |
| 2 | 2 | 2 | 0 |
| 3 | 0 | 0 | 1 |
| 4 | 0 | 0 | 0 |
| 5 | 0 | [1, 2] | 0 |
+-------+------+------------+------+
SELECT *
FROM
JSON_TABLE(
'[{"x":2,"y":"8"},{"x":"3","y":"7"},{"x":"4","y":6}]',
"$[*]" COLUMNS(
xval VARCHAR(100) PATH "$.x",
yval VARCHAR(100) PATH "$.y"
)
) AS jt1;
+------+------+
| xval | yval |
+------+------+
| 2 | 8 |
| 3 | 7 |
| 4 | 6 |
+------+------+
NESTED PATH( is optional)：为其所属子句中的每个匹配项生成一组记录。如果没有匹配项，则为嵌套路径的所有列都设置 。这将实现最顶层子句和之间的外部连接。
SELECT *
FROM
JSON_TABLE(
'[ {"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3}]',
'$[*]' COLUMNS(
a INT PATH '$.a',
NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$')
)
) AS jt
WHERE b IS NOT NULL;
+------+------+
| a | b |
+------+------+
| 1 | 11 |
| 1 | 111 |
| 2 | 22 |
| 2 | 222 |
+------+------+
同级嵌套路径生成的记录与该列位置相关联
SELECT *
FROM
JSON_TABLE(
'[ {"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]} ]',
'$[*]' 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 | NULL | 22 |
| 2 | NULL | 222 |
+------+------+------+
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 |
+---------+---------+---------+------+-------+
实战案例：
[{"accountId": 112, "entryCode": "1", "entryName": "养老保险", "accountName": "湖南（永州）", "cardinality": 3604, "takeEffectDate": 1654012800000, "variationNature": "1"},
{"accountId": 112, "entryCode": "3", "entryName": "失业保险", "accountName": "湖南（永州）", "cardinality": 3604, "takeEffectDate": 1654012800000, "variationNature": "1"},
{"accountId": 112, "entryCode": "4", "entryName": "工伤保险", "accountName": "湖南（永州）", "cardinality": 2500, "takeEffectDate": 1654012800000, "variationNature": "1"},
{"accountId": 112, "entryCode": "2", "entryName": "医疗保险", "accountName": "湖南（永州）", "cardinality": 3276, "takeEffectDate": 1654012800000, "variationNature": "1"},
{"accountId": 112, "entryCode": "7", "entryName": "生育保险", "accountName": "湖南（永州）", "cardinality": 3276, "takeEffectDate": 1654012800000, "variationNature": "1"}]
SELECT
*
FROM
employee_variation ev
WHERE
ev.variation_data is not null and
(SELECT
COUNT(ac)
FROM
JSON_TABLE (ev.variation_data, "$[*]" COLUMNS ( ac VARCHAR ( 100 ) PATH "$.takeEffectDate" DEFAULT '0' ON EMPTY DEFAULT '0' ON ERROR ) ) AS tt
WHERE ac > 1654012800000
) >0
扫一扫
1435
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
