JSON_ARRAY_APPEND(json_doc, path, val[, path, val] ...)

将值追加到 JSON 文档中指定数组的末尾并返回结果。如果任何参数为 NULL，则返回 NULL。如果 json_doc 参数不是有效的 JSON 文档，或者任何 path 参数不是有效的路径表达式或包含 * 或 ** 通配符，将会发生错误。Appends values to the end of the indicated arrays within a JSON document and returns the result. Returns NULL if any argument is NULL. An error occurs if the json_doc argument is not a valid JSON document or any path argument is not a valid path expression or contains a * or ** wildcard.

路径-值对从左到右进行求值。通过求值一个对生成的文档成为下一个对进行求值时的新值。The path-value pairs are evaluated left to right. The document produced by evaluating one pair becomes the new value against which the next pair is evaluated.

如果路径选择一个标量或对象值，该值会自动封装在一个数组中，并将新值添加到该数组中。对于路径未在 JSON 文档中识别任何值的键值对，将被忽略。If a path selects a scalar or object value, that value is autowrapped within an array and the new value is added to that array. Pairs for which the path does not identify any value in the JSON document are ignored.

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"]                 |
+---------------------------------+

JSON_ARRAY_INSERT(json_doc, path, val[, path, val] ...)

更新 JSON 文档，将值插入文档中的数组并返回修改后的文档。如果任何参数为 NULL，则返回 NULL。如果 json_doc 参数不是有效的 JSON 文档，或者任何 path 参数不是有效的路径表达式，或包含 * 或 ** 通配符，或未以数组元素标识符结尾，则会发生错误。Updates a JSON document, inserting into an array within the document and returning the modified document. Returns NULL if any argument is NULL. An error occurs if the json_doc argument is not a valid JSON document or any path argument is not a valid path expression or contains a * or ** wildcard or does not end with an array element identifier.

路径-值对从左到右进行求值。通过求值一个对生成的文档成为下一个对进行求值时的新值。The path-value pairs are evaluated left to right. The document produced by evaluating one pair becomes the new value against which the next pair is evaluated.

对于路径未在 JSON 文档中标识任何数组的键值对，将被忽略。如果路径标识了数组元素，相应的值将插入该元素位置，并将后续的值向右移动。如果路径标识了超出数组末尾的位置，则该值将插入数组末尾。Pairs for which the path does not identify any array in the JSON document are ignored. If a path identifies an array element, the corresponding value is inserted at that element position, shifting any following values to the right. If a path identifies an array position past the end of an array, the value is inserted at the end of the array.

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]]                  |
+----------------------------------------------------+

早先的修改会影响数组中后续元素的位置，因此在同一次 JSON_ARRAY_INSERT() 调用中的后续路径应考虑到这一点。在最后的示例中，第二个路径没有插入任何内容，因为在第一次插入后，该路径已不再匹配任何内容。Earlier modifications affect the positions of the following elements in the array, so subsequent paths in the same JSON_ARRAY_INSERT() call should take this into account. In the final example, the second path inserts nothing because the path no longer matches anything after the first insert.

JSON_INSERT(json_doc, path, val[, path, val] ...)

将数据插入到 JSON 文档中并返回结果。如果任何参数为 NULL，则返回 NULL。如果 json_doc 参数不是有效的 JSON 文档，或任何 path 参数不是有效的路径表达式，或者包含 * 或 ** 通配符，则会发生错误。Inserts data into a JSON document and returns the result. Returns NULL if any argument is NULL. An error occurs if the json_doc argument is not a valid JSON document or any path argument is not a valid path expression or contains a * or ** wildcard.

路径-值对从左到右进行求值。通过求值一个对生成的文档成为下一个对进行求值时的新值。The path-value pairs are evaluated left to right. The document produced by evaluating one pair becomes the new value against which the next pair is evaluated.

文档中已存在路径的路径-值对将被忽略，不会覆盖现有的文档值。文档中不存在路径的路径-值对如果路径指向以下类型的值，则会将该值添加到文档中：A path-value pair for an existing path in the document is ignored and does not overwrite the existing document value. A path-value pair for a nonexisting path in the document adds the value to the document if the path identifies one of these types of values:

否则，文档中不存在路径的路径-值对将被忽略，并且没有影响。Otherwise, a path-value pair for a nonexisting path in the document is ignored and has no effect.

有关 JSON_INSERT()、JSON_REPLACE() 和 JSON_SET() 的比较，请参见 JSON_SET() 的讨论。For a comparison of JSON_INSERT(), JSON_REPLACE(), and JSON_SET(), see the discussion of JSON_SET().

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]"}        |
+----------------------------------------------------+

结果中列出的第三个也是最后一个值是一个带引号的字符串，而不是像第二个那样的数组（输出中没有引号）；没有将值转换为 JSON 类型。要将数组作为数组插入，必须像这里所示显式进行此类转换：The third and final value listed in the result is a quoted string and not an array like the second one (which is not quoted in the output); no casting of values to the JSON type is performed. To insert the array as an array, you must perform such casts explicitly, as shown here:

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)

JSON_MERGE(json_doc, json_doc[, json_doc] ...)

已弃用的 JSON_MERGE_PRESERVE() 同义词。Deprecated synonym for JSON_MERGE_PRESERVE().

JSON_MERGE_PATCH(json_doc, json_doc[, json_doc] ...)

执行两个或多个 JSON 文档的 RFC 7396 兼容合并，并返回合并结果，不保留具有重复键的成员。如果传递给此函数的至少一个文档无效，则会引发错误。Performs an RFC 7396 compliant merge of two or more JSON documents and returns the merged result, without preserving members having duplicate keys. Raises an error if at least one of the documents passed as arguments to this function is not valid.

JSON_MERGE_PATCH() 的合并操作如下：JSON_MERGE_PATCH() performs a merge as follows:

更多信息，请参见 JSON 值的规范化、合并和自动封装。For additional information, see Normalization, Merging, and Autowrapping of JSON Values.

mysql> SELECT JSON_MERGE_PATCH('[1, 2]', '[true, false]');
+---------------------------------------------+
| JSON_MERGE_PATCH('[1, 2]', '[true, false]') |
+---------------------------------------------+
| [true, false]                               |
+---------------------------------------------+

mysql> SELECT JSON_MERGE_PATCH('{"name": "x"}', '{"id": 47}');
+-------------------------------------------------+
| JSON_MERGE_PATCH('{"name": "x"}', '{"id": 47}') |
+-------------------------------------------------+
| {"id": 47, "name": "x"}                         |
+-------------------------------------------------+

mysql> SELECT JSON_MERGE_PATCH('1', 'true');
+-------------------------------+
| JSON_MERGE_PATCH('1', 'true') |
+-------------------------------+
| true                          |
+-------------------------------+

mysql> SELECT JSON_MERGE_PATCH('[1, 2]', '{"id": 47}');
+------------------------------------------+
| JSON_MERGE_PATCH('[1, 2]', '{"id": 47}') |
+------------------------------------------+
| {"id": 47}                               |
+------------------------------------------+

mysql> SELECT JSON_MERGE_PATCH('{ "a": 1, "b":2 }',
     >     '{ "a": 3, "c":4 }');
+-----------------------------------------------------------+
| JSON_MERGE_PATCH('{ "a": 1, "b":2 }','{ "a": 3, "c":4 }') |
+-----------------------------------------------------------+
| {"a": 3, "b": 2, "c": 4}                                  |
+-----------------------------------------------------------+

mysql> SELECT JSON_MERGE_PATCH('{ "a": 1, "b":2 }','{ "a": 3, "c":4 }',
     >     '{ "a": 5, "d":6 }');
+-------------------------------------------------------------------------------+
| JSON_MERGE_PATCH('{ "a": 1, "b":2 }','{ "a": 3, "c":4 }','{ "a": 5, "d":6 }') |
+-------------------------------------------------------------------------------+
| {"a": 5, "b": 2, "c": 4, "d": 6}                                              |
+-------------------------------------------------------------------------------+

您可以使用此函数通过在第二个参数中将同一成员的值指定为 null 来删除该成员，如下所示：You can use this function to remove a member by specifying null as the value of the same member in the second argument, as shown here:

mysql> SELECT JSON_MERGE_PATCH('{"a":1, "b":2}', '{"b":null}');
+--------------------------------------------------+
| JSON_MERGE_PATCH('{"a":1, "b":2}', '{"b":null}') |
+--------------------------------------------------+
| {"a": 1}                                         |
+--------------------------------------------------+

这个例子表明该函数以递归方式运行；也就是说，成员的值不仅限于标量，而是可以自身就是 JSON 文档：This example shows that the function operates in a recursive fashion; that is, values of members are not limited to scalars, but rather can themselves be JSON documents:

mysql> SELECT JSON_MERGE_PATCH('{"a":{"x":1}}', '{"a":{"y":2}}');
+----------------------------------------------------+
| JSON_MERGE_PATCH('{"a":{"x":1}}', '{"a":{"y":2}}') |
+----------------------------------------------------+
| {"a": {"x": 1, "y": 2}}                            |
+----------------------------------------------------+

JSON_MERGE_PATCH() 与 JSON_MERGE_PRESERVE() 的比较。 JSON_MERGE_PATCH() 的行为与 JSON_MERGE_PRESERVE() 相同，但有以下两个例外：JSON_MERGE_PATCH() compared with JSON_MERGE_PRESERVE().  The behavior of JSON_MERGE_PATCH() is the same as that of JSON_MERGE_PRESERVE(), with the following two exceptions:

这个示例比较了合并相同的 3 个 JSON 对象（每个对象都有一个匹配的键 "a"）时使用这两个函数的结果：This example compares the results of merging the same 3 JSON objects, each having a matching key "a", with each of these two functions:

mysql> SET @x = '{ "a": 1, "b": 2 }',
     >     @y = '{ "a": 3, "c": 4 }',
     >     @z = '{ "a": 5, "d": 6 }';

mysql> SELECT  JSON_MERGE_PATCH(@x, @y, @z)    AS Patch,
    ->         JSON_MERGE_PRESERVE(@x, @y, @z) AS Preserve\G
*************************** 1. row ***************************
   Patch: {"a": 5, "b": 2, "c": 4, "d": 6}
Preserve: {"a": [1, 3, 5], "b": 2, "c": 4, "d": 6}

JSON_MERGE_PRESERVE(json_doc, json_doc[, json_doc] ...)

合并两个或多个 JSON 文档并返回合并后的结果。如果任何参数为 NULL，则返回 NULL。如果任何参数不是有效的 JSON 文档，则会发生错误。Merges two or more JSON documents and returns the merged result. Returns NULL if any argument is NULL. An error occurs if any argument is not a valid JSON document.

合并按照以下规则进行。有关更多信息，请参见 JSON 值的规范化、合并和自动包装。Merging takes place according to the following rules. For additional information, see Normalization, Merging, and Autowrapping of JSON Values.

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}                                         |
+----------------------------------------------------------------------------------+

此函数与JSON_MERGE_PATCH()类似，但在重要方面有所不同；有关更多信息，请参见JSON_MERGE_PATCH() 与 JSON_MERGE_PRESERVE() 的比较。This function is similar to but differs from JSON_MERGE_PATCH() in significant respects; see JSON_MERGE_PATCH() compared with JSON_MERGE_PRESERVE(), for more information.

JSON_REMOVE(json_doc, path[, path] ...)

从 JSON 文档中移除数据并返回结果。如果任何参数为 NULL，则返回 NULL。如果 json_doc 参数不是有效的 JSON 文档，或者任何 path 参数不是有效的路径表达式，或者是 $，或包含 * 或 ** 通配符，则会发生错误。Removes data from a JSON document and returns the result. Returns NULL if any argument is NULL. An error occurs if the json_doc argument is not a valid JSON document or any path argument is not a valid path expression or is $ or contains a * or ** wildcard.

按从左到右的顺序计算 path 参数。通过计算一个路径得到的文档将成为下一个路径计算的新的值。The path arguments are evaluated left to right. The document produced by evaluating one path becomes the new value against which the next path is evaluated.

如果要移除的元素在文档中不存在，这不是错误；在这种情况下，路径不会影响文档。It is not an error if the element to be removed does not exist in the document; in that case, the path does not affect the document.

mysql> SET @j = '["a", ["b", "c"], "d"]';
mysql> SELECT JSON_REMOVE(@j, '$[1]');
+-------------------------+
| JSON_REMOVE(@j, '$[1]') |
+-------------------------+
| ["a", "d"]              |
+-------------------------+

JSON_REPLACE(json_doc, path, val[, path, val] ...)

替换 JSON 文档中的现有值并返回结果。如果 json_doc 或任何 path 参数为 NULL，则返回 NULL。如果 json_doc 参数不是有效的 JSON 文档，或任何 path 参数不是有效的路径表达式，或者包含 * 或 ** 通配符，则会发生错误。Replaces existing values in a JSON document and returns the result. Returns NULL if json_doc or any path argument is NULL. An error occurs if the json_doc argument is not a valid JSON document or any path argument is not a valid path expression or contains a * or ** wildcard.

路径-值对从左到右进行求值。通过求值一个对生成的文档成为下一个对进行求值时的新值。The path-value pairs are evaluated left to right. The document produced by evaluating one pair becomes the new value against which the next pair is evaluated.

文档中现有路径的路径-值对会用新值覆盖现有的文档值。文档中不存在的路径的路径-值对会被忽略且没有任何效果。A path-value pair for an existing path in the document overwrites the existing document value with the new value. A path-value pair for a nonexisting path in the document is ignored and has no effect.

优化器可以对JSON列执行部分就地更新，而不是删除旧文档并将新文档完整写入该列。对于使用JSON_REPLACE()函数的更新语句，并且满足JSON 值的部分更新中概述的条件时，可以执行此优化。The optimizer can perform a partial, in-place update of a JSON column instead of removing the old document and writing the new document in its entirety to the column. This optimization can be performed for an update statement that uses the JSON_REPLACE() function and meets the conditions outlined in Partial Updates of JSON Values.

有关 JSON_INSERT()、JSON_REPLACE() 和 JSON_SET() 的比较，请参见 JSON_SET() 的讨论。For a comparison of JSON_INSERT(), JSON_REPLACE(), and JSON_SET(), see the discussion of JSON_SET().

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]}                              |
+-----------------------------------------------------+

mysql> SELECT JSON_REPLACE(NULL, '$.a', 10, '$.c', '[true, false]');
+-------------------------------------------------------+
| JSON_REPLACE(NULL, '$.a', 10, '$.c', '[true, false]') |
+-------------------------------------------------------+
| NULL                                                  |
+-------------------------------------------------------+

mysql> SELECT JSON_REPLACE(@j, NULL, 10, '$.c', '[true, false]');
+----------------------------------------------------+
| JSON_REPLACE(@j, NULL, 10, '$.c', '[true, false]') |
+----------------------------------------------------+
| NULL                                               |
+----------------------------------------------------+

mysql> SELECT JSON_REPLACE(@j, '$.a', NULL, '$.c', '[true, false]');
+-------------------------------------------------------+
| JSON_REPLACE(@j, '$.a', NULL, '$.c', '[true, false]') |
+-------------------------------------------------------+
| {"a": null, "b": [2, 3]}                              |
+-------------------------------------------------------+

JSON_SET(json_doc, path, val[, path, val] ...)

在 JSON 文档中插入或更新数据并返回结果。如果 json_doc 或 path 为 NULL，或者当提供的 path 未定位到对象时，返回 NULL。否则，如果 json_doc 参数不是有效的 JSON 文档，或者任何 path 参数不是有效的路径表达式，或包含 * 或 ** 通配符，则会发生错误。Inserts or updates data in a JSON document and returns the result. Returns NULL if json_doc or path is NULL, or if path, when given, does not locate an object. Otherwise, an error occurs if the json_doc argument is not a valid JSON document or any path argument is not a valid path expression or contains a * or ** wildcard.

路径-值对从左到右进行求值。通过求值一个对生成的文档成为下一个对进行求值时的新值。The path-value pairs are evaluated left to right. The document produced by evaluating one pair becomes the new value against which the next pair is evaluated.

文档中已有路径的路径-值对会用新值覆盖现有的文档值。文档中不存在路径的路径-值对会将值添加到文档中，如果该路径标识以下类型的值之一：A path-value pair for an existing path in the document overwrites the existing document value with the new value. A path-value pair for a nonexisting path in the document adds the value to the document if the path identifies one of these types of values:

否则，文档中不存在路径的路径-值对将被忽略，并且没有影响。Otherwise, a path-value pair for a nonexisting path in the document is ignored and has no effect.

优化器可以对 JSON 列执行部分的就地更新，而不是删除旧文档并将新文档完整写入该列。对于使用 JSON_SET() 函数并符合 JSON 值的部分更新 中列出的条件的更新语句，可以执行此优化。The optimizer can perform a partial, in-place update of a JSON column instead of removing the old document and writing the new document in its entirety to the column. This optimization can be performed for an update statement that uses the JSON_SET() function and meets the conditions outlined in Partial Updates of JSON Values.

JSON_SET()、JSON_INSERT() 和 JSON_REPLACE() 函数相关：The JSON_SET(), JSON_INSERT(), and JSON_REPLACE() functions are related:

下面的示例通过使用文档中存在的路径 ($.a) 和不存在的路径 ($.c) 来说明这些差异：The following examples illustrate these differences, using one path that does exist in the document ($.a) and another that does not exist ($.c):

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]}                              |
+-----------------------------------------------------+

JSON_UNQUOTE(json_val)

取消引用 JSON 值并将结果作为 utf8mb4 字符串返回。如果参数为 NULL，则返回 NULL。如果值以双引号开始和结束但不是有效的 JSON 字符串字面量，则会发生错误。Unquotes JSON value and returns the result as a utf8mb4 string. Returns NULL if the argument is NULL. An error occurs if the value starts and ends with double quotes but is not a valid JSON string literal.

在字符串中，某些序列具有特殊含义，除非启用了 NO_BACKSLASH_ESCAPES SQL 模式。每个这样的序列都以反斜杠（\）开头，这称为 转义字符。MySQL 识别 表 14.23，“JSON_UNQUOTE() 特殊字符转义序列” 中显示的转义序列。对于所有其他转义序列，反斜杠会被忽略。也就是说，转义字符将被解释为未转义。例如，\x 仅被解释为 x。这些序列区分大小写。例如，\b 被解读为退格，但 \B 被解读为 B。Within a string, certain sequences have special meaning unless the NO_BACKSLASH_ESCAPES SQL mode is enabled. Each of these sequences begins with a backslash (\), known as the escape character. MySQL recognizes the escape sequences shown in Table 14.23, “JSON_UNQUOTE() Special Character Escape Sequences”. For all other escape sequences, backslash is ignored. That is, the escaped character is interpreted as if it was not escaped. For example, \x is just x. These sequences are case-sensitive. For example, \b is interpreted as a backspace, but \B is interpreted as B.

这里展示了该函数的两个简单使用示例：Two simple examples of the use of this function are shown here:

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]        |
+-----------+------------------+

以下示例集展示了在禁用和启用 NO_BACKSLASH_ESCAPES 时，JSON_UNQUOTE 如何处理转义字符：The following set of examples shows how JSON_UNQUOTE handles escapes with NO_BACKSLASH_ESCAPES disabled and enabled:

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                         |
+----------------------------+