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 ORDINALITYCOLUMNS
子句中的行;名为name
的列是一个计数器,其类型为UNSIGNED INT
,初始值为1。这等同于在CREATE TABLE
语句中指定一个列为AUTO_INCREMENT
,可以用来区分具有相同值的多个行,该行由NESTED [PATH]
子句生成。 -
: 这种类型的列用于提取由name
type
PATHstring_路径
[on_empty
] [on_error
]string_路径
指定的值。type
是 MySQL 标量数据类型(即不能是对象或数组)。JSON_TABLE()
将数据提取为 JSON,然后将其转换为列类型,使用 MySQL 对 JSON 数据的常规自动类型转换。缺少值触发on_empty
子句。保存对象或数组触发可选on_error
子句;这也发生在将 JSON 值保存到表列时的错误转换,例如尝试将字符串 ''asd'
' 保存到整数列。 -
: 这个列返回如果指定的位置中存在任何数据,則为 1,否则为 0。name
type
EXISTS PATH路径
type
可以是任何有效的 MySQL 数据类型,但通常应该指定为某种形式的INT
。 -
NESTED [PATH]
: 将 JSON 数据中的嵌套对象或数组展平到一个行中,并将父对象或数组的 JSON 值一起返回。使用多个path
COLUMNS (column_list
)PATH
选项可以将多级嵌套的 JSON 值投影到一个行中。path
是相对于JSON_ TABLE()
的父路径或NESTED [PATH]
子句的路径(在嵌套路径的情况下)。
on empty
如果指定,确定了 JSON_ TABLE()
在数据缺失时的行为。这一子句也会在 NESTED PATH
子句没有匹配项且产生一个 NULL 补充行时被触发。on empty
可以取以下值之一:
-
NULL ON EMPTY
: 列设置为NULL
;这是默认行为。 -
DEFAULT
: 提供的json_ string
ON EMPTYjson_ string
将被解析为 JSON,假设它是有效的,并将其存储而不是缺失值。列类型规则也适用于默认值。 -
ERROR ON EMPTY
: 发生错误。
如果使用,on_error
可以取以下值之一,以显示相应的结果:
-
NULL ON ERROR
: 列设置为NULL
;这是默认行为。 -
DEFAULT
: 将json字符串
ON ERRORjson_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
。