MySQL8-中文参考-五十二-

布客飞龙
33 阅读36分钟

MySQL8 中文参考（五十二）

原文：docs.oracle.com/javase/tutorial/reallybigindex.html

14.16.13 空间便利函数

原文：dev.mysql.com/doc/refman/8.0/en/spatial-convenience-functions.html

本节中的函数提供对几何值的便利操作。

除非另有说明，本节中的函数处理它们的几何参数如下：

  • 如果任何参数为NULL，返回值为NULL。

  • 如果任何几何参数不是语法上良好的几何形状，则会发生ER_GIS_INVALID_DATA错误。

  • 如果任何几何参数是在未定义的空间参考系统（SRS）中的语法上良好的几何形状，则会发生ER_SRS_NOT_FOUND错误。

  • 对于接受多个几何参数的函数，如果这些参数不在相同的 SRS 中，则会发生ER_GIS_DIFFERENT_SRIDS错误。

  • 否则，返回值为非NULL。

这些便利函数可用：

  • ST_Distance_Sphere(*g1*, *g2* [, *radius*])

    返回Point或MultiPoint参数在球面上的最小距离，单位为米。（对于通用距离计算，请参见ST_Distance()函数。）可选的*radius*参数应以米为单位给出。

    如果两个几何参数都是有效的笛卡尔Point或MultiPoint值在 SRID 0 中，返回值是提供的半径上两个几何之间的最短距离。如果省略， 默认半径为 6,370,986 米，点 X 和 Y 坐标分别解释为经度和纬度，单位为度。

    如果两个几何参数都是在地理空间参考系统（SRS）中的有效Point或MultiPoint值，则返回值是提供的半径上两个几何之间的最短距离。如果省略，默认半径等于平均半径，定义为（2a+b）/3，其中 a 是 SRS 的半长轴，b 是半短轴。

    ST_Distance_Sphere()处理其参数如本节介绍的那样，但有以下例外：

    • 支持的几何参数组合为Point和Point，或Point和MultiPoint（任意参数顺序）。如果至少一个几何图形既不是Point也不是MultiPoint，且其 SRID 为 0，则会出现ER_NOT_IMPLEMENTED_FOR_CARTESIAN_SRS错误。如果至少一个几何图形既不是Point也不是MultiPoint，且其 SRID 指向地理 SRS，则会出现ER_NOT_IMPLEMENTED_FOR_GEOGRAPHIC_SRS错误。如果任何几何图形指向投影 SRS，则会出现ER_NOT_IMPLEMENTED_FOR_PROJECTED_SRS错误。

    • 如果任何参数的经度或纬度超出范围，将会出现错误：

      • 如果经度值不在范围（−180, 180]内，将会出现ER_GEOMETRY_PARAM_LONGITUDE_OUT_OF_RANGE错误（在 MySQL 8.0.12 之前为ER_LONGITUDE_OUT_OF_RANGE）。

      • 如果纬度值不在范围[−90, 90]内，则会出现ER_GEOMETRY_PARAM_LATITUDE_OUT_OF_RANGE错误（在 MySQL 8.0.12 之前为ER_LATITUDE_OUT_OF_RANGE）。

      显示的范围以度为单位。如果一个 SRS 使用其他单位，范围将使用其单位中的相应值。由于浮点运算，确切的范围限制略有偏差。

    • 如果*radius*参数存在但不是正数，则会出现ER_NONPOSITIVE_RADIUS错误。

    • 如果距离超出双精度数的范围，则会出现ER_STD_OVERFLOW_ERROR错误。

    mysql> SET @pt1 = ST_GeomFromText('POINT(0 0)');
mysql> SET @pt2 = ST_GeomFromText('POINT(180 0)');
mysql> SELECT ST_Distance_Sphere(@pt1, @pt2);
+--------------------------------+
| ST_Distance_Sphere(@pt1, @pt2) |
+--------------------------------+
|             20015042.813723423 |
+--------------------------------+

  • ST_IsValid(*g*)

    如果参数在几何上有效，则返回 1，如果参数在几何上无效，则返回 0。几何有效性由 OGC 规范定义。

    唯一有效的空几何以空几何集合值的形式表示。在这种情况下，ST_IsValid()返回 1。MySQL 不支持 GIS EMPTY 值，如 POINT EMPTY。

    ST_IsValid()处理其参数如本节介绍的那样，但有一个例外：

    • 如果几何图形具有经度或纬度超出范围的地理 SRS，则会出现错误：

      • 如果经度值不在范围(−180, 180]内，则会发生ER_GEOMETRY_PARAM_LONGITUDE_OUT_OF_RANGE错误��在 MySQL 8.0.12 之前为ER_LONGITUDE_OUT_OF_RANGE）。

      • 如果纬度值不在范围[−90, 90]内，则会发生ER_GEOMETRY_PARAM_LATITUDE_OUT_OF_RANGE错误（在 MySQL 8.0.12 之前为ER_LATITUDE_OUT_OF_RANGE）。

      显示的范围以度为单位。如果 SRS 使用另一个单位，则范围使用其单位中的相应值。由于浮点运算，确切的范围限制略有偏差。

    mysql> SET @ls1 = ST_GeomFromText('LINESTRING(0 0,-0.00 0,0.0 0)');
mysql> SET @ls2 = ST_GeomFromText('LINESTRING(0 0, 1 1)');
mysql> SELECT ST_IsValid(@ls1);
+------------------+
| ST_IsValid(@ls1) |
+------------------+
|                0 |
+------------------+
mysql> SELECT ST_IsValid(@ls2);
+------------------+
| ST_IsValid(@ls2) |
+------------------+
|                1 |
+------------------+

  • ST_MakeEnvelope(*pt1*, *pt2*)

    返回围绕两点形成的矩形作为Point、LineString或Polygon。

    计算是在笛卡尔坐标系上进行的，而不是在球体、椭球体或地球上进行的。

    给定两个点*pt1和pt2*，ST_MakeEnvelope()在一个类似这样的抽象平面上创建结果几何体：

    • 如果*pt1和pt2相等，则结果是点pt1*。

    • 否则，如果(*pt1*, *pt2*)是垂直或水平线段，则结果是线段(*pt1*, *pt2*)。

    • 否则，结果是使用*pt1和pt2*作为对角点的多边形。

    结果几何体的 SRID 为 0。

    ST_MakeEnvelope()处理其参数如本节介绍中所述，但有以下例外：

    • 如果参数不是Point值，则会发生ER_WRONG_ARGUMENTS错误。

    • 对于两点的任何坐标值为无穷大或NaN的额外条件，会发生ER_GIS_INVALID_DATA错误。

    • 如果任何几何体具有地理空间参考系统（SRS）的 SRID 值，则会发生ER_NOT_IMPLEMENTED_FOR_GEOGRAPHIC_SRS错误。

    mysql> SET @pt1 = ST_GeomFromText('POINT(0 0)');
mysql> SET @pt2 = ST_GeomFromText('POINT(1 1)');
mysql> SELECT ST_AsText(ST_MakeEnvelope(@pt1, @pt2));
+----------------------------------------+
| ST_AsText(ST_MakeEnvelope(@pt1, @pt2)) |
+----------------------------------------+
| POLYGON((0 0,1 0,1 1,0 1,0 0))         |
+----------------------------------------+

  • ST_Simplify(*g*, *max_distance*)

    使用 Douglas-Peucker 算法简化几何体，并返回相同类型的简化值。

    几何体可以是任何几何类型，尽管 Douglas-Peucker 算法可能实际上并未处理每种类型。几何体集合通过逐个将其组件传递给简化算法来处理，并将返回的几何体放入几何体集合中作为结果。

    *max_distance*参数是要删除的顶点到其他线段的距离（以输入坐标单位表示）。在简化折线时，距离内的顶点将被移除。

    根据 Boost.Geometry，几何图形可能由于简化过程而变得无效，并且该过程可能会创建自相交。要检查结果的有效性，请将其传递给ST_IsValid()。

    ST_Simplify()处理其参数的方式如本节介绍所述，但有以下例外：

    • 如果*max_distance*参数不是正数，或为NaN，则会发生ER_WRONG_ARGUMENTS错误。
    mysql> SET @g = ST_GeomFromText('LINESTRING(0 0,0 1,1 1,1 2,2 2,2 3,3 3)');
mysql> SELECT ST_AsText(ST_Simplify(@g, 0.5));
+---------------------------------+
| ST_AsText(ST_Simplify(@g, 0.5)) |
+---------------------------------+
| LINESTRING(0 0,0 1,1 1,2 3,3 3) |
+---------------------------------+
mysql> SELECT ST_AsText(ST_Simplify(@g, 1.0));
+---------------------------------+
| ST_AsText(ST_Simplify(@g, 1.0)) |
+---------------------------------+
| LINESTRING(0 0,3 3)             |
+---------------------------------+

  • ST_Validate(*g*)

    根据 OGC 规范验证几何图形。几何图形可以在语法上良好（WKB 值加 SRID），但在几何上无效。例如，此多边形在几何上无效：POLYGON((0 0, 0 0, 0 0, 0 0, 0 0))

    如果几何图形在语法上良好且几何上有效，则ST_Validate()返回该几何图形，如果参数在语法上不良好或几何上无效或为NULL，则返回NULL。

    ST_Validate()可用于过滤无效的几何数据，尽管会有一定代价。对于需要更精确结果且不受无效数据影响的应用程序，这种惩罚可能是值得的。

    如果几何参数有效，则原样返回，但在检查有效性之前，如果输入的Polygon或MultiPolygon具有顺时针环，则将这些环反转。如果几何有效，则返回具有反转环的值。

    唯一有效的空几何以空几何集合值的形式表示。在这种情况下，ST_Validate()直接返回它，无需进一步检查。

    截至 MySQL 8.0.13，ST_Validate()处理其参数的方式如本节介绍所述，但有以下例外：

    • 如果几何图形具有地理 SRS，并且经度或纬度超出范围，则会发生错误：

      • 如果经度值不在范围（−180, 180]内，则会发生ER_GEOMETRY_PARAM_LONGITUDE_OUT_OF_RANGE错误（在 MySQL 8.0.12 之前为ER_LONGITUDE_OUT_OF_RANGE）。

      • 如果纬度值不在范围[-90, 90]内，则会发生ER_GEOMETRY_PARAM_LATITUDE_OUT_OF_RANGE错误（在 MySQL 8.0.12 之前为ER_LATITUDE_OUT_OF_RANGE）。

      显示的范围单位为度。由于浮点运算，确切的范围限制略有偏差。

    在 MySQL 8.0.13 之前，ST_Validate()处理其参数的方式如本节介绍中所述，但有以下例外：

    • 如果几何图形不符合语法规范，则返回值为NULL。不会发生ER_GIS_INVALID_DATA错误。

    • 如果几何图形具有地理空间参考系统（SRS）的 SRID 值，则会发生ER_NOT_IMPLEMENTED_FOR_GEOGRAPHIC_SRS错误。

    mysql> SET @ls1 = ST_GeomFromText('LINESTRING(0 0)');
mysql> SET @ls2 = ST_GeomFromText('LINESTRING(0 0, 1 1)');
mysql> SELECT ST_AsText(ST_Validate(@ls1));
+------------------------------+
| ST_AsText(ST_Validate(@ls1)) |
+------------------------------+
| NULL                         |
+------------------------------+
mysql> SELECT ST_AsText(ST_Validate(@ls2));
+------------------------------+
| ST_AsText(ST_Validate(@ls2)) |
+------------------------------+
| LINESTRING(0 0,1 1)          |
+------------------------------+

14.17 JSON 函数

原文：dev.mysql.com/doc/refman/8.0/en/json-functions.html

14.17.1 JSON 函数参考

14.17.2 创建 JSON 值的函数

14.17.3 搜索 JSON 值的函数

14.17.4 修改 JSON 值的函数

14.17.5 返回 JSON 值属性的函数

14.17.6 JSON 表函数

14.17.7 JSON 模式验证函数

14.17.8 JSON 实用函数

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

对于接受 JSON 参数的函数，如果参数不是有效的 JSON 值，则会发生错误。以 JSON 解析的参数由*json_doc表示；由val*表示的参数未解析。

返回 JSON 值的函数始终对这些值进行规范化（参见 JSON 值的规范化、合并和自动包装。

14.17.1 JSON 函数参考

原文：dev.mysql.com/doc/refman/8.0/en/json-function-reference.html

表格 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() 的同义词是
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/08.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 值，使用JSON_PRETTY()函数。您可以通过JSON_STORAGE_SIZE()和JSON_STORAGE_FREE()函数，分别查看给定 JSON 值占用的存储空间以及剩余的存储空间。有关这些函数的完整描述，请参见第 14.17.8 节，“JSON 实用函数”。

14.17.2 创建 JSON 值的函数

原文：dev.mysql.com/doc/refman/8.0/en/json-creation-functions.html

此部分列出的函数从组件元素组成 JSON 值。

  • JSON_ARRAY([*值*[, *值*] ...])

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

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

  • JSON_OBJECT([*键*, *值*[, *键*, *值*] ...])

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

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

  • JSON_QUOTE(*字符串*)

    通过用双引号字符包装字符串并转义内部引号和其他字符，将字符串引用为 JSON 值，然后将结果作为utf8mb4字符串返回。如果参数为NULL，则返回NULL。

    此函数通常用于生成 JSON 文档中的有效 JSON 字符串文字。

    某些特殊字符使用反斜杠进行转义，具体转义序列请参见表 14.23，“JSON_UNQUOTE()特殊字符转义序列”特殊字符转义序列")。

    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]"             |
+-------------------------+

你也可以通过将其他类型的值转换为JSON类型并使用CAST(*值* AS JSON)来获取 JSON 值；更多信息请参见在 JSON 和非 JSON 值之间转换。

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

14.17.3 搜索 JSON 值的函数

原文：dev.mysql.com/doc/refman/8.0/en/json-search-functions.html

本节中的函数执行 JSON 值的搜索或比较操作，以从中提取数据，报告数据是否存在于其中的位置，或报告数据在其中的路径。此外，MEMBER OF() 运算符也在此处记录。

  • JSON_CONTAINS(*target*, *candidate*[, *path*])

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

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

    以下规则定义包含：

    • 如果且仅如果候选标量包含在目标标量中，则它们是可比较且相等。如果两个标量值具有相同的JSON_TYPE()类型，则它们是可比较的，但INTEGER和DECIMAL类型的值也可以相互比较。

    • 如果且仅如果候选数组中的每个元素包含在目标数组的某个元素中，则候选数组包含在目标数组中。

    • 如果且仅如果候选非数组包含在目标数组中，则候选包含在目标的某个元素中。

    • 如果且仅如果候选对象包含在目标对象中，则对于候选中的每个键，目标中都有一个同名键，并且与候选键关联的值包含在与目标键关联的值中。

    否则，候选值不包含在目标文档中。

    从 MySQL 8.0.17 开始，在InnoDB表上使用JSON_CONTAINS()的查询可以通过多值索引进行优化；有关更多信息，请参见多值索引。

    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 |
+-------------------------------+

  • JSON_CONTAINS_PATH(*json_doc*, *one_or_all*, *path*[, *path*] ...)

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

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

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

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

    • 'all'：如果所有路径都存在于文档中，则为 1，否则为 0。

    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 |
+----------------------------------------+

  • JSON_EXTRACT(*json_doc*, *path*[, *path*] ...)

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

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

    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 路径的 2 个参数。

  • *column*->*path*

    当与两个参数一起使用时，->运算符在左侧是列标识符，在右侧是针对 JSON 文档（列值）进行评估的 JSON 路径（字符串文字）时，充当JSON_EXTRACT()函数的别名。您可以在 SQL 语句中的任何列引用出现的地方使用这样的表达式。

    此处显示的两个SELECT语句产生相同的输出：

    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，如下所示：

    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 数组值，如下所示：

    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，如下所示：

    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()时看到的情况相同：

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

  • *column*->>*path*

    这是一个改进的去引号提取运算符。而->运算符仅仅提取一个值，->>运算符除此之外还去除提取结果的引号。换句话说，给定一个JSON列值*column和一个路径表达式path*（一个字符串文字），以下三个表达式返回相同的值：

    • JSON_UNQUOTE( JSON_EXTRACT(*column*, *path*) )

    • JSON_UNQUOTE(*column* -> *path*)

    • *column*->>*path*

    ->>运算符可以在任何允许使用JSON_UNQUOTE(JSON_EXTRACT())的地方使用。这包括（但不限于）SELECT列表、WHERE和HAVING子句，以及ORDER BY和GROUP BY子句。

    接下来的几个语句演示了一些->>运算符与mysql客户端中其他表达式的等价性：

    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 数组一起使用，如下所示：

    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输出中总是展开的，如下例所示：

    mysql> EXPLAIN SELECT c->>'$.name' AS name
 ->     FROM jemp WHERE g > 2\G
*************************** 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 WARNINGS\G
*************************** 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 在相同情况下展开->运算符的方式。

  • JSON_KEYS(*json_doc*[, *path*])

    将 JSON 对象的顶级值的键作为 JSON 数组返回，或者如果给定了*path参数，则从所选路径返回顶级键。如果任何参数为NULL，json_doc参数不是对象，或者如果给定了path，则无法定位对象，则返回NULL。如果json_doc参数不是有效的 JSON 文档，或者path*参数不是有效的路径表达式或包含*或**通配符，则会发生错误。

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

    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"]                                        |
+----------------------------------------------+

  • JSON_OVERLAPS(*json_doc1*, *json_doc2*)

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

    此函数作为 JSON_CONTAINS() 的对应函数，要求搜索的数组中的所有元素都存在于搜索的数组中。因此，JSON_CONTAINS() 对搜索键执行 AND 操作，而 JSON_OVERLAPS() 执行 OR 操作。

    在 WHERE 子句中使用 JSON_OVERLAPS() 查询 InnoDB 表的 JSON 列可以使用多值索引进行优化。多值索引 提供了详细信息和示例。

    在比较两个数组时，如果它们共享一个或多个数组元素，则 JSON_OVERLAPS() 返回真，否则返回假：

    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)

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

    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)

    在比较对象时，如果它们至少有一个键值对相同，则结果为真。

    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() 执行简单的相等性测试：

    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]，如下所示：

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

    JSON_OVERLAPS() 在 MySQL 8.0.17 中添加。

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

    返回 JSON 文档中给定字符串的路径。如果 json_doc、search_str 或 path 参数中有任何一个为 NULL；文档中不存在 path；或未找到 search_str，则返回 NULL。如果 json_doc 参数不是有效的 JSON 文档，任何 path 参数不是有效的路径表达式，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 #32449181）。

    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 路径语法。

  • JSON_VALUE(*json_doc*, *path*)

    从指定文档中给定路径提取值，并返回提取的值，可选择将其转换为所需类型。完整的语法如下所示：

    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 空字面量组成或解析为 JSON 空字面量时，该函数返回 SQL NULL。

    on_empty，如果指定，确定了当在给定路径找不到数据时 JSON_VALUE() 的行为；此子句可以取以下值：

    • NULL ON EMPTY: 函数返回 NULL；这是默认的 ON EMPTY 行为。

    • DEFAULT *value* ON EMPTY: 返回提供的 value。该值的类型必须与返回类型匹配。

    • 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 输入（文档和路径）都会被检查其有效性。如果其中任何部分无效，则会抛出 SQL 错误，而不会触发 ON ERROR 子句。

    • 当发生以下事件之一时触发 ON ERROR：

      • 尝试提取对象或数组，例如由解析为 JSON 文档中多个位置的路径导致的对象或数组

      • 转换错误，例如尝试将 'asdf' 转换为 UNSIGNED 值

      • 值的截断

    • 即使指定了 NULL ON ERROR 或 DEFAULT ... ON ERROR，转换错误始终会触发警告。

    • 当源 JSON 文档（expr）在指定位置（path）不包含数据时，会触发 ON EMPTY 子句。

    JSON_VALUE() 在 MySQL 8.0.21 中引入。

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

    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*) 等同于以下语句：

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

    JSON_VALUE() 简化了在 JSON 列上创建索引的过程，因为在许多情况下不需要创建一个生成列，然后在生成列上创建索引。您可以在创建具有 JSON 列的表 t1 时，通过在使用 JSON_VALUE() 操作该列（使用与该列中值匹配的路径）的表达式上创建索引来实现这一点，如下所示：

    CREATE TABLE t1(
    j JSON,
    INDEX i1 ( (JSON_VALUE(j, '$.id' RETURNING UNSIGNED)) )
);

    以下EXPLAIN输出显示，针对t1的查询在WHERE子句中使用索引表达式，使用了创建的索引：

    mysql> EXPLAIN SELECT * FROM t1
 ->     WHERE JSON_VALUE(j, '$.id' RETURNING UNSIGNED) = 123\G
*************************** 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 列提供索引的生成列索引）几乎相同的效果，如下所示：

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

    针对这个表的查询的EXPLAIN输出，引用生成的列，显示索引的使用方式与针对表t1的先前查询相同：

    mysql> EXPLAIN SELECT * FROM t2 WHERE g  = 123\G
*************************** 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 列提供索引的生成列索引。

  • *value* MEMBER OF(*json_array*)

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

    在WHERE子句中对InnoDB表的 JSON 列使用MEMBER OF()查询可以通过使用多值索引进行优化。有关详细信息和示例，请参见多值索引。

    简单标量被视为数组值，如下所示：

    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)

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

    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)

    不执行到字符串类型的转换：

    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)来实现这一点：

    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()函数执行必要的转换，如下所示：

    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进行转换。下面的语句序列演示了这一点：

    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)

    MEMBER OF()运算符在 MySQL 8.0.17 中添加。

14.17.4 修改 JSON 值的函数

原文：dev.mysql.com/doc/refman/8.0/en/json-modification-functions.html

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

  • JSON_ARRAY_APPEND(*json_doc*, *path*, *val*[, *path*, *val*] ...)

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

    路径-值对从左到右进行评估。通过评估一个对产生的文档成为下一个对进行评估的新值。

    如果路径选择标量或对象值，则该值会自动包装在数组中，并将新值添加到该数组中。在 JSON 文档中，路径未识别任何值的对将被忽略。

    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 中不再支持该名称。

  • JSON_ARRAY_INSERT(*json_doc*, *path*, *val*[, *path*, *val*] ...)

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

    路径-值对从左到右进行评估。通过评估一个对产生的文档成为下一个对进行评估的新值。

    对于路径未识别 JSON 文档中任何数组的对将被忽略。如果路径标识数组元素，则相应值将插入到该元素位置，将任何后续值向右移动。如果路径标识超出数组末尾的数组位置，则该值将插入到数组末尾。

    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()调用中的后续路径应考虑这一点。在最后一个示例中，第二个路径不插入任何内容，因为在第一次插入后路径不再匹配任何内容。

  • JSON_INSERT(*json_doc*, *path*, *val*[, *path*, *val*] ...)

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

    路径-值对从左到右进行评估。通过评估一个对生成的文档成为下一个对要评估的新值。

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

    • 不存在于现有对象中的成员。将该成员添加到对象中并与新值关联。

    • 超出现有数组末尾的位置。数组将使用新值扩展。如果现有值不是数组，则会自动包装为数组，然后使用新值扩展。

    否则，文档中不存在路径的路径-值对将被忽略且不起作用。

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

    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 类型的操作。要将数组插入为数组，必须显式执行此类转换，如下所示：

    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)

  • JSON_MERGE(*json_doc*, *json_doc*[, *json_doc*] ...)

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

    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 WARNINGS\G
*************************** 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()的条目。

  • JSON_MERGE_PATCH(*json_doc*, *json_doc*[, *json_doc*] ...)

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

    注意

    有关此函数与JSON_MERGE_PRESERVE()之间差异的解释和示例，请参见 JSON_MERGE_PATCH()与 JSON_MERGE_PRESERVE()的比较与 JSON_MERGE_PRESERVE()的比较")。

    JSON_MERGE_PATCH()执行如下合并：

    1. 如果第一个参数不是对象，则合并的结果与将空对象与第二个参数合并的结果相同。

    2. 如果第二个参数不是对象，则合并的结果是第二个参数。

    3. 如果两个参数都是对象，则合并的结果是具有以下成员的对象：

      • 第一个对象中所有没有与第二个对象中具有相同键的成员。

      • 第二个对象的所有成员，这些成员在第一个对象中没有相应的键，并且其值不是 JSON null文字。

      • 所有具有在第一个和第二个对象中都存在的键的成员，并且在第二个对象中的值不是 JSON null文字。这些成员的值是通过递归地将第一个对象中的值与第二个对象中的值合并而得到的结果。

    有关更多信息，请参阅 JSON 值的规范化、合并和自动包装。

    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来删除成员，如下所示：

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

    此示例显示该函数以递归方式运行；即，成员的值不仅限于标量，而是可以是 JSON 文档本身：

    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_PATCH()在 MySQL 8.0.3 及更高版本中受支持。

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

    • JSON_MERGE_PATCH()会删除第一个对象中具有与第二个对象中匹配键的成员，前提是与键相关联的值在第二个对象中不是 JSON null。

    • 如果第二个对象具有与第一个对象中的成员匹配的键的成员，JSON_MERGE_PATCH()会用第二个对象中的值替换第一个对象中的值，而JSON_MERGE_PRESERVE()会附加第二个值到第一个值。

    此示例比较了合并具有匹配键"a"的相同 3 个 JSON 对象的结果，每个函数都有：

    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 Preserve\G
*************************** 1\. row ***************************
   Patch: {"a": 5, "b": 2, "c": 4, "d": 6}
Preserve: {"a": [1, 3, 5], "b": 2, "c": 4, "d": 6}

  • JSON_MERGE_PRESERVE(*json_doc*, *json_doc*[, *json_doc*] ...)

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

    合并按照以下规则进行。有关更多信息，请参阅 JSON 值的规范化、合并和自动包装。

    • 相邻的数组会合并为一个数组。

    • 相邻的对象会合并为一个对象。

    • 标量值会自动包装为数组并作为数组合并。

    • 通过将对象自动包装为数组并合并两个数组，可以合并相邻的数组和对象。

    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() 的比较 与 JSON_MERGE_PRESERVE() 的比较")。

  • JSON_REMOVE(*json_doc*, *path*[, *path*] ...)

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

    path 参数从左到右进行评估。通过评估一个路径，生成的文档成为下一个路径要评估的新值。

    如果要移除的元素在文档中不存在，不会出错；在这种情况下，路径不会影响文档。

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

  • JSON_REPLACE(*json_doc*, *path*, *val*[, *path*, *val*] ...)

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

    路径-值对从左到右进行评估。通过评估一个对，生成的文档成为下一个对要评估的新值。

    对于文档中现有路径的路径-值对，将现有文档值覆盖为新值。对于文档中不存在的路径的路径-值对，将被忽略且不起作用。

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

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

    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]}                              |
+-----------------------------------------------------+

  • JSON_SET(*json_doc*, *path*, *val*[, *path*, *val*] ...)

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

    路径-值对从左到右进行评估。通过评估一个对产生的文档成为下一个对进行评估的新值。

    文档中现有路径的路径-值对将使用新值覆盖现有文档值。文档中不存在的路径的路径-值对将在路径标识以下类型值之一时将值添加到文档中：

    • 一个不在现有对象中的成员。该成员将添加到对象中并与新值关联。

    • 超出现有数组末尾的位置。数组将使用新值扩展。如果现有值不是数组，则会自动包装为数组，然后使用新值扩展。

    否则，文档中不存在的路径的路径-值对将被忽略且不起作用。

    在 MySQL 8.0.4 中，优化器可以对 JSON 列执行部分、原地更新，而不是将旧文档删除并完整地写入新文档到列中。这种优化可以用于使用 JSON_SET() 函数的更新语句，并满足 JSON 值的部分更新 中概述的条件。

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

    • JSON_SET() 替换现有值并添加不存在的值。

    • JSON_INSERT() 插入值而不替换现有值。

    • JSON_REPLACE() 仅 替换现有值。

    以下示例说明了这些差异，使用一个存在于文档中的路径 ($.a) 和另一个不存在的路径 ($.c)：

    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]}                              |
+-----------------------------------------------------+

  • 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制表符
    \\反斜杠（\）字符
    \u*XXXX*Unicode 值 XXXX 的 UTF-8 字节

    这个函数的两个简单示例如下所示：

    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]        |
+-----------+------------------+

    以下一组示例展示了JSON_UNQUOTE如何处理启用和禁用NO_BACKSLASH_ESCAPES时的转义：

    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"') |
+------------------------------+
| \t\u0032                     |
+------------------------------+

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

14.17.5 返回 JSON 值属性的函数

原文：dev.mysql.com/doc/refman/8.0/en/json-attribute-functions.html

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

  • JSON_DEPTH(*json_doc*)

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

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

    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 |
+-------------------------------+

  • JSON_LENGTH(*json_doc*[, *path*])

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

    文档的长度如下确定：

    • 标量的长度为 1。

    • 一个数组的长度是数组元素的数量。

    • 一个对象的长度是对象成员的数量。

    • 长度不包括嵌套数组或对象的长度。

    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 |
+------------------------------------------------+

  • JSON_TYPE(*json_val*)

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

    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                               |
+---------------------------------------+

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

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

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

    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, DOUBLE") 和 FLOAT - FLOAT, DOUBLE") 标量

      • DECIMAL: MySQL DECIMAL - DECIMAL, NUMERIC") 和 NUMERIC - 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（原始位）

  • JSON_VALID(*val*)

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

    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 |
+---------------------+-----------------------+

14.17.6 JSON 表函数

译文：dev.mysql.com/doc/refman/8.0/en/json-table-functions.html

本节包含将 JSON 数据转换为表格数据的 JSON 函数的信息。MySQL 8.0 支持一种名为JSON_TABLE()的函数。

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

从 JSON 文档中提取数据，并将其作为具有指定列的关系表返回。此函数的完整语法如下所示：

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()支持四种列类型，描述如下：

  1. *name* FOR ORDINALITY：此类型在COLUMNS子句中枚举行；名为*name*的列是一个计数器，其类型为UNSIGNED INT，初始值为 1。这相当于在CREATE TABLE语句中指定列为AUTO_INCREMENT，并可用于区分由NESTED [PATH]子句生成的多行中具有相同值的父行。

  2. *name* *type* PATH *string_path* [*on_empty*] [*on_error*]：此类型的列用于提取由*string_path指定的值。type是 MySQL 标量数据类型（即，不能是对象或数组）。JSON_TABLE()将数据提取为 JSON，然后将其强制转换为列类型，使用 MySQL 中适用于 JSON 数据的常规自动类型转换。缺少值会触发on_empty子句。保存对象或数组会触发可选的on_error*子句；当在将保存为 JSON 的值从 JSON 转换为表列时发生错误时，例如尝试将字符串'asd'保存到整数列时，也会发生这种情况。

  3. *name* *type* EXISTS PATH *path*：如果指定的 path 位置存在任何数据，则此列返回 1，否则返回 0。type 可以是任何有效的 MySQL 数据类型，但通常应指定为某种类型的 INT - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT")。

  4. 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 标准相悖，后者规定，如果指定了 ON EMPTY，则必须在任何 ON ERROR 子句之前。因此，从 MySQL 8.0.20 开始，指定 ON ERROR 在 ON EMPTY 之前已被弃用；尝试这样做会导致服务器发出警告。预计在未来的 MySQL 版本中将删除对非标准语法的支持。

当将一个值保存到列中时被截断，例如将 3.14159 保存在 DECIMAL(10,1) - DECIMAL, NUMERIC") 列中，将发出警告，与任何 ON ERROR 选项无关。当在单个语句中截断多个值时，只会发出一次警告。

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

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的使用。对应于{"b":1}的行在路径"$.a"上为空，并尝试将[1,2]保存为标量会产生错误；这些行在输出中被突出显示。

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关键字之前的*path*的每个匹配项映射到结果表中的一个单独行。例如，以下查询给出了这里显示的结果：

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    |
+------+------+

表达式"$[*]"匹配数组的每个元素。您可以通过修改路径来过滤结果中的行。例如，使用"$[1]"将提取限制为用作源的 JSON 数组的第二个元素，如下所示：

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    |
+------+------+

在列定义中，"$"将整个匹配项传递给列；"$.x"和"$.y"分别仅传递与该匹配项中的键x和y对应的值。有关更多信息，请参见 JSON 路径语法。

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

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]修饰符生成的所有记录的总和，而不是乘积，如下所示：

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子句生成的记录，并可用于区分嵌套路径的父记录，特别是如果父记录中的值相同，则可以看到：

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 关键字的 MySQL 版本中（8.0.13 及更早版本），这些被视为横向派生表。 在支持 LATERAL 的版本中（8.0.14 及更高版本），它是隐式的，并且因此在 JSON_TABLE() 之前不允许使用。

假设您已经创建并使用以下语句填充了一个名为 t1 的表：

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() 充当派生表，同时引用先前引用表中的列：

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。

avatar
文章
阅读
粉丝