This section contains information about JSON functions that convert JSON data to tabular data. 本节包含有关将JSON数据转换为表格数据的JSON函数的信息。In MySQL 8.0.4 and later, one such function—在MySQL 8.0.4及更高版本中，支持这样的函数：JSON_TABLE()—is supported.JSON_TABLE()。
JSON_TABLE(expr, path COLUMNS (column_list) [AS] alias)
Extracts data from a JSON document and returns it as a relational table having the specified columns. 从JSON文档中提取数据，并将其作为具有指定列的关系表返回。The complete syntax for this function is shown here:此函数的完整语法如下所示：
JSON_TABLE(expr,pathCOLUMNS (column_list) ) [AS]aliascolumn_list:column[,column][, ...]column:nameFOR ORDINALITY |nametypePATHstring path[on_empty] [on_error] |nametypeEXISTS PATHstring path| NESTED [PATH]pathCOLUMNS (column_list)on_empty: {NULL | DEFAULTjson_string| ERROR} ON EMPTYon_error: {NULL | DEFAULTjson_string| ERROR} ON ERROR
expr: This is an expression that returns JSON data. ：这是一个返回JSON数据的表达式。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')).'{"a":1}'）、一列（t1.json_data，给定的表t1在FROM子句中的JSON_TABLE()之前指定）或一个函数调用（JSON_EXTRACT(t1.json_data,'$.post.comments')）。
path: A JSON path expression, which is applied to the data source. ：应用于数据源的JSON路径表达式。We refer to the JSON value matching the path as the row source; this is used to generate a row of relational data. 我们将匹配路径的JSON值作为行源；这用于生成一行关系数据。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.COLUMNS子句计算行源，在行源中查找特定的JSON值，并将这些JSON值作为SQL值返回到关系数据行的各个列中。
The alias is required. alias是必需的。The usual rules for table aliases apply (see Section 9.2, “Schema Object Names”).表别名的常规规则适用（请参阅第9.2节，“架构对象命名”）。
Beginning with MySQL 8.0.27, this function compares column names in case-insensitive fashion.从MySQL8.0.27开始，这个函数以不区分大小写的方式比较列名。
JSON_TABLE() supports four types of columns, described in the following list:JSON_TABLE()支持四种类型的列，如下表所示：
name FOR ORDINALITY: This type enumerates rows in the ：此类型枚举COLUMNS clause; the column named name is a counter whose type is UNSIGNED INT, and whose initial value is 1. COLUMNS子句中的行；名为name的列是一个计数器，其类型为UNSIGNED INT，其初始值为1。This is equivalent to specifying a column as 这相当于在AUTO_INCREMENT in a CREATE TABLE statement, and can be used to distinguish parent rows with the same value for multiple rows generated by a NESTED [PATH] clause.CREATE TABLE语句中将列指定为AUTO_INCREMENT，并可用于区分NESTED [PATH]子句生成的多行中具有相同值的父行。
name type PATH string_path [on_empty] [on_error]: Columns of this type are used to extract values specified by ：此类型的列用于提取string_path. string_path指定的值。type is a MySQL scalar data type (that is, it cannot be an object or array). type是MySQL标量数据类型（即，它不能是对象或数组）。JSON_TABLE() extracts data as JSON then coerces it to the column type, using the regular automatic type conversion applying to JSON data in MySQL. JSON_TABLE()将数据提取为JSON，然后将其强制为列类型，使用应用于MySQL中JSON数据的常规自动类型转换。A missing value triggers the 缺少值会触发on_empty clause. on_empty子句。Saving an object or array triggers the optional 保存对象或数组会触发可选的on error clause; this also occurs when an error takes place during coercion from the value saved as JSON to the table column, such as trying to save the string 'asd' to an integer column.on error子句；当从保存为JSON的值强制到表列期间发生错误时，也会发生这种情况，例如尝试将字符串'asd'保存为整数列。
name type EXISTS PATH path: This column returns 1 if any data is present at the location specified by ：如果path, and 0 otherwise. path指定的位置存在任何数据，则此列返回1，否则返回0。type can be any valid MySQL data type, but should normally be specified as some variety of INT.type可以是任何有效的MySQL数据类型，但通常应指定为INT的某种变体。
NESTED [PATH] path COLUMNS (column_list): This flattens nested objects or arrays in JSON data into a single row along with the JSON values from the parent object or array. ：这会将JSON数据中的嵌套对象或数组与父对象或数组中的JSON值一起展平到一行中。Using multiple 使用多PATH options allows projection of JSON values from multiple levels of nesting into a single row.PATH选项可以将JSON值从多个嵌套级别投影到一行中。
The path is relative to the parent path row path of JSON_TABLE(), or the path of the parent NESTED [PATH] clause in the event of nested paths.path相对于JSON_TABLE()的父路径行路径，或者在嵌套路径的情况下相对于父NESTED [PATH]子句的路径。
on empty, if specified, determines what ，如果指定了的话，则用于确定在数据丢失时JSON_TABLE() does in the event that data is missing (depending on type). JSON_TABLE()的作用（取决于类型）。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. NESTED PATH子句中的列没有匹配项并且为该列生成一个空的补全行时，也会触发该子句。on empty takes one of the following values:on empty采用以下值之一：
NULL ON EMPTY: The column is set to ：列设置为NULL; this is the default behavior.NULL；这是默认行为。
DEFAULT json_string ON EMPTY: the provided ：提供的json_string is parsed as JSON, as long as it is valid, and stored instead of the missing value. json_string被解析为json，只要它是有效的，并存储而不是丢失的值。Column type rules also apply to the default value.列类型规则也适用于默认值。
ERROR ON EMPTY: An error is thrown.：抛出错误。
If used, on_error takes one of the following values with the corresponding result as shown here:on_error如果使用，它采用以下值之一，其相应结果如下所示：
NULL ON ERROR: The column is set to ：列设置为NULL; this is the default behavior.NULL；这是默认行为。
DEFAULT json string ON ERROR: The ：json_string is parsed as JSON (provided that it is valid) and stored instead of the object or array.json_string被解析为JSON（只要它是有效的）并存储，而不是对象或数组。
ERROR ON ERROR: An error is thrown.：抛出错误。
Prior to MySQL 8.0.20, a warning was thrown if a type conversion error occurred with 在MySQL8.0.20之前，如果发生类型转换错误，并且指定或暗示了NULL ON ERROR or DEFAULT ... ON ERROR was specified or implied. NULL ON ERROR或DEFAULTON ERROR，则会抛出警告。In MySQL 8.0.20 and later, this is no longer the case. (Bug #30628330)在MySQL 8.0.20及更高版本中，情况不再如此。(错误#30628330)
Previously, it was possible to specify 以前，可以按任意顺序指定ON EMPTY and ON ERROR clauses in either order. ON EMPTY和ON ERROR子句。This runs counter to the SQL standard, which stipulates that 这与SQL标准背道而驰，后者规定如果指定了ON EMPTY, if specified, must precede any ON ERROR clause. ON EMPTY，则必须在任何ON ERROR子句之前。For this reason, beginning with MySQL 8.0.20, specifying 因此，从MySQL 8.0.20开始，不推荐在ON ERROR before ON EMPTY is deprecated; trying to do so causes the server to issue a warning. ON EMPTY之前指定ON ERROR；尝试这样做会导致服务器发出警告。Expect support for the nonstandard syntax to be removed in a future version of MySQL.在MySQL的未来版本中，对非标准语法的支持将被删除。
When a value saved to a column is truncated, such as saving 3.14159 in a 当保存到列中的值被截断时，例如将3.14159保存在DECIMAL(10,1) column, a warning is issued independently of any ON ERROR option. DECIMAL(10,1)列中，将独立于任何ON ERROR选项发出警告。When multiple values are truncated in a single statement, the warning is issued only once.当在一条语句中截断多个值时，只发出一次警告。
Prior to MySQL 8.0.21, when the expression and path passed to this function resolved to JSON null, 在MySQL 8.0.21之前，当传递给此函数的表达式和路径解析为JSON JSON_TABLE() raised an error. null时，JSON_TABLE()引发了一个错误。In MySQL 8.0.21 and later, it returns SQL 在MySQL 8.0.21及更高版本中，根据SQL标准，在这种情况下返回SQL NULL in such cases, in accordance with the SQL standard, as shown here (Bug #31345503, Bug #99557):NULL，如下所示（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)
The following query demonstrates the use of 下面的查询演示了ON EMPTY and ON ERROR. ON EMPTY和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."$.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)
Column names are subject to the usual rules and limitations governing table column names. 列名受管理表列名的常规规则和限制的约束。See Section 9.2, “Schema Object Names”.请参阅第9.2节，“架构对象命名”。
All JSON and JSON path expressions are checked for validity; an invalid expression of either type causes an error.检查所有JSON和JSON路径表达式的有效性；任何一种类型的无效表达式都会导致错误。
Each match for the path preceding the COLUMNS keyword maps to an individual row in the result table. COLUMNS关键字前面的path的每个匹配项都映射到结果表中的一行。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 | +------+------+
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:"$[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 | +------+------+
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. "$"将整个匹配传递给列；"$.x"和"$.y"仅传递与该匹配中分别对应于键x和y的值。For more information, see JSON Path Syntax.有关更多信息，请参阅JSON路径语法。
NESTED PATH (or simply NESTED; PATH is optional) produces a set of records for each match in the COLUMNS clause to which it belongs. NESTED PATH（或简化为NESTED；PATH是可选的）为它所属的COLUMNS子句中的每个匹配项生成一组记录。If there is no match, all columns of the nested path are set to 如果不匹配，则嵌套路径的所有列都设置为NULL. NULL。This implements an outer join between the topmost clause and 这实现了最顶层子句和NESTED [PATH]. NESTED [PATH]之间的外部联接。An inner join can be emulated by applying a suitable condition in the 可以通过在WHERE clause, as shown here: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 | +------+------+
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. COLUMNS中NESTED [PATH]的两个或多个实例一次处理一个接一个。While one nested path is producing records, columns of any sibling nested path expressions are set to 当一个嵌套路径生成记录时，任何同级嵌套路径表达式的列都设置为NULL. 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: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 | +------+------+------+
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: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 | +---------+---------+---------+------+-------+
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. apath和bpath的值是相同的；这意味着它们不能用来确定lpath值是来自同一个父级还是来自不同的父级。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. ord列的值与top_ord等于1的记录集保持相同，因此这两个值来自单个对象。The remaining two values are from different objects, since they have different values in the 其余两个值来自不同的对象，因为它们在ord column.ord列中有不同的值。