MySQL 8.0 参考手册  / 第 11 章数据类型  /  11.5 JSON数据类型

11.5 JSON数据类型

从 MySQL 5.7.8 开始,MySQL 支持RFC 7159JSON 定义的本机数据类型 ,可以高效访问 JSON(JavaScript 对象表示法)文档中的数据。与在字符串列中存储 JSON 格式的字符串相比,该数据类型具有以下优势: JSON

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

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

笔记

本讨论使用JSONmonotype 来具体表示 JSON 数据类型,使用常规字体的JSON 来泛指 JSON 数据。

存储文档所需的空间与or JSON大致相同;有关详细信息,请参阅 第 11.7 节,“数据类型存储要求”。请务必记住,存储在列中的任何 JSON 文档的大小都限于系统变量的值。(当服务器在内存中内部操作 JSON 值时,它可以大于此值;该限制适用于服务器存储它时。) LONGBLOBLONGTEXTJSONmax_allowed_packet

JSON不能有非NULL默认值。

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

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

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

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

MySQL NDB Cluster 7.5(7.5.2 及更高版本)支持 JSON列和 MySQL JSON 函数,包括在从列生成的 JSON列上创建索引作为无法索引JSON列的解决方法。每个表 最多支持 3 JSON列 。NDB

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

创建 JSON 值

JSON 数组包含以逗号分隔并包含在[] 字符中的值列表:

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

一个 JSON 对象包含一组以逗号分隔并包含在{}字符中的键值对:

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

如示例所示,JSON 数组和对象可以包含标量值(字符串或数字)、JSON 空文字或 JSON 布尔值 true 或 false 文字。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 值的函数(通常在 MySQL JSON 函数的文档中显示为 json_docjson_val),如以下示例所示:

  • 如果值是有效的 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.

utf8mb4MySQL 使用字符集和 utf8mb4_bin排序 规则处理 JSON 上下文中使用的 字符串。其他字符集中的字符串根据需要转换utf8mb4为。(对于asciiutf8字符集中的字符串,不需要转换,因为asciiutf8是 的子集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()获取两个或多个 JSON 文档并返回组合结果:

mysql> SELECT JSON_MERGE('["a", 1]', '{"key": "value"}');
+--------------------------------------------+
| JSON_MERGE('["a", 1]', '{"key": "value"}') |
+--------------------------------------------+
| ["a", 1, {"key": "value"}]                 |
+--------------------------------------------+

有关合并规则的信息,请参阅 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 nulltruefalse文字,它们始终必须以小写字母书写:

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 NULLTRUEFALSE文字,后者可以用任何字母大小写:

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\"."} |
+---------------------------------------------------------+

要查找此特定句子 employing mascot作为键,您可以使用列路径运算符 ->,如下所示:

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

这使反斜杠以及周围的引号保持不变。要使用 as key 显示所需的值 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": 1, "key2": "abc"}                           |
+------------------------------------------------------+
笔记

这种对重复键的首键获胜”处理与RFC 7159不一致 。这是 MySQL 5.7 中的一个已知问题,已在 MySQL 8.0 中修复。(缺陷 #86866,缺陷 #26369555)

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

生成 JSON 值的 MySQL 函数(参见 第 12.18.2 节,“创建 JSON 值的函数”)总是返回规范化的值。

为了提高查找效率,它还会对 JSON 对象的键进行排序。您应该知道,此排序的结果可能会发生变化,并且不能保证在不同版本之间保持一致

合并 JSON 值

在组合多个数组的上下文中,通过将稍后命名的数组连接到第一个数组的末尾,将数组合并为一个数组。在以下示例中, JSON_MERGE()将其参数合并到一个数组中:

mysql> SELECT JSON_MERGE('[1, 2]', '["a", "b"]', '[true, false]');
+-----------------------------------------------------+
| JSON_MERGE('[1, 2]', '["a", "b"]', '[true, false]') |
+-----------------------------------------------------+
| [1, 2, "a", "b", true, false]                       |
+-----------------------------------------------------+

将值插入 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> SELECT JSON_MERGE('{"a": 1, "b": 2}', '{"c": 3, "a": 4}');
+----------------------------------------------------+
| JSON_MERGE('{"a": 1, "b": 2}', '{"c": 3, "a": 4}') |
+----------------------------------------------------+
| {"a": [1, 4], "b": 2, "c": 3}                      |
+----------------------------------------------------+

在需要数组值的上下文中使用的非数组值是自动包装的:该值由[]字符包围以将其转换为数组。在以下语句中,每个参数都自动包装为一个数组 ( [1], [2])。然后将它们合并以生成单个结果数组:

mysql> SELECT JSON_MERGE('1', '2');
+----------------------+
| JSON_MERGE('1', '2') |
+----------------------+
| [1, 2]               |
+----------------------+

通过将对象自动包装为数组并合并两个数组来合并数组和对象值:

mysql> SELECT JSON_MERGE('[10, 20]', '{"a": "x", "b": "y"}');
+------------------------------------------------+
| JSON_MERGE('[10, 20]', '{"a": "x", "b": "y"}') |
+------------------------------------------------+
| [10, 20, {"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)
  • 路径可以包含***通配符:

    • .[*]计算 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]                                                  |
+---------------------------------------------------------+

在 MySQL 5.7.9 及更高版本中,您可以使用 with JSON 列标识符和 JSON 路径表达式作为 . 有关详细信息,请参阅 第 12.18.3 节,“搜索 JSON 值的函数”。另请参阅索引生成的列以提供 JSON 列索引column->pathJSON_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]Nhttp://www.ecma-international.org/ecma-262/5.1/#sec-7.6). 路径表达式(如 JSON 文本)应使用 asciiutf8utf8mb4字符集进行编码。其他字符编码被隐式强制为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 不支持这些。

通配符*** 标记的使用如下:

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

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

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

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

有关路径语法示例,请参阅各种将路径作为参数的 JSON 函数的描述,例如 JSON_CONTAINS_PATH()JSON_SET()JSON_REPLACE()。有关包括使用***通配符的示例,请参阅 JSON_SEARCH()函数说明。

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 值,比较规则是类型特定的:

  • BLOB

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

  • BIT

    与 相同的规则BLOB

  • OPAQUE

    与 相同的规则BLOBOPAQUE值是未分类为其他类型之一的值。

  • DATETIME

    表示较早时间点的值排在表示较晚时间点的值之前。如果两个值最初分别来自 MySQL DATETIMETIMESTAMP 类型,那么如果它们表示相同的时间点,则它们是相等的。

  • TIME

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

  • DATE

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

  • ARRAY

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

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

    例子:

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

    JSON 假文字小于 JSON 真文字。

  • OBJECT

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

    例子:

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

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

  • STRING

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

    例子:

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

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

    "A" < "a"
  • INTEGER,DOUBLE

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

    比较本机 MySQL 数字类型的规则在第 12.3 节,“表达式评估中的类型转换”中讨论,但是比较 JSON 值中的数字的规则有些不同:

    • INT在分别使用本机 MySQL和 数字类型 的两个列之间的比较中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 值与 SQLNULL的比较,结果是UNKNOWN.

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

在 JSON 和非 JSON 值之间转换

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

表 11.3 JSON 转换规则

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

ORDER BY对于 JSON 值,GROUP BY根据以下原则工作:

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

  • 对于升序排序,SQLNULL在所有 JSON 值之前排序,包括 JSON 空文字;对于降序排序,SQLNULL在所有 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.10 节,“生成列索引的优化器使用”

JSON 值的聚合

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