MySQL 之 JSON 支持（三）—— JSON 函数

官方文档链接：14.17 JSON Functions

本章描述的函数对 JSON 值执行操作。有关 JSON 数据类型的讨论以及显示如何使用这些函数的其它示例，参阅“第13.5节 JSON 数据类型”。

对于使用 JSON 参数的函数，如果参数不是有效的 JSON 值，则会发生错误。解析为 JSON 的参数由 json_doc 表示；不解析 val 指示的参数。

返回 JSON 值的函数总是对这些值执行规范化（参阅“JSON 值的规范化、合并和自动封装”），然后对它们进行排序。排序的确切结果随时可能发生变化，因此不要依赖它来保证不同版本之间的一致性。

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

一、JSON 函数参考

表 14.22 JSON 函数

名称	描述	引入	已弃用
->	评估路径后返回JSON列的值；等效于JSON_EXTRACT()。
->>	评估路径后返回JSON列的值，并且去掉扩在结果外面的引号；等效于JSON_UNQUOTE(JSON_EXTRACT())。
JSON_ARRAY()	创建 JSON 数组
JSON_ARRAY_APPEND()	将数据追加到JSON文档
JSON_ARRAY_INSERT()	插入JSON数组
JSON_CONTAINS()	JSON文档在路径中是否包含特定对象
JSON_CONTAINS_PATH()	JSON文档中是否包含指定路径
JSON_DEPTH()	JSON文档的最大深度
JSON_EXTRACT()	从JSON文档返回数据
JSON_INSERT()	将数据插入JSON文档
JSON_KEYS()	JSON文档中的键数组
JSON_LENGTH()	JSON文档中的元素个数
JSON_MERGE()	合并JSON文档，并保留重复键。JSON_MERGE_PRESERVE()的同义词，已弃用		Yes
JSON_MERGE_PATCH()	合并JSON文档，替换重复键的值
JSON_MERGE_PRESERVE()	合并JSON文档，保留重复键的值
JSON_OBJECT()	创建JSON对象
JSON_OVERLAPS()	比较两个JSON文档，如果它们有任何共同的键值对或数组元素，则返回TRUE （1），否则返回FALSE （0）	8.0.17
JSON_PRETTY()	以可读格式打印JSON文档
JSON_QUOTE()	把JSON文档用引号括起来
JSON_REMOVE()	从JSON文档中删除数据
JSON_REPLACE()	替换JSON文档中的值
JSON_SCHEMA_VALID()	根据JSON模式验证JSON文档；如果文档符合模式，则返回TRUE/1；负责返回FALSE/0	8.0.17
JSON_SCHEMA_VALIDATION_REPORT()	根据JSON模式验证JSON文档；返回JSON格式的验证结果报告，包括成功或失败以及失败原因	8.0.17
JSON_SEARCH()	返回JSON文档中给定字符串的路径。
JSON_SET()	插入JSON数据
JSON_STORAGE_FREE()	JSON列值部分更新后的二进制表示的空闲空间大小
JSON_STORAGE_SIZE()	用于存储JSON文档的二进制表示的空间大小
JSON_TABLE()	将JSON表达式中的数据作为关系表返回
JSON_TYPE()	JSON值的数据类型
JSON_UNQUOTE()	去掉JSON值外面的引号
JSON_VALID()	JSON值是否有效
JSON_VALUE()	在提供的路径指向的位置从JSON文档中提取值；将该值作为VARCHAR(512)或指定类型返回	8.0.21
MEMBER OF()	如果第一个操作数与作为第二个操作数传递的JSON数组的任何元素匹配，则返回true（1），否则返回false（0）	8.0.17

MySQL 支持两个聚合 JSON 函数 JSON_ARRAYAGG() 和 JSON_OBJECTAGG()。有关它们的功能描述，参见“第14.19节聚合函数”。

MySQL 还支持使用 JSON_PRETTY() 函数以易读的格式“漂亮地打印” JSON 值。可以分别使用 JSON_STORAGE_SIZE() 和 JSON_STORAGE_FREE() 来查看给定 JSON 值占用了多少存储空间，以及还有多少空间用于其它存储。有关这些函数的完整描述，参阅“第14.17.8节 JSON 工具函数”。

二、创建 JSON 值的函数

本节中列出的函数将组件元素组成 JSON 值。

1. JSON_ARRAY([val[, val] ...])

评估一个值列表（可能为空），并返回一个包含这些值的 JSON 数组。

代码语言：javascript复制

mysql> SELECT JSON_ARRAY(1, "abc", NULL, TRUE, CURTIME());
 --------------------------------------------- 
| JSON_ARRAY(1, "abc", NULL, TRUE, CURTIME()) |
 --------------------------------------------- 
| [1, "abc", null, true, "11:30:24.000000"]   |
 ---------------------------------------------

2. JSON_OBJECT([key, val[, key, val] ...])

评估键值对的列表（可能为空），并返回包含这些对的 JSON 对象。如果任何键名为 NULL 或参数数为奇数，则会发生错误。

代码语言：javascript复制

mysql> SELECT JSON_OBJECT('id', 87, 'name', 'carrot');
 ----------------------------------------- 
| JSON_OBJECT('id', 87, 'name', 'carrot') |
 ----------------------------------------- 
| {"id": 87, "name": "carrot"}            |
 -----------------------------------------

3. JSON_QUOTE(string)

通过用双引号字符封装字符串，并转义内部引号和其它字符，将其作为 JSON 值，然后将结果作为 utf8mb4 字符串返回。如果参数为 NULL，则返回 NULL。此函数通常用于生成一个有效的 JSON 字符串文本，以便包含在 JSON 文档中。根据“表 14.23 JSON_UNQUOTE() 特殊字符转义序列”中所示的转义序列，某些特殊字符使用反斜杠进行转义。

代码语言：javascript复制

mysql> SELECT JSON_QUOTE('null'), JSON_QUOTE('"null"');
 -------------------- ---------------------- 
| JSON_QUOTE('null') | JSON_QUOTE('"null"') |
 -------------------- ---------------------- 
| "null"             | ""null""           |
 -------------------- ---------------------- 
mysql> SELECT JSON_QUOTE('[1, 2, 3]');
 ------------------------- 
| JSON_QUOTE('[1, 2, 3]') |
 ------------------------- 
| "[1, 2, 3]"             |
 -------------------------

还可以通过使用 CAST(value AS JSON) 将其它类型的值强制转换为 JSON 类型来获得 JSON 值；有关更多信息，参阅 JSON 和非 JSON 值之间的转换。

有两个生成 JSON 值的聚合函数。JSON_ARRAYAGG() 将结果集作为单个 JSON 数组返回，JSON_OBJECTAGG() 将结果集作为单个 JSON 对象返回。有关更多信息，参阅“第14.19节聚合函数”。

三、搜索 JSON 值的函数

本节中的函数对 JSON 值执行搜索或比较操作，以从中提取数据，报告数据是否存在于 JSON 值中的某个位置，或报告 JSON 值的数据所在路径。这里还记录了 MEMBER OF() 操作符。

1. JSON_CONTAINS(target, candidate[, path])

通过返回 1 或 0 指示给定的候选 JSON 文档是否包含在目标 JSON 文档中。如果提供了路径参数，指示是否在目标的特定路径中找到该候选 JSON 文档。如果任何参数为 NULL，或者路径参数未标识目标文档的某段，则返回 NULL。如果目标或候选者不是有效的 JSON 文档，或者路径参数不是有效的路径表达式或包含 * 或 ** 通配符，则会发生错误。

若要仅检查路径中是否存在任何数据，改用 JSON_CONTAINS_PATH()。

以下规则定义了该函数的判断控制：

候选标量包含在目标标量中，当且仅当它们可比较且相等。如果两个标量值具有相同的 JSON_TYPE() 类型，则它们是可比较的，类型为 INTEGER 和 DECIMAL 的值也可以相互比较。
候选数组包含在目标数组中，当且仅当候选中的每个元素都包含在目标的某些元素中。
候选非数组包含在目标数组中，当且仅当候选包含在目标的某个元素中。
候选对象被包含在目标对象中，当且仅当对于候选中的每个键，在目标中存在具有相同名称的键，并且与候选键相关联的值被包含在与同名目标键相关联的值中。

除此之外，候选值将不包含在目标文档中。

从 MySQL 8.0.17 开始，可以使用多值索引优化 InnoDB 表上使用 JSON_CONTAINS() 的查询；有关详细信息，参阅多值索引。

代码语言：javascript复制

mysql> SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}';
mysql> SET @j2 = '1';
mysql> SELECT JSON_CONTAINS(@j, @j2, '$.a');
 ------------------------------- 
| JSON_CONTAINS(@j, @j2, '$.a') |
 ------------------------------- 
|                             1 |
 ------------------------------- 
mysql> SELECT JSON_CONTAINS(@j, @j2, '$.b');
 ------------------------------- 
| JSON_CONTAINS(@j, @j2, '$.b') |
 ------------------------------- 
|                             0 |
 ------------------------------- 

mysql> SET @j2 = '{"d": 4}';
mysql> SELECT JSON_CONTAINS(@j, @j2, '$.a');
 ------------------------------- 
| JSON_CONTAINS(@j, @j2, '$.a') |
 ------------------------------- 
|                             0 |
 ------------------------------- 
mysql> SELECT JSON_CONTAINS(@j, @j2, '$.c');
 ------------------------------- 
| JSON_CONTAINS(@j, @j2, '$.c') |
 ------------------------------- 
|                             1 |
 -------------------------------

2. JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...)

返回 0 或 1 以指示 JSON 文档中是否包含给定路径。如果任何参数为 NULL，则返回 NULL。如果 json_doc 参数不是有效的 JSON 文档，任何路径参数不是有效的路径表达式，或者 one_or_all 不是 “one” 或 “all”，则会发生错误。

若要检查路径中的特定值，改用 JSON_CONTAINS()。

如果文档中不存在指定的路径，则返回值为 0。否则，返回值取决于 one_or_all 参数：

'one'：如果文档中至少存在一个路径，则为 1，否则为 0。
'all'：如果文档中存在所有路径，则为 1，否则为 0。

代码语言：javascript复制

mysql> SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}';
mysql> SELECT JSON_CONTAINS_PATH(@j, 'one', '$.a', '$.e');
 --------------------------------------------- 
| JSON_CONTAINS_PATH(@j, 'one', '$.a', '$.e') |
 --------------------------------------------- 
|                                           1 |
 --------------------------------------------- 
mysql> SELECT JSON_CONTAINS_PATH(@j, 'all', '$.a', '$.e');
 --------------------------------------------- 
| JSON_CONTAINS_PATH(@j, 'all', '$.a', '$.e') |
 --------------------------------------------- 
|                                           0 |
 --------------------------------------------- 
mysql> SELECT JSON_CONTAINS_PATH(@j, 'one', '$.c.d');
 ---------------------------------------- 
| JSON_CONTAINS_PATH(@j, 'one', '$.c.d') |
 ---------------------------------------- 
|                                      1 |
 ---------------------------------------- 
mysql> SELECT JSON_CONTAINS_PATH(@j, 'one', '$.a.d');
 ---------------------------------------- 
| JSON_CONTAINS_PATH(@j, 'one', '$.a.d') |
 ---------------------------------------- 
|                                      0 |
 ----------------------------------------

3. JSON_EXTRACT(json_doc, path[, path] ...)

返回 JSON 文档中的数据，该数据是从路径参数匹配的文档部分中选择的。如果任何参数为 NULL 或在文档路径中没有找到值，则返回 NULL。如果 json_doc 参数不是有效的 JSON 文档，或者任何路径参数不是有效的路径表达式，则会发生错误。

返回值由路径参数匹配的所有值组成。如果这些参数可能返回多个值，则匹配的值将自动封装为数组，顺序与生成它们的路径相对应。否则，返回值为单个匹配值。

代码语言：javascript复制

mysql> SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]');
 -------------------------------------------- 
| JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]') |
 -------------------------------------------- 
| 20                                         |
 -------------------------------------------- 
mysql> SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]', '$[0]');
 ---------------------------------------------------- 
| JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]', '$[0]') |
 ---------------------------------------------------- 
| [20, 10]                                           |
 ---------------------------------------------------- 
mysql> SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[2][*]');
 ----------------------------------------------- 
| JSON_EXTRACT('[10, 20, [30, 40]]', '$[2][*]') |
 ----------------------------------------------- 
| [30, 40]                                      |
 -----------------------------------------------

MySQL 支持 -> 操作符作为该函数的简写，与两个参数一起使用，其中左边是 JSON 列标识符（而不是表达式），右边是列中要匹配的 JSON 路径。

4. column->path

当与两个参数一起使用时，-> 操作符符充当 JSON_EXTRACT() 函数的别名，左边是列标识符，右边是用于 JSON 文档（列值）评估的 JSON 路径（字符串文本）。无论列引用出现在 SQL 语句中的哪个位置，都可以使用此类表达式来代替它们。此处显示的两个 SELECT 语句产生相同的输出：

代码语言：javascript复制

mysql> SELECT c, JSON_EXTRACT(c, "$.id"), g
     > FROM jemp
     > WHERE JSON_EXTRACT(c, "$.id") > 1
     > ORDER BY JSON_EXTRACT(c, "$.name");
 ------------------------------- ----------- ------ 
| c                             | c->"$.id" | g    |
 ------------------------------- ----------- ------ 
| {"id": "3", "name": "Barney"} | "3"       |    3 |
| {"id": "4", "name": "Betty"}  | "4"       |    4 |
| {"id": "2", "name": "Wilma"}  | "2"       |    2 |
 ------------------------------- ----------- ------ 
3 rows in set (0.00 sec)

mysql> SELECT c, c->"$.id", g
     > FROM jemp
     > WHERE c->"$.id" > 1
     > ORDER BY c->"$.name";
 ------------------------------- ----------- ------ 
| c                             | c->"$.id" | g    |
 ------------------------------- ----------- ------ 
| {"id": "3", "name": "Barney"} | "3"       |    3 |
| {"id": "4", "name": "Betty"}  | "4"       |    4 |
| {"id": "2", "name": "Wilma"}  | "2"       |    2 |
 ------------------------------- ----------- ------ 
3 rows in set (0.00 sec)

此功能不限于 SELECT，如下所示：

代码语言：javascript复制

mysql> ALTER TABLE jemp ADD COLUMN n INT;
Query OK, 0 rows affected (0.68 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> UPDATE jemp SET n=1 WHERE c->"$.id" = "4";
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT c, c->"$.id", g, n
     > FROM jemp
     > WHERE JSON_EXTRACT(c, "$.id") > 1
     > ORDER BY c->"$.name";
 ------------------------------- ----------- ------ ------ 
| c                             | c->"$.id" | g    | n    |
 ------------------------------- ----------- ------ ------ 
| {"id": "3", "name": "Barney"} | "3"       |    3 | NULL |
| {"id": "4", "name": "Betty"}  | "4"       |    4 |    1 |
| {"id": "2", "name": "Wilma"}  | "2"       |    2 | NULL |
 ------------------------------- ----------- ------ ------ 
3 rows in set (0.00 sec)

mysql> DELETE FROM jemp WHERE c->"$.id" = "4";
Query OK, 1 row affected (0.04 sec)

mysql> SELECT c, c->"$.id", g, n
     > FROM jemp
     > WHERE JSON_EXTRACT(c, "$.id") > 1
     > ORDER BY c->"$.name";
 ------------------------------- ----------- ------ ------ 
| c                             | c->"$.id" | g    | n    |
 ------------------------------- ----------- ------ ------ 
| {"id": "3", "name": "Barney"} | "3"       |    3 | NULL |
| {"id": "2", "name": "Wilma"}  | "2"       |    2 | NULL |
 ------------------------------- ----------- ------ ------ 
2 rows in set (0.00 sec)

（有关用于创建和填充刚才显示的表的语句，参阅为生成列创建索引以提供 JSON 列索引。）这也适用于 JSON 数组值，如下所示：

代码语言：javascript复制

mysql> CREATE TABLE tj10 (a JSON, b INT);
Query OK, 0 rows affected (0.26 sec)

mysql> INSERT INTO tj10
     > VALUES ("[3,10,5,17,44]", 33), ("[3,10,5,17,[22,44,66]]", 0);
Query OK, 1 row affected (0.04 sec)

mysql> SELECT a->"$[4]" FROM tj10;
 -------------- 
| a->"$[4]"    |
 -------------- 
| 44           |
| [22, 44, 66] |
 -------------- 
2 rows in set (0.00 sec)

mysql> SELECT * FROM tj10 WHERE a->"$[0]" = 3;
 ------------------------------ ------ 
| a                            | b    |
 ------------------------------ ------ 
| [3, 10, 5, 17, 44]           |   33 |
| [3, 10, 5, 17, [22, 44, 66]] |    0 |
 ------------------------------ ------ 
2 rows in set (0.00 sec)

支持嵌套数组。如果在目标 JSON 文档中找不到匹配的键，则使用 -> 的表达式评估为 NULL，如下所示：

代码语言：javascript复制

mysql> SELECT * FROM tj10 WHERE a->"$[4][1]" IS NOT NULL;
 ------------------------------ ------ 
| a                            | b    |
 ------------------------------ ------ 
| [3, 10, 5, 17, [22, 44, 66]] |    0 |
 ------------------------------ ------ 

mysql> SELECT a->"$[4][1]" FROM tj10;
 -------------- 
| a->"$[4][1]" |
 -------------- 
| NULL         |
| 44           |
 -------------- 
2 rows in set (0.00 sec)

这与使用 JSON_EXTRACT() 时在此类情况下看到的行为相同：

代码语言：javascript复制

mysql> SELECT JSON_EXTRACT(a, "$[4][1]") FROM tj10;
 ---------------------------- 
| JSON_EXTRACT(a, "$[4][1]") |
 ---------------------------- 
| NULL                       |
| 44                         |
 ---------------------------- 
2 rows in set (0.00 sec)

5. column->>path

这是一种改进的、无引号的提取操作符。-> 操作符只是提取一个值，而 ->> 操作符则会对提取的结果进行去引号。换句话说，给定 JSON 列和路径（字符串文本），以下三个表达式返回相同的值：

JSON_UNQUOTE( JSON_EXTRACT(column, path) )
JSON_UNQUOTE(column -> path)
column->>path

->> 运算符可以用于任何允许使用 JSON_UNQUOTE(JSON_EXTRACT()) 的地方。这包括但不限于 SELECT 列表、WHERE 和 HAVING 子句以及 ORDER BY 和 GROUP BY 子句。接下来的几条语句演示了一些在 mysql 客户端中，->> 运算符与其它表达式的等价性：

代码语言：javascript复制

mysql> SELECT * FROM jemp WHERE g > 2;
 ------------------------------- ------ 
| c                             | g    |
 ------------------------------- ------ 
| {"id": "3", "name": "Barney"} |    3 |
| {"id": "4", "name": "Betty"}  |    4 |
 ------------------------------- ------ 
2 rows in set (0.01 sec)

mysql> SELECT c->'$.name' AS name
    ->     FROM jemp WHERE g > 2;
 ---------- 
| name     |
 ---------- 
| "Barney" |
| "Betty"  |
 ---------- 
2 rows in set (0.00 sec)

mysql> SELECT JSON_UNQUOTE(c->'$.name') AS name
    ->     FROM jemp WHERE g > 2;
 -------- 
| name   |
 -------- 
| Barney |
| Betty  |
 -------- 
2 rows in set (0.00 sec)

mysql> SELECT c->>'$.name' AS name
    ->     FROM jemp WHERE g > 2;
 -------- 
| name   |
 -------- 
| Barney |
| Betty  |
 -------- 
2 rows in set (0.00 sec)

参阅为生成列创建索引以提供 JSON 列索引，了解刚才显示的示例中用于创建和填充 jemp 表的 SQL 语句。此运算符也可以与 JSON 数组一起使用，如下所示：

代码语言：javascript复制

mysql> CREATE TABLE tj10 (a JSON, b INT);
Query OK, 0 rows affected (0.26 sec)

mysql> INSERT INTO tj10 VALUES
    ->     ('[3,10,5,"x",44]', 33),
    ->     ('[3,10,5,17,[22,"y",66]]', 0);
Query OK, 2 rows affected (0.04 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT a->"$[3]", a->"$[4][1]" FROM tj10;
 ----------- -------------- 
| a->"$[3]" | a->"$[4][1]" |
 ----------- -------------- 
| "x"       | NULL         |
| 17        | "y"          |
 ----------- -------------- 
2 rows in set (0.00 sec)

mysql> SELECT a->>"$[3]", a->>"$[4][1]" FROM tj10;
 ------------ --------------- 
| a->>"$[3]" | a->>"$[4][1]" |
 ------------ --------------- 
| x          | NULL          |
| 17         | y             |
 ------------ --------------- 
2 rows in set (0.00 sec)

与 -> 一样，->> 运算符总是在 EXPLAIN 的输出中展开，如下例所示：

代码语言：javascript复制

mysql> EXPLAIN SELECT c->>'$.name' AS name
    ->     FROM jemp WHERE g > 2G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: jemp
   partitions: NULL
         type: range
possible_keys: i
          key: i
      key_len: 5
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGSG
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select
json_unquote(json_extract(`jtest`.`jemp`.`c`,'$.name')) AS `name` from
`jtest`.`jemp` where (`jtest`.`jemp`.`g` > 2)
1 row in set (0.00 sec)

这与 MySQL 在相同情况下展开 -> 运算符的方式类似。

6. JSON_KEYS(json_doc[, path])

以 JSON 数组的形式返回 JSON 对象的顶级键。或者，如果给定了路径参数，则返回所选路径中的顶级键。如果任何参数为 NULL、json_doc 参数不是对象，或者给定路径未找到对象，则返回 NULL。如果 json_doc 参数不是有效的 JSON 文档，或者路径参数不是有效路径表达式，或者包含 * 或 ** 通配符，则会发生错误。

如果所选对象为空，则结果数组为空。如果顶级值具有嵌套的子对象，则返回值不包括这些子对象的键。

代码语言：javascript复制

mysql> SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}');
 --------------------------------------- 
| JSON_KEYS('{"a": 1, "b": {"c": 30}}') |
 --------------------------------------- 
| ["a", "b"]                            |
 --------------------------------------- 
mysql> SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}', '$.b');
 ---------------------------------------------- 
| JSON_KEYS('{"a": 1, "b": {"c": 30}}', '$.b') |
 ---------------------------------------------- 
| ["c"]                                        |
 ----------------------------------------------

7. JSON_OVERLAPS(json_doc1, json_doc2)

比较两个 JSON 文档，如果两个文档有任何共同的键值对或数组元素，则返回 true（1）。如果两个参数都是标量，那么函数将执行一个简单的等式测试。如果其中一个参数为 NULL，则函数将返回 NULL。

此函数与 JSON_CONTAIN() 相对应，后者要求搜索的数组的所有元素都存在于被搜索的数组中。因此，JSON_CONTAINS() 对搜索键执行 AND 运算，而 JSON_OVERLAPS() 执行 OR 运算。

在 WHERE 子句中使用 JSON_OVERLAPS() 对 InnoDB 表的 JSON 列进行查询可以使用多值索引进行优化。多值索引，提供了详细信息和示例。当比较两个数组时，如果它们共享一个或多个公共数组元素，则 JSON_OVERLAPS() 返回 true，如果它们不共享，则返回 false：

代码语言：javascript复制

mysql> SELECT JSON_OVERLAPS("[1,3,5,7]", "[2,5,7]");
 --------------------------------------- 
| JSON_OVERLAPS("[1,3,5,7]", "[2,5,7]") |
 --------------------------------------- 
|                                     1 |
 --------------------------------------- 
1 row in set (0.00 sec)

mysql> SELECT JSON_OVERLAPS("[1,3,5,7]", "[2,6,7]");
 --------------------------------------- 
| JSON_OVERLAPS("[1,3,5,7]", "[2,6,7]") |
 --------------------------------------- 
|                                     1 |
 --------------------------------------- 
1 row in set (0.00 sec)

mysql> SELECT JSON_OVERLAPS("[1,3,5,7]", "[2,6,8]");
 --------------------------------------- 
| JSON_OVERLAPS("[1,3,5,7]", "[2,6,8]") |
 --------------------------------------- 
|                                     0 |
 --------------------------------------- 
1 row in set (0.00 sec)

部分匹配被视为不匹配，如下所示：

代码语言：javascript复制

mysql> SELECT JSON_OVERLAPS('[[1,2],[3,4],5]', '[1,[2,3],[4,5]]');
 ----------------------------------------------------- 
| JSON_OVERLAPS('[[1,2],[3,4],5]', '[1,[2,3],[4,5]]') |
 ----------------------------------------------------- 
|                                                   0 |
 ----------------------------------------------------- 
1 row in set (0.00 sec)

当比较对象时，如果它们至少有一个共同的键值对，则结果为 true。

代码语言：javascript复制

mysql> SELECT JSON_OVERLAPS('{"a":1,"b":10,"d":10}', '{"c":1,"e":10,"f":1,"d":10}');
 ----------------------------------------------------------------------- 
| JSON_OVERLAPS('{"a":1,"b":10,"d":10}', '{"c":1,"e":10,"f":1,"d":10}') |
 ----------------------------------------------------------------------- 
|                                                                     1 |
 ----------------------------------------------------------------------- 
1 row in set (0.00 sec)

mysql> SELECT JSON_OVERLAPS('{"a":1,"b":10,"d":10}', '{"a":5,"e":10,"f":1,"d":20}');
 ----------------------------------------------------------------------- 
| JSON_OVERLAPS('{"a":1,"b":10,"d":10}', '{"a":5,"e":10,"f":1,"d":20}') |
 ----------------------------------------------------------------------- 
|                                                                     0 |
 ----------------------------------------------------------------------- 
1 row in set (0.00 sec)

如果使用两个标量作为函数的参数，JSON_OVERLAPS() 将执行一个简单的相等性测试：

代码语言：javascript复制

mysql> SELECT JSON_OVERLAPS('5', '5');
 ------------------------- 
| JSON_OVERLAPS('5', '5') |
 ------------------------- 
|                       1 |
 ------------------------- 
1 row in set (0.00 sec)

mysql> SELECT JSON_OVERLAPS('5', '6');
 ------------------------- 
| JSON_OVERLAPS('5', '6') |
 ------------------------- 
|                       0 |
 ------------------------- 
1 row in set (0.00 sec)

将标量与数组进行比较时，JSON_OVERLAPS() 尝试将标量视为数组元素。在下面这个例子中，第二个参数 6 被解释为 [6]，如下所示：

代码语言：javascript复制

mysql> SELECT JSON_OVERLAPS('[4,5,6,7]', '6');
 --------------------------------- 
| JSON_OVERLAPS('[4,5,6,7]', '6') |
 --------------------------------- 
|                               1 |
 --------------------------------- 
1 row in set (0.00 sec)

该函数不执行类型转换：

代码语言：javascript复制

mysql> SELECT JSON_OVERLAPS('[4,5,"6",7]', '6');
 ----------------------------------- 
| JSON_OVERLAPS('[4,5,"6",7]', '6') |
 ----------------------------------- 
|                                 0 |
 ----------------------------------- 
1 row in set (0.00 sec)

mysql> SELECT JSON_OVERLAPS('[4,5,6,7]', '"6"');
 ----------------------------------- 
| JSON_OVERLAPS('[4,5,6,7]', '"6"') |
 ----------------------------------- 
|                                 0 |
 ----------------------------------- 
1 row in set (0.00 sec)

MySQL 8.0.17 中新增了 JSON_OVERLAPS() 函数。

8. JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])

返回 JSON 文档中给定字符串的路径。如果 json_doc、search_str 或路径参数中的任何一个为 NULL；文档中不存在路径；或者找不到 search_str，则返回 NULL。如果 json_doc 参数不是有效的 JSON 文档，任何路径参数不是有效的路径表达式，one_or_all 不是 “one” 或 “all”，或者 escape_char 不是常量表达式，则会发生错误。

one_or_all 参数对搜索的影响如下：

'one'：搜索在第一个匹配后终止，并返回一个路径字符串。未定义哪种匹配被视为第一个匹配。
'all'：搜索返回所有匹配的路径字符串，这样就不包括重复的路径。如果有多个字符串，它们将自动封装为一个数组。数组元素的顺序未定义。

在 search_str 搜索字符串参数中，% 和 _ 字符的作用与 LIKE 操作符相同：% 匹配任意数量的字符（包括零个字符），而 _ 恰好匹配一个字符。

若要在搜索字符串中指定文字 % 或 _ 字符，需要在其前面加转义符。如果 escape_char 参数缺失或为 NULL，则默认值为。否则，escape_char 必须是一个空的常量或一个字符。

有关匹配和转义符行为的更多信息，参阅“第14.8.1节字符串比较函数和操作符”中对 LIKE 的描述。对于转义符处理，与 LIKE 行为的区别在于，JSON_SEARCH() 的转义符必须在编译时计算为常量，而不仅仅是在执行时。例如，如果在准备语句中使用 JSON_SEARCH()，并且 escape_char 参数是使用 ? 参数，参数值在执行时可能是常量，但在编译时不是。 search_str 和 path 始终被解释为 utf8mb4 字符串，而不管它们的实际编码如何。这是一个已知的问题，已在 MySQL 8.0.24 中修复（Bug#332449181）。

代码语言：javascript复制

mysql> SET @j = '["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]';

mysql> SELECT JSON_SEARCH(@j, 'one', 'abc');
 ------------------------------- 
| JSON_SEARCH(@j, 'one', 'abc') |
 ------------------------------- 
| "$[0]"                        |
 ------------------------------- 

mysql> SELECT JSON_SEARCH(@j, 'all', 'abc');
 ------------------------------- 
| JSON_SEARCH(@j, 'all', 'abc') |
 ------------------------------- 
| ["$[0]", "$[2].x"]            |
 ------------------------------- 

mysql> SELECT JSON_SEARCH(@j, 'all', 'ghi');
 ------------------------------- 
| JSON_SEARCH(@j, 'all', 'ghi') |
 ------------------------------- 
| NULL                          |
 ------------------------------- 

mysql> SELECT JSON_SEARCH(@j, 'all', '10');
 ------------------------------ 
| JSON_SEARCH(@j, 'all', '10') |
 ------------------------------ 
| "$[1][0].k"                  |
 ------------------------------ 

mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$');
 ----------------------------------------- 
| JSON_SEARCH(@j, 'all', '10', NULL, '$') |
 ----------------------------------------- 
| "$[1][0].k"                             |
 ----------------------------------------- 

mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[*]');
 -------------------------------------------- 
| JSON_SEARCH(@j, 'all', '10', NULL, '$[*]') |
 -------------------------------------------- 
| "$[1][0].k"                                |
 -------------------------------------------- 

mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$**.k');
 --------------------------------------------- 
| JSON_SEARCH(@j, 'all', '10', NULL, '$**.k') |
 --------------------------------------------- 
| "$[1][0].k"                                 |
 --------------------------------------------- 

mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[*][0].k');
 ------------------------------------------------- 
| JSON_SEARCH(@j, 'all', '10', NULL, '$[*][0].k') |
 ------------------------------------------------- 
| "$[1][0].k"                                     |
 ------------------------------------------------- 

mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[1]');
 -------------------------------------------- 
| JSON_SEARCH(@j, 'all', '10', NULL, '$[1]') |
 -------------------------------------------- 
| "$[1][0].k"                                |
 -------------------------------------------- 

mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[1][0]');
 ----------------------------------------------- 
| JSON_SEARCH(@j, 'all', '10', NULL, '$[1][0]') |
 ----------------------------------------------- 
| "$[1][0].k"                                   |
 ----------------------------------------------- 

mysql> SELECT JSON_SEARCH(@j, 'all', 'abc', NULL, '$[2]');
 --------------------------------------------- 
| JSON_SEARCH(@j, 'all', 'abc', NULL, '$[2]') |
 --------------------------------------------- 
| "$[2].x"                                    |
 --------------------------------------------- 

mysql> SELECT JSON_SEARCH(@j, 'all', '%a%');
 ------------------------------- 
| JSON_SEARCH(@j, 'all', '%a%') |
 ------------------------------- 
| ["$[0]", "$[2].x"]            |
 ------------------------------- 

mysql> SELECT JSON_SEARCH(@j, 'all', '%b%');
 ------------------------------- 
| JSON_SEARCH(@j, 'all', '%b%') |
 ------------------------------- 
| ["$[0]", "$[2].x", "$[3].y"]  |
 ------------------------------- 

mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', NULL, '$[0]');
 --------------------------------------------- 
| JSON_SEARCH(@j, 'all', '%b%', NULL, '$[0]') |
 --------------------------------------------- 
| "$[0]"                                      |
 --------------------------------------------- 

mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', NULL, '$[2]');
 --------------------------------------------- 
| JSON_SEARCH(@j, 'all', '%b%', NULL, '$[2]') |
 --------------------------------------------- 
| "$[2].x"                                    |
 --------------------------------------------- 

mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', NULL, '$[1]');
 --------------------------------------------- 
| JSON_SEARCH(@j, 'all', '%b%', NULL, '$[1]') |
 --------------------------------------------- 
| NULL                                        |
 --------------------------------------------- 

mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', '', '$[1]');
 ------------------------------------------- 
| JSON_SEARCH(@j, 'all', '%b%', '', '$[1]') |
 ------------------------------------------- 
| NULL                                      |
 ------------------------------------------- 

mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', '', '$[3]');
 ------------------------------------------- 
| JSON_SEARCH(@j, 'all', '%b%', '', '$[3]') |
 ------------------------------------------- 
| "$[3].y"                                  |
 -------------------------------------------

有关 MySQL 支持的 JSON 路径语法的更多信息，包括通配符运算符 * 和 ** 的规则，参阅 JSON 路径语法。

9. JSON_VALUE(json_doc, path)

按照指定文档中给定的路径从 JSON 文档中提取一个值，然后返回提取的值，也可以将其转换为所需的类型。完整的语法如下所示：

代码语言：javascript复制

JSON_VALUE(json_doc, path [RETURNING type] [on_empty] [on_error])

on_empty:
    {NULL | ERROR | DEFAULT value} ON EMPTY

on_error:
    {NULL | ERROR | DEFAULT value} ON ERROR

json_doc 是一个有效的 JSON 文档。如果是 NULL，函数将返回 NULL。 path 是指向文档中某个位置的 JSON 路径，必须是字符串文本值。 type 是以下数据类型之一：

FLOAT
DOUBLE
DECIMAL
SIGNED
UNSIGNED
DATE
TIME
DATETIME
YEAR（MySQL 8.0.22 及其以后版本），不支持一位数或两位数的 YEAR 值。
CHAR
JSON

这里列出的类型与 CAST() 函数支持的（非数组）类型相同。

如果 RETURNING 子句未指定，则 JSON_VALUE() 函数的返回类型为 VARCHAR(512)。当没有为返回类型指定字符集时，JSON_VALUE() 将 utf8mb4 与区分大小写的二进制排序规则一起使用；如果将 utf8mb4 指定为结果的字符集，则服务器将使用该字符集的默认排序规则，该排序规则不区分大小写。

当指定路径上的数据由 JSON null 文本组成或解析为 JSON null 文本时，函数将返回 SQL NULL。

如果指定 on_empty，则确定在给定路径上找不到数据时 JSON_VALUE() 的行为；该子句使用以下值之一：

NULL ON EMPTY：函数返回 NULL；这是默认的 ON EMPTY 行为。
DEFAULT value ON EMPTY：返回提供的值。值的类型必须与返回的类型匹配。
ERROR ON EMPTY：函数抛出一个错误。

如果使用 on_error，当出现错误时，会取以下值之一并得到相应的结果，如下所示：

NULL ON ERROR：JSON_VALUE() 返回 NULL；如果未使用 ON ERROR 子句，则这是默认行为。
DEFAULT value ON ERROR：这是返回的值；其类型必须与返回类型匹配。
ERROR ON ERROR：引发错误。

如果使用 ON EMPTY，则必须在任何 ON ERROR 子句之前。以错误的顺序指定它们会导致语法错误。

错误处理。通常，错误由 JSON_VALUE() 处理，如下所示：

检查所有 JSON 输入（文档和路径）的有效性。如果其中任何一个无效，则在不触发 ON ERROR 子句的情况下引发 SQL 错误。
只要发生以下任何事件，就会触发 ON ERROR：
- 试图提取对象或数组，例如解析到 JSON 文档中多个位置的路径所产生的对象或数组
- 转换错误，例如试图将 “asdf” 转换为 UNSIGNED 值
- 截断值
转换错误总是触发警告，即使指定了 NULL ON ERROR 或 DEFAULT ... ON ERROR。
当源 JSON 文档（表达式）在指定位置（路径）不包含数据时，会触发 ON EMPTY 子句。

JSON_VALUE() 是在 MySQL 8.0.21 中引入的。

示例。这里显示了两个简单的示例：

代码语言：javascript复制

mysql> SELECT JSON_VALUE('{"fname": "Joe", "lname": "Palmer"}', '$.fname');
 -------------------------------------------------------------- 
| JSON_VALUE('{"fname": "Joe", "lname": "Palmer"}', '$.fname') |
 -------------------------------------------------------------- 
| Joe                                                          |
 -------------------------------------------------------------- 

mysql> SELECT JSON_VALUE('{"item": "shoes", "price": "49.95"}', '$.price'
    -> RETURNING DECIMAL(4,2)) AS price;
 ------- 
| price |
 ------- 
| 49.95 |
 -------

SELECT JSON_VALUE(json_doc, path RETURNING type) 语句等效于以下语句：

代码语言：javascript复制

SELECT CAST(
    JSON_UNQUOTE( JSON_EXTRACT(json_doc, path) )
    AS type
);

以下 EXPLAIN 输出显示，对 t1 的查询中的 WHERE 子句会使用索引表达式，并使用由此创建的索引：

代码语言：javascript复制

mysql> EXPLAIN SELECT * FROM t1
    ->     WHERE JSON_VALUE(j, '$.id' RETURNING UNSIGNED) = 123G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ref
possible_keys: i1
          key: i1
      key_len: 9
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL

这实现了与在生成列上创建具有索引的表 t2 相同的效果（参阅为生成列创建索引以提供 JSON 列索引），如下所示：

代码语言：javascript复制

CREATE TABLE t2 (
    j JSON,
    g INT GENERATED ALWAYS AS (j->"$.id"),
    INDEX i1 (g)
);

针对该表的查询的 EXPLAIN 输出引用了生成列，表明索引的使用方式与前面针对表 t1 的查询相同：

代码语言：javascript复制

mysql> EXPLAIN SELECT * FROM t2 WHERE g  = 123G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t2
   partitions: NULL
         type: ref
possible_keys: i1
          key: i1
      key_len: 5
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL

有关使用生成列的索引对 JSON 列进行间接索引的信息，参阅为生成列创建索引以提供 JSON 列索引。

10. value MEMBER OF(json_array)

如果 value 是 json_array 的元素，则返回 true（1），否则返回 false（0）。value 必须是标量或 JSON 文档；如果它是一个标量，则操作符会尝试将其视为 JSON 数组的元素。如果 value 或 json_array 为 NULL，则函数将返回 NULL。

WHERE 子句中使用 MEMBER OF() 对 InnoDB 表的 JSON 列进行查询可以使用多值索引进行优化。有关详细信息和示例，参阅多值索引。简单标量被视为数组值，如下所示：

代码语言：javascript复制

mysql> SELECT 17 MEMBER OF('[23, "abc", 17, "ab", 10]');
 ------------------------------------------- 
| 17 MEMBER OF('[23, "abc", 17, "ab", 10]') |
 ------------------------------------------- 
|                                         1 |
 ------------------------------------------- 
1 row in set (0.00 sec)

mysql> SELECT 'ab' MEMBER OF('[23, "abc", 17, "ab", 10]');
 --------------------------------------------- 
| 'ab' MEMBER OF('[23, "abc", 17, "ab", 10]') |
 --------------------------------------------- 
|                                           1 |
 --------------------------------------------- 
1 row in set (0.00 sec)

数组元素值的部分匹配为不匹配：

代码语言：javascript复制

mysql> SELECT 7 MEMBER OF('[23, "abc", 17, "ab", 10]');
 ------------------------------------------ 
| 7 MEMBER OF('[23, "abc", 17, "ab", 10]') |
 ------------------------------------------ 
|                                        0 |
 ------------------------------------------ 
1 row in set (0.00 sec)

mysql> SELECT 'a' MEMBER OF('[23, "abc", 17, "ab", 10]');
 -------------------------------------------- 
| 'a' MEMBER OF('[23, "abc", 17, "ab", 10]') |
 -------------------------------------------- 
|                                          0 |
 -------------------------------------------- 
1 row in set (0.00 sec)

不执行与字符串类型之间的转换：

代码语言：javascript复制

mysql> SELECT
    -> 17 MEMBER OF('[23, "abc", "17", "ab", 10]'),
    -> "17" MEMBER OF('[23, "abc", 17, "ab", 10]')G
*************************** 1. row ***************************
17 MEMBER OF('[23, "abc", "17", "ab", 10]'): 0
"17" MEMBER OF('[23, "abc", 17, "ab", 10]'): 0
1 row in set (0.00 sec)

要将此操作符与本身为数组的值一起使用，必须将其显式转换为 JSON 数组。可以使用 CAST(... AS JSON) 执行此操作：

代码语言：javascript复制

mysql> SELECT CAST('[4,5]' AS JSON) MEMBER OF('[[3,4],[4,5]]');
 -------------------------------------------------- 
| CAST('[4,5]' AS JSON) MEMBER OF('[[3,4],[4,5]]') |
 -------------------------------------------------- 
|                                                1 |
 -------------------------------------------------- 
1 row in set (0.00 sec)

也可以使用 JSON_ARRAY() 函数执行必要的强制转换，如下所示：

代码语言：javascript复制

mysql> SELECT JSON_ARRAY(4,5) MEMBER OF('[[3,4],[4,5]]');
 -------------------------------------------- 
| JSON_ARRAY(4,5) MEMBER OF('[[3,4],[4,5]]') |
 -------------------------------------------- 
|                                          1 |
 -------------------------------------------- 
1 row in set (0.00 sec)

任何用作要测试的值或出现在目标数组中的 JSON 对象都必须使用 CAST(... AS JSON) 或 JSON_OBJECT() 强制转换为正确的类型。此外，包含 JSON 对象的目标数组本身必须使用 JSON_ARRAY 进行强制转换。这在以下一系列语句中得到证实：

代码语言：javascript复制

mysql> SET @a = CAST('{"a":1}' AS JSON);
Query OK, 0 rows affected (0.00 sec)

mysql> SET @b = JSON_OBJECT("b", 2);
Query OK, 0 rows affected (0.00 sec)

mysql> SET @c = JSON_ARRAY(17, @b, "abc", @a, 23);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @a MEMBER OF(@c), @b MEMBER OF(@c);
 ------------------ ------------------ 
| @a MEMBER OF(@c) | @b MEMBER OF(@c) |
 ------------------ ------------------ 
|                1 |                1 |
 ------------------ ------------------ 
1 row in set (0.00 sec)

在 MySQL 8.0.17 中新增了 MEMBER OF() 操作符。

四、修改 JSON 值的函数

本节中的函数修改 JSON 值并返回结果。

1. JSON_ARRAY_APPEND(json_doc, path, val[, path, val] ...)

将值追加到 JSON 文档中指示的数组的末尾，并返回结果。如果任何参数为 NULL，则返回 NULL。如果 json_doc 参数不是有效的 json 文档，或者任何路径参数不是有效的路径表达式，或者包含 * 或 ** 通配符，则会发生错误。

路径值对是从左到右计算的。通过评估一对生成的文档将成为评估下一对的新值。如果路径选择标量值或对象值，则该值将自动封装在数组中，并且新值将被添加到该数组中。路径在 JSON 文档中没有标识任何值的对将被忽略。

代码语言：javascript复制

mysql> SET @j = '["a", ["b", "c"], "d"]';
mysql> SELECT JSON_ARRAY_APPEND(@j, '$[1]', 1);
 ---------------------------------- 
| JSON_ARRAY_APPEND(@j, '$[1]', 1) |
 ---------------------------------- 
| ["a", ["b", "c", 1], "d"]        |
 ---------------------------------- 
mysql> SELECT JSON_ARRAY_APPEND(@j, '$[0]', 2);
 ---------------------------------- 
| JSON_ARRAY_APPEND(@j, '$[0]', 2) |
 ---------------------------------- 
| [["a", 2], ["b", "c"], "d"]      |
 ---------------------------------- 
mysql> SELECT JSON_ARRAY_APPEND(@j, '$[1][0]', 3);
 ------------------------------------- 
| JSON_ARRAY_APPEND(@j, '$[1][0]', 3) |
 ------------------------------------- 
| ["a", [["b", 3], "c"], "d"]         |
 ------------------------------------- 

mysql> SET @j = '{"a": 1, "b": [2, 3], "c": 4}';
mysql> SELECT JSON_ARRAY_APPEND(@j, '$.b', 'x');
 ------------------------------------ 
| JSON_ARRAY_APPEND(@j, '$.b', 'x')  |
 ------------------------------------ 
| {"a": 1, "b": [2, 3, "x"], "c": 4} |
 ------------------------------------ 
mysql> SELECT JSON_ARRAY_APPEND(@j, '$.c', 'y');
 -------------------------------------- 
| JSON_ARRAY_APPEND(@j, '$.c', 'y')    |
 -------------------------------------- 
| {"a": 1, "b": [2, 3], "c": [4, "y"]} |
 -------------------------------------- 

mysql> SET @j = '{"a": 1}';
mysql> SELECT JSON_ARRAY_APPEND(@j, '$', 'z');
 --------------------------------- 
| JSON_ARRAY_APPEND(@j, '$', 'z') |
 --------------------------------- 
| [{"a": 1}, "z"]                 |
 ---------------------------------

在 MySQL 5.7 中，这个函数被命名为 JSON_APPEND()。MySQL 8.0 不再支持该名称。

2. JSON_ARRAY_INSERT(json_doc, path, val[, path, val] ...)

更新 JSON 文档，插入文档中的数组并返回修改后的文档。如果任何参数为 NULL，则返回 NULL。如果 json_doc 参数不是有效的 JSON 文档，或者任何路径参数不是有效的路径表达式，或者包含 * 或 ** 通配符，或者没有以数组元素标识符结尾，则会发生错误。

路径值对是从左到右计算的。通过评估一对生成的文档将成为评估下一对的新值。

路径没有标识 JSON 文档中任何数组的对将被忽略。如果路径标识了一个数组元素，则会在该元素位置插入相应的值，并将后面所有值向右移动。如果路径标识了超过数组的末尾位置，则会在数组末尾插入该值。

代码语言：javascript复制

mysql> SET @j = '["a", {"b": [1, 2]}, [3, 4]]';
mysql> SELECT JSON_ARRAY_INSERT(@j, '$[1]', 'x');
 ------------------------------------ 
| JSON_ARRAY_INSERT(@j, '$[1]', 'x') |
 ------------------------------------ 
| ["a", "x", {"b": [1, 2]}, [3, 4]]  |
 ------------------------------------ 
mysql> SELECT JSON_ARRAY_INSERT(@j, '$[100]', 'x');
 -------------------------------------- 
| JSON_ARRAY_INSERT(@j, '$[100]', 'x') |
 -------------------------------------- 
| ["a", {"b": [1, 2]}, [3, 4], "x"]    |
 -------------------------------------- 
mysql> SELECT JSON_ARRAY_INSERT(@j, '$[1].b[0]', 'x');
 ----------------------------------------- 
| JSON_ARRAY_INSERT(@j, '$[1].b[0]', 'x') |
 ----------------------------------------- 
| ["a", {"b": ["x", 1, 2]}, [3, 4]]       |
 ----------------------------------------- 
mysql> SELECT JSON_ARRAY_INSERT(@j, '$[2][1]', 'y');
 --------------------------------------- 
| JSON_ARRAY_INSERT(@j, '$[2][1]', 'y') |
 --------------------------------------- 
| ["a", {"b": [1, 2]}, [3, "y", 4]]     |
 --------------------------------------- 
mysql> SELECT JSON_ARRAY_INSERT(@j, '$[0]', 'x', '$[2][1]', 'y');
 ---------------------------------------------------- 
| JSON_ARRAY_INSERT(@j, '$[0]', 'x', '$[2][1]', 'y') |
 ---------------------------------------------------- 
| ["x", "a", {"b": [1, 2]}, [3, 4]]                  |
 ----------------------------------------------------

前面的修改会影响数组中后面元素的位置，因此在同一个 JSON_ARRAY_INSERT() 调用中的后续路径应该考虑到这一点。在最后一个示例中，第二个路径不插入任何内容，因为该路径在第一个插入之后不再匹配任何内容。

3. JSON_INSERT(json_doc, path, val[, path, val] ...)

将数据插入 JSON 文档并返回结果。如果任何参数为 NULL，则返回 NULL。如果 json_doc 参数不是有效的 JSON 文档，或者任何路径参数不是有效的路径表达式，或者包含 * 或 ** 通配符，则会发生错误。

路径值对是从左到右计算的。通过评估一对生成的文档将成为评估下一对的新值。

文档中现有路径的路径值对将被忽略，并且不会覆盖现有文档值。如果文档中不存在的路径标识以下类型的值之一，则该路径的路径值对会将该值添加到文档中：

现有对象中不存在的成员。成员将添加到对象中，并与新值相关联。
超过现有数组末尾的位置。数组将使用新值进行扩展。如果现有值不是数组，则会自动封装为数组，然后使用新值进行扩展。

否则，文档中不存在的路径的路径值对将被忽略，并且没有任何影响。

有关 JSON_INSERT()、JSON_REPLACE() 和 JSON_SET() 的比较，参阅对 JSON_SET() 的讨论。

代码语言：javascript复制

mysql> SET @j = '{ "a": 1, "b": [2, 3]}';
mysql> SELECT JSON_INSERT(@j, '$.a', 10, '$.c', '[true, false]');
 ---------------------------------------------------- 
| JSON_INSERT(@j, '$.a', 10, '$.c', '[true, false]') |
 ---------------------------------------------------- 
| {"a": 1, "b": [2, 3], "c": "[true, false]"}        |
 ----------------------------------------------------

结果中列出的第三个也是最后一个值是一个带引号的字符串，而不是像第二个值那样的数组（输出中没有带引号）；不执行值到 JSON 类型的强制转换。要将数组作为数组插入，必须显式执行此类强制转换，如下所示：

代码语言：javascript复制

mysql> SELECT JSON_INSERT(@j, '$.a', 10, '$.c', CAST('[true, false]' AS JSON));
 ------------------------------------------------------------------ 
| JSON_INSERT(@j, '$.a', 10, '$.c', CAST('[true, false]' AS JSON)) |
 ------------------------------------------------------------------ 
| {"a": 1, "b": [2, 3], "c": [true, false]}                        |
 ------------------------------------------------------------------ 
1 row in set (0.00 sec)

4. JSON_MERGE(json_doc, json_doc[, json_doc] ...)

合并两个或多个 JSON 文档。JSON_MERGE_PRESERVE() 的同义词；在 MySQL 8.0.3 中已弃用，并可能在将来的版本中删除。

代码语言：javascript复制

mysql> SELECT JSON_MERGE('[1, 2]', '[true, false]');
 --------------------------------------- 
| JSON_MERGE('[1, 2]', '[true, false]') |
 --------------------------------------- 
| [1, 2, true, false]                   |
 --------------------------------------- 
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGSG
*************************** 1. row ***************************
  Level: Warning
   Code: 1287
Message: 'JSON_MERGE' is deprecated and will be removed in a future release. 
 Please use JSON_MERGE_PRESERVE/JSON_MERGE_PATCH instead
1 row in set (0.00 sec)

有关其它示例，参阅条目 JSON_MERGE_PRESERVE()。

5. JSON_MERGE_PATCH(json_doc, json_doc[, json_doc] ...)

对两个或多个 JSON 文档执行符合 RFC 7396 的合并，并返回合并结果，不保留具有重复键的成员。如果作为参数传递到此函数的至少一个文档无效，则引发错误。

说明：有关此函数与 JSON_MERGE_PRESERVE() 之间差异的解释和示例，参阅 JSON_MERGE_PATCH() 与 JSON_MERGE_PRESERVE() 的比较。

JSON_MERGE_PATCH() 执行如下所示的合并：

如果第一个参数不是对象，则合并的结果与将空对象与第二个参数合并时的结果相同。
如果第二个参数不是对象，则合并的结果是第二个参数。
如果两个参数都是对象，则合并的结果是具有以下成员的对象：

在第二个对象中没有具有相同键的对应成员的第一个对象的所有成员。
第二个对象的所有成员，这些成员在第一个对象中没有相应的键，并且其值不是 JSON null 文本。
第一个和第二个对象中都存在的键，并且第二个对象中的值不是 JSON null 文本的所有成员。这些成员的值是将第一个对象中的值与第二个对象中的值递归合并的结果。

有关其它信息，参阅 JSON 值的规范化、合并和自动封装。

代码语言：javascript复制

mysql> SELECT JSON_MERGE_PATCH('[1, 2]', '[true, false]');
 --------------------------------------------- 
| JSON_MERGE_PATCH('[1, 2]', '[true, false]') |
 --------------------------------------------- 
| [true, false]                               |
 --------------------------------------------- 

mysql> SELECT JSON_MERGE_PATCH('{"name": "x"}', '{"id": 47}');
 ------------------------------------------------- 
| JSON_MERGE_PATCH('{"name": "x"}', '{"id": 47}') |
 ------------------------------------------------- 
| {"id": 47, "name": "x"}                         |
 ------------------------------------------------- 

mysql> SELECT JSON_MERGE_PATCH('1', 'true');
 ------------------------------- 
| JSON_MERGE_PATCH('1', 'true') |
 ------------------------------- 
| true                          |
 ------------------------------- 

mysql> SELECT JSON_MERGE_PATCH('[1, 2]', '{"id": 47}');
 ------------------------------------------ 
| JSON_MERGE_PATCH('[1, 2]', '{"id": 47}') |
 ------------------------------------------ 
| {"id": 47}                               |
 ------------------------------------------ 

mysql> SELECT JSON_MERGE_PATCH('{ "a": 1, "b":2 }',
     >     '{ "a": 3, "c":4 }');
 ----------------------------------------------------------- 
| JSON_MERGE_PATCH('{ "a": 1, "b":2 }','{ "a": 3, "c":4 }') |
 ----------------------------------------------------------- 
| {"a": 3, "b": 2, "c": 4}                                  |
 ----------------------------------------------------------- 

mysql> SELECT JSON_MERGE_PATCH('{ "a": 1, "b":2 }','{ "a": 3, "c":4 }',
     >     '{ "a": 5, "d":6 }');
 ------------------------------------------------------------------------------- 
| JSON_MERGE_PATCH('{ "a": 1, "b":2 }','{ "a": 3, "c":4 }','{ "a": 5, "d":6 }') |
 ------------------------------------------------------------------------------- 
| {"a": 5, "b": 2, "c": 4, "d": 6}                                              |
 -------------------------------------------------------------------------------

可以使用此函数通过在第二个参数中指定 null 作为同一成员的值来删除成员，如下所示：

代码语言：javascript复制

mysql> SELECT JSON_MERGE_PATCH('{"a":1, "b":2}', '{"b":null}');
 -------------------------------------------------- 
| JSON_MERGE_PATCH('{"a":1, "b":2}', '{"b":null}') |
 -------------------------------------------------- 
| {"a": 1}                                         |
 --------------------------------------------------

下面这个例子表明函数是以递归的方式运行的；也就是说，成员的值不限于标量，也可以是 JSON 文档：

代码语言：javascript复制

mysql> SELECT JSON_MERGE_PATCH('{"a":{"x":1}}', '{"a":{"y":2}}');
 ---------------------------------------------------- 
| JSON_MERGE_PATCH('{"a":{"x":1}}', '{"a":{"y":2}}') |
 ---------------------------------------------------- 
| {"a": {"x": 1, "y": 2}}                            |
 ----------------------------------------------------

JSON_MERGE_PACH() 被 MySQL 8.0.3 及更高版本所支持。

JSON_MERGE_PATCH() 与 JSON_MERGE_PRESERVE() 的比较 JSON_MERGE_PATCH() 的行为与 JSON_MERGE_PRESERVE() 的相同，但有以下两个区别：

如果与第二个对象中的键关联的值不是 JSON null，则 JSON_MERGE_PATCH() 将删除第一个对象中具有相同键的任何成员。
如果第二个对象有一个成员，其键与第一个对象中的成员相同，则 JSON_MERGE_PATCH() 会将第一个对象的值替换为第二个对象中的值，而 JSON_MERGE_PRESERVE() 则会将第二个值附加到第一个值上。

下面的示例将都有一个匹配的关键字 “a” 的 3 个 JSON 对象，分别用两个函数合并的结果进行比较：

代码语言：javascript复制

mysql> SET @x = '{ "a": 1, "b": 2 }',
     >     @y = '{ "a": 3, "c": 4 }',
     >     @z = '{ "a": 5, "d": 6 }';

mysql> SELECT  JSON_MERGE_PATCH(@x, @y, @z)    AS Patch,
    ->         JSON_MERGE_PRESERVE(@x, @y, @z) AS PreserveG
*************************** 1. row ***************************
   Patch: {"a": 5, "b": 2, "c": 4, "d": 6}
Preserve: {"a": [1, 3, 5], "b": 2, "c": 4, "d": 6}

6. JSON_MERGE_PRESERVE(json_doc, json_doc[, json_doc] ...)

合并两个或多个 JSON 文档并返回合并后的结果。如果任何参数为 NULL，则返回 NULL。如果任何参数不是有效的 JSON 文档，则会发生错误。

合并根据以下规则进行的。有关其它信息，参阅 JSON 值的规范化、合并和自动封装。

相邻的数组合并为一个数组。
相邻的对象将合并为一个对象。
标量值自动并合并封装为数组。
通过将对象自动封装为数组并合并这两个数组，可以合并相邻的数组和对象。

代码语言：javascript复制

mysql> SELECT JSON_MERGE_PRESERVE('[1, 2]', '[true, false]');
 ------------------------------------------------ 
| JSON_MERGE_PRESERVE('[1, 2]', '[true, false]') |
 ------------------------------------------------ 
| [1, 2, true, false]                            |
 ------------------------------------------------ 

mysql> SELECT JSON_MERGE_PRESERVE('{"name": "x"}', '{"id": 47}');
 ---------------------------------------------------- 
| JSON_MERGE_PRESERVE('{"name": "x"}', '{"id": 47}') |
 ---------------------------------------------------- 
| {"id": 47, "name": "x"}                            |
 ---------------------------------------------------- 

mysql> SELECT JSON_MERGE_PRESERVE('1', 'true');
 ---------------------------------- 
| JSON_MERGE_PRESERVE('1', 'true') |
 ---------------------------------- 
| [1, true]                        |
 ---------------------------------- 

mysql> SELECT JSON_MERGE_PRESERVE('[1, 2]', '{"id": 47}');
 --------------------------------------------- 
| JSON_MERGE_PRESERVE('[1, 2]', '{"id": 47}') |
 --------------------------------------------- 
| [1, 2, {"id": 47}]                          |
 --------------------------------------------- 

mysql> SELECT JSON_MERGE_PRESERVE('{ "a": 1, "b": 2 }',
     >    '{ "a": 3, "c": 4 }');
 -------------------------------------------------------------- 
| JSON_MERGE_PRESERVE('{ "a": 1, "b": 2 }','{ "a": 3, "c":4 }') |
 -------------------------------------------------------------- 
| {"a": [1, 3], "b": 2, "c": 4}                                |
 -------------------------------------------------------------- 

mysql> SELECT JSON_MERGE_PRESERVE('{ "a": 1, "b": 2 }','{ "a": 3, "c": 4 }',
     >    '{ "a": 5, "d": 6 }');
 ---------------------------------------------------------------------------------- 
| JSON_MERGE_PRESERVE('{ "a": 1, "b": 2 }','{ "a": 3, "c": 4 }','{ "a": 5, "d": 6 }') |
 ---------------------------------------------------------------------------------- 
| {"a": [1, 3, 5], "b": 2, "c": 4, "d": 6}                                         |
 ----------------------------------------------------------------------------------

此函数是在 MySQL 8.0.3 中作为 JSON_MERGE() 的同义词添加的。JSON_MERGE() 函数现在已被弃用，并将在 MySQL 的未来版本中删除。

此函数与 JSON_MERGE_PATCH() 在许多方面相似但有所不同；有关详细信息，参阅 JSON_MERGE_PATCH() 与 JSON_MERGE_PRESERVE() 的比较。

7. JSON_REMOVE(json_doc, path[, path] ...)

从 JSON 文档中删除数据并返回结果。如果任何参数为 NULL，则返回 NULL。如果 json_doc 参数不是有效的 JSON 文档，或者任何路径参数不是有效的路径表达式，或者是 $ 或包含 * 或 ** 通配符，则会发生错误。

路径参数是从左到右计算的。通过评估一条路径生成的文档将成为评估下一条路径所依据的新值。

如果文档中不存在要删除的元素，则不属于错误；在这种情况下，路径不会影响文档。

代码语言：javascript复制

mysql> SET @j = '["a", ["b", "c"], "d"]';
mysql> SELECT JSON_REMOVE(@j, '$[1]');
 ------------------------- 
| JSON_REMOVE(@j, '$[1]') |
 ------------------------- 
| ["a", "d"]              |
 -------------------------

8. JSON_REPLACE(json_doc, path, val[, path, val] ...)

替换 JSON 文档中的现有值并返回结果。如果任何参数为 NULL，则返回 NULL。如果 json_doc 参数不是有效的 JSON 文档，或者任何路径参数不是有效的路径表达式，或者包含 * 或 ** 通配符，则会发生错误。

路径值对是从左到右计算的。通过评估一对生成的文档将成为评估下一对的新值。

文档中现有路径的路径值对用新值覆盖现有文档值。文档中不存在的路径的路径值对将被忽略，并且没有任何影响。

在 MySQL 8.0.4 中，优化器可以执行 JSON 列的部分就地更新，而不是删除旧文档并将新文档全部写入该列。可以对使用 JSON_REPLACE() 函数并满足 JSON 值的部分更新中列出的条件的更新语句执行此优化。

有关 JSON_INSERT()、JSON_REPLACE() 和 JSON_SET() 的比较，参阅对 JSON_SET() 的讨论。

代码语言：javascript复制

mysql> SET @j = '{ "a": 1, "b": [2, 3]}';
mysql> SELECT JSON_REPLACE(@j, '$.a', 10, '$.c', '[true, false]');
 ----------------------------------------------------- 
| JSON_REPLACE(@j, '$.a', 10, '$.c', '[true, false]') |
 ----------------------------------------------------- 
| {"a": 10, "b": [2, 3]}                              |
 -----------------------------------------------------

9. JSON_SET(json_doc, path, val[, path, val] ...)

在 JSON 文档中插入或更新数据并返回结果。如果 json_doc 或 path 为 NULL，或者给定的 path 未定位对象，则返回 NULL。否则，如果 json_doc 参数不是有效的 JSON 文档，或者任何路径参数不是有效的路径表达式，或者包含 * 或 ** 通配符，则会发生错误。

路径值对是从左到右计算的。通过评估一对生成的文档将成为评估下一对的新值。

文档中现有路径的路径值对用新值覆盖现有文档值。如果文档中不存在的路径标识以下类型的值之一，则该路径的路径值对会将该值添加到文档中：

现有对象中不存在成员。成员将添加到对象中，并与新值相关联。
超过现有数组末尾的位置。数组将使用新值进行扩展。如果现有值不是数组，则会自动封装为数组，然后使用新值进行扩展。

否则，文档中不存在的路径的路径值对将被忽略，并且没有任何影响。

在 MySQL 8.0.4 中，优化器可以执行 JSON 列的部分就地更新，而不是删除旧文档并将新文档全部写入该列。可以对使用 JSON_SET() 函数并满足 JSON 值的部分更新中列出的条件的更新语句执行此优化。

JSON_SET()、JSON_INSERT() 和 JSON_REPLACE() 函数相关：

JSON_SET() 替换现有值并添加不存在的值。
JSON_INSERT() 插入值而不替换现有值。
JSON_REPLACE() 仅替换现有值。

以下示例说明了这些差异，使用的是文档中存在的一个路径（.a）和不存在的另一条路径（.c）：

代码语言：javascript复制

mysql> SET @j = '{ "a": 1, "b": [2, 3]}';
mysql> SELECT JSON_SET(@j, '$.a', 10, '$.c', '[true, false]');
 ------------------------------------------------- 
| JSON_SET(@j, '$.a', 10, '$.c', '[true, false]') |
 ------------------------------------------------- 
| {"a": 10, "b": [2, 3], "c": "[true, false]"}    |
 ------------------------------------------------- 
mysql> SELECT JSON_INSERT(@j, '$.a', 10, '$.c', '[true, false]');
 ---------------------------------------------------- 
| JSON_INSERT(@j, '$.a', 10, '$.c', '[true, false]') |
 ---------------------------------------------------- 
| {"a": 1, "b": [2, 3], "c": "[true, false]"}        |
 ---------------------------------------------------- 
mysql> SELECT JSON_REPLACE(@j, '$.a', 10, '$.c', '[true, false]');
 ----------------------------------------------------- 
| JSON_REPLACE(@j, '$.a', 10, '$.c', '[true, false]') |
 ----------------------------------------------------- 
| {"a": 10, "b": [2, 3]}                              |
 -----------------------------------------------------

10. JSON_UNQUOTE(json_val)

去掉 JSON 值的引号，并将结果作为 utf8mb4 字符串返回。如果参数为 NULL，则返回 NULL。如果值以双引号开始和结束，但不是有效的 JSON 字符串文字，则会发生错误。

在字符串中，除非启用了 NO_BACKSLASH_ESCAPES SQL 模式，否则某些序列具有特殊意义。这些序列中的每一个都以一个反斜杠（）开头，称为转义符。MySQL 识别“表14.23 JSON_UNQUOTE() 特殊字符转义序列”中所示的转义序列。对于所有其它转义序列，将忽略反斜杠。也就是说，转义后的字符被解释为没有转义。例如，x 只是 x。这些序列区分大小写。例如，B 被解释为退格，但 b 被理解为 b。

表14.23 JSON_UNQUOTE() 特殊字符转义序列

转义序列	转移序列表示的字符
”	双引号
b	退格符
f	换页符
n	换行符
r	回车符
t	制表符
\	反斜杠
uXXXX	表示Unicode值XXXX的UTF-8字节

下面显示了使用此函数的两个简单示例：

代码语言：javascript复制

mysql> SET @j = '"abc"';
mysql> SELECT @j, JSON_UNQUOTE(@j);
 ------- ------------------ 
| @j    | JSON_UNQUOTE(@j) |
 ------- ------------------ 
| "abc" | abc              |
 ------- ------------------ 
mysql> SET @j = '[1, 2, 3]';
mysql> SELECT @j, JSON_UNQUOTE(@j);
 ----------- ------------------ 
| @j        | JSON_UNQUOTE(@j) |
 ----------- ------------------ 
| [1, 2, 3] | [1, 2, 3]        |
 ----------- ------------------

以下一组示例显示了在禁用和启用 NO_BACKSLASH_ESCAPES 的情况下，JSON_UNQUOTE 如何处理转义：

代码语言：javascript复制

mysql> SELECT @@sql_mode;
 ------------ 
| @@sql_mode |
 ------------ 
|            |
 ------------ 

mysql> SELECT JSON_UNQUOTE('"\t\u0032"');
 ------------------------------ 
| JSON_UNQUOTE('"\t\u0032"') |
 ------------------------------ 
|       2                      |
 ------------------------------ 

mysql> SET @@sql_mode = 'NO_BACKSLASH_ESCAPES';
mysql> SELECT JSON_UNQUOTE('"\t\u0032"');
 ------------------------------ 
| JSON_UNQUOTE('"\t\u0032"') |
 ------------------------------ 
| tu0032                     |
 ------------------------------ 

mysql> SELECT JSON_UNQUOTE('"tu0032"');
 ---------------------------- 
| JSON_UNQUOTE('"tu0032"') |
 ---------------------------- 
|       2                    |
 ----------------------------

五、返回 JSON 值属性的函数

本节中的函数返回 JSON 值的属性。

1. JSON_DEPTH(json_doc)

返回 JSON 文档的最大深度。如果参数为 NULL，则返回 NULL。如果参数不是有效的 JSON 文档，则会发生错误。

空数组、空对象或标量值的深度为 1。仅包含深度为 1 的元素的非空数组或仅包含深度 1 的成员值的非空对象的深度为 2。否则，JSON 文档的深度将大于 2。

代码语言：javascript复制

mysql> SELECT JSON_DEPTH('{}'), JSON_DEPTH('[]'), JSON_DEPTH('true');
 ------------------ ------------------ -------------------- 
| JSON_DEPTH('{}') | JSON_DEPTH('[]') | JSON_DEPTH('true') |
 ------------------ ------------------ -------------------- 
|                1 |                1 |                  1 |
 ------------------ ------------------ -------------------- 
mysql> SELECT JSON_DEPTH('[10, 20]'), JSON_DEPTH('[[], {}]');
 ------------------------ ------------------------ 
| JSON_DEPTH('[10, 20]') | JSON_DEPTH('[[], {}]') |
 ------------------------ ------------------------ 
|                      2 |                      2 |
 ------------------------ ------------------------ 
mysql> SELECT JSON_DEPTH('[10, {"a": 20}]');
 ------------------------------- 
| JSON_DEPTH('[10, {"a": 20}]') |
 ------------------------------- 
|                             3 |
 -------------------------------

2. JSON_LENGTH(json_doc[, path])

返回 JSON 文档的长度，或者，如果给定了路径参数，则返回由路径标识的文档中的值的长度。如果任何参数为 NULL 或路径参数未标识文档中的值，则返回 NULL。如果 json_doc 参数不是有效的 JSON 文档，或者路径参数不是有效路径表达式，则会发生错误。在 MySQL 8.0.26 之前，如果路径表达式包含 * 或 ** 通配符，也会引发错误。

文档长度确定如下：

标量的长度为1。
数组的长度是数组元素的数量。
对象的长度是对象成员的数量。
长度不计算嵌套数组或对象的长度。

代码语言：javascript复制

mysql> SELECT JSON_LENGTH('[1, 2, {"a": 3}]');
 --------------------------------- 
| JSON_LENGTH('[1, 2, {"a": 3}]') |
 --------------------------------- 
|                               3 |
 --------------------------------- 
mysql> SELECT JSON_LENGTH('{"a": 1, "b": {"c": 30}}');
 ----------------------------------------- 
| JSON_LENGTH('{"a": 1, "b": {"c": 30}}') |
 ----------------------------------------- 
|                                       2 |
 ----------------------------------------- 
mysql> SELECT JSON_LENGTH('{"a": 1, "b": {"c": 30}}', '$.b');
 ------------------------------------------------ 
| JSON_LENGTH('{"a": 1, "b": {"c": 30}}', '$.b') |
 ------------------------------------------------ 
|                                              1 |
 ------------------------------------------------

3. JSON_TYPE(json_val)

返回一个 utf8mb4 字符串，指示 JSON 值的类型。可以是对象、数组或标量类型，如下所示：

代码语言：javascript复制

mysql> SET @j = '{"a": [10, true]}';
mysql> SELECT JSON_TYPE(@j);
 --------------- 
| JSON_TYPE(@j) |
 --------------- 
| OBJECT        |
 --------------- 
mysql> SELECT JSON_TYPE(JSON_EXTRACT(@j, '$.a'));
 ------------------------------------ 
| JSON_TYPE(JSON_EXTRACT(@j, '$.a')) |
 ------------------------------------ 
| ARRAY                              |
 ------------------------------------ 
mysql> SELECT JSON_TYPE(JSON_EXTRACT(@j, '$.a[0]'));
 --------------------------------------- 
| JSON_TYPE(JSON_EXTRACT(@j, '$.a[0]')) |
 --------------------------------------- 
| INTEGER                               |
 --------------------------------------- 
mysql> SELECT JSON_TYPE(JSON_EXTRACT(@j, '$.a[1]'));
 --------------------------------------- 
| JSON_TYPE(JSON_EXTRACT(@j, '$.a[1]')) |
 --------------------------------------- 
| BOOLEAN                               |
 ---------------------------------------

如果参数为 NULL，则 JSON_TYPE() 返回 NULL：

代码语言：javascript复制

mysql> SELECT JSON_TYPE(NULL);
 ----------------- 
| JSON_TYPE(NULL) |
 ----------------- 
| NULL            |
 -----------------

如果参数不是有效的 JSON 值，则会发生错误：

代码语言：javascript复制

mysql> SELECT JSON_TYPE(1);
ERROR 3146 (22032): Invalid data type for JSON data in argument 1
to function json_type; a JSON string or JSON type is required.

对于非 NULL、非错误的结果，以下列表描述了可能的 JSON_TYPE() 返回值：

纯 JSON 类型：
- OBJECT：JSON 对象
- ARRAY：JSON 数组
- BOOLEAN：JSON true 和 false 文本
- NULL：JSON null 文本
数字类型：
- INTEGER：MySQL TINYINT、SMALLINT、MEDIUMINT 以及 INT 和 BIGINT 标量
- DOUBLE：MySQL DOUBLE FLOAT 标量
- DECIMAL：MySQL DECIMAL 和 NUMERIC 标量
时间类型：
- DATETIME：MySQL DATETIME 和 TIMESTAMP 标量
- DATE：MySQL DATE 标量
- TIME：MySQL TIME 标量
字符串类型：
- STRING：MySQL utf8mb3 字符类型标量：CHAR、VARCHAR、TEXT、ENUM 和 SET
二进制类型：
- BLOB：MySQL 二进制类型标量，包括 BINARY、VARBINARY、BLOB 和 BIT
所有其他类型：
- OPAQUE（原始位）

4. JSON_VALID(val)

返回 0 或 1 以指示值是否为有效的 JSON。如果参数为 NULL，则返回 NULL。

代码语言：javascript复制

mysql> SELECT JSON_VALID('{"a": 1}');
 ------------------------ 
| JSON_VALID('{"a": 1}') |
 ------------------------ 
|                      1 |
 ------------------------ 
mysql> SELECT JSON_VALID('hello'), JSON_VALID('"hello"');
 --------------------- ----------------------- 
| JSON_VALID('hello') | JSON_VALID('"hello"') |
 --------------------- ----------------------- 
|                   0 |                     1 |
 --------------------- -----------------------

六、JSON 表函数

本节包含有关将 JSON 数据转换为表格数据的 JSON 函数的信息。MySQL 8.0 支持一个这样的函数，JSON_TABLE()。

JSON_TABLE(expr, path COLUMNS (column_list) [AS] alias) 从 JSON 文档中提取数据，并将其作为具有指定列的关系表返回。此函数的完整语法如下所示：

代码语言：javascript复制

JSON_TABLE(
    expr,
    path COLUMNS (column_list)
)   [AS] alias

column_list:
    column[, column][, ...]

column:
    name FOR ORDINALITY
    |  name type PATH string path [on_empty] [on_error]
    |  name type EXISTS PATH string path
    |  NESTED [PATH] path COLUMNS (column_list)

on_empty:
    {NULL | DEFAULT json_string | ERROR} ON EMPTY

on_error:
    {NULL | DEFAULT json_string | ERROR} ON ERROR

expr：这是一个返回 JSON 数据的表达式。可以是常量（'{"a":1}'）、列（t1.json_data，给定在 FROM 子句中的 json_table() 之前指定的表 t1）或函数调用（JSON_EXTRACT(t1.json_data,'$.post.comments')）。 path：一个 JSON 路径表达式，应用于数据源。将与路径匹配的 JSON 值称为行源；这用于生成一行关系数据。COLUMNS 子句评估行源，在行源中查找特定的 JSON 值，并将这些 JSON 值作为关系数据行的各个列中的 SQL 值返回。

alias 是必需的。应用表别名的通常规则（参见“第11.2节模式对象名称”）。

从 MySQL 8.0.27 开始，此函数以不区分大小写的方式比较列名。

JSON_TABLE() 支持四种类型的列，如下表所示：

name FOR ORDINALITY：此类型枚举 COLUMNS 子句中的行；名为 name 的列是一个计数器，其类型为 UNSIGNED INT，初始值为 1。这相当于在 CREATE TABLE 语句中将列指定为 AUTO_INCREMENT，并可用于区分 NESTED [PATH] 子句生成的多行中具有相同值的父行。
name type PATH string_path [on_empty] [on_error]：此类型的列用于提取字符串路径指定的值。type 是 MySQL 标量数据类型（也就是说，它不能是对象或数组）。JSON_TABLE() 将数据提取为 JSON，然后使用 MySQL 中 JSON 数据的常规自动类型转换将其强制为列类型。缺少的值会触发 on_empty 子句。保存对象或数组会触发可选的 on error 子句；当在从保存为 JSON 的值强制到表列的过程中发生错误时，也会发生这种情况，例如试图将字符串 “asd” 保存到整数列。
name type EXISTS PATH path：如果路径指定的位置存在任何数据，则此列返回 1，否则返回 0。类型可以是任何有效的 MySQL 数据类型，但通常应指定为某种 INT。
NESTED [PATH] path COLUMNS (column_list)：这将 JSON 数据中的嵌套对象或数组与父对象或数组中的 JSON 值一起展开为一行。使用多个 PATH 选项可以将 JSON 值从多个嵌套级别投影到一行中。

path 是相对于 JSON_TABLE() 的父路径行路径，或者在嵌套路径的情况下相对于父 NESTED [path] 子句的路径。

如果指定了 on empty，则确定在数据缺失的情况下，JSON_TABLE() 将执行什么操作（取决于类型）。当 NESTED PATH 子句中的列没有匹配项并且为其生成了 NULL 补齐行时，该子句也会在该列上触发。on empty 采用以下值之一：

NULL ON EMPTY：该列设置为 NULL；这是默认行为。
DEFAULT json_string ON EMPTY：只要提供的 json_string 是有效的，它将被解析为 JSON，并代替缺失值被存储。列类型规则也适用于默认值。
ERROR ON EMPTY：引发错误。

如果使用，on_error 取以下值之一，相应的结果如下所示：

NULL ON ERROR：列设置为 NULL；这是默认行为。
DEFAULT json string ON ERROR：json_string 被解析为 JSON（前提是它是有效的）并被存储，而不是对象或数组。
ERROR ON ERROR：引发错误。

在 MySQL 8.0.20 之前，如果指定了 NULL ON ERROR 或 DEFAULT ... ON ERROR，在显式或隐式类型转换出现错误时会引发警告。在 MySQL 8.0.20 及更高版本中，情况不再如此。（Bug#30628330）

以前，可以按任意顺序指定 ON EMPTY 和 ON ERROR 子句。这与 SQL 标准背道而驰，SQL 标准规定，如果指定 ON EMPTY，则必须在任何 ON ERROR 子句之前。因此，从 MySQL 8.0.20 开始，不建议在 ON EMPTY 之前指定 ON ERROR；尝试这样做会导致服务器发出警告。期望在 MySQL 的未来版本中删除对非标准语法的支持。

当保存到列的值被截断时，例如在 DECIMAL(10,1) 列中保存 3.14159 时，将独立于任何 ON ERROR 选项发出警告。当在一条语句中截断多个值时，只会发出一次警告。

在 MySQL 8.0.21 之前，当传递到此函数的表达式和路径解析为 JSON null 时，JSON_TABLE() 引发了一个错误。在 MySQL 8.0.21 及更高版本中，在这种情况下，它会根据 SQL 标准返回 SQL NULL，如下所示（Bug#31345503，Bug#99557）：

代码语言：javascript复制

mysql> SELECT *
    ->   FROM
    ->     JSON_TABLE(
    ->       '[ {"c1": null} ]',
    ->       '$[*]' COLUMNS( c1 INT PATH '$.c1' ERROR ON ERROR )
    ->     ) as jt;
 ------ 
| c1   |
 ------ 
| NULL |
 ------ 
1 row in set (0.00 sec)

以下查询演示了 ON EMPTY 和 ON ERROR 的使用。对于路径 "$.a"，对应于 {"b":1} 的行为空，尝试将 [1,2] 保存为标量会产生错误；这些行在显示的输出中高亮显示。

代码语言：javascript复制

mysql> SELECT *
    -> FROM
    ->   JSON_TABLE(
    ->     '[{"a":"3"},{"a":2},{"b":1},{"a":0},{"a":[1,2]}]',
    ->     "$[*]"
    ->     COLUMNS(
    ->       rowid FOR ORDINALITY,
    ->       ac VARCHAR(100) PATH "$.a" DEFAULT '111' ON EMPTY DEFAULT '999' ON ERROR,
    ->       aj JSON PATH "$.a" DEFAULT '{"x": 333}' ON EMPTY,
    ->       bx INT EXISTS PATH "$.b"
    ->     )
    ->   ) AS tt;

 ------- ------ ------------ ------ 
| rowid | ac   | aj         | bx   |
 ------- ------ ------------ ------ 
|     1 | 3    | "3"        |    0 |
|     2 | 2    | 2          |    0 |
|     3 | 111  | {"x": 333} |    1 |
|     4 | 0    | 0          |    0 |
|     5 | 999  | [1, 2]     |    0 |
 ------- ------ ------------ ------ 
5 rows in set (0.00 sec)

列名受管理表列名的常规规则和限制的约束。参见“第11.2节模式对象名称”。

会检查所有 JSON 和 JSON 路径表达式的有效性；任何一种类型的无效表达式都会导致错误。 COLUMNS 关键字前面的路径的每个匹配项都映射到结果表中的单个行。例如，以下查询给出的结果如下所示：

代码语言：javascript复制

mysql> SELECT *
    -> FROM
    ->   JSON_TABLE(
    ->     '[{"x":2,"y":"8"},{"x":"3","y":"7"},{"x":"4","y":6}]',
    ->     "$[*]" COLUMNS(
    ->       xval VARCHAR(100) PATH "$.x",
    ->       yval VARCHAR(100) PATH "$.y"
    ->     )
    ->   ) AS  jt1;

 ------ ------ 
| xval | yval |
 ------ ------ 
| 2    | 8    |
| 3    | 7    |
| 4    | 6    |
 ------ ------

表达式 "

代码语言：javascript复制

mysql> SELECT *
    -> FROM
    ->   JSON_TABLE(
    ->     '[{"x":2,"y":"8"},{"x":"3","y":"7"},{"x":"4","y":6}]',
    ->     "$[1]" COLUMNS(
    ->       xval VARCHAR(100) PATH "$.x",
    ->       yval VARCHAR(100) PATH "$.y"
    ->     )
    ->   ) AS  jt1;

 ------ ------ 
| xval | yval |
 ------ ------ 
| 3    | 7    |
 ------ ------

在列定义中，"

NESTED PATH（或简称 NESTED；PATH 是可选的）为其所属的 COLUMNS 子句中的每个匹配生成一组记录。如果不匹配，则嵌套路径的所有列都设置为 NULL。这实现了最顶层子句和 NESTED [PATH] 之间的外连接。可以通过在 WHERE 子句中应用适当的条件来模拟内连接，如下所示：

代码语言：javascript复制

mysql> SELECT *
    -> FROM
    ->   JSON_TABLE(
    ->     '[ {"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3}]',
    ->     '$[*]' COLUMNS(
    ->             a INT PATH '$.a',
    ->             NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$')
    ->            )
    ->    ) AS jt
    -> WHERE b IS NOT NULL;

 ------ ------ 
| a    | b    |
 ------ ------ 
|    1 |   11 |
|    1 |  111 |
|    2 |   22 |
|    2 |  222 |
 ------ ------

同级嵌套路径——也就是说，同一 COLUMNS 子句中的两个或多个 NESTED [PATH] 实例——一个接一个地处理，一次一个。当一个嵌套路径生成记录时，任何同级嵌套路径表达式的列都设置为 NULL。这意味着单个包含 COLUMNS 子句中单个匹配的记录总数是 NESTED [PATH] 修饰符生成的所有记录的总和，而不是乘积，如下所示：

代码语言：javascript复制

mysql> SELECT *
    -> FROM
    ->   JSON_TABLE(
    ->     '[{"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}]',
    ->     '$[*]' COLUMNS(
    ->         a INT PATH '$.a',
    ->         NESTED PATH '$.b[*]' COLUMNS (b1 INT PATH '$'),
    ->         NESTED PATH '$.b[*]' COLUMNS (b2 INT PATH '$')
    ->     )
    -> ) AS jt;

 ------ ------ ------ 
| a    | b1   | b2   |
 ------ ------ ------ 
|    1 |   11 | NULL |
|    1 |  111 | NULL |
|    1 | NULL |   11 |
|    1 | NULL |  111 |
|    2 |   22 | NULL |
|    2 |  222 | NULL |
|    2 | NULL |   22 |
|    2 | NULL |  222 |
 ------ ------ ------

FOR ORDINALITY 列枚举 COLUMNS 子句生成的记录，可用于区分嵌套路径的父记录，尤其是在父记录中的值相同的情况下，如图所示：

代码语言：javascript复制

mysql> SELECT *
    -> FROM
    ->   JSON_TABLE(
    ->     '[{"a": "a_val",
    '>       "b": [{"c": "c_val", "l": [1,2]}]},
    '>     {"a": "a_val",
    '>       "b": [{"c": "c_val","l": [11]}, {"c": "c_val", "l": [22]}]}]',
    ->     '$[*]' COLUMNS(
    ->       top_ord FOR ORDINALITY,
    ->       apath VARCHAR(10) PATH '$.a',
    ->       NESTED PATH '$.b[*]' COLUMNS (
    ->         bpath VARCHAR(10) PATH '$.c',
    ->         ord FOR ORDINALITY,
    ->         NESTED PATH '$.l[*]' COLUMNS (lpath varchar(10) PATH '$')
    ->         )
    ->     )
    -> ) as jt;

 --------- --------- --------- ------ ------- 
| top_ord | apath   | bpath   | ord  | lpath |
 --------- --------- --------- ------ ------- 
|       1 |  a_val  |  c_val  |    1 | 1     |
|       1 |  a_val  |  c_val  |    1 | 2     |
|       2 |  a_val  |  c_val  |    1 | 11    |
|       2 |  a_val  |  c_val  |    2 | 22    |
 --------- --------- --------- ------ -------

源文档包含一个由两个元素组成的数组；这些元素中的每一个都产生两行。apath 和 bpath 的值在整个结果集中是相同的；这意味着它们不能用于确定 lpath 值是来自相同的还是不同的父级。ord 列的值与 top_ord 等于 1 的记录集保持相同，因此这两个值来自单个对象。其余两个值来自不同的对象，因为它们在 ord 列中具有不同的值。

通常，不能连接依赖于同一 FROM 子句中前面表列的派生表。MySQL 按照 SQL 标准，为表函数产生一个异常；即使在还不支持 LATERAL 关键字（8.0.13 及更早版本）的 MySQL 版本中，这些表也被视为横向派生表。在支持 LATERAL 的版本（8.0.14 及更高版本）中，它是隐式的，因此也是根据标准，在 JSON_TABLE() 之前是不允许的。

假设使用以下语句创建并填充了一个表 t1：

代码语言：javascript复制

CREATE TABLE t1 (c1 INT, c2 CHAR(1), c3 JSON);

INSERT INTO t1 () VALUES
    ROW(1, 'z', JSON_OBJECT('a', 23, 'b', 27, 'c', 1)),
    ROW(1, 'y', JSON_OBJECT('a', 44, 'b', 22, 'c', 11)),
    ROW(2, 'x', JSON_OBJECT('b', 1, 'c', 15)),
    ROW(3, 'w', JSON_OBJECT('a', 5, 'b', 6, 'c', 7)),
    ROW(5, 'v', JSON_OBJECT('a', 123, 'c', 1111))
;

然后可以执行连接，例如下面的连接，其中 JSON_TABLE() 充当派生表，同时引用以前引用的表中的列：

代码语言：javascript复制

SELECT c1, c2, JSON_EXTRACT(c3, '$.*') 
FROM t1 AS m 
JOIN 
JSON_TABLE(
  m.c3, 
  '$.*' 
  COLUMNS(
    at VARCHAR(10) PATH '$.a' DEFAULT '1' ON EMPTY, 
    bt VARCHAR(10) PATH '$.b' DEFAULT '2' ON EMPTY, 
    ct VARCHAR(10) PATH '$.c' DEFAULT '3' ON EMPTY
  )
) AS tt
ON m.c1 > tt.at;

尝试将 LATERAL 关键字与此查询一起使用会引发 ER_PARSE_ERROR。

七、JSON 模式验证函数

从 MySQL 8.0.17 开始支持根据符合 JSON 模式规范草案 4 的 JSON 模式验证 JSON 文档。可以使用本节中介绍的任何一个函数来完成，这两个函数都接受两个参数，一个 JSON 模式和一个根据该模式验证的 JSON 文档。如果文档通过模式验证，JSON_SCHEMA_VALID() 返回 true，否则返回 false；JSON_SCHEMA_VALIDATION_REPORT() 以 JSON 格式提供验证报告。

这两个函数都如下处理 null 或无效输入：

如果至少有一个参数为 NULL，则函数将返回 NULL。
如果至少有一个参数不是有效的 JSON，则函数会引发错误（ER_INVALID_TYPE_FOR_JSON）
此外，如果模式不是有效的 JSON 对象，则函数返回 ER_INVALID_JSON_TYPE。

MySQL 支持 JSON 模式中的 required 属性，以强制包含必需的属性（参阅函数描述中的示例）。

MySQL 支持 JSON 模式中的 id、$schema、description 和 type 属性，但不强制要求这些属性。

MySQL 不支持 JSON 模式中的外部资源；使用 $ref 关键字会导致 JSON_SCHEMA_VALID() 失败，并显示 ER_NOT_SUPPORTED_YET。

说明：MySQL 支持 JSON 模式中的正则表达式模式，JSON 模式支持但忽略无效模式（示例参见 JSON_schema_VALID() 的描述）。

以下列表详细介绍了这些功能。

1. JSON_SCHEMA_VALID(schema,document)

根据 JSON 模式验证 JSON 文档。模式和文档都是必需的。模式必须是有效的 JSON 对象；文档必须是有效的 JSON 文档。满足这些条件后：如果文档根据模式通过验证，则函数返回 true（1）；否则返回 false（0）。

在本例中，将用户变量 @schema 设置为地理坐标的 JSON 模式的值，将另一个变量 @document 设置为包含一个此类坐标的 JSON 文档的值。然后将 @document 作为 JSON_schema_VALID() 的参数，验证它们是否通过 @schema 验证：

代码语言：javascript复制

mysql> SET @schema = '{
    '>  "id": "http://json-schema.org/geo",
    '> "$schema": "http://json-schema.org/draft-04/schema#",
    '> "description": "A geographical coordinate",
    '> "type": "object",
    '> "properties": {
    '>   "latitude": {
    '>     "type": "number",
    '>     "minimum": -90,
    '>     "maximum": 90
    '>   },
    '>   "longitude": {
    '>     "type": "number",
    '>     "minimum": -180,
    '>     "maximum": 180
    '>   }
    '> },
    '> "required": ["latitude", "longitude"]
    '>}';
Query OK, 0 rows affected (0.01 sec)

mysql> SET @document = '{
    '> "latitude": 63.444697,
    '> "longitude": 10.445118
    '>}';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT JSON_SCHEMA_VALID(@schema, @document);
 --------------------------------------- 
| JSON_SCHEMA_VALID(@schema, @document) |
 --------------------------------------- 
|                                     1 |
 --------------------------------------- 
1 row in set (0.00 sec)

由于 @schema 包含必需的属性，可以将 @document 设置为一个在其它方面有效但不包含必需属性的值，然后根据 @schema 进行测试，如下所示：

代码语言：javascript复制

mysql> SET @document = '{}';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT JSON_SCHEMA_VALID(@schema, @document);
 --------------------------------------- 
| JSON_SCHEMA_VALID(@schema, @document) |
 --------------------------------------- 
|                                     0 |
 --------------------------------------- 
1 row in set (0.00 sec)

如果现在将 @schema 的值设置为相同的 JSON 模式，但没有 required 属性，则 @document 会通过验证，因为它是一个有效的 JSON 对象，即使它不包含任何属性，如下所示：

代码语言：javascript复制

mysql> SET @schema = '{
    '> "id": "http://json-schema.org/geo",
    '> "$schema": "http://json-schema.org/draft-04/schema#",
    '> "description": "A geographical coordinate",
    '> "type": "object",
    '> "properties": {
    '>   "latitude": {
    '>     "type": "number",
    '>     "minimum": -90,
    '>     "maximum": 90
    '>   },
    '>   "longitude": {
    '>     "type": "number",
    '>     "minimum": -180,
    '>     "maximum": 180
    '>   }
    '> }
    '>}';
Query OK, 0 rows affected (0.00 sec)


mysql> SELECT JSON_SCHEMA_VALID(@schema, @document);
 --------------------------------------- 
| JSON_SCHEMA_VALID(@schema, @document) |
 --------------------------------------- 
|                                     1 |
 --------------------------------------- 
1 row in set (0.00 sec)

JSON_SCHEMA_VALID() 和 CHECK 约束 JSON_SCHEMA_VALID() 也可以用于强制执行 CHECK 约束。考虑如下所示创建的 geo 表，其中 JSON 列坐标表示地图上的经纬度点，受 JSON_SCHEMA_VALID() 调用中用作参数的 JSON 模式控制，该调用作为此表上传递给 CHECK 约束的表达式：

代码语言：javascript复制

mysql> CREATE TABLE geo (
    ->     coordinate JSON,
    ->     CHECK(
    ->         JSON_SCHEMA_VALID(
    ->             '{
    '>                 "type":"object",
    '>                 "properties":{
    '>                       "latitude":{"type":"number", "minimum":-90, "maximum":90},
    '>                       "longitude":{"type":"number", "minimum":-180, "maximum":180}
    '>                 },
    '>                 "required": ["latitude", "longitude"]
    '>             }',
    ->             coordinate
    ->         )
    ->     )
    -> );
Query OK, 0 rows affected (0.45 sec)

说明：因为 MySQL CHECK 约束不能包含对变量的引用，所以在使用 JSON 模式为表指定此类约束时，必须将其内联传递给 JSON_SCHEMA_VALID()。

下面将表示坐标的 JSON 值分配给三个变量，如下所示：

代码语言：javascript复制

mysql> SET @point1 = '{"latitude":59, "longitude":18}';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @point2 = '{"latitude":91, "longitude":0}';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @point3 = '{"longitude":120}';
Query OK, 0 rows affected (0.00 sec)

第一个值是有效的，如以下 INSERT 语句所示：

代码语言：javascript复制

mysql> INSERT INTO geo VALUES(@point1);
Query OK, 1 row affected (0.05 sec)

第二个 JSON 值无效，因此使约束失败，如下所示：

代码语言：javascript复制

mysql> INSERT INTO geo VALUES(@point2);
ERROR 3819 (HY000): Check constraint 'geo_chk_1' is violated.

在 MySQL 8.0.19 及更高版本中，可以通过发出 SHOW WARNINGS 语句来获得有关失败原因的精确信息——在本例下，纬度值超过了模式中定义的最大值：

代码语言：javascript复制

mysql> SHOW WARNINGSG
*************************** 1. row ***************************
  Level: Error
   Code: 3934
Message: The JSON document location '#/latitude' failed requirement 'maximum' at
JSON Schema location '#/properties/latitude'.
*************************** 2. row ***************************
  Level: Error
   Code: 3819
Message: Check constraint 'geo_chk_1' is violated.
2 rows in set (0.00 sec)

上面定义的第三个坐标值也是无效的，因为它缺少所需的纬度属性。和前面一样，可以通过尝试将值插入 geo 表，然后发出 SHOW WARNINGS 来看到这一点：

代码语言：javascript复制

mysql> INSERT INTO geo VALUES(@point3);
ERROR 3819 (HY000): Check constraint 'geo_chk_1' is violated.
mysql> SHOW WARNINGSG
*************************** 1. row ***************************
  Level: Error
   Code: 3934
Message: The JSON document location '#' failed requirement 'required' at JSON
Schema location '#'.
*************************** 2. row ***************************
  Level: Error
   Code: 3819
Message: Check constraint 'geo_chk_1' is violated.
2 rows in set (0.00 sec)

有关更多信息，参见“第15.1.20.6节 CHECK 约束”。

JSON Schema 支持为字符串指定正则表达式模式，但 MySQL 使用的实现会忽略无效模式。这意味着即使正则表达式模式无效，JSON_SCHEMA_VALID() 也可以返回 true，如下所示：

代码语言：javascript复制

mysql> SELECT JSON_SCHEMA_VALID('{"type":"string","pattern":"("}', '"abc"');
 --------------------------------------------------------------- 
| JSON_SCHEMA_VALID('{"type":"string","pattern":"("}', '"abc"') |
 --------------------------------------------------------------- 
|                                                             1 |
 --------------------------------------------------------------- 
1 row in set (0.04 sec)

2. JSON_SCHEMA_VALIDATION_REPORT(schema,document)

根据 JSON 模式验证 JSON 文档。模式和文档都是必需的。与 JSON_VALID_SCHEMA() 一样，模式必须是一个有效的 JSON 对象，文档必须是有效的 JSON 文档。如果满足这些条件，函数将以 JSON 文档的形式返回验证结果的报告。如果根据 JSON 模式，JSON 文档被认为是有效的，那么函数将返回一个 JSON 对象，其中一个 valid 属性值为“true”。如果 JSON 文档验证失败，函数将返回一个 JSON 对象，该对象包括此处列出的属性：

valid：对于失败的模式验证总是“false”
reason：包含失败原因的可读字符串
schema-location：JSON 指针 URI 片段标识符，指示 JSON 模式中验证失败的位置（参阅此列表后面的说明）
document-location：JSON 指针 URI 片段标识符，指示 JSON 文档中验证失败的位置（参阅此列表后面的说明）
schema-failed-keyword：包含违反的 JSON 模式中关键字或属性名称的字符串

说明：JSON 指针 URI 片段标识符在 RFC 6901 - JavaScript 对象表示法（JSON）指针中定义。（这与 JSON_EXTRACT() 和其它 MySQL JSON 函数使用的 JSON 路径表示法不同。）在这种表示法中，# 表示整个文档，#/myprop 表示名为 myprop 的顶级属性中包含的文档部分。有关更多信息，参阅刚才指出的规范和本节后面显示的示例。在下面的例子中，将用户变量 @schema 设置为地理坐标的 JSON 模式的值，将另一个变量 @document 设置为包含一个此类坐标的 JSON 文档的值。然后使用它们作为 JSON_SCHEMA_VALIDATION_REORT() 的参数，验证 @document 是否符合模式 @schema ：

代码语言：javascript复制

mysql> SET @schema = '{
    '>  "id": "http://json-schema.org/geo",
    '> "$schema": "http://json-schema.org/draft-04/schema#",
    '> "description": "A geographical coordinate",
    '> "type": "object",
    '> "properties": {
    '>   "latitude": {
    '>     "type": "number",
    '>     "minimum": -90,
    '>     "maximum": 90
    '>   },
    '>   "longitude": {
    '>     "type": "number",
    '>     "minimum": -180,
    '>     "maximum": 180
    '>   }
    '> },
    '> "required": ["latitude", "longitude"]
    '>}';
Query OK, 0 rows affected (0.01 sec)

mysql> SET @document = '{
    '> "latitude": 63.444697,
    '> "longitude": 10.445118
    '>}';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT JSON_SCHEMA_VALIDATION_REPORT(@schema, @document);
 --------------------------------------------------- 
| JSON_SCHEMA_VALIDATION_REPORT(@schema, @document) |
 --------------------------------------------------- 
| {"valid": true}                                   |
 --------------------------------------------------- 
1 row in set (0.00 sec)

现在设置 @document，将其中一个属性指定非法值，如下所示：

代码语言：javascript复制

mysql> SET @document = '{
    '> "latitude": 63.444697,
    '> "longitude": 310.445118
    '> }';

使用 JSON_SCHEMA_VALIDATION_REPORT() 测试时，这次 @document 的验证将失败。函数调用的输出包含有关失败的详细信息（函数由 JSON_PRETTY() 封装以提供更好的格式），如下所示：

代码语言：javascript复制

mysql> SELECT JSON_PRETTY(JSON_SCHEMA_VALIDATION_REPORT(@schema, @document))G
*************************** 1. row ***************************
JSON_PRETTY(JSON_SCHEMA_VALIDATION_REPORT(@schema, @document)): {
  "valid": false,
  "reason": "The JSON document location '#/longitude' failed requirement 'maximum' at JSON Schema location '#/properties/longitude'",
  "schema-location": "#/properties/longitude",
  "document-location": "#/longitude",
  "schema-failed-keyword": "maximum"
}
1 row in set (0.00 sec)

由于 @schema 包含 required 属性，可以将 @document 设置为一个在其他方面有效但不包含必需属性的值，然后根据 @schema 进行测试。JSON_SCHEMA_VALIDATION_REPORT() 的输出显示验证由于缺少所需元素而失败，如下所示：

代码语言：javascript复制

mysql> SET @document = '{}';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT JSON_PRETTY(JSON_SCHEMA_VALIDATION_REPORT(@schema, @document))G
*************************** 1. row ***************************
JSON_PRETTY(JSON_SCHEMA_VALIDATION_REPORT(@schema, @document)): {
  "valid": false,
  "reason": "The JSON document location '#' failed requirement 'required' at JSON Schema location '#'",
  "schema-location": "#",
  "document-location": "#",
  "schema-failed-keyword": "required"
}
1 row in set (0.00 sec)

代码语言：javascript复制

mysql> SET @schema = '{
    '> "id": "http://json-schema.org/geo",
    '> "$schema": "http://json-schema.org/draft-04/schema#",
    '> "description": "A geographical coordinate",
    '> "type": "object",
    '> "properties": {
    '>   "latitude": {
    '>     "type": "number",
    '>     "minimum": -90,
    '>     "maximum": 90
    '>   },
    '>   "longitude": {
    '>     "type": "number",
    '>     "minimum": -180,
    '>     "maximum": 180
    '>   }
    '> }
    '>}';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT JSON_SCHEMA_VALIDATION_REPORT(@schema, @document);
 --------------------------------------------------- 
| JSON_SCHEMA_VALIDATION_REPORT(@schema, @document) |
 --------------------------------------------------- 
| {"valid": true}                                   |
 --------------------------------------------------- 
1 row in set (0.00 sec)

八、JSON 工具函数

本节介绍作用于 JSON 值，或者可以解析为 JSON 值的字符串的工具函数。JSON_PRETTY() 以易于读取的格式打印出 JSON 值。JSON_STORAGE_SIZE() 和 JSON_STORAGE_FREE() 分别显示给定 JSON 值使用的存储空间大小和部分更新后 JSON 列中剩余的空间大小。

1. JSON_PRETTY(json_val)

提供类似于在 PHP、其它语言或数据库系统中实现的 JSON 值的格式化输出。提供的值必须是 JSON 值或 JSON 值的有效字符串表示形式。此值中存在的额外空白和换行符对输出没有影响。对于 NULL 值，函数将返回 NULL。如果该值不是 JSON 文档，或者无法将其解析为 JSON 文档，则函数将失败并返回错误。

此函数输出的格式遵循以下规则：

每个数组元素或对象成员都显示在单独的一行上，与其父级相比缩进一级。
每一级缩进都会添加两个前导空格。
分隔单个数组元素或对象成员的逗号将打印在分隔两个元素或成员的换行符之前。
对象成员的键和值由冒号和空格（': '）分隔。
空对象或数组打印在一行上。左大括号和右大括号之间没有空格。
字符串标量和键名中的特殊字符采用与 JSON_QUOTE() 函数相同的规则进行转义。

代码语言：javascript复制

mysql> SELECT JSON_PRETTY('123'); # scalar
 -------------------- 
| JSON_PRETTY('123') |
 -------------------- 
| 123                |
 -------------------- 

mysql> SELECT JSON_PRETTY("[1,3,5]"); # array
 ------------------------ 
| JSON_PRETTY("[1,3,5]") |
 ------------------------ 
| [
  1,
  3,
  5
]      |
 ------------------------ 

mysql> SELECT JSON_PRETTY('{"a":"10","b":"15","x":"25"}'); # object
 --------------------------------------------- 
| JSON_PRETTY('{"a":"10","b":"15","x":"25"}') |
 --------------------------------------------- 
| {
  "a": "10",
  "b": "15",
  "x": "25"
}   |
 --------------------------------------------- 

mysql> SELECT JSON_PRETTY('["a",1,{"key1":
    '>    "value1"},"5",     "77" ,
    '>       {"key2":["value3","valueX",
    '> "valueY"]},"j", "2"   ]')G  # nested arrays and objects
*************************** 1. row ***************************
JSON_PRETTY('["a",1,{"key1":
             "value1"},"5",     "77" ,
                {"key2":["value3","valuex",
          "valuey"]},"j", "2"   ]'): [
  "a",
  1,
  {
    "key1": "value1"
  },
  "5",
  "77",
  {
    "key2": [
      "value3",
      "valuex",
      "valuey"
    ]
  },
  "j",
  "2"
]

2. JSON_STORAGE_FREE(json_val)

对于 JSON 列值，此函数显示在使用 JSON_SET()、JSON_REPLACE() 或 JSON_REMOVE() 就地更新后，其二进制表示释放了多少存储空间。参数可以是一个有效的 JSON 文档或一个字符串，该字符串可以被解析为文本值或用户变量的值。在后一种情况下，函数返回 0。如果参数是如前所述已更新的 JSON 列值，当返回一个正值时，表示其二进制表示比更新前占用的空间少。对于已更新的 JSON 列，如果其二进制表示的大小与更新前相同或更大，或者如果更新无法利用部分更新，则返回 0；如果参数为 NULL，则返回 NULL。

如果 json_val 不为 NULL，并且既不是有效的 JSON 文档，也不能成功解析为一个 JSON 文档，则会导致错误。

下例中创建一个包含 JSON 列的表，然后插入一行包含 JSON 对象：

代码语言：javascript复制

mysql> CREATE TABLE jtable (jcol JSON);
Query OK, 0 rows affected (0.38 sec)

mysql> INSERT INTO jtable VALUES
    ->     ('{"a": 10, "b": "wxyz", "c": "[true, false]"}');
Query OK, 1 row affected (0.04 sec)

mysql> SELECT * FROM jtable;
 ---------------------------------------------- 
| jcol                                         |
 ---------------------------------------------- 
| {"a": 10, "b": "wxyz", "c": "[true, false]"} |
 ---------------------------------------------- 
1 row in set (0.00 sec)

现在使用 JSON_SET() 更新列值，以便可以执行部分更新；本例中将 c 键指向的值（数组 [true，false]）替换为占用较少空间的值（整数 1）：

代码语言：javascript复制

mysql> UPDATE jtable
    ->     SET jcol = JSON_SET(jcol, "$.a", 10, "$.b", "wxyz", "$.c", 1);
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM jtable;
 -------------------------------- 
| jcol                           |
 -------------------------------- 
| {"a": 10, "b": "wxyz", "c": 1} |
 -------------------------------- 
1 row in set (0.00 sec)

mysql> SELECT JSON_STORAGE_FREE(jcol) FROM jtable;
 ------------------------- 
| JSON_STORAGE_FREE(jcol) |
 ------------------------- 
|                      14 |
 ------------------------- 
1 row in set (0.00 sec)

连续的部分更新对此可用空间的影响是累积的，如下所示，使用 JSON_SET() 来减少键 b 的值所占用的空间（并且不进行其它更改）：

代码语言：javascript复制

mysql> UPDATE jtable
    ->     SET jcol = JSON_SET(jcol, "$.a", 10, "$.b", "wx", "$.c", 1);
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT JSON_STORAGE_FREE(jcol) FROM jtable;
 ------------------------- 
| JSON_STORAGE_FREE(jcol) |
 ------------------------- 
|                      16 |
 ------------------------- 
1 row in set (0.00 sec)

不使用 JSON_SET()、JSON_REPLACE() 或 JSON_REMOVE() 更新列意味着优化器无法就地执行更新；在这种情况下，JSON_STORAGE_FREE() 返回0，如下所示：

代码语言：javascript复制

mysql> UPDATE jtable SET jcol = '{"a": 10, "b": 1}';
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT JSON_STORAGE_FREE(jcol) FROM jtable;
 ------------------------- 
| JSON_STORAGE_FREE(jcol) |
 ------------------------- 
|                       0 |
 ------------------------- 
1 row in set (0.00 sec)

JSON 文档的部分更新只能对列值执行。对于存储 JSON 值的用户变量，即使使用 JSON_SET() 执行更新，该值也始终被完全替换：

代码语言：javascript复制

mysql> SET @j = '{"a": 10, "b": "wxyz", "c": "[true, false]"}';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @j = JSON_SET(@j, '$.a', 10, '$.b', 'wxyz', '$.c', '1');
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @j, JSON_STORAGE_FREE(@j) AS Free;
 ---------------------------------- ------ 
| @j                               | Free |
 ---------------------------------- ------ 
| {"a": 10, "b": "wxyz", "c": "1"} |    0 |
 ---------------------------------- ------ 
1 row in set (0.00 sec)

对于 JSON 文本，此函数始终返回 0：

代码语言：javascript复制

mysql> SELECT JSON_STORAGE_FREE('{"a": 10, "b": "wxyz", "c": "1"}') AS Free;
 ------ 
| Free |
 ------ 
|    0 |
 ------ 
1 row in set (0.00 sec)

3. JSON_STORAGE_SIZE(json_val)

此函数返回用于存储 JSON 文档的二进制表示形式的字节数。当参数是 JSON 列时，这是在 JSON 文档插入列后，在随后可能对其执行的任何部分更新之前，用于存储 JSON 文档的空间。json_val 必须是有效的 JSON 文档或可以解析为一个的字符串。在字符串的情况下，函数返回二进制表示的 JSON 存储空间大小，JSON 二进制表示是通过将字符串解析为 JSON 并将其转换为二进制来创建的。如果参数为 NULL，则返回 NULL。

当 json_val 不为 NULL，并且不是 JSON 文档或无法成功解析为 JSON 文档时，会出现错误。

为了说明此函数与 JSON 列一起使用时的行为，创建一个名为 jtable 的表，其中包含 JSON 列 jcol，在表中插入一个 JSON 值，然后使用 JSON_STORAGE_SIZE() 获取该列使用的存储空间，如下所示：

代码语言：javascript复制

mysql> CREATE TABLE jtable (jcol JSON);
Query OK, 0 rows affected (0.42 sec)

mysql> INSERT INTO jtable VALUES
    ->     ('{"a": 1000, "b": "wxyz", "c": "[1, 3, 5, 7]"}');
Query OK, 1 row affected (0.04 sec)

mysql> SELECT
    ->     jcol,
    ->     JSON_STORAGE_SIZE(jcol) AS Size,
    ->     JSON_STORAGE_FREE(jcol) AS Free
    -> FROM jtable;
 ----------------------------------------------- ------ ------ 
| jcol                                          | Size | Free |
 ----------------------------------------------- ------ ------ 
| {"a": 1000, "b": "wxyz", "c": "[1, 3, 5, 7]"} |   47 |    0 |
 ----------------------------------------------- ------ ------ 
1 row in set (0.00 sec)

根据 JSON_STORAGE_SIZE() 的输出，插入列中的 JSON 文档占用 47 个字节。这里还使用 JSON_STORAGE_FREE() 检查了该列的任何部分更新之前所释放的空间大小；由于尚未执行任何更新，因此如预期的那样是 0。

下面对表执行 UPDATE，这将导致存储在 jcol 中的文档的部分更新，然后再测试结果，如下所示：

代码语言：javascript复制

mysql> UPDATE jtable SET jcol = 
    ->     JSON_SET(jcol, "$.b", "a");
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT
    ->     jcol,
    ->     JSON_STORAGE_SIZE(jcol) AS Size,
    ->     JSON_STORAGE_FREE(jcol) AS Free
    -> FROM jtable;
 -------------------------------------------- ------ ------ 
| jcol                                       | Size | Free |
 -------------------------------------------- ------ ------ 
| {"a": 1000, "b": "a", "c": "[1, 3, 5, 7]"} |   47 |    3 |
 -------------------------------------------- ------ ------ 
1 row in set (0.00 sec)

JSON_STORAGE_FREE() 在上一个查询中返回的值表示对 JSON 文档执行了部分更新，并释放了用于存储该文档的 3 个字节的空间。JSON_STTORAGE_SIZE() 返回的结果在部分更新后保持不变。

使用JSON_SET()、JSON_REPLACE() 或 JSON_REMOVE() 的更新支持部分更新。无法部分更新时，会将值直接分配给 JSON 列；在这样的更新之后，JSON_STORAGE_SIZE() 始终显示用于新设置值的存储大小：

代码语言：javascript复制

mysql> UPDATE jtable
mysql>     SET jcol = '{"a": 4.55, "b": "wxyz", "c": "[true, false]"}';
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT
    ->     jcol,
    ->     JSON_STORAGE_SIZE(jcol) AS Size,
    ->     JSON_STORAGE_FREE(jcol) AS Free
    -> FROM jtable;
 ------------------------------------------------ ------ ------ 
| jcol                                           | Size | Free |
 ------------------------------------------------ ------ ------ 
| {"a": 4.55, "b": "wxyz", "c": "[true, false]"} |   56 |    0 |
 ------------------------------------------------ ------ ------ 
1 row in set (0.00 sec)

JSON 用户变量无法部分更新。这意味着此函数始终显示当前用于在用户变量中存储 JSON 文档的空间大小：

代码语言：javascript复制

mysql> SET @j = '[100, "sakila", [1, 3, 5], 425.05]';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @j, JSON_STORAGE_SIZE(@j) AS Size;
 ------------------------------------ ------ 
| @j                                 | Size |
 ------------------------------------ ------ 
| [100, "sakila", [1, 3, 5], 425.05] |   45 |
 ------------------------------------ ------ 
1 row in set (0.00 sec)

mysql> SET @j = JSON_SET(@j, '$[1]', "json");
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @j, JSON_STORAGE_SIZE(@j) AS Size;
 ---------------------------------- ------ 
| @j                               | Size |
 ---------------------------------- ------ 
| [100, "json", [1, 3, 5], 425.05] |   43 |
 ---------------------------------- ------ 
1 row in set (0.00 sec)

mysql> SET @j = JSON_SET(@j, '$[2][0]', JSON_ARRAY(10, 20, 30));
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @j, JSON_STORAGE_SIZE(@j) AS Size;
 --------------------------------------------- ------ 
| @j                                          | Size |
 --------------------------------------------- ------ 
| [100, "json", [[10, 20, 30], 3, 5], 425.05] |   56 |
 --------------------------------------------- ------ 
1 row in set (0.00 sec)

对于 JSON 文本，此函数始终返回当前使用的存储空间：

代码语言：javascript复制

mysql> SELECT
    ->     JSON_STORAGE_SIZE('[100, "sakila", [1, 3, 5], 425.05]') AS A,
    ->     JSON_STORAGE_SIZE('{"a": 1000, "b": "a", "c": "[1, 3, 5, 7]"}') AS B,
    ->     JSON_STORAGE_SIZE('{"a": 1000, "b": "wxyz", "c": "[1, 3, 5, 7]"}') AS C,
    ->     JSON_STORAGE_SIZE('[100, "json", [[10, 20, 30], 3, 5], 425.05]') AS D;
 ---- ---- ---- ---- 
| A  | B  | C  | D  |
 ---- ---- ---- ---- 
| 45 | 44 | 47 | 56 |
 ---- ---- ---- ---- 
1 row in set (0.00 sec)

json mysql 对象函数数组

0 人点赞