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

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

A JSON object contains a set of key-value pairs separated by commas and enclosed within { and } characters:JSON对象包含一组由逗号分隔并包含在{和}字符内的键值对：

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

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. 如示例所示，JSON数组和对象可以包含字符串或数字的标量值、JSON null的文本或JSON 布尔值true或false的文本。Keys in JSON objects must be strings. JSON对象中的键必须是字符串。Temporal (date, time, or datetime) scalar values are also permitted:还允许使用时间（DATE、TIME或DATETIME时间）标量值：

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

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

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

You can also obtain JSON values from a number of functions supplied by MySQL for this purpose (see Section 12.18.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). 您还可以从MySQL为此提供的许多函数中获取JSON值（请参阅第12.18.2节，“创建JSON值的函数”），也可以使用CAST(value AS JSON)将其他类型的值强制转换为JSON类型（请参阅JSON值和非JSON值之间的转换）。The next several paragraphs describe how MySQL handles JSON values provided as input.接下来的几段将描述MySQL如何处理作为输入提供的JSON值。

In MySQL, JSON values are written as strings. 在MySQL中，JSON值被写成字符串。MySQL parses any string used in a context that requires a JSON value, and produces an error if it is not valid as JSON. MySQL解析在需要JSON值的上下文中使用的任何字符串，如果该字符串作为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:这些上下文包括将值插入到具有JSON数据类型的列中，并将参数传递给需要JSON值的函数（在MySQL JSON函数的文档中通常显示为json_doc或json_val），如下示例所示：

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

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

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

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

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

JSON_MERGE_PRESERVE() takes two or more JSON documents and returns the combined result:JSON_MERGE_PRESERVE()获取两个或多个JSON文档并返回组合结果：

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)

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

(MySQL 8.0.3 and later also support JSON_MERGE_PATCH(), which has somewhat different behavior. （MySQL 8.0.3及更高版本也支持JSON_MERGE_PATCH()，它的行为有些不同。See JSON_MERGE_PATCH() compared with JSON_MERGE_PRESERVE(), for information about the differences between these two functions.)有关这两个函数之间差异的信息，请参阅JSON_MERGE_PATCH()与JSON_MERGE_PRESERVE()的比较。）

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

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

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. 但是，用户定义的变量不能是JSON数据类型，因此尽管前面示例中的@j看起来像JSON值，并且具有与JSON值相同的字符集和排序规则，但它没有JSON数据类型。Instead, the result from JSON_OBJECT() is converted to a string when assigned to the variable.相反，JSON_OBJECT()的结果在赋值给变量时会转换为字符串。

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

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

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

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

Case sensitivity also applies to the JSON null, true, and false literals, which always must be written in lowercase:区分大小写也适用于JSON null、true和false文本，这些文本必须始终以小写形式编写：

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'.

Case sensitivity of the JSON literals differs from that of the SQL NULL, TRUE, and FALSE literals, which can be written in any lettercase:JSON文本的大小写敏感度与SQL NULL、TRUE和FALSE文本的大小写敏感度不同，SQL NULL、TRUE和FALSE文本可以用任何字母大小写：

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

Sometimes it may be necessary or desirable to insert quote characters (" or ') into a JSON document. 有时，在JSON文档中插入引号字符（"或'）可能是必要的或可取的。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:在本例中，假设您希望将一些JSON对象插入到使用以下SQL语句创建的表中，这些JSON对象包含表示有关MySQL的一些事实的字符串，每个字符串都与相应的关键字配对：

mysql> CREATE TABLE facts (sentence JSON);

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

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

One way to insert this as a JSON object into the facts table is to use the MySQL JSON_OBJECT() function. 将其作为JSON对象插入facts表的一种方法是使用MySQL JSON_object()函数。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\"."));

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:如果将值作为JSON对象文本插入，则工作方式不同，在这种情况下，必须使用双反斜杠转义序列，如下所示：

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

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. 使用双反斜杠可以防止MySQL执行转义序列处理，而是使其将字符串文本传递给存储引擎进行处理。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:以上述任一方式插入JSON对象后，您可以通过执行简单的SELECT来看到JSON列值中存在反斜杠，如下所示：

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

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

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

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:要使用mascot作为键显示所需的值，但不包括周围的引号或任何转义符，请使用内联路径运算符->>，如下：

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".'));

When a string is parsed and found to be a valid JSON document, it is also normalized. 当字符串被解析并发现是有效的JSON文档时，它也会被规范化。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:以下JSON_object()调用生成的对象值仅包含第二个key1元素，因为该键名出现在值的前面，如下所示：

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

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

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

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

In versions of MySQL prior to 8.0.3, members with keys that duplicated a key found earlier in the document were discarded. 在8.0.3之前的MySQL版本中，具有与文档中先前找到的键重复的键的成员被丢弃。The object value produced by the following JSON_OBJECT() call does not include the second key1 element because that key name occurs earlier in the value:以下JSON_object()调用生成的对象值不包含第二个key1元素，因为该键名出现在值的前面：

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

Prior to MySQL 8.0.3, this “first duplicate key wins” normalization was also performed when inserting values into JSON columns.在MySQL8.0.3之前，在JSON列中插入值时，也会执行这种“第一个重复的key-wins”规范化。

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": 17} |
| {"x": 17} |
+-----------+

MySQL also 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. MySQL还丢弃了原始JSON文档中键、值或元素之间的额外空白，并在显示时在每个逗号（,）或冒号（:）后留下（必要时插入）一个空格。This is done to enhance readibility.这样做是为了提高可读性。

MySQL functions that produce JSON values (see Section 12.18.2, “Functions That Create JSON Values”) always return normalized values.生成JSON值的MySQL函数（参见第12.18.2节，“创建JSON值的函数”）总是返回标准化值。

To make lookups more efficient, MySQL also sorts the keys of a JSON object. 为了提高查找效率，MySQL还对JSON对象的键进行排序。You should be aware that the result of this ordering is subject to change and not guaranteed to be consistent across releases.您应该知道，此订购的结果可能会发生更改，并且不能保证在各个版本中保持一致。

Merging JSON Values合并JSON值

Two merging algorithms are supported in MySQL 8.0.3 (and later), implemented by the functions JSON_MERGE_PRESERVE() and JSON_MERGE_PATCH(). MySQL 8.0.3(及更高版本）支持两种合并算法，由函数JSON_MERGE_PRESERVE()和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. 它们处理重复键的方式有所不同：JSON_MERGE_PRESERVE()保留重复键的值，而JSON_MERGE_PATCH()丢弃除最后一个值以外的所有值。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文档（即对象和数组）不同组合的合并。

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_PRESERVE()通过将稍后命名的数组连接到第一个数组的末尾来实现这一点。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. JSON_MERGE_PATCH()将每个参数视为一个由单个元素组成的数组（因此索引为0)，然后应用“最后重复键赢”逻辑仅选择最后一个参数。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]

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_PRESERVE()通过组合数组中该键的所有唯一值来处理具有相同键的多个对象；然后将此数组用作结果中该键的值。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. JSON_MERGE_PATCH()从左到右丢弃找到重复键的值，以便结果只包含该键的最后一个值。The following query illustrates the difference in the results for the duplicate key a:以下查询说明了重复键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}

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]). 在下面的语句中，每个参数都自动包装为一个数组（[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:然后合并这些结果以生成单个结果数组；与前两种情况一样，JSON_MERGE_PRESERVE()组合具有相同键的值，而JSON_MERGE_PATCH()丢弃除最后一个键以外的所有重复键的值，如下所示：

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() or JSON_MERGE_PATCH(), respectively), as can be seen in this example:数组和对象值是通过将对象自动包装为数组并通过组合值或根据合并函数的选择（分别是JSON_MERGE_PRESERVE()或JSON_MERGE_PATCH()）通过“最后一个重复键赢”来合并数组来合并的，如本例所示：

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

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

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

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

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:让$引用这个包含三个元素的JSON数组：

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

Then:则：

Because $[1] and $[2] evaluate to nonscalar values, they can be used as the basis for more-specific path expressions that select nested values. 因为$[1]和$[2]的计算结果是非标量值，所以它们可以用作选择嵌套值的更具体路径表达式的基础。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]                                                  |
+------------------------------------------------------------+

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:在以下示例中，路径$**.b计算为多个路径（$.a.b和$.c.b），并生成匹配路径值的数组：

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

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

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)

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. 语法是M to N，其中M和N分别是JSON数组中一系列元素的第一个和最后一个索引。N must be greater than M; M must be greater than or equal to 0. N必须大于M；M必须大于或等于0。Array elements are indexed beginning with 0.数组元素的索引从0开始。

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

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

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)

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

Some functions take an existing JSON document, modify it in some way, and return the resulting modified document. 有些函数获取现有的JSON文档，以某种方式对其进行修改，并返回修改后的文档。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. 例如，JSON_SET()、JSON_INSERT()和JSON_REPLACE()函数分别获取一个JSON文档，以及一个或多个描述在何处修改文档和要使用的值的路径值对。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() replaces values for paths that exist and adds values for paths that do not exist:.JSON_SET()替换现有路径的值，并为不存在的路径添加值：

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

In this case, the path $[1].b[0] selects an existing value (true), which is replaced with the value following the path argument (1). 在本例中，路径$[1].b[0]选择一个现有值（true），该值将替换为路径参数（1）后面的值。The path $[2][2] does not exist, so the corresponding value (2) is added to the value selected by $[2].路径$[2][2]不存在，因此相应的值（2）被添加到由$[2]选择的值中。

JSON_INSERT() adds new values but does not replace existing values:JSON_INSERT()添加新值但不替换现有值：

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() replaces existing values and ignores new values:JSON_REPLACE()替换现有值并忽略新值：

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() takes a JSON document and one or more paths that specify values to be removed from the document. JSON_REMOVE()接受一个JSON文档和一个或多个指定要从文档中删除的值的路径。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:路径具有以下效果：

Many of the JSON functions supported by MySQL and described elsewhere in this Manual (see Section 12.18, “JSON Functions”) require a path expression in order to identify a specific element in a JSON document. MySQL支持并在本手册其他地方介绍的许多JSON函数（参见第12.18节，“JSON函数”）都需要一个路径表达式来标识JSON文档中的特定元素。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. 对于MySQL JSON函数中使用的路径，作用域始终是正在搜索或以其他方式操作的文档，由前导$字符表示。Path legs are separated by period characters (.). 路径分支由句点字符（.）分隔。Cells in arrays are represented by [N], where N is a non-negative integer. 数组中的单元格由[N]表示，其中N是非负整数。Names of keys must be double-quoted strings or valid ECMAScript identifiers (see Identifier Names and Identifiers, in the ECMAScript Language Specification). 键的名称必须是双引号字符串或有效的ECMAScript标识符（请参阅ECMAScript语言规范文档中的标识符名称和标识符）。Path expressions, like JSON text, should be encoded using the ascii, utf8, or utf8mb4 character set. 路径表达式（如JSON文本）应使用ascii、utf8或utf8mb4字符集进行编码。Other character encodings are implicitly coerced to utf8mb4. 其他字符编码隐式强制为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:
    ']'

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

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

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(). 有关路径语法示例，请参阅以路径为参数的各种JSON函数的说明，例如JSON_CONTAINS_path()、JSON_SET()和JSON_REPLACE()。For examples which include the use of the * and ** wildcards, see the description of the JSON_SEARCH() function.有关使用*和**通配符的示例，请参阅JSON_SEARCH()函数的说明。

MySQL 8.0.2 and later 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. MySQL 8.0.2及更高版本还支持使用to关键字（例如$[2 to 10]）以及last关键字作为数组最右边元素同义词的JSON数组子集的范围表示法。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值尚不支持以下比较运算符和函数：

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值强制转换为原生MySQL数值或字符串数据类型，以便它们具有一致的非JSON标量类型。

Comparison of JSON values takes place at two levels. JSON值的比较在两个级别上进行。The first level of comparison is based on the JSON types of the compared values. 第一级比较基于比较值的JSON类型。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类型，则使用特定于类型的规则进行第二级比较。

The following list shows the precedences of JSON types, from highest precedence to the lowest. 下面的列表显示了JSON类型的优先级，从最高优先级到最低优先级。(The type names are those returned by the JSON_TYPE() function.) （类型名称是由JSON_type()函数返回的名称。）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.列表前面列出的JSON类型的任何值都比列表后面列出的JSON类型的任何值大。

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

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

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

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.为了比较JSON和非JSON值，根据下表中的规则将非JSON值转换为JSON，然后按照前面的描述比较值。

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

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

For sorting, it can be beneficial to cast a JSON scalar to some other native MySQL type. 对于排序，将JSON标量转换为其他原生MySQL类型可能是有益的。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:例如，如果名为jdoc的列包含具有由id键和非负值组成的成员的JSON对象，请使用此表达式按id值排序：

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

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. 如果碰巧有一个生成的列被定义为使用与ORDER BY中相同的表达式，MySQL优化器会识别出这一点，并考虑将索引用于查询执行计划。See Section 8.3.11, “Optimizer Use of Generated Column Indexes”.请参阅第8.3.11节，“优化器使用生成的列索引”。

For aggregation of JSON values, SQL NULL values are ignored as for other data types. 对于JSON值的聚合，SQLNULL值与其他数据类型一样被忽略。Non-NULL values are converted to a numeric type and aggregated, except for MIN(), MAX(), and GROUP_CONCAT(). 非NULL值将转换为数字类型并聚合，但MIN()、MAX()和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. 对数字的转换应该为数字标量的JSON值产生有意义的结果，尽管（取决于值）可能会发生截断和精度损失。Conversion to number of other JSON values may not produce a meaningful result.转换为其他JSON值的数目可能不会产生有意义的结果。