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 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
是必需的。表别名的常规规则适用(请参见“模式对象名称”)。JSON_TABLE()
支持四种类型的列,如下列表所述:name FOR ORDINALITY
:此类型枚举COLUMNS
子句中的行;名为的列name
是一个计数器,其类型为UNSIGNED INT
,其初始值为1。这等效于AUTO_INCREMENT
在CREATE TABLE
语句中指定一列,并且可用于区分由NESTED[PATH]
子句生成的多行具有相同值的父行。
:此类型的列用于提取由指定的值name
type
PATHstring_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 ERROR
beforeON 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,否则返回path
0。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
列中具有不同的值。