(目录)
本文涉及函数简介
| 函数 | 作用 |
|---|---|
| JSON_ARRAY_APPEND | 在数组后追加元素 |
| JSON_ARRAY_INSERT | 在JSON数据中的指定位置插入元素 |
| JSON_INSERT | 如果存在值，不操作，否则插入值 |
| JSON_REPLACE | 如果存在值，更新该值，否则不操作 |
| JSON_SET | 如果存在值，就更新，否则就插入 |
| JSON_REMOVE | 如果存在值，就删除，否则无效果 |
| JSON_MERGE_PATCH | 合并多个JSON文档，不保留重复值 |
| JSON_MERGE_PRESERVE = JSON_MERGE | 合并多个JSON文档，保留重复值 |
| JSON_QUOTE | 将字符串转换为JSON值，对其中的特殊符号进行转义。 |
| JSON_UNQUOTE | 将JSON值转换为字符串，并去除其中的转义字符 |
更改JSON的函数
数组相关函数
JSON_ARRAY_APPEND
JSON_ARRAY_APPEND(json_doc, path, val[, path, val] ...)
从JSON文档中查找path路径的内容，然后在其内容的最后追加值，如果path的类型不是数组，将其视为数组中的一个元素
mysql> select value,JSON_ARRAY_APPEND(value,"$[0]",1),JSON_ARRAY_APPEND(value,"$[1]",3),JSON_ARRAY_APPEND(value,"$[2]","c") from test_json\G
*************************** 1. row ***************************
value: ["a", ["b", "c"], {"d": 5}, "f"]
-- $[0] = "a", 视为 ["a"] 在后方追加1 = ["a", 1]
JSON_ARRAY_APPEND(value,"$[0]",1): [["a", 1], ["b", "c"], {"d": 5}, "f"]
-- $[1] = ["b", "c"] ,在后方追加3 = ["b", "c", 3]
JSON_ARRAY_APPEND(value,"$[1]",3): ["a", ["b", "c", 3], {"d": 5}, "f"]
-- $[2] = {"d": 5} , 视为 [{"d": 5}] 在后方追加"c" = [{"d": 5}, "c"]
JSON_ARRAY_APPEND(value,"$[2]","c"): ["a", ["b", "c"], [{"d": 5}, "c"], "f"]
JSON_ARRAY_INSERT
从JSON文档中查找path路径的内容，如果path是数组中的元素，就将在该位置插入值，其他元素右移，如果path不是数组中的元素，无作用，如果path的位置超过了数组的末尾，就将值插入到数组的末尾
JSON_ARRAY_INSERT(json_doc, path, val[, path, val] ...)
mysql> select value,JSON_ARRAY_INSERT(value,"$[0]",1),JSON_ARRAY_INSERT(value,"$[1]",3),JSON_ARRAY_INSERT(value,"$[1][1]","c"),JSON_ARRAY_INSERT(value,"$[2][0]","c") from test_json\G
*************************** 1. row ***************************
value: ["a", ["b", "c"], {"d": 5}, "f"]
-- "$" 是数组，插入到$[0]的位置
JSON_ARRAY_INSERT(value,"$[0]",1): [1, "a", ["b", "c"], {"d": 5}, "f"]
JSON_ARRAY_INSERT(value,"$[1]",3): ["a", 3, ["b", "c"], {"d": 5}, "f"]
-- "$[1]" = ["b", "c"] 是数组，插入到$[1][1]的位置
JSON_ARRAY_INSERT(value,"$[1][1]","c"): ["a", ["b", "c", "c"], {"d": 5}, "f"]
-- "$[2]" = {"d": 5} 不是数组，插入无效
JSON_ARRAY_INSERT(value,"$[2][0]","c"): ["a", ["b", "c"], {"d": 5}, "f"]
增删改
示例详见参考MySQL8.0 JSON数据类型的更新JSON值部分
JSON_INSERT
JSON_INSERT(json_doc, path, val[, path, val] ...)
如果指定path不存在内容，插入数据，否则无效果
JSON_REPLACE
JSON_REPLACE(json_doc, path, val[, path, val] ...)
如果指定path存在内容，更新数据，否则无效果
JSON_SET
JSON_SET(json_doc, path, val[, path, val] ...)
如果指定path存在内容，更新数据，否则插入数据
JSON_REMOVE
JSON_REMOVE(json_doc, path[, path] ...)
如果指定path存在内容，删除该内容
合并JSON值
JSON_MERGE_PATCH
JSON_MERGE_PATCH(json_doc, json_doc[, json_doc] ...)
合并多个JSON文档，不保留重复值
合并规则：
- 如果第一个参数不是JSON对象，将空对象和第二个参数合并
- 如果第二个参数不是JSON对象，合并的结果就是第二个对象
- 如果两个参数都是JSON对象，结果如下：
- 如果第一个参数中存在值为null的键，保留键值对
- 如果第二个参数中存在值为null的键，并且该键再第一个参数中存在，删除该键值对
- 具有不同键的值保留原值，具有相同键的值，保留最后一个参数中的值 合并会递归执行，也就是，如果在两个参数中存在相同的键，并且键值都是JSON对象，其中的JSON对象也会进行合并，然后保留最终合并的结果
mysql> SELECT JSON_MERGE_PATCH('[1, 2]', '[true, false]'),JSON_MERGE_PATCH('{"name": "x"}', '{"id": 47}'),JSON_MERGE_PATCH('1', 'true'),JSON_MERGE_PATCH('{ "a": 1, "b":2 }','{ "a": 3, "c":4 }'),JSON_MERGE_PATCH('{"a":null}','{"b":1}'),JSON_MERGE_PATCH('{"a":1,"c":5}','{"c":null}'),JSON_MERGE_PATCH('{"a":{"x":1}}', '{"a":{"y":2}}')\G
*************************** 1. row ***************************
-- 两个数组合并，结果是后一个数组
JSON_MERGE_PATCH('[1, 2]', '[true, false]'): [true, false]
-- 两个JSON对象合并，结果是一个包含两组键值对的JSON对象
JSON_MERGE_PATCH('{"name": "x"}', '{"id": 47}'): {"id": 47, "name": "x"}
-- 数值和布尔值合并，结果是后一个布尔值
JSON_MERGE_PATCH('1', 'true'): true
-- 两个JSON对象合并，由于"a"键在两个文档中都存在，保留后面出现的值=3
JSON_MERGE_PATCH('{ "a": 1, "b":2 }','{ "a": 3, "c":4 }'): {"a": 3, "b": 2, "c": 4}
-- 第一个参数中存在值为null的键值对保留
JSON_MERGE_PATCH('{"a":null}','{"b":1}'): {"a": null, "b": 1}
-- 第二个参数中存在值为null的键，把前一个参数中的键值对也移除
JSON_MERGE_PATCH('{"a":1,"c":5}','{"c":null}'): {"a": 1}
-- 存在子文档，嵌套合并
JSON_MERGE_PATCH('{"a":{"x":1}}', '{"a":{"y":2}}'): {"a": {"x": 1, "y": 2}}
JSON_MERGE_PRESERVE
JSON_MERGE_PRESERVE(json_doc, json_doc[, json_doc] ...)
同义词：JSON_MERGE(json_doc, json_doc[, json_doc] ...)
合并多个JSON文档，保留重复值
合并规则：
- 多个数组合并为一个数组
- 多个对象合并为一个对象
- 其余值被视为存在单个元素的数组，并与数组合并
- 数组和对象合并为一个数组
mysql> SELECT JSON_MERGE_PRESERVE('[1, 2]', '[true, false]'),JSON_MERGE_PRESERVE('{"name": "x"}', '{"id": 47}'),JSON_MERGE_PRESERVE('1', 'true'),JSON_MERGE_PRESERVE('[1, 2]', '{"id": 47}'),JSON_MERGE_PRESERVE('{ "a": 1, "b": 2 }','{ "a": null, "c": 4 }'),JSON_MERGE_PRESERVE('{"a":{"x":1}}', '{"a":{"x":3,"y":2}}') \G
*************************** 1. row ***************************
-- 两个数组合并成一个数组
JSON_MERGE_PRESERVE('[1, 2]', '[true, false]'): [1, 2, true, false]
-- 两个对象合并成一个对象
JSON_MERGE_PRESERVE('{"name": "x"}', '{"id": 47}'): {"id": 47, "name": "x"}
-- 数值和布尔值合并成一个数组，数值和布尔值分别作为数组中的一个元素
JSON_MERGE_PRESERVE('1', 'true'): [1, true]
-- 数组和对象合并成一个数组，对象作为数组中的一个元素
JSON_MERGE_PRESERVE('[1, 2]', '{"id": 47}'): [1, 2, {"id": 47}]
-- 多个对象存在相同键，将键值进行合并。null值不会导致键删除，而是作为数组中的一个元素合并
JSON_MERGE_PRESERVE('{ "a": 1, "b": 2 }','{ "a": null, "c": 4 }'): {"a": [1, null], "b": 2, "c": 4}
-- 存在子文档，嵌套合并
JSON_MERGE_PRESERVE('{"a":{"x":1}}', '{"a":{"x":3,"y":2}}'): {"a": {"x": [1, 3], "y": 2}}
字符串和JSON格式转换
JSON_QUOTE
JSON_QUOTE(string)
将字符串转换为JSON值，对其中的特殊符号进行转义。
mysql> SELECT JSON_QUOTE('null'), JSON_QUOTE('"null"'),JSON_QUOTE('[1, 2, 3]')\G
*************************** 1. row ***************************
JSON_QUOTE('null'): "null"
JSON_QUOTE('"null"'): "\"null\""
JSON_QUOTE('[1, 2, 3]'): "[1, 2, 3]"
JSON_UNQUOTE
取消JSON值外的引号以及内部的转义字符并以utf8mb4字符串的形式返回结果。是JSON_QUOTE的反向转换
JSON_UNQUOTE(json_val)
mysql> select JSON_UNQUOTE("NULL"),JSON_UNQUOTE("\"null\""),JSON_UNQUOTE("[1, 2, 3]")\G
*************************** 1. row ***************************
JSON_UNQUOTE("NULL"): NULL
JSON_UNQUOTE("\"null\""): null
JSON_UNQUOTE("[1, 2, 3]"): [1, 2, 3]
特殊字符说明
转义字符区分大小写，如果不是JSON_UNQUOTE函数支持的转义字符，会忽略反斜杠，直接返回后面的字符，例如\b等同于退格符，但是\B等同于B
JSON_UNQUOTE函数支持的转义字符表
| 转义后 | 转义前 |
|---|---|
\" |
双引号" |
\b |
退格符（Backspace） |
\f |
换页符 |
\n |
换行符（Enter） |
\r |
回车符 |
\t |
水平制表符（Tab） |
\\ |
反斜杠\ |
\uXXXX |
Unicode值为XXXX的UTF-8字节 |
