11.5 JSON 数据类型

• 创建 JSON 值

• JSON 值的规范化、合并和自动包装

• 搜索和修改 JSON 值

• JSON 路径语法

• JSON值的比较和排序

• 在 JSON 和非 JSON 值之间转换

• JSON 值的聚合

  MySQL 支持原生JSON定义的数据类型RFC 7159 (opens new window)可以有效地访问 JSON（JavaScript Object Notation）文档中的数据。这JSON与将 JSON 格式的字符串存储在字符串列中相比，数据类型具有以下优势：

• 自动验证存储在JSON列。无效的文档会产生错误。

• 优化的存储格式。JSON 文档存储在JSON列被转换为允许快速读取文档元素的内部格式。当服务器稍后必须读取以这种二进制格式存储的 JSON 值时，不需要从文本表示中解析该值。二进制格式的结构使服务器能够直接通过键或数组索引查找子对象或嵌套值，而无需读取文档中它们之前或之后的所有值。

  MySQL 8.0 还支持JSON 合并补丁中定义的格式RFC 7396 (opens new window)， 使用JSON_MERGE_PATCH()功能。请参阅此功能的说明，以及JSON 值的规范化、合并和自动包装，以获取示例和更多信息。

笔记

本讨论使用JSON在monotype中具体表示JSON数据类型，在常规字体中使用“JSON”表示JSON数据。

存储一个文件所需的空间JSON文档与的大致相同长条或长文; 看见第11.7节，“数据类型存储要求”，以获取更多信息。重要的是要记住，存储在JSON列仅限于最大允许包数系统变量。（当服务器在内存中内部操作JSON值时，该值可能大于此值；当服务器存储JSON值时，该限制适用。）您可以使用JSON_存储_大小（）作用请注意JSON列、存储大小以及该函数返回的值是在对该列执行任何部分更新之前该列所使用的值（请参阅本节后面对JSON部分更新优化的讨论）。

在MySQL 8.0.13之前JSON列不能有非-无效的默认值。

以及JSON数据类型，一组SQL函数可用于启用对JSON值的操作，例如创建、操作和搜索。下面的讨论展示了这些操作的示例。有关各个函数的详细信息，请参阅第12.18节，“JSON函数”.

还提供了一组用于操作GeoJSON值的空间函数。看见第12.17.11节，“空间地理功能”.

JSON与其他二进制类型的列一样，列不直接编制索引；相反，您可以在生成的列上创建一个索引，从中提取标量值JSON柱看见索引生成的列以提供JSON列索引，以获取详细示例。

MySQL优化器还会在与JSON表达式匹配的虚拟列上查找兼容的索引。

在MySQL 8.0.17及更高版本中InnoDB存储引擎支持JSON数组上的多值索引。看见多值索引.

MySQL NDB Cluster 8.0支持JSONcolumns和MySQL JSON函数，包括在从JSON列作为无法为JSON柱最多3个JSON每列NDB表是受支持的。

JSON值的部分更新

在MySQL 8.0中，优化器可以对JSON列，而不是删除旧文档并将新文档全部写入列。可以对满足以下条件的更新执行此优化：

• 正在更新的列被声明为JSON.

• 这个使现代化语句使用这三个函数中的任意一个JSON_SET（）, JSON_REPLACE（）或JSON_REMOVE（）更新该列。列值的直接赋值（例如，更新mytable集合jcol='{a:10，“b:25}')无法作为部分更新执行。

  更新多个JSON单列中的列使现代化语句可以以这种方式进行优化；MySQL只能对使用上述三个函数更新其值的列执行部分更新。

• 输入列和目标列必须是同一列；声明，如更新mytable SET jcol1=JSON_SET（jcol2，$.a'，100）无法作为部分更新执行。

  只要输入列和目标列相同，更新可以以任何组合使用对上一项中列出的任何函数的嵌套调用。

• 所有更改都会将现有数组或对象值替换为新值，并且不会向父对象或数组添加任何新元素。

• 被替换的值必须至少与替换值一样大。换句话说，新值不能大于旧值。

  如果之前的部分更新为较大的值留出了足够的空间，则可能会出现此要求的例外情况。你可以使用这个功能JSON_存储_免费（）查看文件的任何部分更新释放了多少空间JSON柱

  这样的部分更新可以使用节省空间的紧凑格式写入二进制日志；这可以通过设置binlog_行_值_选项系统变量为部分_JSON.

  重要的是要区分一个数据库的部分更新JSON将行的部分更新写入二进制日志时存储在表中的列值。可以完全更新JSON列作为部分更新记录在二进制日志中。当上一个列表中的最后两个条件中的一个（或两个）不满足，但其他条件满足时，就会发生这种情况。

  另请参见binlog_行_值_选项.

  接下来的几节将提供有关JSON值的创建和操作的基本信息。

创建JSON值

JSON数组包含由逗号分隔并包含在[和]人物：

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

JSON对象包含一组由逗号分隔的键值对，并包含在{和}人物：

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

如示例所示，JSON数组和对象可以包含标量值，即字符串或数字、JSON空文本或JSON布尔真或假文本。JSON对象中的键必须是字符串。还允许使用时间（日期、时间或日期时间）标量值：

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

JSON数组元素和JSON对象键值中允许嵌套：

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

您还可以从MySQL为此提供的许多函数中获取JSON值（请参阅第12.18.2节，“创建JSON值的函数”)以及将其他类型的值强制转换为JSON打字投下(*价值*作为JSON）（见在JSON和非JSON值之间转换).接下来的几段描述了MySQL如何处理作为输入提供的JSON值。

在MySQL中，JSON值被写成字符串。MySQL解析在需要JSON值的上下文中使用的任何字符串，如果该字符串不是有效的JSON，则会生成错误。这些上下文包括在具有JSON数据类型，并将参数传递给需要JSON值的函数（通常显示为*json_文件或json_val*在MySQL JSON函数的文档中），如下示例所示：

• 正在尝试将值插入到JSON如果该值是有效的JSON值，则列成功，如果不是，则列失败：

  mysql> CREATE TABLE t1 (jdoc JSON);
  Query OK, 0 rows affected (0.20 sec)
  
  mysql> INSERT INTO t1 VALUES('{"key1": "value1", "key2": "value2"}');
  Query OK, 1 row affected (0.01 sec)
  
  mysql> INSERT INTO t1 VALUES('[1, 2,');
  ERROR 3140 (22032) at line 2: Invalid JSON text:
  "Invalid value." at position 6 in value (or column) '[1, 2,'.
  

  “at position”的位置*N*“在这种情况下，错误消息是基于0的，但应被视为是值中问题实际发生位置的粗略指示。

• 这个JSON_TYPE（）函数需要一个JSON参数，并尝试将其解析为JSON值。如果值有效，则返回该值的JSON类型，否则会产生错误：

  mysql> SELECT JSON_TYPE('["a", "b", 1]');
  +----------------------------+
  | JSON_TYPE('["a", "b", 1]') |
  +----------------------------+
  | ARRAY                      |
  +----------------------------+
  
  mysql> SELECT JSON_TYPE('"hello"');
  +----------------------+
  | JSON_TYPE('"hello"') |
  +----------------------+
  | STRING               |
  +----------------------+
  
  mysql> SELECT JSON_TYPE('hello');
  ERROR 3146 (22032): Invalid data type for JSON data in argument 1
  to function json_type; a JSON string or JSON type is required.
  

  MySQL使用utf8mb4字符集和utf8mb4_-bin整理。其他字符集中的字符串将转换为utf8mb4必要时。（适用于ascii码或utf8字符集，不需要转换，因为ascii码和utf8是utf8mb4.)

  作为使用文本字符串编写JSON值的替代方法，存在用于从组件元素合成JSON值的函数。JSON_数组（）获取值列表（可能为空），并返回包含这些值的JSON数组：

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

JSON_OBJECT（）获取键值对列表（可能为空），并返回包含这些键值对的JSON对象：

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

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)

有关合并规则的信息，请参见JSON值的规范化、合并和自动包装.

（MySQL 8.0.3及更高版本也支持JSON_MERGE_PATCH（），它有一些不同的行为。看见JSON_合并_PATCH（）与JSON的比较_合并_保存，了解这两个函数之间的差异。）

JSON值可以分配给用户定义的变量：

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

但是，用户定义的变量不能是JSON数据类型，所以@j在前面的示例中，它看起来像一个JSON值，并且与JSON值具有相同的字符集和排序规则不拥有JSON数据类型。相反，结果来自JSON_OBJECT（）当分配给变量时，将转换为字符串。

通过转换JSON值生成的字符串的字符集为utf8mb4以及utf8mb4_-bin:

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

因为utf8mb4_-bin是一种二进制排序规则，JSON值的比较区分大小写。

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

区分大小写也适用于JSON无效的, 符合事实的和错误的文字，必须始终用小写字母书写：

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无效的, 符合事实的和错误的文字，可以用任何大小写：

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

有时可能需要或希望插入引号字符("或')转换为JSON文档。假设在本例中，您希望将一些JSON对象插入到使用以下SQL语句创建的表中，这些JSON对象包含表示有关MySQL的一些事实的字符串，每个事实都与适当的关键字配对：

mysql> CREATE TABLE facts (sentence JSON);

在这些关键词句子对中，有这样一个：

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

将其作为JSON对象插入事实表是使用MySQL的JSON_OBJECT（）作用在这种情况下，必须使用反斜杠转义每个引号字符，如下所示：

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

如果将该值作为JSON对象文本插入，则其工作方式不同，在这种情况下，必须使用双反斜杠转义序列，如下所示：

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

使用双反斜杠可以防止MySQL执行转义序列处理，而是使其将字符串文本传递给存储引擎进行处理。在以上述任何一种方式插入JSON对象后，通过执行简单的选择，就像这样：

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

查找这个特殊的句子吉祥物作为键，可以使用列路径操作符->，如图所示：

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

这使得反斜杠和周围的引号保持不变。要显示所需的值，请使用吉祥物作为键，但不包括周围的引号或任何转义符，请使用内联路径操作符->>，就像这样：

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

笔记

如果没有反斜杠逃脱服务器SQL模式已启用。如果设置了此模式，则可以使用一个反斜杠而不是两个反斜杠来插入JSON对象文字，并保留反斜杠。如果你使用JSON_OBJECT（）函数在执行插入操作并设置此模式时，必须交替使用单引号和双引号，如下所示：

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

请参见对JSON_UNQUOTE（）函数获取有关此模式对JSON值中转义字符的影响的更多信息。

JSON值的规范化、合并和自动包装

当字符串被解析并发现是有效的JSON文档时，它也会被规范化。这意味着，如果成员的密钥与文档中稍后找到的密钥重复，并且从左到右读取，则该成员将被丢弃。对象值由以下项生成：JSON_OBJECT（）电话只包括第二个关键1元素，因为该键名出现在值的前面，如下所示：

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

将值插入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]} |
+------------------+

这种“最后一次复制密钥获胜”行为是由RFC 7159 (opens new window)并由大多数JavaScript解析器实现。（错误#86866，错误#26369555）

在8.0.3之前的MySQL版本中，具有与文档中先前找到的密钥重复的密钥的成员被丢弃。对象值由以下项生成：JSON_OBJECT（）电话不包括第二个关键1元素，因为该键名出现在值的前面：

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

在MySQL 8.0.3之前，在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": 17} |
| {"x": 17} |
+-----------+

MySQL还丢弃了原始JSON文档中键、值或元素之间的额外空格，并在每个逗号后留下（或在必要时插入）一个空格(,)还是结肠(:)当显示它时。这样做是为了提高可读性。

产生JSON值的MySQL函数（参见第12.18.2节，“创建JSON值的函数”)始终返回标准化值。

为了提高查找效率，MySQL还对JSON对象的键进行排序。您应该知道，此订购的结果可能会发生更改，并且不能保证在各个版本中保持一致.

合并JSON值

MySQL 8.0.3（及更高版本）支持两种合并算法，由函数实现JSON_MERGE_PRESERVE（）和JSON_MERGE_PATCH（）。它们处理重复密钥的方式不同：JSON_MERGE_PRESERVE（）保留重复关键点的值，而JSON_MERGE_PATCH（）放弃除最后一个值以外的所有值。接下来的几段将解释这两个函数如何处理JSON文档（即对象和数组）的不同组合的合并。

笔记

JSON_MERGE_PRESERVE（）和JSON_MERGE（）在以前版本的MySQL中找到的函数（在MySQL 8.0.3中重命名）。JSON_MERGE（）仍支持作为的别名JSON_MERGE_PRESERVE（）在MySQL 8.0中，但已被弃用，并可能在未来的版本中删除。

**合并数组。**在组合多个数组的上下文中，这些数组将合并为单个数组。JSON_MERGE_PRESERVE（）通过将稍后命名的数组连接到第一个数组的末尾来实现这一点。JSON_MERGE_PATCH（）将每个参数视为由单个元素组成的数组（因此索引为0），然后应用“last duplicate key wins”逻辑仅选择最后一个参数。您可以比较此查询显示的结果：

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

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（）放弃除最后一个键外的所有重复键的值，如下所示：

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

通过将对象自动包装为一个数组，并通过组合值或根据合并功能的选择通过“最后一次复制关键点赢”合并数组，可以合并数组和对象值(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"}

搜索和修改JSON值

JSON路径表达式在JSON文档中选择一个值。

路径表达式对于提取JSON文档的一部分或修改JSON文档的函数非常有用，可以指定在该文档中操作的位置。例如，以下查询从JSON文档中提取具有名称关键：

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

路径语法使用前导字符$字符表示考虑中的JSON文档，可选后跟选择器，依次指示文档的更具体部分：

• 后跟键名的句点用给定键命名对象中的成员。如果不带引号的名称在路径表达式中不合法（例如，如果包含空格），则必须在双引号内指定键名。

• [*N*]附在*路径它选择一个数组，并将位置处的值命名N在数组中。数组位置是从零开始的整数。如果路径不选择数组值，路径[0]计算结果与路径*:

  mysql> SELECT JSON_SET('"x"', '$[0]', 'a');
  +------------------------------+
  | JSON_SET('"x"', '$[0]', 'a') |
  +------------------------------+
  | "a"                          |
  +------------------------------+
  1 row in set (0.00 sec)
  

• [*M*到*N*]指定从位置处的值开始的数组值的子集或范围*M，并以位置处的值结束N*.

  最后的作为最右边数组元素索引的同义词受支持。还支持数组元素的相对寻址。如果*路径不选择数组值，路径[最后的]计算结果与路径*，如本节下文所示（参见最右边的数组元素).

• 路径可以包含*或**通配符：

  • .[*]计算为JSON对象中所有成员的值。

  • [*]计算为JSON数组中所有元素的值。

  • *前缀****后缀*计算以命名前缀开头并以命名后缀结尾的所有路径。

• 文档中不存在的路径（计算为不存在的数据）计算为无效的.

  允许$请使用以下三个元素引用此JSON数组：

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

然后：

• $[0]评估为3..

• $[1]评估为{“a”：[5,6]，“b”：10}.

• $[2]评估为[99, 100].

• $[3]评估为无效的（它指的是第四个数组元素，它不存在）。

  因为$[1]和$[2]计算为非标度值时，它们可以用作选择嵌套值的更具体路径表达式的基础。例如：

• $[1].A.评估为[5, 6].

• $[1].a[1]评估为6..

• $[1].b评估为10.

• $[2][0]评估为99.

  如前所述，如果未加引号的键名在路径表达式中不合法，则必须对命名键的路径组件加引号。允许$请参考此值：

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

两个键都包含空格，并且必须引用：

• $“一条鱼”评估为鲨鱼.

• $“一只鸟”评估为麻雀.

  使用通配符的路径计算为可以包含多个值的数组：

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)并生成匹配路径值的数组：

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

**范围从JSON数组。**您可以使用带有到关键字指定JSON数组的子集。例如$[1至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)

语法是*M*到*N*哪里*M和N分别是JSON数组中一系列元素的第一个和最后一个索引。N必须大于M*; *M*必须大于或等于0。数组元素的索引从0开始。

可以在支持通配符的上下文中使用范围。

**最右边的数组元素。**那个最后的关键字支持作为数组中最后一个元素的索引的同义词。形式的表达最后-*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)

如果根据非数组的值计算路径，则计算结果与将该值包装在单个元素数组中的结果相同：

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

你可以用*柱*->*路径*使用JSON列标识符和JSON路径表达式作为JSON_摘录(*柱*, *路径*)看见第12.18.3节，“搜索JSON值的函数”，以获取更多信息。另见索引生成的列以提供JSON列索引.

有些函数获取现有的JSON文档，以某种方式对其进行修改，然后返回修改后的文档。路径表达式指示要在文档中的何处进行更改。例如JSON_SET（）, JSON_INSERT（）和JSON_REPLACE（）每个函数都获取一个JSON文档，外加一个或多个路径值对，这些路径值对描述了在何处修改文档和要使用的值。这些函数在处理文档中现有值和不存在值的方式上有所不同。

考虑这份文件：

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

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

在本例中，路径$[1].b[0]选择现有值(符合事实的)，替换为路径参数后面的值(1.).这条路$[2][2]不存在，因此相应的值(2.)被添加到所选的值中$[2].

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（）替换现有值并忽略新值：

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

路径值对从左到右求值。通过对一对进行求值而生成的文档将成为下一对求值的新值。

JSON_REMOVE（）获取JSON文档和一个或多个路径，这些路径指定要从文档中删除的值。返回值是原始文档减去文档中存在的路径选择的值：

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

路径具有以下效果：

• $[2]比赛[10, 20]并将其移除。

• 第一次$[1].b[1]比赛错误的在b元素并将其删除。

• 第二次$[1].b[1]不匹配：该元素已被删除，路径不再存在，并且无效。

JSON路径语法

MySQL支持的许多JSON函数以及本手册其他地方描述的函数（请参阅第12.18节，“JSON函数”)需要一个路径表达式来标识JSON文档中的特定元素。路径由路径范围和一个或多个路径分支组成。对于MySQL JSON函数中使用的路径，作用域始终是正在搜索或以其他方式操作的文档，由前导字符表示$性格路径分支由句点字符分隔(.).数组中的单元格由[*N*]哪里*N是一个非负整数。键的名称必须是双引号字符串或有效的ECMAScript标识符（请参阅标识符名称和标识符 (opens new window)，在ECMAScript语言规范*).Path表达式，如JSON文本，应使用ascii码, utf8或utf8mb4字符集。其他字符编码被隐式强制为utf8mb4.完整的语法如下所示：

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路径表达式中文档的同义词。

笔记

一些实现支持JSON路径范围的列引用；MySQL 8.0不支持这些。

通配符*和**代币的使用方式如下：

• .*表示对象中所有成员的值。

• [*]表示数组中所有单元格的值。

• [*前缀*]***后缀*表示以开头的所有路径*前缀最后是后缀*. *前缀是可选的，而后缀*是必需的；换句话说，一条路可能不会以**.

  此外，路径可能不包含序列***.

  有关路径语法示例，请参阅以路径为参数的各种JSON函数的描述，例如JSON_包含_路径（）, JSON_SET（）和JSON_REPLACE（）。例如，包括使用*和**通配符，请参见JSON_SEARCH（）作用

  MySQL 8.0还支持使用到关键词（例如$[2至10])，以及最后的关键字作为数组最右边元素的同义词。看见搜索和修改JSON值，以获取更多信息和示例。

JSON值的比较和排序

可以使用=, <, <=, >, >=, <>, !=和<=>接线员。

JSON值尚不支持以下比较运算符和函数：

• 之间

• 在（）

• 最伟大的

• 至少

  刚刚列出的比较运算符和函数的一个变通方法是将JSON值强制转换为原生MySQL数字或字符串数据类型，以便它们具有一致的非JSON标量类型。

  JSON值的比较在两个级别进行。第一级比较基于比较值的JSON类型。如果类型不同，比较结果仅取决于哪个类型具有更高的优先级。如果两个值具有相同的JSON类型，则使用特定于类型的规则进行第二级比较。

  下面的列表显示了JSON类型的优先级，从最高优先级到最低优先级。（类型名称是由JSON_TYPE（）功能。）在一行中同时显示的类型具有相同的优先级。列表前面列出的任何JSON类型的值都比列表后面列出的任何JSON类型的值大。

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

对于相同优先级的JSON值，比较规则是特定于类型的：

• 斑点

  第一个*N比较两个值的字节，其中N是较短值中的字节数。如果第一个N*两个值的字节相同，较短的值在较长的值之前排序。

• 一点

  和你的规则一样斑点.

• 不透明的

  和你的规则一样斑点. 不透明的值是未分类为其他类型之一的值。

• 约会时间

  表示较早时间点的值在表示较晚时间点的值之前排序。如果两个值最初来自MySQL约会时间和时间戳类型，如果它们代表相同的时间点，则它们是相等的。

• 时间

  两个时间值中较小的时间值排序在较大的时间值之前。

• 日期

  较早的日期在较近的日期之前订购。

• 大堆

  如果两个JSON数组的长度相同，且数组中相应位置的值相等，则它们是相等的。

  如果数组不相等，它们的顺序由第一个有差异的位置的元素决定。首先对该位置值较小的数组进行排序。如果较短数组的所有值都等于较长数组中的相应值，则先对较短数组进行排序。

  例子：

  [] < ["a"] < ["ab"] < ["ab", "cd", "ef"] < ["ab", "ef"]
  

• 布尔值

  JSON假文本小于JSON真文本。

• 对象

  如果两个JSON对象具有相同的键集，并且每个键在两个对象中具有相同的值，则它们是相等的。

  例子：

  {"a": 1, "b": 2} = {"b": 2, "a": 1}
  

  两个不相等的对象的顺序是不确定的，但具有确定性。

• 一串

  字符串按词汇顺序排列在第一行*N字节数utf8mb4表示正在比较的两个字符串，其中N是较短字符串的长度。如果第一个N*两个字符串的字节相同，短字符串被认为比长字符串小。

  例子：

  "a" < "ab" < "b" < "bc"
  

  这种排序相当于使用排序规则对SQL字符串进行排序utf8mb4_-bin因为utf8mb4_-bin是二进制排序规则，JSON值的比较区分大小写：

  "A" < "a"
  

• 整数, 双重的

  JSON值可以包含精确值和近似值。有关这些类型数字的一般性讨论，请参阅第9.1.2节，“数字文字”.

  中讨论了比较原生MySQL数值类型的规则第12.3节，“表达式计算中的类型转换”，但在JSON值中比较数字的规则有所不同：

  • 在比较两个使用本机MySQL的列时国际的和双重的已知所有比较都涉及一个整数和一个double，因此所有行的整数都转换为double。也就是说，精确的数值被转换为近似的数值。

  • 另一方面，如果查询比较两个包含数字的JSON列，则无法提前知道数字是整数还是双精度。为了在所有行中提供最一致的行为，MySQL将近似值转换为精确值。由此产生的顺序是一致的，不会失去精确数值的精度。例如，给定标量9223372036854775805、9223372036854775806、9223372036854775807和9.223372036854776e18，顺序如下：

    9223372036854775805 < 9223372036854775806 < 9223372036854775807
    < 9.223372036854776e18 = 9223372036854776000 < 9223372036854776001
    

    如果JSON比较使用非JSON数字比较规则，可能会出现顺序不一致的情况。通常的MySQL数字比较规则会产生以下顺序：

  • 整数比较：

    9223372036854775805 < 9223372036854775806 < 9223372036854775807
    

    （9.223372036854776e18未定义）

  • 双重比较：

    9223372036854775805 = 9223372036854775806 = 9223372036854775807 = 9.223372036854776e18
    

  用于将任何JSON值与SQL进行比较无效的，结果是未知的.

  为了比较JSON和非JSON值，根据下表中的规则将非JSON值转换为JSON，然后按照前面所述比较值。

在JSON和非JSON值之间转换

下表总结了MySQL在JSON值和其他类型的值之间转换时遵循的规则：

表11.3 JSON转换规则

其他类型	CAST（其他类型为JSON）	强制转换（JSON作为其他类型）
JSON	不变	不变
utf8字符类型(utf8mb4, utf8, ascii码)	字符串被解析为JSON值。	JSON值被序列化为utf8mb4一串
其他字符类型	其他字符编码隐式转换为utf8mb4并按utf8字符类型所述进行处理。	JSON值被序列化为utf8mb4字符串，然后转换为另一个字符编码。结果可能没有意义。
无效的	结果是无效的JSON类型的值。	不适用。
几何图形类型	通过调用ST_AsGeoJSON（）.	非法操作。解决方法：传递投下(*json_val*作为字符）到ST_GeomFromGeoJSON（）.
所有其他类型	结果生成一个由单个标量值组成的JSON文档。	如果JSON文档包含目标类型的单个标量值，并且该标量值可以转换为目标类型，则成功。否则，返回无效的并发出警告。

订购人和分组对于JSON，值的工作原理如下：

• 标量JSON值的排序使用与前面讨论中相同的规则。

• 对于升序排序，SQL无效的所有JSON值之前的顺序，包括JSON null文本；对于降序排序，SQL无效的在所有JSON值之后排序，包括JSON null文本。

• JSON值的排序键由最大排序长度系统变量，因此仅在第一个最大排序长度字节的比较是相等的。

• 当前不支持对非标度值进行排序，并出现警告。

  对于排序，将JSON标量强制转换为其他原生MySQL类型可能是有益的。例如，如果一个名为jdoc包含JSON对象，其成员由身份证件键和非负值，使用此表达式进行排序身份证件价值观：

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

如果碰巧有一个生成的列被定义为使用与订购人，MySQL优化器会意识到这一点，并考虑将索引用于查询执行计划。看见第8.3.11节，“优化器使用生成的列索引”.

JSON值的聚合

对于JSON值的聚合，SQL无效的与其他数据类型一样，忽略值。非-无效的值被转换为数字类型并聚合，除了MIN（）, 麦克斯（）和组_CONCAT（）。对于数值标量的JSON值，转换为数字应该会产生有意义的结果，尽管（取决于值）可能会发生截断和精度损失。转换为其他JSON值的数量可能不会产生有意义的结果。