本节包含将 JSON 数据转换为表格数据的 JSON 函数的信息。MySQL 8.4 支持一个这样的函数，JSON_TABLE()。This section contains information about JSON functions that convert JSON data to tabular data. MySQL 8.4 supports one such function, JSON_TABLE().

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

从 JSON 文档中提取数据，并将其作为具有指定列的关系表返回。此函数的完整语法如下所示：Extracts data from a JSON document and returns it as a relational table having the specified columns. The complete syntax for this function is shown here:

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')）。expr: This is an expression that returns JSON data. This can be a constant ('{"a":1}'), a column (t1.json_data, given table t1 specified prior to JSON_TABLE() in the FROM clause), or a function call (JSON_EXTRACT(t1.json_data,'$.post.comments')).

path：JSON 路径表达式，应用于数据源。我们称与路径匹配的 JSON 值为 行源；该行源用于生成关系型数据的一行。COLUMNS 子句对行源进行评估，在行源中查找特定的 JSON 值，并将这些 JSON 值作为 SQL 值返回到关系数据的一行的各个列中。path: A JSON path expression, which is applied to the data source. We refer to the JSON value matching the path as the row source; this is used to generate a row of relational data. The COLUMNS clause evaluates the row source, finds specific JSON values within the row source, and returns those JSON values as SQL values in individual columns of a row of relational data.

需要alias。表别名的常规规则适用（参见第 11.2 节，“模式对象名称”）。The alias is required. The usual rules for table aliases apply (see Section 11.2, “Schema Object Names”).

此函数以不区分大小写的方式比较列名。This function compares column names in case-insensitive fashion.

JSON_TABLE() 支持四种类型的列，如下列表所述：JSON_TABLE() supports four types of columns, described in the following list:

on empty，如果指定，会确定在数据缺失时（取决于类型）JSON_TABLE() 的行为。当 NESTED PATH 子句中的列没有匹配并生成了一个 NULL 补充行时，也会触发此子句。on empty 可以取以下值之一：on empty, if specified, determines what JSON_TABLE() does in the event that data is missing (depending on type). This clause is also triggered on a column in a NESTED PATH clause when the latter has no match and a NULL complemented row is produced for it. on empty takes one of the following values:

如果使用，on_error 可取下列值之一，并产生如下相应结果：If used, on_error takes one of the following values with the corresponding result as shown here:

在 ON ERROR 之前指定 ON EMPTY 是非标准做法，并且在 MySQL 中已弃用；尝试这样做会导致服务器发出警告。预计未来 MySQL 版本将移除对非标准语法的支持。Specifying ON ERROR before ON EMPTY is nonstandard and deprecated in MySQL; trying to do so causes the server to issue a warning. Expect support for the nonstandard syntax to be removed in a future version of MySQL.

当保存到列的值被截断时，比如在 DECIMAL(10,1) 列中保存 3.14159，将会发出警告，这与任何 ON ERROR 选项无关。当在单个语句中截断多个值时，警告只会发出一次。When a value saved to a column is truncated, such as saving 3.14159 in a DECIMAL(10,1) column, a warning is issued independently of any ON ERROR option. When multiple values are truncated in a single statement, the warning is issued only once.

当传递给此函数的表达式和路径解析为 JSON null 时，JSON_TABLE() 会返回 SQL NULL，符合 SQL 标准，如下所示：When the expression and path passed to this function resolve to JSON null, JSON_TABLE() returns SQL NULL, in accordance with the SQL standard, as shown here:

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] 保存为标量会产生错误；这些行在所示输出中已被高亮显示。The following query demonstrates the use of ON EMPTY and ON ERROR. The row corresponding to {"b":1} is empty for the path "$.a", and attempting to save [1,2] as a scalar produces an error; these rows are highlighted in the output shown.

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 节，“模式对象名称”。Column names are subject to the usual rules and limitations governing table column names. See Section 11.2, “Schema Object Names”.

所有 JSON 和 JSON 路径表达式都会被检查其有效性；任意一种类型的无效表达式都会导致错误。All JSON and JSON path expressions are checked for validity; an invalid expression of either type causes an error.

每个匹配 path 的结果位于 COLUMNS 关键字之前，会映射到结果表中的单独行。例如，以下查询会给出如下所示的结果：Each match for the path preceding the COLUMNS keyword maps to an individual row in the result table. For example, the following query gives the result shown here:

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 数组的第二个元素，如下所示：The expression "$[*]" matches each element of the array. You can filter the rows in the result by modifying the path. For example, using "$[1]" limits extraction to the second element of the JSON array used as the source, as shown here:

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 Path 语法。Within a column definition, "$" passes the entire match to the column; "$.x" and "$.y" pass only the values corresponding to the keys x and y, respectively, within that match. For more information, see JSON Path Syntax.

NESTED PATH（或简称 NESTED；PATH 为可选）会为其所属的 COLUMNS 子句中的每个匹配生成一组记录。如果没有匹配，则嵌套路径的所有列都设置为 NULL。这实现了最顶层子句与 NESTED [PATH] 之间的外连接。通过在 WHERE 子句中应用适当的条件，可以模拟内连接，如下所示：NESTED PATH (or simply NESTED; PATH is optional) produces a set of records for each match in the COLUMNS clause to which it belongs. If there is no match, all columns of the nested path are set to NULL. This implements an outer join between the topmost clause and NESTED [PATH]. An inner join can be emulated by applying a suitable condition in the WHERE clause, as shown here:

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] 修饰符生成的所有记录的总和，而不是乘积，如下所示：Sibling nested paths—that is, two or more instances of NESTED [PATH] in the same COLUMNS clause—are processed one after another, one at a time. While one nested path is producing records, columns of any sibling nested path expressions are set to NULL. This means that the total number of records for a single match within a single containing COLUMNS clause is the sum and not the product of all records produced by NESTED [PATH] modifiers, as shown here:

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 子句生成的记录进行编号，并可用于区分嵌套路径的父记录，特别是在父记录的值相同时，如下所示：A FOR ORDINALITY column enumerates records produced by the COLUMNS clause, and can be used to distinguish parent records of a nested path, especially if values in parent records are the same, as can be seen here:

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 列中具有不同的值。The source document contains an array of two elements; each of these elements produces two rows. The values of apath and bpath are the same over the entire result set; this means that they cannot be used to determine whether lpath values came from the same or different parents. The value of the ord column remains the same as the set of records having top_ord equal to 1, so these two values are from a single object. The remaining two values are from different objects, since they have different values in the ord column.

通常，你不能连接依赖于同一个 FROM 子句中前面表的列的派生表。根据 SQL 标准，MySQL 对表函数作出了例外；这些被视为横向派生表。这是隐式的，因此也不允许在 JSON_TABLE() 之前使用，根据标准也是如此。Normally, you cannot join a derived table which depends on columns of preceding tables in the same FROM clause. MySQL, per the SQL standard, makes an exception for table functions; these are considered lateral derived tables. This is implicit, and for this reason is not allowed before JSON_TABLE(), also according to the standard.

假设你有一个表 t1，通过这里显示的语句创建并填充：Suppose you have a table t1 created and populated using the statements shown here:

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() 作为派生表，同时它引用了之前引用的表中的某一列：You can then execute joins, such as this one, in which JSON_TABLE() acts as a derived table while at the same time it refers to a column in a previously referenced 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。Attempting to use the LATERAL keyword with this query raises ER_PARSE_ERROR.