mysql5.7的JSON相关函数说明来自mysql

狒狒 2020-12-5 技术札记 600 次

分类	函数	描述
创建json	json_array	创建json数组
	json_object	创建json对象
	json_quote	将json转成json字符串类型
查询json	json_contains	判断是否包含某个json值
	json_contains_path	判断某个路径下是否包json值
	json_extract	提取json值
	column->path	json_extract的简洁写法，MySQL 5.7.9开始支持
	column->>path	json_unquote(column -> path)的简洁写法
	json_keys	提取json中的键值为json数组
	json_search	按给定字符串关键字搜索json，返回匹配的路径
修改json	json_append	废弃，MySQL 5.7.9开始改名为json_array_append
	json_array_append	末尾添加数组元素，如果原有值是数值或json对象，则转成数组后，再添加元素
	json_array_insert	插入数组元素
	json_insert	插入值（插入新值，但不替换已经存在的旧值）
	json_merge	合并json数组或对象
	json_remove	删除json数据
	json_replace	替换值（只替换已经存在的旧值）
	json_set	设置值（替换旧值，并插入不存在的新值）
	json_unquote	去除json字符串的引号，将值转成string类型
返回json属性	json_depth	返回json文档的最大深度
	json_length	返回json文档的长度
	json_type	返回json值得类型
	json_valid	判断是否为合法json文档

JSON_CONTAINS(target, candidate[, path])

mysql> SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}'; mysql> SET @j2 = '1'; mysql> SELECT JSON_CONTAINS(@j, @j2, '$.a');
JSON_CONTAINS(@j, @j2, '$.a') 1

mysql> SELECT JSON_CONTAINS(@j, @j2, '$.b');
JSON_CONTAINS(@j, @j2, '$.b') 0

mysql> SET @j2 = '{"d": 4}';
mysql> SELECT JSON_CONTAINS(@j, @j2, '$.a');
JSON_CONTAINS(@j, @j2, '$.a') 0

mysql> SELECT JSON_CONTAINS(@j, @j2, '$.c');
JSON_CONTAINS(@j, @j2, '$.c') 1
JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...)

mysql> SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}'; mysql> SELECT JSON_CONTAINS_PATH(@j, 'one', '$.a', '$.e');
JSON_CONTAINS_PATH(@j, 'one', '$.a', '$.e') 1

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

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

mysql> SELECT JSON_CONTAINS_PATH(@j, 'one', '$.a.d');
JSON_CONTAINS_PATH(@j, 'one', '$.a.d') 0
JSON_EXTRACT(json_doc, path[, path] ...)

mysql> SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]');
JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]') | 20 |

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

JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]', '$[0]') [20, 10]

mysql> SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[2][*]');
JSON_EXTRACT('[10, 20, [30, 40]]', '$[2][*]') [30, 40] |
column->path

mysql> SELECT c, JSON_EXTRACT(c, "$.id"), g > FROM jemp > WHERE JSON_EXTRACT(c, "$.id") > 1 > ORDER BY JSON_EXTRACT(c, "$.name");
c | c->"$.id" | g |
{"id": "3", "name": "Barney"} | "3" | 3 |
{"id": "4", "name": "Betty"} | "4" | 4 |
{"id": "2", "name": "Wilma"} | "2" | 2 |

mysql> SELECT c, c->"$.id", g > FROM jemp > WHERE c->"$.id" > 1 > ORDER BY c->"$.name";
| c | c->"$.id" | g |
{"id": "3", "name": "Barney"} | "3" | 3 |
{"id": "4", "name": "Betty"} | "4" | 4 |
{"id": "2", "name": "Wilma"} | "2" | 2 |
mysql> SELECT JSON_EXTRACT(a, "$[4][1]") FROM tj10;
JSON_EXTRACT(a, "$[4][1]") NULL | | 44
column->>path

JSON_UNQUOTE( JSON_EXTRACT(column, path) )

JSON_UNQUOTE(column -> path)

column->>path

Message: /* select#1 */ select
json_unquote(json_extract(`jtest`.`jemp`.`c`,'$.name')) AS `name` from
`jtest`.`jemp` where (`jtest`.`jemp`.`g` > 2) 1 row in set (0.00 sec)
JSON_KEYS(json_doc[, path])

mysql> SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}');
JSON_KEYS('{"a": 1, "b": {"c": 30}}') ["a", "b"] |

mysql> SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}', '$.b');
JSON_KEYS('{"a": 1, "b": {"c": 30}}', '$.b') ["c"]
JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])

mysql> SET @j = '["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]';
mysql> SELECT JSON_SEARCH(@j, 'one', 'abc');
JSON_SEARCH(@j, 'one', 'abc') "$[0]"

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

mysql> SELECT JSON_SEARCH(@j, 'all', 'ghi');
JSON_SEARCH(@j, 'all', 'ghi')

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

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

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

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

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

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

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

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

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

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

mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', NULL, '$[0]');
JSON_SEARCH(@j, 'all', '%b%', NULL, '$[0]') "$[0]"

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

mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', NULL, '$[1]');
JSON_SEARCH(@j, 'all', '%b%', NULL, '$[1]') NULL

mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', '', '$[1]');
JSON_SEARCH(@j, 'all', '%b%', '', '$[1]')NULL

mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', '', '$[3]');
JSON_SEARCH(@j, 'all', '%b%', '', '$[3]') "$[3].y"