MySQL :: MySQL 8.4 参考手册 :: 14.17.6 JSON 表函数

版本 8.4

9.0
8.0

8.0 日语

MySQL 8.4 参考手册 / ... / JSON 表函数

14.17.6 JSON 表函数

本节包含有关将 JSON 数据转换为表格数据的 JSON 函数的信息。MySQL 8.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，在 FROM 子句中 JSON_TABLE() 之前指定的表 t1) 或函数调用 (JSON_EXTRACT(t1.json_data,'$.post.comments'))。

path：一个 JSON 路径表达式，它应用于数据源。我们将匹配路径的 JSON 值称为行源；它用于生成关系数据行。COLUMNS 子句对行源进行评估，在行源中查找特定 JSON 值，并将这些 JSON 值作为 SQL 值返回到关系数据行的单个列中。

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

此函数以不区分大小写的形式比较列名。

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]：此类型的列用于提取由 string_path 指定的值。type 是一个 MySQL 标量数据类型（即，它不能是对象或数组）。JSON_TABLE() 将数据作为 JSON 提取，然后使用 MySQL 中应用于 JSON 数据的常规自动类型转换将其强制转换为列类型。缺失值会触发 on_empty 子句。保存对象或数组将触发可选的 on error 子句；当从作为 JSON 保存的值到表列的强制转换过程中发生错误时，也会发生这种情况，例如尝试将字符串 'asd' 保存到整数列中。
name type EXISTS PATH path：如果在由 path 指定的位置存在任何数据，则此列返回 1，否则返回 0。type 可以是任何有效的 MySQL 数据类型，但通常应指定为 INT 的某种变体。
NESTED [PATH] path COLUMNS (column_list)：这将 JSON 数据中的嵌套对象或数组扁平化为一行，以及来自父对象或数组的 JSON 值。使用多个 PATH 选项允许将来自多个嵌套级别的 JSON 值投影到一行中。
path 相对于 JSON_TABLE() 的父路径行路径，或者在嵌套路径的情况下相对于父 NESTED [PATH] 子句的路径。

如果指定了 on empty，则它会确定 JSON_TABLE() 在数据丢失的情况下如何执行操作（取决于类型）。当后者没有匹配项且为其生成一个 NULL 补充行时，此子句也会在 NESTED PATH 子句中的列上触发。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 中，在 ON EMPTY 之前指定 ON ERROR 是非标准的并且已弃用；尝试这样做会导致服务器发出警告。预计在 MySQL 的未来版本中，将删除对非标准语法的支持。

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

当传递给此函数的表达式和路径解析为 JSON null 时，JSON_TABLE() 返回 SQL NULL，符合 SQL 标准，如下所示

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)

列名遵循表格列名的常规规则和限制。请参见第 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 标准，对表函数做出了例外；这些被视为横向派生表。这是隐式的，因此根据标准，也不允许在 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。

PREV HOME UP NEXT