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 | ERROR json_string} ON EMPTY on_error: {NULL | DEFAULT | ERROR json_string} ON ERRORexpr：这是一个返回JSON数据的表达式。这可以是常量（'{"a":1}'），列（t1.json_data，在条款中t1指定的给定表）或函数调用（）。JSON_TABLE()FROMJSON_EXTRACT(t1.json_data,'$.post.comments')path：一个JSON路径表达式，该表达式应用于数据源。我们将与路径匹配的JSON值作为行源; 这用于生成一行相关数据。该COLUMNS子句评估行源，在行源中查找特定的JSON值，并在关系数据行的各个列中将这些JSON值作为SQL值返回。将
alias是必需的。表别名的常规规则适用（请参见“模式对象名称”）。JSON_TABLE()支持四种类型的列，如下列表所述：name FOR ORDINALITY：此类型枚举COLUMNS子句中的行；名为的列name是一个计数器，其类型为UNSIGNED INT，其初始值为1。这等效于AUTO_INCREMENT在CREATE TABLE语句中指定一列，并且可用于区分由NESTED[PATH]子句生成的多行具有相同值的父行。nametypePATHstring_path[on_empty][on_error]string_path。type是MySQL标量数据类型（也就是说，它不能是对象或数组）。JSON_TABLE()使用适用于MySQL中JSON数据的常规自动类型转换，将数据提取为JSON，然后将其强制转换为列类型。缺少值将触发该on_empty子句。保存对象或数组会触发可选on error子句；当强制将保存为JSON的值保存到表列时发生错误时，例如尝试将字符串保存'asd'到整数列时，也会发生这种情况。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 string ON ERROR：json_string解析为JSON（假设它是有效的）并存储而不是对象或数组。ERROR ON ERROR：引发错误。
在MySQL 8.0.20之前，如果发生类型转换错误，
NULL ON ERROR或者DEFAULT ... ON ERROR已指定或隐含了类型转换错误，则会引发警告。在MySQL 8.0.20和更高版本中，情况不再如此。（错误＃30628330）以前，可以按任一顺序指定
ON EMPTY和ON ERROR子句。这与SQL标准背道而驰，SQL标准规定ON EMPTY，如果指定，则必须在任何ON ERROR子句之前。因此，从MySQL 8.0.20开始，不推荐使用ON ERRORbeforeON EMPTY；尝试这样做会导致服务器发出警告。在将来的MySQL版本中将删除对非标准语法的支持。当保存到列的值被截断时（例如将3.14159保存在
DECIMAL(10,1)列中），将独立于任何ON ERROR选项发出警告。在单个语句中截断多个值时，警告仅发出一次。以下查询演示了
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 ( -> rowidFOR ORDINALITY , -> ac VARCHAR(100)PATH "$.a"DEFAULT '111'ON EMPTY DEFAULT '999'ON ERROR , -> aj JSONPATH "$.a"DEFAULT '{"x": 333}'ON EMPTY , -> bx INTEXISTS 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)nametype EXISTS PATH path：如果在指定的位置存在任何数据，则此列返回1，否则返回path0。type可以是任何有效的MySQL数据类型，但通常应指定为的各种形式INT。NESTED[PATH]path COLUMNS(column_list)：这会将JSON数据中的嵌套对象或数组与父对象或数组中的JSON值一起展平为一行。使用多个PATH选项可将JSON值从多层嵌套投射到单行中。path是相对于的父路径行路径JSON_TABLE()，NESTED[PATH]如果是嵌套路径，则是相对于父子句的路径。
列名受制于控制表列名的常规规则和限制。请参见“模式对象名称”。
检查所有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。这实现了top子句和之间的外部联接NESTED[PATH]。可以通过在WHERE子句中应用适当的条件来模拟内部联接，如下所示：mysql>
SELECT * ->FROM ->JSON_TABLE ( -> '[ {"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3}]', -> '$[*]'COLUMNS ( -> a INTPATH '$.a', ->NESTED PATH '$.b[*]'COLUMNS (b INTPATH '$') -> ) -> )AS jt ->WHERE b IS NOT NULL; +------ +------ + | a | b | +------ +------ + | 1 | 11 | | 1 | 111 | | 2 | 22 | | 2 | 222 | +------ +------ +同级嵌套路径（即
NESTED[PATH]同一COLUMNS子句中的两个或多个实例）被一个接一个地处理，一次处理。当一个嵌套路径生成记录时，任何同级嵌套路径表达式的列都设置为NULL。这意味着单个包含COLUMNS子句中单个匹配项的记录总数是总和，而不是NESTED[PATH]修饰符产生的所有记录的乘积，如下所示：mysql>
SELECT * ->FROM ->JSON_TABLE ( -> '[{"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}]', -> '$[*]'COLUMNS ( -> a INTPATH '$.a', ->NESTED PATH '$.b[*]'COLUMNS (b1 INTPATH '$'), ->NESTED PATH '$.b[*]'COLUMNS (b2 INTPATH '$') -> ) -> )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列中具有不同的值。