1.JSON_ARRAY_APPEND(json_doc, path, val[, path, val] ...)
在指定的数组的最后附加一个值,然后返回修改后的json_doc。JSON_APPEND函数已被弃用,功能和JSON_ARRAY_APPEND一样,已在8.0版本中删除。
mysql> SET @j = '["a", ["b", "c"], "d"]'; mysql> SELECT JSON_ARRAY_APPEND(@j, '$[1]', 1); +----------------------------------+ | JSON_ARRAY_APPEND(@j, '$[1]', 1) | +----------------------------------+ | ["a", ["b", "c", 1], "d"] | +----------------------------------+ mysql> SELECT JSON_ARRAY_APPEND(@j, '$[0]', 2); +----------------------------------+ | JSON_ARRAY_APPEND(@j, '$[0]', 2) | +----------------------------------+ | [["a", 2], ["b", "c"], "d"] | +----------------------------------+ mysql> SELECT JSON_ARRAY_APPEND(@j, '$[1][0]', 3); +-------------------------------------+ | 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'); +------------------------------------+ | JSON_ARRAY_APPEND(@j, '$.b', 'x') | +------------------------------------+ | {"a": 1, "b": [2, 3, "x"], "c": 4} | +------------------------------------+ mysql> SELECT JSON_ARRAY_APPEND(@j, '$.c', 'y'); +--------------------------------------+ | JSON_ARRAY_APPEND(@j, '$.c', 'y') | +--------------------------------------+ | {"a": 1, "b": [2, 3], "c": [4, "y"]} | +--------------------------------------+ mysql> SET @j = '{"a": 1}'; mysql> SELECT JSON_ARRAY_APPEND(@j, '$', 'z'); +---------------------------------+ | JSON_ARRAY_APPEND(@j, '$', 'z') | +---------------------------------+ | [{"a": 1}, "z"] | +---------------------------------+
2.JSON_ARRAY_INSERT(json_doc, path, val[, path, val] ...)
向指定的位置(path)插入值(val),返回更新后的json_doc。
mysql> SET @j = '["a", {"b": [1, 2]}, [3, 4]]'; mysql> SELECT JSON_ARRAY_INSERT(@j, '$[1]', 'x'); +------------------------------------+ | JSON_ARRAY_INSERT(@j, '$[1]', 'x') | +------------------------------------+ | ["a", "x", {"b": [1, 2]}, [3, 4]] | +------------------------------------+ mysql> SELECT JSON_ARRAY_INSERT(@j, '$[100]', 'x'); +--------------------------------------+ | JSON_ARRAY_INSERT(@j, '$[100]', 'x') | +--------------------------------------+ | ["a", {"b": [1, 2]}, [3, 4], "x"] | +--------------------------------------+ mysql> SELECT JSON_ARRAY_INSERT(@j, '$[1].b[0]', 'x'); +-----------------------------------------+ | JSON_ARRAY_INSERT(@j, '$[1].b[0]', 'x') | +-----------------------------------------+ | ["a", {"b": ["x", 1, 2]}, [3, 4]] | +-----------------------------------------+ mysql> SELECT JSON_ARRAY_INSERT(@j, '$[2][1]', 'y'); +---------------------------------------+ | JSON_ARRAY_INSERT(@j, '$[2][1]', 'y') | +---------------------------------------+ | ["a", {"b": [1, 2]}, [3, "y", 4]] | +---------------------------------------+ mysql> SELECT JSON_ARRAY_INSERT(@j, '$[0]', 'x', '$[2][1]', 'y'); +----------------------------------------------------+ | JSON_ARRAY_INSERT(@j, '$[0]', 'x', '$[2][1]', 'y') | +----------------------------------------------------+ | ["x", "a", {"b": [1, 2]}, [3, 4]] | +----------------------------------------------------+
在最后一个sql语句中,因为执行完插入'$[0]', 'x'的操作后'$[2][1]'的路径并不存在了,所以,第二条插入并没有起到什么效果。
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]'); +----------------------------------------------------+ | JSON_INSERT(@j, '$.a', 10, '$.c', '[true, false]') | +----------------------------------------------------+ | {"a": 1, "b": [2, 3], "c": "[true, false]"} | +----------------------------------------------------+
可以看到上面的结果中,c的值是个字符串而不是JSON数组,因为它被引用了。如果要变成JSON数组,要做如下修改
mysql> SELECT JSON_INSERT(@j, '$.a', 10, '$.c', CAST('[true, false]' AS JSON)); +------------------------------------------------------------------+ | JSON_INSERT(@j, '$.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_PATCH(json_doc, json_doc[, json_doc] ...)
前面已经介绍过:https://www.hhfate.cn/t/617
5.JSON_MERGE_PRESERVE(json_doc, json_doc[, json_doc] ...)
该函数相较于JSON_MERGE_PATCH,不会后值不会覆盖前值,而是把相同key的值组成一个JSON数组。
mysql> SELECT JSON_MERGE_PRESERVE('[1, 2]', '[true, false]'); +------------------------------------------------+ | JSON_MERGE_PRESERVE('[1, 2]', '[true, false]') | +------------------------------------------------+ | [1, 2, true, false] | +------------------------------------------------+ mysql> SELECT JSON_MERGE_PRESERVE('{"name": "x"}', '{"id": 47}'); +----------------------------------------------------+ | JSON_MERGE_PRESERVE('{"name": "x"}', '{"id": 47}') | +----------------------------------------------------+ | {"id": 47, "name": "x"} | +----------------------------------------------------+ mysql> SELECT JSON_MERGE_PRESERVE('1', 'true'); +----------------------------------+ | JSON_MERGE_PRESERVE('1', 'true') | +----------------------------------+ | [1, true] | +----------------------------------+ mysql> SELECT JSON_MERGE_PRESERVE('[1, 2]', '{"id": 47}'); +---------------------------------------------+ | JSON_MERGE_PRESERVE('[1, 2]', '{"id": 47}') | +---------------------------------------------+ | [1, 2, {"id": 47}] | +---------------------------------------------+ mysql> SELECT JSON_MERGE_PRESERVE('{ "a": 1, "b": 2 }', > '{ "a": 3, "c": 4 }'); +--------------------------------------------------------------+ | JSON_MERGE_PRESERVE('{ "a": 1, "b": 2 }','{ "a": 3, "c":4 }') | +--------------------------------------------------------------+ | {"a": [1, 3], "b": 2, "c": 4} | +--------------------------------------------------------------+ 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} | +----------------------------------------------------------------------------------+
6.JSON_REMOVE(json_doc, path[, path] ...)
删除节点,嗯,这个很容易理解。如果path不存在,则不作操作。
mysql> SET @j = '["a", ["b", "c"], "d"]'; mysql> SELECT JSON_REMOVE(@j, '$[1]'); +-------------------------+ | JSON_REMOVE(@j, '$[1]') | +-------------------------+ | ["a", "d"] | +-------------------------+
7.JSON_REPLACE(json_doc, path, val[, path, val] ...)
替换对应路径的值,只对存在的key有效。
mysql> SET @j = '{ "a": 1, "b": [2, 3]}'; mysql> SELECT JSON_REPLACE(@j, '$.a', 10, '$.c', '[true, false]'); +-----------------------------------------------------+ | JSON_REPLACE(@j, '$.a', 10, '$.c', '[true, false]') | +-----------------------------------------------------+ | {"a": 10, "b": [2, 3]} | +-----------------------------------------------------+
8.JSON_SET(json_doc, path, val[, path, val] ...)
如果存在,则覆盖值;如果不存在,则新增值。
下面是 JSON_SET(), JSON_INSERT(), JSON_REPLACE()三个函数的比较:
JSON_SET() 替换现有值,添加新值
JSON_INSERT() 新加新值,不会替换现有值
JSON_REPLACE() 只替换现有值
mysql> SET @j = '{ "a": 1, "b": [2, 3]}'; mysql> SELECT JSON_SET(@j, '$.a', 10, '$.c', '[true, false]'); +-------------------------------------------------+ | JSON_SET(@j, '$.a', 10, '$.c', '[true, false]') | +-------------------------------------------------+ | {"a": 10, "b": [2, 3], "c": "[true, false]"} | +-------------------------------------------------+ mysql> SELECT JSON_INSERT(@j, '$.a', 10, '$.c', '[true, false]'); +----------------------------------------------------+ | JSON_INSERT(@j, '$.a', 10, '$.c', '[true, false]') | +----------------------------------------------------+ | {"a": 1, "b": [2, 3], "c": "[true, false]"} | +----------------------------------------------------+ mysql> SELECT JSON_REPLACE(@j, '$.a', 10, '$.c', '[true, false]'); +-----------------------------------------------------+ | JSON_REPLACE(@j, '$.a', 10, '$.c', '[true, false]') | +-----------------------------------------------------+ | {"a": 10, "b": [2, 3]} | +-----------------------------------------------------+
9.JSON_UNQUOTE(json_val)
很简单,去掉引用(引号),返回一个utf8mb4格式的字符串。
mysql> SET @j = '"abc"'; mysql> SELECT @j, JSON_UNQUOTE(@j); +-------+------------------+ | @j | JSON_UNQUOTE(@j) | +-------+------------------+ | "abc" | abc | +-------+------------------+ mysql> SET @j = '[1, 2, 3]'; mysql> SELECT @j, JSON_UNQUOTE(@j); +-----------+------------------+ | @j | JSON_UNQUOTE(@j) | +-----------+------------------+ | [1, 2, 3] | [1, 2, 3] | +-----------+------------------+
一些转义符的处理:
转义符 | 反引用后的字符 |
\" | 双引号 |
\b | 退格符 |
\f | 换页符 |
\n | 换行符 |
\r | 回车符 |
\t | tab符 |
\\ | 反斜杠(\) |
\uXXXX | Unicode解码后的内容 |
mysql> SELECT @@sql_mode; +------------+ | @@sql_mode | +------------+ | | +------------+ mysql> SELECT JSON_UNQUOTE('"\\t\\u0032"'); +------------------------------+ | JSON_UNQUOTE('"\\t\\u0032"') | +------------------------------+ | 2 | +------------------------------+ mysql> SET @@sql_mode = 'NO_BACKSLASH_ESCAPES'; mysql> SELECT JSON_UNQUOTE('"\\t\\u0032"'); +------------------------------+ | JSON_UNQUOTE('"\\t\\u0032"') | +------------------------------+ | \t\u0032 | +------------------------------+ mysql> SELECT JSON_UNQUOTE('"\t\u0032"'); +----------------------------+ | JSON_UNQUOTE('"\t\u0032"') | +----------------------------+ | 2 | +----------------------------+