MySQL的函数和运算符 - JSON 函数 - JSON 表函数

数据库杂货铺 2021-09-12

834

JSON 表函数

本部分包含关于将JSON数据转换为表格数据的JSON函数的信息。在 MySQL 8.0.4 及更高版本中，支持函数 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，假设表 t1 在 FROM 子句中 JSON_TABLE() 之前指定)，或函数调用 (JSON_EXTRACT(t1.json_data,'$.post.comments'))。

path: JSON 路径表达式，应用于数据源。我们将匹配路径的 JSON 值作为行数据来源，用于生成一行关系数据。COLUMNS 子句计算行来源，在行数据来源中查找特定的 JSON 值，返回这些 JSON 值，作为行关系数据的各个列中的 SQL 值。

alias 是必需的。适用表别名的一般规则。

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

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

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

b. name type PATH string_path [on_empty] [on_error]: 此类型的列用于提取 string_path 指定的值。type 是 MySQL 标量数据类型(也就是说，它不能是对象或数组)。JSON_TABLE() 将数据提取为 JSON，然后使用 MySQL 中应用于 JSON 数据的常规自动类型转换将其强制转换为列类型。缺失的值会触发 on_empty 子句。保存对象或数组会触发可选的 on error 子句，当将保存为 JSON 的值强制转换为表列时发生错误时，也会发生这种情况，例如试图将字符串 'asd' 保存为整数列。

c. name type EXISTS PATH path: 如果在 path 指定的位置存在任何数据，该列返回 1，否则返回 0。type 可以是任何有效的 MySQL 数据类型，但通常应该指定为某种 INT 类型。

d. NESTED [PATH] path COLUMNS (column_list): 将 JSON 数据中的嵌套对象或数组与来自父对象或数组的 JSON 值合并成一行。使用多个 PATH 选项允许将 JSON 值从多个嵌套层投影到单个行。

path 是相对于 JSON_TABLE() 的父路径行路径，或者在发生嵌套路径时，相对于父 NESTED [PATH] 子句的路径。

如果指定 on empty，则确定 JSON_TABLE() 在数据丢失的情况下做什么(取决于类型)。当嵌套 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 和更高版本中，情况不再是这样。

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

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

在 MySQL 8.0.21 之前，当传递给这个函数的表达式和路径解析为 JSON 空时，JSON_TABLE() 将引发错误。在 MySQL 8.0.21 及以后的版本中，按照 SQL 标准，在这种情况下会返回 SQL NULL，如下所示：

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] 保存为标量将产生错误，这些行在显示的输出中突出显示。

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)

列名遵从表列名的通常规则和限制的约束。

检查所有 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 对应的值。

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 列中具有不同的值。

官方网址：

https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html

mysql

文章转载自数据库杂货铺，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL的函数和运算符 - JSON 函数 - JSON 表函数

评论