JSON 数组包含由逗号分隔并用 [ 和 ] 括起来的值列表：A JSON array contains a list of values separated by commas and enclosed within [ and ] characters:

["abc", 10, null, true, false]

JSON 对象包含由逗号分隔并用 { 和 } 括起来的键值对集合：A JSON object contains a set of key-value pairs separated by commas and enclosed within { and } characters:

{"k1": "value", "k2": 10}

如示例所示，JSON 数组和对象可以包含标量值，这些标量值可以是字符串或数字，也可以是 JSON 的 null 字面量，或者 JSON 的布尔 true 或 false 字面量。JSON 对象中的键必须是字符串。也允许使用时间（日期、时间或日期时间）标量值：As the examples illustrate, JSON arrays and objects can contain scalar values that are strings or numbers, the JSON null literal, or the JSON boolean true or false literals. Keys in JSON objects must be strings. Temporal (date, time, or datetime) scalar values are also permitted:

["12:18:29.000000", "2015-07-29", "2015-07-29 12:18:29.000000"]

JSON 数组元素和 JSON 对象键值中允许嵌套：Nesting is permitted within JSON array elements and JSON object key values:

[99, {"id": "HK500", "cost": 75.99}, ["hot", "cold"]]
{"k1": "value", "k2": [10, 20]}

你也可以通过 MySQL 提供的一些函数获取 JSON 值（参见 第 14.17.2 节，“创建 JSON 值的函数”），也可以通过将其他类型的值转换为 JSON 类型来获取 JSON 值，方法是使用 CAST(value AS JSON)（参见 在 JSON 与非 JSON 值之间转换）。接下来的几段将描述 MySQL 如何处理作为输入提供的 JSON 值。You can also obtain JSON values from a number of functions supplied by MySQL for this purpose (see Section 14.17.2, “Functions That Create JSON Values”) as well as by casting values of other types to the JSON type using CAST(value AS JSON) (see Converting between JSON and non-JSON values). The next several paragraphs describe how MySQL handles JSON values provided as input.

在 MySQL 中，JSON 值以字符串形式书写。MySQL 会解析在需要 JSON 值的上下文中使用的任何字符串，如果字符串不是有效的 JSON 值，则会生成错误。这些上下文包括向具有 JSON 数据类型的列插入值，以及向期望 JSON 值的函数传递参数（通常在 MySQL JSON 函数文档中显示为 json_doc 或 json_val），如下示例所示：In MySQL, JSON values are written as strings. MySQL parses any string used in a context that requires a JSON value, and produces an error if it is not valid as JSON. These contexts include inserting a value into a column that has the JSON data type and passing an argument to a function that expects a JSON value (usually shown as json_doc or json_val in the documentation for MySQL JSON functions), as the following examples demonstrate:

MySQL 在 JSON 上下文中处理字符串时使用 utf8mb4 字符集和 utf8mb4_bin 排序规则。其他字符集的字符串会在必要时转换为 utf8mb4。（对于 ascii 或 utf8mb3 字符集中的字符串，则无需转换，因为 ascii 和 utf8mb3 都是 utf8mb4 的子集。）MySQL handles strings used in JSON context using the utf8mb4 character set and utf8mb4_bin collation. Strings in other character sets are converted to utf8mb4 as necessary. (For strings in the ascii or utf8mb3 character sets, no conversion is needed because ascii and utf8mb3 are subsets of utf8mb4.)

作为使用字面字符串编写 JSON 值的替代方法，可以使用函数从组件元素组成 JSON 值。JSON_ARRAY() 接受一个（可能为空的）值列表，并返回包含这些值的 JSON 数组：As an alternative to writing JSON values using literal strings, functions exist for composing JSON values from component elements. JSON_ARRAY() takes a (possibly empty) list of values and returns a JSON array containing those values:

mysql> SELECT JSON_ARRAY('a', 1, NOW());
+----------------------------------------+
| JSON_ARRAY('a', 1, NOW())              |
+----------------------------------------+
| ["a", 1, "2015-07-27 09:43:47.000000"] |
+----------------------------------------+

JSON_OBJECT() 接受一个（可能为空的）键值对列表，并返回包含这些键值对的 JSON 对象：JSON_OBJECT() takes a (possibly empty) list of key-value pairs and returns a JSON object containing those pairs:

mysql> SELECT JSON_OBJECT('key1', 1, 'key2', 'abc');
+---------------------------------------+
| JSON_OBJECT('key1', 1, 'key2', 'abc') |
+---------------------------------------+
| {"key1": 1, "key2": "abc"}            |
+---------------------------------------+

JSON_MERGE_PRESERVE() 接受两个或更多 JSON 文档，并返回合并后的结果：JSON_MERGE_PRESERVE() takes two or more JSON documents and returns the combined result:

mysql> SELECT JSON_MERGE_PRESERVE('["a", 1]', '{"key": "value"}');
+-----------------------------------------------------+
| JSON_MERGE_PRESERVE('["a", 1]', '{"key": "value"}') |
+-----------------------------------------------------+
| ["a", 1, {"key": "value"}]                          |
+-----------------------------------------------------+
1 row in set (0.00 sec)

有关合并规则的信息，请参见 JSON 值的规范化、合并和自动包装。For information about the merging rules, see Normalization, Merging, and Autowrapping of JSON Values.

（MySQL 还支持 JSON_MERGE_PATCH()，其行为略有不同。请参见 JSON_MERGE_PATCH() 与 JSON_MERGE_PRESERVE() 的比较，了解这两个函数之间的差异。）(MySQL also supports JSON_MERGE_PATCH(), which has somewhat different behavior. See JSON_MERGE_PATCH() compared with JSON_MERGE_PRESERVE(), for information about the differences between these two functions.)

JSON 值可以分配给用户自定义变量：JSON values can be assigned to user-defined variables:

mysql> SET @j = JSON_OBJECT('key', 'value');
mysql> SELECT @j;
+------------------+
| @j               |
+------------------+
| {"key": "value"} |
+------------------+

然而，用户定义的变量不能是JSON数据类型，因此虽然前面示例中的@j看起来像一个 JSON 值，并且其字符集和排序规则与 JSON 值相同，但它不是JSON数据类型。相反，当将JSON_OBJECT()的结果赋值给变量时，该结果会被转换为字符串。However, user-defined variables cannot be of JSON data type, so although @j in the preceding example looks like a JSON value and has the same character set and collation as a JSON value, it does not have the JSON data type. Instead, the result from JSON_OBJECT() is converted to a string when assigned to the variable.

通过转换 JSON 值生成的字符串，其字符集为utf8mb4，排序规则为utf8mb4_bin：Strings produced by converting JSON values have a character set of utf8mb4 and a collation of utf8mb4_bin:

mysql> SELECT CHARSET(@j), COLLATION(@j);
+-------------+---------------+
| CHARSET(@j) | COLLATION(@j) |
+-------------+---------------+
| utf8mb4     | utf8mb4_bin   |
+-------------+---------------+

由于utf8mb4_bin是二进制排序规则，因此 JSON 值的比较是区分大小写的。Because utf8mb4_bin is a binary collation, comparison of JSON values is case-sensitive.

mysql> SELECT JSON_ARRAY('x') = JSON_ARRAY('X');
+-----------------------------------+
| JSON_ARRAY('x') = JSON_ARRAY('X') |
+-----------------------------------+
|                                 0 |
+-----------------------------------+

大小写敏感性同样适用于 JSON 中的 null、true 和 false 字面量，它们必须始终以小写书写：Case sensitivity also applies to the JSON null, true, and false literals, which always must be written in lowercase:

mysql> SELECT JSON_VALID('null'), JSON_VALID('Null'), JSON_VALID('NULL');
+--------------------+--------------------+--------------------+
| JSON_VALID('null') | JSON_VALID('Null') | JSON_VALID('NULL') |
+--------------------+--------------------+--------------------+
|                  1 |                  0 |                  0 |
+--------------------+--------------------+--------------------+

mysql> SELECT CAST('null' AS JSON);
+----------------------+
| CAST('null' AS JSON) |
+----------------------+
| null                 |
+----------------------+
1 row in set (0.00 sec)

mysql> SELECT CAST('NULL' AS JSON);
ERROR 3141 (22032): Invalid JSON text in argument 1 to function cast_as_json:
"Invalid value." at position 0 in 'NULL'.

JSON 字面量的大小写敏感性与 SQL 中的 NULL、TRUE 和 FALSE 字面量不同，后者可以用任意大小写书写：Case sensitivity of the JSON literals differs from that of the SQL NULL, TRUE, and FALSE literals, which can be written in any lettercase:

mysql> SELECT ISNULL(null), ISNULL(Null), ISNULL(NULL);
+--------------+--------------+--------------+
| ISNULL(null) | ISNULL(Null) | ISNULL(NULL) |
+--------------+--------------+--------------+
|            1 |            1 |            1 |
+--------------+--------------+--------------+

有时可能有必要或希望在 JSON 文档中插入引号字符（" 或 '）。在本例中，假设你想插入一些 JSON 对象，这些对象包含表示关于 MySQL 的某些事实的字符串，每个都与一个适当的关键字配对，插入到使用此处显示的 SQL 语句创建的表中：Sometimes it may be necessary or desirable to insert quote characters (" or ') into a JSON document. Assume for this example that you want to insert some JSON objects containing strings representing sentences that state some facts about MySQL, each paired with an appropriate keyword, into a table created using the SQL statement shown here:

mysql> CREATE TABLE facts (sentence JSON);

在这些关键词-句子对中有这样一个:Among these keyword-sentence pairs is this one:

mascot: The MySQL mascot is a dolphin named "Sakila".

将其作为 JSON 对象插入 facts 表的一种方法是使用 MySQL 的 JSON_OBJECT() 函数。在这种情况下，必须使用反斜杠转义每个引号字符，如下所示：One way to insert this as a JSON object into the facts table is to use the MySQL JSON_OBJECT() function. In this case, you must escape each quote character using a backslash, as shown here:

mysql> INSERT INTO facts VALUES
     >   (JSON_OBJECT("mascot", "Our mascot is a dolphin named \"Sakila\"."));

如果你将值作为 JSON 对象字面量插入，情况就不一样了，在这种情况下，你必须使用双反斜杠转义序列，如下所示：This does not work in the same way if you insert the value as a JSON object literal, in which case, you must use the double backslash escape sequence, like this:

mysql> INSERT INTO facts VALUES
     >   ('{"mascot": "Our mascot is a dolphin named \\"Sakila\\"."}');

使用双反斜杠可以防止 MySQL 执行转义序列处理，而是将字符串字面值传递给存储引擎进行处理。在以刚才显示的任意方式插入 JSON 对象后，你可以通过简单的 SELECT 语句查看 JSON 列值中存在的反斜杠，如下所示：Using the double backslash keeps MySQL from performing escape sequence processing, and instead causes it to pass the string literal to the storage engine for processing. After inserting the JSON object in either of the ways just shown, you can see that the backslashes are present in the JSON column value by doing a simple SELECT, like this:

mysql> SELECT sentence FROM facts;
+---------------------------------------------------------+
| sentence                                                |
+---------------------------------------------------------+
| {"mascot": "Our mascot is a dolphin named \"Sakila\"."} |
+---------------------------------------------------------+

要使用 mascot 作为键查找这个特定的句子，你可以使用列路径操作符 ->，如下所示：To look up this particular sentence employing mascot as the key, you can use the column-path operator ->, as shown here:

mysql> SELECT col->"$.mascot" FROM qtest;
+---------------------------------------------+
| col->"$.mascot"                             |
+---------------------------------------------+
| "Our mascot is a dolphin named \"Sakila\"." |
+---------------------------------------------+
1 row in set (0.00 sec)

这会保留反斜杠以及周围的引号。要使用 mascot 作为键显示所需的值，但不包括周围的引号或任何转义字符，请使用内联路径运算符 ->>，如下所示：This leaves the backslashes intact, along with the surrounding quote marks. To display the desired value using mascot as the key, but without including the surrounding quote marks or any escapes, use the inline path operator ->>, like this:

mysql> SELECT sentence->>"$.mascot" FROM facts;
+-----------------------------------------+
| sentence->>"$.mascot"                   |
+-----------------------------------------+
| Our mascot is a dolphin named "Sakila". |
+-----------------------------------------+

mysql> INSERT INTO facts VALUES
     > (JSON_OBJECT('mascot', 'Our mascot is a dolphin named "Sakila".'));

当解析一个字符串并发现它是有效的 JSON 文档时，它也会被规范化。这意味着具有重复键的成员（从左到右读取文档时发现的重复键）将被舍弃。以下 JSON_OBJECT() 调用生成的对象值只包括第二个 key1 元素，因为该键名称在值中早先出现，如下所示：When a string is parsed and found to be a valid JSON document, it is also normalized. This means that members with keys that duplicate a key found later in the document, reading from left to right, are discarded. The object value produced by the following JSON_OBJECT() call includes only the second key1 element because that key name occurs earlier in the value, as shown here:

mysql> SELECT JSON_OBJECT('key1', 1, 'key2', 'abc', 'key1', 'def');
+------------------------------------------------------+
| JSON_OBJECT('key1', 1, 'key2', 'abc', 'key1', 'def') |
+------------------------------------------------------+
| {"key1": "def", "key2": "abc"}                       |
+------------------------------------------------------+

当值被插入到 JSON 列中时，也会执行规范化，如下所示：Normalization is also performed when values are inserted into JSON columns, as shown here:

mysql> CREATE TABLE t1 (c1 JSON);

mysql> INSERT INTO t1 VALUES
     >     ('{"x": 17, "x": "red"}'),
     >     ('{"x": 17, "x": "red", "x": [3, 5, 7]}');

mysql> SELECT c1 FROM t1;
+------------------+
| c1               |
+------------------+
| {"x": "red"}     |
| {"x": [3, 5, 7]} |
+------------------+

这种“最后的重复键获胜”行为由RFC 7159建议，并且被大多数 JavaScript 解析器实现。（Bug #86866, Bug #26369555）This “last duplicate key wins” behavior is suggested by RFC 7159 and is implemented by most JavaScript parsers. (Bug #86866, Bug #26369555)

MySQL 会丢弃原始 JSON 文档中键、值或元素之间的多余空格，并在显示时在每个逗号（,）或冒号（:）之后保持（或在必要时插入）一个空格。这是为了增强可读性。MySQL discards extra whitespace between keys, values, or elements in the original JSON document, and leaves (or inserts, when necessary) a single space following each comma (,) or colon (:) when displaying it. This is done to enhance readability.

生成 JSON 值的 MySQL 函数（参见第 14.17.2 节，“生成 JSON 值的函数”）始终返回规范化的值。MySQL functions that produce JSON values (see Section 14.17.2, “Functions That Create JSON Values”) always return normalized values.

为了使查找更高效，MySQL 还会对 JSON 对象的键进行排序。你应该注意，这种排序的结果可能会发生变化，并且不能保证在不同版本中保持一致。To make lookups more efficient, MySQL also sorts the keys of a JSON object. You should be aware that the result of this ordering is subject to change and not guaranteed to be consistent across releases.

合并 JSON 值Merging JSON Values

支持两种合并算法，由函数 JSON_MERGE_PRESERVE() 和 JSON_MERGE_PATCH() 实现。它们在处理重复键时有所不同：JSON_MERGE_PRESERVE() 会保留重复键的值，而 JSON_MERGE_PATCH() 则丢弃除了最后一个值之外的所有值。接下来的几段将解释这两个函数如何处理不同组合的 JSON 文档（即对象和数组）的合并。Two merging algorithms are supported, implemented by the functions JSON_MERGE_PRESERVE() and JSON_MERGE_PATCH(). These differ in how they handle duplicate keys: JSON_MERGE_PRESERVE() retains values for duplicate keys, while JSON_MERGE_PATCH() discards all but the last value. The next few paragraphs explain how each of these two functions handles the merging of different combinations of JSON documents (that is, of objects and arrays).

合并数组。 在合并多个数组的情况下，这些数组会合并为一个数组。JSON_MERGE_PRESERVE() 通过将后面命名的数组连接到第一个数组的末尾来实现这一点。JSON_MERGE_PATCH() 将每个参数视为包含单个元素的数组（因此索引为 0），然后应用“最后的重复键获胜”逻辑，仅选择最后一个参数。您可以通过此查询比较显示的结果：Merging arrays.  In contexts that combine multiple arrays, the arrays are merged into a single array. JSON_MERGE_PRESERVE() does this by concatenating arrays named later to the end of the first array. JSON_MERGE_PATCH() considers each argument as an array consisting of a single element (thus having 0 as its index) and then applies “last duplicate key wins” logic to select only the last argument. You can compare the results shown by this query:

mysql> SELECT
    ->   JSON_MERGE_PRESERVE('[1, 2]', '["a", "b", "c"]', '[true, false]') AS Preserve,
    ->   JSON_MERGE_PATCH('[1, 2]', '["a", "b", "c"]', '[true, false]') AS Patch\G
*************************** 1. row ***************************
Preserve: [1, 2, "a", "b", "c", true, false]
   Patch: [true, false]

多个对象合并时会生成一个单一对象。JSON_MERGE_PRESERVE() 处理具有相同键的多个对象时，会将该键的所有唯一值组合在一个数组中；然后该数组被用作结果中该键的值。JSON_MERGE_PATCH() 会丢弃找到重复键的值，从左到右工作，因此结果中仅包含该键的最后一个值。以下查询展示了重复键 a 结果的差异：Multiple objects when merged produce a single object. JSON_MERGE_PRESERVE() handles multiple objects having the same key by combining all unique values for that key in an array; this array is then used as the value for that key in the result. JSON_MERGE_PATCH() discards values for which duplicate keys are found, working from left to right, so that the result contains only the last value for that key. The following query illustrates the difference in the results for the duplicate key a:

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

在需要数组值的上下文中使用非数组值时，它们会被自动包装：该值会被 [ 和 ] 字符包围，以将其转换为数组。在下面的语句中，每个参数都被自动包装为数组（[1]、[2]）。然后，它们被合并以生成一个单一的结果数组；如前两个例子所示，JSON_MERGE_PRESERVE() 会合并具有相同键的值，而 JSON_MERGE_PATCH() 则会丢弃所有重复键的值，只保留最后一个，如下所示：Nonarray values used in a context that requires an array value are autowrapped: The value is surrounded by [ and ] characters to convert it to an array. In the following statement, each argument is autowrapped as an array ([1], [2]). These are then merged to produce a single result array; as in the previous two cases, JSON_MERGE_PRESERVE() combines values having the same key while JSON_MERGE_PATCH() discards values for all duplicate keys except the last, as shown here:

mysql> SELECT
	  ->   JSON_MERGE_PRESERVE('1', '2') AS Preserve,
	  ->   JSON_MERGE_PATCH('1', '2') AS Patch\G
*************************** 1. row ***************************
Preserve: [1, 2]
   Patch: 2

Array and object values are merged by autowrapping the object as an array and merging the arrays by combining values or by “last duplicate key wins” according to the choice of merging function (JSON_MERGE_PRESERVE()或者JSON_MERGE_PATCH(), respectively), as can be seen in this example:Array and object values are merged by autowrapping the object as an array and merging the arrays by combining values or by “last duplicate key wins” according to the choice of merging function (JSON_MERGE_PRESERVE() or JSON_MERGE_PATCH(), respectively), as can be seen in this example:

mysql> SELECT
	  ->   JSON_MERGE_PRESERVE('[10, 20]', '{"a": "x", "b": "y"}') AS Preserve,
	  ->   JSON_MERGE_PATCH('[10, 20]', '{"a": "x", "b": "y"}') AS Patch\G
*************************** 1. row ***************************
Preserve: [10, 20, {"a": "x", "b": "y"}]
   Patch: {"a": "x", "b": "y"}

JSON 路径表达式用于在 JSON 文档中选择一个值。A JSON path expression selects a value within a JSON document.

路径表达式在使用从 JSON 文档中提取部分内容或修改 JSON 文档的函数时非常有用，用于指定在文档中的操作位置。例如，以下查询从 JSON 文档中提取具有 name 键的成员的值：Path expressions are useful with functions that extract parts of or modify a JSON document, to specify where within that document to operate. For example, the following query extracts from a JSON document the value of the member with the name key:

mysql> SELECT JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name');
+---------------------------------------------------------+
| JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name') |
+---------------------------------------------------------+
| "Aztalan"                                               |
+---------------------------------------------------------+

路径语法使用一个开头的 $ 字符来表示正在考虑的 JSON 文档，后面可选择性地跟随选择器，这些选择器指示文档中依次更具体的部分：Path syntax uses a leading $ character to represent the JSON document under consideration, optionally followed by selectors that indicate successively more specific parts of the document:

令 $ 指代包含三个元素的这个 JSON 数组：Let $ refer to this JSON array with three elements:

[3, {"a": [5, 6], "b": 10}, [99, 100]]

然后：Then:

因为 $[1] 和 $[2] 的值是非标量的，它们可以用作更具体路径表达式的基础，以选择嵌套值。示例：Because $[1] and $[2] evaluate to nonscalar values, they can be used as the basis for more-specific path expressions that select nested values. Examples:

如前所述，如果未加引号的键名在路径表达式中不合法，则命名键的路径组件必须加引号。让 $ 指向此值：As mentioned previously, path components that name keys must be quoted if the unquoted key name is not legal in path expressions. Let $ refer to this value:

{"a fish": "shark", "a bird": "sparrow"}

这两个键都包含空格，必须加引号：The keys both contain a space and must be quoted:

使用通配符的路径会被评估为可以包含多个值的数组：Paths that use wildcards evaluate to an array that can contain multiple values:

mysql> SELECT JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.*');
+---------------------------------------------------------+
| JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.*') |
+---------------------------------------------------------+
| [1, 2, [3, 4, 5]]                                       |
+---------------------------------------------------------+
mysql> SELECT JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.c[*]');
+------------------------------------------------------------+
| JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.c[*]') |
+------------------------------------------------------------+
| [3, 4, 5]                                                  |
+------------------------------------------------------------+

在以下示例中，路径 $**.b 会求值为多个路径（$.a.b 和 $.c.b），并生成一个包含匹配路径值的数组：In the following example, the path $**.b evaluates to multiple paths ($.a.b and $.c.b) and produces an array of the matching path values:

mysql> SELECT JSON_EXTRACT('{"a": {"b": 1}, "c": {"b": 2}}', '$**.b');
+---------------------------------------------------------+
| JSON_EXTRACT('{"a": {"b": 1}, "c": {"b": 2}}', '$**.b') |
+---------------------------------------------------------+
| [1, 2]                                                  |
+---------------------------------------------------------+

来自 JSON 数组的范围。 你可以使用 to 关键字来指定 JSON 数组的子集。例如，$[1 to 3] 包含数组的第二、第三和第四个元素，如下所示：Ranges from JSON arrays.  You can use ranges with the to keyword to specify subsets of JSON arrays. For example, $[1 to 3] includes the second, third, and fourth elements of an array, as shown here:

mysql> SELECT JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[1 to 3]');
+----------------------------------------------+
| JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[1 to 3]') |
+----------------------------------------------+
| [2, 3, 4]                                    |
+----------------------------------------------+
1 row in set (0.00 sec)

语法是 M to N，其中 M 和 N 分别是 JSON 数组中元素范围的第一个和最后一个索引。N 必须大于 M；M 必须大于或等于 0。数组元素的索引从 0 开始。The syntax is M to N, where M and N are, respectively, the first and last indexes of a range of elements from a JSON array. N must be greater than M; M must be greater than or equal to 0. Array elements are indexed beginning with 0.

您可以在支持通配符的上下文中使用范围。You can use ranges in contexts where wildcards are supported.

最右边的数组元素。 last 关键字被支持作为数组中最后一个元素索引的同义词。形式为 last - N 的表达式可以用于相对寻址，并且可以在范围定义中使用，如下所示：Rightmost array element.  The last keyword is supported as a synonym for the index of the last element in an array. Expressions of the form last - N can be used for relative addressing, and within range definitions, like this:

mysql> SELECT JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[last-3 to last-1]');
+--------------------------------------------------------+
| JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[last-3 to last-1]') |
+--------------------------------------------------------+
| [2, 3, 4]                                              |
+--------------------------------------------------------+
1 row in set (0.01 sec)

如果路径针对一个不是数组的值进行评估，评估结果与该值被包装在单元素数组中的结果相同：If the path is evaluated against a value that is not an array, the result of the evaluation is the same as if the value had been wrapped in a single-element array:

mysql> SELECT JSON_REPLACE('"Sakila"', '$[last]', 10);
+-----------------------------------------+
| JSON_REPLACE('"Sakila"', '$[last]', 10) |
+-----------------------------------------+
| 10                                      |
+-----------------------------------------+
1 row in set (0.00 sec)

你可以使用 column->path，其中包含 JSON 列标识符和 JSON 路径表达式，作为 JSON_EXTRACT(column, path) 的同义词。更多信息请参见 14.17.3 节，“搜索 JSON 值的函数”。另请参见 对生成列进行索引以提供 JSON 列索引。You can use column->path with a JSON column identifier and JSON path expression as a synonym for JSON_EXTRACT(column, path). See Section 14.17.3, “Functions That Search JSON Values”, for more information. See also Indexing a Generated Column to Provide a JSON Column Index.

一些函数会接收一个已有的 JSON 文档，对其进行某种方式的修改，并返回修改后的文档。路径表达式指示在文档中进行更改的位置。例如，JSON_SET()、JSON_INSERT() 和 JSON_REPLACE() 函数都会接收一个 JSON 文档，以及一个或多个描述要修改文档位置和值的路径-值对。这些函数在处理文档中已存在和不存在的值时有所不同。Some functions take an existing JSON document, modify it in some way, and return the resulting modified document. Path expressions indicate where in the document to make changes. For example, the JSON_SET(), JSON_INSERT(), and JSON_REPLACE() functions each take a JSON document, plus one or more path-value pairs that describe where to modify the document and the values to use. The functions differ in how they handle existing and nonexisting values within the document.

请考虑这份文件：Consider this document:

mysql> SET @j = '["a", {"b": [true, false]}, [10, 20]]';

JSON_SET() 会替换已存在路径的值，并为不存在的路径添加值。JSON_SET() replaces values for paths that exist and adds values for paths that do not exist:.

mysql> SELECT JSON_SET(@j, '$[1].b[0]', 1, '$[2][2]', 2);
+--------------------------------------------+
| JSON_SET(@j, '$[1].b[0]', 1, '$[2][2]', 2) |
+--------------------------------------------+
| ["a", {"b": [1, false]}, [10, 20, 2]]      |
+--------------------------------------------+

在这种情况下，路径 $[1].b[0] 选择了一个已存在的值 (true)，该值将被路径参数之后的值 (1) 替换。路径 $[2][2] 不存在，因此对应的值 (2) 会被添加到路径 $[2] 选中的值中。In this case, the path $[1].b[0] selects an existing value (true), which is replaced with the value following the path argument (1). The path $[2][2] does not exist, so the corresponding value (2) is added to the value selected by $[2].

JSON_INSERT() 会添加新值，但不会替换已存在的值：JSON_INSERT() adds new values but does not replace existing values:

mysql> SELECT JSON_INSERT(@j, '$[1].b[0]', 1, '$[2][2]', 2);
+-----------------------------------------------+
| JSON_INSERT(@j, '$[1].b[0]', 1, '$[2][2]', 2) |
+-----------------------------------------------+
| ["a", {"b": [true, false]}, [10, 20, 2]]      |
+-----------------------------------------------+

JSON_REPLACE() 替换现有值并忽略新值：JSON_REPLACE() replaces existing values and ignores new values:

mysql> SELECT JSON_REPLACE(@j, '$[1].b[0]', 1, '$[2][2]', 2);
+------------------------------------------------+
| JSON_REPLACE(@j, '$[1].b[0]', 1, '$[2][2]', 2) |
+------------------------------------------------+
| ["a", {"b": [1, false]}, [10, 20]]             |
+------------------------------------------------+

路径-值对从左到右进行求值。通过求值一个对生成的文档成为下一个对进行求值时的新值。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_REMOVE() 接收一个 JSON 文档，以及一个或多个路径，这些路径指定要从文档中移除的值。返回值是原始文档减去由文档中存在的路径选择的值后的结果：JSON_REMOVE() takes a JSON document and one or more paths that specify values to be removed from the document. The return value is the original document minus the values selected by paths that exist within the document:

mysql> SELECT JSON_REMOVE(@j, '$[2]', '$[1].b[1]', '$[1].b[1]');
+---------------------------------------------------+
| JSON_REMOVE(@j, '$[2]', '$[1].b[1]', '$[1].b[1]') |
+---------------------------------------------------+
| ["a", {"b": [true]}]                              |
+---------------------------------------------------+

这些路径具有以下效果：The paths have these effects:

MySQL 支持的许多 JSON 函数，并在本手册的其他部分中描述（见 第 14.17 节，“JSON 函数”），需要路径表达式来识别 JSON 文档中的特定元素。路径由路径的作用域后跟一个或多个路径段组成。对于在 MySQL JSON 函数中使用的路径，作用域始终是被搜索或操作的文档，用前导 $ 字符表示。路径段由句点字符 (.) 分隔。数组中的单元格由 [N] 表示，其中 N 是非负整数。键的名称必须是双引号字符串或有效的 ECMAScript 标识符（参见 标识符名称和标识符，在 ECMAScript 语言规范 中）。路径表达式如同 JSON 文本一样，应使用 ascii、utf8mb3 或 utf8mb4 字符集编码。其他字符编码将隐式转换为 utf8mb4。完整语法如下所示：Many of the JSON functions supported by MySQL and described elsewhere in this Manual (see Section 14.17, “JSON Functions”) require a path expression in order to identify a specific element in a JSON document. A path consists of the path's scope followed by one or more path legs. For paths used in MySQL JSON functions, the scope is always the document being searched or otherwise operated on, represented by a leading $ character. Path legs are separated by period characters (.). Cells in arrays are represented by [N], where N is a non-negative integer. Names of keys must be double-quoted strings or valid ECMAScript identifiers (see Identifier Names and Identifiers, in the ECMAScript Language Specification). Path expressions, like JSON text, should be encoded using the ascii, utf8mb3, or utf8mb4 character set. Other character encodings are implicitly coerced to utf8mb4. The complete syntax is shown here:

pathExpression:
    scope[(pathLeg)*]

pathLeg:
    member | arrayLocation | doubleAsterisk

member:
    period ( keyName | asterisk )

arrayLocation:
    leftBracket ( nonNegativeInteger | asterisk ) rightBracket

keyName:
    ESIdentifier | doubleQuotedString

doubleAsterisk:
    '**'

period:
    '.'

asterisk:
    '*'

leftBracket:
    '['

rightBracket:
    ']'

如前所述，在 MySQL 中，路径的作用域始终是正在操作的文档，表示为 $。您可以在 JSON 路径表达式中使用 '$' 作为文档的同义词。As noted previously, in MySQL, the scope of the path is always the document being operated on, represented as $. You can use '$' as a synonym for the document in JSON path expressions.

通配符 * 和 ** 的使用方法如下：The wildcard * and ** tokens are used as follows:

有关路径语法示例，请参阅接受路径作为参数的各种 JSON 函数的说明，例如 JSON_CONTAINS_PATH()、JSON_SET() 和 JSON_REPLACE()。有关包含 * 和 ** 通配符使用的示例，请参阅 JSON_SEARCH() 函数的说明。For path syntax examples, see the descriptions of the various JSON functions that take paths as arguments, such as JSON_CONTAINS_PATH(), JSON_SET(), and JSON_REPLACE(). For examples which include the use of the * and ** wildcards, see the description of the JSON_SEARCH() function.

MySQL 还支持使用 to 关键字对 JSON 数组的子集进行范围表示（例如 $[2 to 10]），以及使用 last 关键字作为数组最右侧元素的同义词。有关更多信息和示例，请参阅 搜索和修改 JSON 值。MySQL also supports range notation for subsets of JSON arrays using the to keyword (such as $[2 to 10]), as well as the last keyword as a synonym for the rightmost element of an array. See Searching and Modifying JSON Values, for more information and examples.

JSON 值可以使用 =、<、<=、>、>=、<>、!= 和 <=> 运算符进行比较。JSON values can be compared using the =, <, <=, >, >=, <>, !=, and <=> operators.

以下比较运算符和函数尚不支持 JSON 值：The following comparison operators and functions are not yet supported with JSON values:

针对刚列出的比较运算符和函数的一个解决方法是将 JSON 值转换为 MySQL 的原生数字或字符串数据类型，以便它们具有一致的非 JSON 标量类型。A workaround for the comparison operators and functions just listed is to cast JSON values to a native MySQL numeric or string data type so they have a consistent non-JSON scalar type.

JSON 值的比较发生在两个层面。第一个比较层面基于所比较值的 JSON 类型。如果类型不同，比较结果仅由哪种类型具有较高优先级来决定。如果两个值具有相同的 JSON 类型，则使用特定类型的规则进行第二层比较。Comparison of JSON values takes place at two levels. The first level of comparison is based on the JSON types of the compared values. If the types differ, the comparison result is determined solely by which type has higher precedence. If the two values have the same JSON type, a second level of comparison occurs using type-specific rules.

下面的列表显示了 JSON 类型的优先级，从最高优先级到最低优先级。（类型名称是 JSON_TYPE() 函数返回的名称。）在同一行显示的类型具有相同的优先级。列表中较前的 JSON 类型的任何值与列表中较后的 JSON 类型的任何值进行比较时，都被认为更大。The following list shows the precedences of JSON types, from highest precedence to the lowest. (The type names are those returned by the JSON_TYPE() function.) Types shown together on a line have the same precedence. Any value having a JSON type listed earlier in the list compares greater than any value having a JSON type listed later in the list.

BLOB
BIT
OPAQUE
DATETIME
TIME
DATE
BOOLEAN
ARRAY
OBJECT
STRING
INTEGER, DOUBLE
NULL

对于具有相同优先级的 JSON 值，比较规则是类型特定的：For JSON values of the same precedence, the comparison rules are type specific:

对于任何 JSON 值与 SQL NULL 的比较，结果是 UNKNOWN。For comparison of any JSON value to SQL NULL, the result is UNKNOWN.

对于 JSON 值和非 JSON 值的比较，非 JSON 值将按照下表中的规则转换为 JSON，然后按照之前描述的方法进行比较。For comparison of JSON and non-JSON values, the non-JSON value is converted to JSON according to the rules in the following table, then the values compared as described previously.

下表总结了 MySQL 在 JSON 值与其他类型值之间转换时所遵循的规则：The following table provides a summary of the rules that MySQL follows when casting between JSON values and values of other types:

JSON 值的 ORDER BY 和 GROUP BY 根据以下原则工作：ORDER BY and GROUP BY for JSON values works according to these principles:

对于排序，将 JSON 标量转换为某种其他原生 MySQL 类型可能会有帮助。例如，如果名为 jdoc 的列包含具有 id 键和非负值的 JSON 对象成员，可以使用此表达式按 id 值进行排序：For sorting, it can be beneficial to cast a JSON scalar to some other native MySQL type. For example, if a column named jdoc contains JSON objects having a member consisting of an id key and a nonnegative value, use this expression to sort by id values:

ORDER BY CAST(JSON_EXTRACT(jdoc, '$.id') AS UNSIGNED)

如果恰好有一个生成列定义使用与 ORDER BY 中相同的表达式，MySQL 优化器会识别这一点，并考虑在查询执行计划中使用索引。请参见 第 10.3.11 节，“优化器使用生成列索引”。If there happens to be a generated column defined to use the same expression as in the ORDER BY, the MySQL optimizer recognizes that and considers using the index for the query execution plan. See Section 10.3.11, “Optimizer Use of Generated Column Indexes”.

对于 JSON 值的聚合，SQLNULL值像其他数据类型一样被忽略。非NULL值会被转换为数值类型并进行聚合，除了MIN(), MAX()，以及GROUP_CONCAT()将 JSON 值转换为数字应为数字标量产生有意义的结果，尽管（取决于值）可能会发生截断和精度丢失。将其他 JSON 值转换为数字可能不会产生有意义的结果。For aggregation of JSON values, SQL NULL values are ignored as for other data types. Non-NULL values are converted to a numeric type and aggregated, except for MIN(), MAX(), and GROUP_CONCAT(). The conversion to number should produce a meaningful result for JSON values that are numeric scalars, although (depending on the values) truncation and loss of precision may occur. Conversion to number of other JSON values may not produce a meaningful result.