MySQL :: MySQL 8.4 Reference Manual :: 14.17.6 JSON Table Functions

version 8.4

8.0 current

8.0 Japanese

MySQL 8.4 Reference Manual / ... / JSON Table Functions

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，给定表 t1 在 JSON_TABLE() 之前在 FROM 子句中指定），或函数调用 (JSON_EXTRACT(t1.json_data,'$.post.comments')）。

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

需要指定alias。表别名的规则适用（见第11.2节，“Schema Object Names”）。

该函数将列名进行不区分大小写的比较。

JSON_ TABLE() 支持四种类型的列，以下列表中描述：

name FOR ORDINALITY：该类型枚举了COLUMNS子句中的行；名为name的列是一个计数器，其类型为UNSIGNED INT，初始值为1。这等同于在CREATE TABLE语句中指定一个列为AUTO_INCREMENT，可以用来区分具有相同值的多个行，该行由NESTED [PATH]子句生成。
name type PATH string_路径 [on_empty] [on_error]: 这种类型的列用于提取由 string_路径 指定的值。type 是 MySQL 标量数据类型（即不能是对象或数组）。JSON_TABLE() 将数据提取为 JSON，然后将其转换为列类型，使用 MySQL 对 JSON 数据的常规自动类型转换。缺少值触发 on_empty 子句。保存对象或数组触发可选 on_error 子句；这也发生在将 JSON 值保存到表列时的错误转换，例如尝试将字符串 ''asd'' 保存到整数列。
name type EXISTS 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() 在数据缺失时的行为。这一子句也会在 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字符串 ON ERROR: 将json_string解析为JSON（假设它是有效的），并将其存储而不是对象或数组。
ERROR ON ERROR: 发生错误。

在MySQL中指定ON ERROR前ON EMPTY是非标准的且已弃用；尝试这样做将导致服务器发出警告。预计将来版本中的支持将被删除。

当将值保存到列时，例如将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的使用。对应于{"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节，“Schema Object Names”。

所有 JSON 和 JSON 路径表达式都将被检查是否有效；无效的表达式将导致错误。

每个匹配的 path 前缀中的 COLUMNS 关键字对应于结果表中的一个单独行。例如，以下查询将显示以下结果：

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

同级嵌套路径—that是，同一个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关键字将raiseER_PARSE_ERROR。

PREV HOME UP NEXT