mysql 5.7 laravel json类型数据相关操作

2018年10月16日18:14:21

官方文档中文翻译版 

原文：https://dev.mysql.com/doc/refman/5.7/en/json.html

最后有部分实例和一个小总结

11.6 JSON数据类型

• 创建JSON值

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

• 搜索和修改JSON值

• JSON值的比较和排序

• 在JSON和非JSON值之间转换

• JSON值的聚合

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

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

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

注意

此讨论使用JSONmonotype来指定JSON数据类型和 常规字体中的“ JSON ”以指示一般的JSON数据。

存储JSON文档所需的空间与LONGBLOB或 大致相同LONGTEXT; 有关更多信息，请参见 第11.8节“数据类型存储要求”。请务必记住，JSON列中存储的任何JSON文档的大小都限制为max_allowed_packet系统变量的值。(当服务器在内存中内部操作JSON值时，它可能大于此值;当服务器存储时，该限制适用。)

一JSON列不能有默认值。

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

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

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

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

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

接下来的几节提供了有关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.16.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,'.

  为位置“ 在位置 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根据需要进行转换。(对于在串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() 获取两个或多个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数据类型。相反，结果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'.

的字面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对象后，您可以通过执行简单操作看到反斜杠存在于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": 1, "key2": "abc"}                           |
+------------------------------------------------------+

注意

重复密钥的 这种“ 第一关键胜利 ”处理与RFC 7159不一致 。这是MySQL 5.7中的已知问题，已在MySQL 8.0中修复。(Bug＃86866，Bug＃26369555)

MySQL还会丢弃原始JSON文档中的键，值或元素之间的额外空格。为了使查找更有效，它还对JSON对象的键进行排序。 您应该知道，此排序的结果可能会发生变化，并且不保证在各个版本中保持一致。

生成JSON值的MySQL函数(请参见 第12.16.2节“创建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]附加到a path，选择一个数组命名数组中位置的值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及更高版本中，您可以使用 JSON列标识符和JSON路径表达式作为同义词 。有关更多信息，请参见 第12.16.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)，该值将替换为path参数(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值的比较和排序

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值是未归类为其他类型之一的值。

• DATETIME

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

• TIME

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

• DATE

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

• ARRAY

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

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

  例：

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

• BOOLEAN

  JSON false文字小于JSON true literal。

• OBJECT

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

  例：

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

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

• STRING

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

  例：

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

  此排序等同于具有排序规则的SQL字符串的排序utf8mb4_bin。因为 utf8mb4_bin是二进制排序规则，JSON值的比较区分大小写：

  "A"< "a"

• INTEGER， DOUBLE

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

  第12.2节“表达式求值中的类型转换” 中讨论了比较本机MySQL数值类型的规则，但是比较JSON值中的数字的规则有所不同：

  • 在分别使用本机MySQL INT和 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进行比较NULL，结果是UNKNOWN。

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

在JSON和非JSON值之间转换

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

表11.3 JSON转换规则

其他类型	CAST(其他类型AS 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 null文字; 对于降序排序，NULL所有JSON值之后的SQL 顺序，包括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值的聚合，SQL NULL 值将与其他数据类型一样被忽略。非NULL值被转换为数字类型和聚合，除 MIN()， MAX()和 GROUP_CONCAT()。转换为数字应该为数字标量的JSON值产生有意义的结果，尽管(取决于值)可能会发生截断和精度损失。转换为其他JSON值的数量可能不会产生有意义的结果。

11.6 JSON数据类型

• 创建JSON值

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

• 搜索和修改JSON值

• JSON值的比较和排序

• 在JSON和非JSON值之间转换

• JSON值的聚合

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

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

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

注意

此讨论使用JSONmonotype来指定JSON数据类型和 常规字体中的“ JSON ”以指示一般的JSON数据。

存储JSON文档所需的空间与LONGBLOB或 大致相同LONGTEXT; 有关更多信息，请参见 第11.8节“数据类型存储要求”。请务必记住，JSON列中存储的任何JSON文档的大小都限制为max_allowed_packet系统变量的值。(当服务器在内存中内部操作JSON值时，它可能大于此值;当服务器存储时，该限制适用。)

一JSON列不能有默认值。

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

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

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

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

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

接下来的几节提供了有关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.16.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,'.

  为位置“ 在位置 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根据需要进行转换。(对于在串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() 获取两个或多个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数据类型。相反，结果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'.

的字面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对象后，您可以通过执行简单操作看到反斜杠存在于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": 1, "key2": "abc"}                           |
+------------------------------------------------------+

注意

重复密钥的 这种“ 第一关键胜利 ”处理与RFC 7159不一致 。这是MySQL 5.7中的已知问题，已在MySQL 8.0中修复。(Bug＃86866，Bug＃26369555)

MySQL还会丢弃原始JSON文档中的键，值或元素之间的额外空格。为了使查找更有效，它还对JSON对象的键进行排序。 您应该知道，此排序的结果可能会发生变化，并且不保证在各个版本中保持一致。

生成JSON值的MySQL函数(请参见 第12.16.2节“创建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]附加到a path，选择一个数组命名数组中位置的值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及更高版本中，您可以使用 JSON列标识符和JSON路径表达式作为同义词 。有关更多信息，请参见 第12.16.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)，该值将替换为path参数(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值的比较和排序

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值是未归类为其他类型之一的值。

• DATETIME

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

• TIME

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

• DATE

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

• ARRAY

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

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

  例：

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

• BOOLEAN

  JSON false文字小于JSON true literal。

• OBJECT

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

  例：

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

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

• STRING

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

  例：

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

  此排序等同于具有排序规则的SQL字符串的排序utf8mb4_bin。因为 utf8mb4_bin是二进制排序规则，JSON值的比较区分大小写：

  "A"< "a"

• INTEGER， DOUBLE

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

  第12.2节“表达式求值中的类型转换” 中讨论了比较本机MySQL数值类型的规则，但是比较JSON值中的数字的规则有所不同：

  • 在分别使用本机MySQL INT和 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进行比较NULL，结果是UNKNOWN。

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

在JSON和非JSON值之间转换

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

表11.3 JSON转换规则

其他类型	CAST(其他类型AS 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 null文字; 对于降序排序，NULL所有JSON值之后的SQL 顺序，包括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值的聚合，SQL NULL 值将与其他数据类型一样被忽略。非NULL值被转换为数字类型和聚合，除 MIN()， MAX()和 GROUP_CONCAT()。转换为数字应该为数字标量的JSON值产生有意义的结果，尽管(取决于值)可能会发生截断和精度损失。转换为其他JSON值的数量可能不会产生有意义的结果。

总结与实例：