[ACDU翻译] MySQL 11.5 JSON 数据类型

• 创建 JSON 值
• JSON 值的规范化、合并和自动包装
• 搜索和修改 JSON 值
• JSON 路径语法
• JSON值的比较和排序
• 在 JSON 和非 JSON 值之间转换
• JSON 值的聚合

MySQL 支持JSON由RFC 7159定义的本机数据类型，可以有效访问 JSON（JavaScript Object Notation）文档中的数据。JSON与将 JSON 格式的字符串存储在字符串列中相比，该 数据类型具有以下优势：

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

MySQL 8.0 还支持RFC 7396 中定义的JSON Merge Patch格式 ，使用该 函数。有关示例和更多信息，请参阅此函数的描述以及 JSON 值的规范化、合并和自动包装。 JSON_MERGE_PATCH()

笔记

本讨论使用JSONmonotype 来专门表示 JSON 数据类型， 使用常规字体“ JSON ”来表示 JSON 数据。

来存储所需的空间JSON文档是大致相同LONGBLOB或 LONGTEXT; 有关详细信息，请参阅 第 11.7 节，“数据类型存储要求”。请务必记住，存储在JSON列中的任何 JSON 文档的大小都受限于max_allowed_packet系统变量的值。（当服务器在内存中内部操作 JSON 值时，它可以大于此值；限制适用于服务器存储它。）您可以使用该JSON_STORAGE_SIZE()函数获取存储 JSON 文档所需的空间量 ；请注意，对于JSON 列，存储大小以及由此函数返回的值是该列在可能已对其执行任何部分更新之前使用的值（请参阅本节后面对 JSON 部分更新优化的讨论）。

在 MySQL 8.0.13 之前，JSON列不能有非NULL默认值。

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

还提供了一组用于对 GeoJSON 值进行操作的空间函数。请参阅第 12.17.11 节，“空间 GeoJSON 函数”。

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

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

在 MySQL 8.0.17 及更高版本中，InnoDB 存储引擎支持 JSON 数组上的多值索引。请参阅 多值索引。

MySQL NDB Cluster 8.0 支持JSON列和 MySQL JSON 函数，包括在从列生成的JSON列上创建索引作为无法索引JSON列的解决方法。JSON每个NDB表最多支持3列 。

JSON 值的部分更新

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

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

• 该UPDATE语句使用任何的三个功能 JSON_SET()， JSON_REPLACE()或 JSON_REMOVE()更新列。UPDATE mytable SET jcol = '{"a": 10, "b": 25}'不能将列值的直接分配（例如 ）作为部分更新执行。

  可以通过这种方式优化JSON单个UPDATE语句 中多个列的更新；MySQL 只能对那些使用刚刚列出的三个函数更新其值的列执行部分更新。

• 输入列和目标列必须是同一列；诸如UPDATE mytable SET jcol1 = JSON_SET(jcol2, '$.a', 100)不能作为部分更新执行的语句。

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

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

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

  当先前的部分更新为较大的值留下足够的空间时，可能会出现此要求的例外情况。您可以使用该函数 JSON_STORAGE_FREE()查看JSON列的任何部分更新释放了多少空间 。

这种部分更新可以使用节省空间的紧凑格式写入二进制日志；这可以通过将binlog_row_value_options 系统变量设置为 来启用PARTIAL_JSON。

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

另见 的描述 binlog_row_value_options。

接下来的几节提供有关 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 值）。接下来的几段描述了 MySQL 如何处理作为输入提供的 JSON 值。 CAST(*value* AS JSON)

在 MySQL 中，JSON 值被写为字符串。MySQL 解析在需要 JSON 值的上下文中使用的任何字符串，如果它作为 JSON 无效，则会产生错误。这些上下文包括将值插入到具有JSON数据类型的列中， 并将参数传递给需要 JSON 值的函数（通常显示为*json_doc*或 *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排序规则处理 JSON 上下文中使用的 字符串 。其他字符集中的字符串utf8mb4根据需要转换为。（对于asciior utf8字符集中的字符串，不需要转换，因为asciiandutf8是 的子集utf8mb4。）

作为使用文字字符串编写 JSON 值的替代方法，存在用于从组件元素组合 JSON 值的函数。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() 获取一个（可能为空的）键值对列表并返回一个包含这些对的 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_MERGE_PATCH() 与 JSON_MERGE_PRESERVE() 的比较，了解有关这两个函数之间差异的信息。）

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

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

但是，用户定义的变量不能是 JSON数据类型，所以虽然 @j在前面的例子中看起来像一个JSON值，并且具有相同的字符集并归类为JSON值，但它不具有 JSON数据类型。相反，结果 from 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 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'.

从该SQL的JSON文字不同的情况下的灵敏度NULL，TRUE和 FALSE文字，它可以在任何大小写被写成：

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

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

mysql> CREATE TABLE facts (sentence JSON);

在这些关键字句对中，有一个：

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

将其作为 JSON 对象插入到facts表中的一种方法 是使用 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 对象后，您可以通过执行简单的 来看到反斜杠出现在 JSON 列值中SELECT，如下所示：

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

要查找这个mascot用作键的特定句子 ，您可以使用 column-path 运算符 ->，如下所示：

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

这使反斜杠以及周围的引号保持不变。要使用mascot作为键显示所需的值 ，但不包括周围的引号或任何转义符->>，请使用内联路径运算符 ，如下所示：

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

笔记

如果NO_BACKSLASH_ESCAPES启用了服务器 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()调用生成的对象值仅包含第二个key1元素，因为该键名出现在值的前面，如下所示：

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建议的， 并且由大多数 JavaScript 解析器实现。（错误 #86866，错误 #26369555）

在 8.0.3 之前的 MySQL 版本中，具有与文档中较早发现的键重复的键的成员被丢弃。以下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"}                           |
+------------------------------------------------------+

在 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 中更名）。 在 MySQL 8.0 中JSON_MERGE()仍然支持作为别名JSON_MERGE_PRESERVE()，但已被弃用，并且在未来的版本中可能会被删除。

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

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 文档中提取具有name键的成员的值 ：

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

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

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

• [*N*]附加到*path选择一个数组的 a 名称的值在N* 数组中的位置。数组位置是从零开始的整数。如果*path不选择数组值，则path*[0] 的计算结果与 相同 path：

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

• [*M* to *N*]指定数组值的子集或范围，从 position 处的值开始，到 position 处的值 M结束N。

  last支持作为最右边数组元素的索引的同义词。还支持数组元素的相对寻址。如果 path不选择数组值，则path[last] 的计算结果与 相同*path*，如本节后面所示（请参阅 最右边的数组元素）。

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

  • .[*] 计算 JSON 对象中所有成员的值。
  • [*] 计算 JSON 数组中所有元素的值。
  • *prefix****suffix* 计算以命名前缀开头并以命名后缀结尾的所有路径。

• 文档中不存在的路径（评估为不存在的数据）评估为NULL。

让我们$用三个元素来引用这个 JSON 数组：

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

然后：

• $[0]评估为3。
• $[1]评估为{"a": [5, 6], "b": 10}。
• $[2]评估为[99, 100]。
• $[3]计算结果为NULL （它指的是第四个数组元素，它不存在）。

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

• $[1].a评估为[5, 6]。
• $[1].a[1]评估为 6。
• $[1].b评估为 10。
• $[2][0]评估为 99。

如前所述，如果未引用的键名在路径表达式中不合法，则必须引用命名键的路径组件。让我们$参考这个值：

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

这两个键都包含一个空格，并且必须用引号引起来：

• $."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 数组的范围。 您可以使用带to关键字的范围来指定 JSON 数组的子集。例如，$[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)

语法是, where 和 are, 分别是 JSON 数组中一系列元素的第一个和最后一个索引。必须大于； 必须大于或等于 0。数组元素从 0 开始索引。 *M* to *N*MNNMM

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

最右边的数组元素。 所述last关键字被支撑为最后一个元素的阵列中的索引的同义词。形式的表达式可用于相对寻址，并在范围定义内，如下所示： 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)

如果路径是针对非数组的值计算的，则计算结果与将值包装在单元素数组中的结果相同：

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

您可以将 JSON 列标识符和 JSON 路径表达式用作 . 有关更多信息，请参阅 第 12.18.3 节，“搜索 JSON 值的函数”。另请参阅索引生成的列以提供 JSON 列索引。 *column*->*path*JSON_EXTRACT(*column*, *path*)

一些函数采用现有的 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]选择一个现有值 ( true)，该值将替换为路径参数 ( 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]相匹配 false的b元素并将其删除。
• 不$[1].b[1]匹配的第二个实例：该元素已被删除，路径不再存在，并且没有效果。

JSON 路径语法

MySQL 支持并在本手册其他地方描述的许多 JSON 函数（请参阅第 12.18 节，“JSON 函数”）需要路径表达式来标识 JSON 文档中的特定元素。路径由路径的范围和一个或多个路径分支组成。对于 MySQL JSON 函数中使用的路径，范围始终是正在搜索或以其他方式操作的文档，由前导$字符表示 。路径腿由句点字符 ( .)分隔。数组中的单元格由 表示 ，其中 是一个非负整数。键名必须是双引号字符串或有效的 ECMAScript 标识符（请参阅 [*N*]N标识符名称和标识符，在 ECMAScript 语言规范中）。路径表达式，如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 不支持这些。

通配符*和** 标记的使用如下：

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

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

• [*prefix*]***suffix* 表示以 开头*prefix*和结尾的 所有路径 suffix。 *prefix*是可选的，while *suffix*是必需的；换句话说，路径可能不会以**.

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

对于路径语法的例子，见该采取的路径作为参数，例如各种JSON功能的说明 JSON_CONTAINS_PATH()， JSON_SET()和 JSON_REPLACE()。有关使用 the*和 **通配符的示例，请参阅JSON_SEARCH()函数说明 。

MySQL 8.0 还支持使用to关键字（例如 $[2 to 10]）的 JSON 数组子集的范围表示法，以及将 last关键字作为数组最右边元素的同义词。有关更多信息和示例，请参阅搜索和修改 JSON 值。

JSON值的比较和排序

JSON值可以使用进行比较 =， <， <=， >， >=， <>， !=，和 <=> 运营商。

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

• BETWEEN
• IN()
• GREATEST()
• LEAST()

刚刚列出的比较运算符和函数的解决方法是将 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 值，比较规则是特定于类型的：

• BLOB

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

• BIT

  与 for 相同的规则BLOB。

• OPAQUE

  与 for 相同的规则BLOB。 OPAQUE值是未被分类为其他类型之一的值。

• DATETIME

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

• TIME

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

• DATE

  较早的日期在较近的日期之前排序。

• ARRAY

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

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

  例子：

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

• BOOLEAN

  JSON false 文字小于 JSON true 文字。

• OBJECT

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

  例子：

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

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

• STRING

  字符串在被比较的两个字符串表示的第一个*N字节 上按词法排序 utf8mb4，其中N是较短字符串的长度。如果N*两个字符串的第一个 字节相同，则认为较短的字符串小于较长的字符串。

  例子：

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

  此排序等同于使用 collation 对 SQL 字符串进行排序utf8mb4_bin。因为 utf8mb4_bin是二进制排序规则，所以 JSON 值的比较区分大小写：

  "A" < "a"
  

• INTEGER, DOUBLE

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

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

  • 在分别使用原生 MySQLINT和 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 的比较NULL，结果是UNKNOWN.

对于 JSON 和非 JSON 值的比较，非 JSON 值根据下表中的规则转换为 JSON，然后按前面所述进行值比较。

在 JSON 和非 JSON 值之间转换

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

表 11.3 JSON 转换规则

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

ORDER BY并GROUP BY为JSON值根据这些原则，工作原理：

• 标量 JSON 值的排序使用与前面讨论中相同的规则。
• 对于升序排序，SQL 排序NULL在所有 JSON 值之前，包括 JSON 空字面量；对于降序排序，SQL 对NULL所有 JSON 值进行排序，包括 JSON 空文字。
• JSON 值的排序键受max_sort_length系统变量值的约束 ，因此仅在第一个max_sort_length字节之后不同的键 比较相等。
• 当前不支持对非标量值进行排序，并且会出现警告。

对于排序，将 JSON 标量转换为其他一些本机 MySQL 类型可能是有益的。例如，如果名为的列 jdoc包含 JSON 对象，其成员由id键和非负值组成，请使用此表达式按id 值排序：

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

如果碰巧有一个生成的列定义为使用与 中相同的表达式ORDER BY，MySQL 优化器会识别出这一点并考虑将索引用于查询执行计划。请参阅 第 8.3.11 节，“优化器使用生成的列索引”。

JSON 值的聚合

对于 JSON 值的聚合，SQLNULL 值与其他数据类型一样被忽略。非NULL值被转换为数字类型和聚合，除 MIN()， MAX()和 GROUP_CONCAT()。对于数字标量的 JSON 值，转换为数字应该会产生有意义的结果，尽管（取决于值）可能会发生截断和精度损失。转换为其他 JSON 值的数量可能不会产生有意义的结果。