[JavaScript知识库]11.5 JSON 数据类型

MySQL 支持由 RFC 7159 定义的原生 JSON 数据类型，可以有效地访问 JSON（JavaScript 对象符号）文档中的数据。与将 JSON 格式的字符串存储在字符串列中相比，JSON 数据类型有以下优点：

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

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

注意

本讨论中使用JSON来明确表示 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 函数。

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

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

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

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

部分更新 JSON 值

在 MySQL 8.0 中，优化器可以对JSON列执行局部的就地更新，而不是删除旧文档并将新文档完整写入该列。这种优化可用于满足以下条件的更新：

• 被更新的列被声明为JSON。
• UPDATE语句使用JSON_SET()、JSON_REPLACE()或JSON_REMOVE()三个函数中的任意一个来更新列。对列值的直接赋值（例如，UPDATE mytable SET jcol = '{"a": 10， "b": 25}'）不能作为部分更新执行。
  
  在一个UPDATE语句中更新多个JSON列可以通过这种方式进行优化；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 null 字面量或 JSON 布尔 true 或 false 字面量。JSON 对象中的键必须是字符串。也允许使用时态（date、time 或 datetime）标量值：

["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 值的函数），以及使用CAST(value as JSON)将其他类型的值转换为JSON类型（参见 JSON 和非 JSON 值之间的转换）。下面几段描述 MySQL 如何处理输入提供的 JSON 值。

在 MySQL 中，JSON 值被写成字符串。MySQL 解析在需要 JSON 值的上下文中使用的任何字符串，如果它不是有效的 JSON，会产生一个错误，。这些上下文包括将一个值插入到JSON数据类型的列中，和将一个参数传递给期望 JSON 值的函数（通常在 MySQL JSON 函数的文档中显示json_doc或json_val），如下示例所示：

（1）尝试将一个值插入到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,'.

在这种错误消息中，“在位置N”的位置是基于 0 的，但应该被认为是一个值中实际发生问题的粗略指示。

（2）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。（对于ascii或utf8字符集的字符串，不需要转换，因为ascii和utf8是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数据类型。相反，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'.

JSON 字面值的大小写敏感性不同于 SQL 的NULL、TRUE和FALSE字面值，它们大小写都可以：

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

有时，在 JSON 文档中插入引号字符（"或'）可能是必要的或理想的。假设在这个例子中，你想要插入一些 JSON 对象，其中包含的字符串表示的句子，陈述了一些关于 MySQL 的事实，每对匹配一个适当的关键字，使用SQL语句创建到一个表中，如下所示：

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 对象后，通过一个简单的SELECT，你可以看到反斜杠出现在 JSON 列的值，像这样：

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

要使用mascot作为键来查找这个特定的句子，你可以使用列路径操作符->，如下所示：

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 值中的转义字符的影响，请参阅JSON_UNQUOTE()函数的描述。

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 解析器实现。(Bug #86866, Bug #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} |
+-----------+

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

生成 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 8.0.3 中重命名了）。JSON_MERGE()在 MySQL 8.0 中仍然支持作为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上，以该数组中位置N的值命名。数组的位置是以 0 开头的整数。如果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]指定从位置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)

语法是M to N，其中M和N分别是一个 JSON 数组中元素范围的第一个和最后一个索引。N必须大于M；M必须大于等于 0。数组元素的索引从 0 开始。

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

最右边的数组元素。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)

您可以使用column->path与 JSON 列标识符和 JSON 路径表达式作为JSON_EXTRACT(column，path)的同义词。更多信息请参见 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]选择一个已经存在的值(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]实例匹配b元素中的false并删除它。
• 第二个$[1].b[1]实例不匹配任何内容：该元素已经被删除，路径不再存在，因此没有任何作用。

JSON 路径语法

许多 MySQL 支持的 JSON 函数以及在本手册其他地方描述的 JSON 函数（见 12.18 JSON 函数）需要一个路径表达式来识别 JSON 文档中的特定元素。路径由路径的作用域和一个或多个路径分支组成。对于在 MySQL JSON 函数中使用的路径，范围总是被搜索的文档或其他操作，由一个前导$字符表示。路径腿由句点字符(.)分隔。数组中的单元由[N]表示，其中N为非负整数。键的名称必须是双引号字符串或有效的 ECMAScript 标识符（请参阅 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结尾的路径。前缀是可选的，后缀是必需的；换句话说，路径不能以**结束。另外，路径不能包含序列***。

关于路径语法示例，请参阅以路径作为参数的各种 JSON 函数的描述，例如JSON_CONTAINS_PATH()、JSON_SET()和JSON_REPLACE()。有关使用*和**通配符的示例，请参阅JSON_SEARCH()函数的描述。

MySQL 8.0 还支持使用to关键字的 JSON 数组子集的范围表示法（例如$[2 to 10]），以及将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
  
  与BLOB的规则相同。

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

• DATATIME
  
  表示较早时间点的值被安排在表示较晚时间点的值之前。如果两个值最初分别来自 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
  
  字符串按词法顺序排列在被比较的两个字符串的utf8mb4表示的前N个字节上，其中N是较短字符串的长度。如果两个字符串的前N个字节相同，则认为较短的字符串小于较长的字符串。
  
  例如："a" < "ab" < "b" < "bc"
  
  这种排序相当于排序规则为utf8mb4_bin的 SQL 字符串的排序。因为utf8mb4_bin是一个二进制排序，JSON 值的比较是区分大小写的："A" < "a"。

• INTEGER, DOUBLE
  
  JSON 值可以包含精确值和近似值。关于这些类型的数字的一般性讨论，请参见 9.1.2 数字字面值。

比较 MySQL 本地数字类型的规则在 12.3 表达式求值中的类型转换 中讨论过，但是在 JSON 值中比较数字的规则有些不同：

• 在两个分别使用本地 MySQL INT和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 转换规则

JSON 值的ORDER BY和GROUP BY根据以下原则工作：

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

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

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

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

JSON 值的聚合

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