MySQL8.0 JSON相关函数(二) -更改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函数支持的转义字符表