JSON_TABLE 函数
描述
JSON_TABLE() 函数子级路径（path），可以从嵌套的 JSON 数据中提取所需信息。
JSON_TABLE() 函数用于将 JSON 数据从半结构化数据转为结构化数据，即从 JSON 文档中提取数据并返回具有指定列的关系表。通过将 JSON 数据的各个部分映射到一个虚拟表的行和列，它创建了一个清晰的表结构，这不仅便于执行查询操作，还允许用户将这些数据插入到新的表或视图中以进行进一步处理。
JSON_TABLE() 为每一个 JSON 值提供了列输出，对于 JSON 内的数组，也提供了多行（列）输出。
语法
JSON_TABLE(expr, path_literal COLUMNS (column_list)) [AS] alias
column_list:
column[, column...]
column:
column_name FOR ORDINALITY
| column_name data_type PATH path_literal [json_value_on_empty_clause] [json_value_on_error_clause]
| column_name data_type EXISTS PATH path_literal
| NESTED [PATH] path_literal COLUMNS (column_list)
json_value_on_empty_clause:
{NULL | DEFAULT json_string | ERROR} ON EMPTY
json_value_on_error_clause:
{NULL | DEFAULT json_string | ERROR} ON ERROR
说明
JSON_TABLE() 必须用在 SELECT 语句的 FROM 子句中。
JSON_TABLE() 函数语法说明如下：
-
expr: 指定转换为 JSON 数据的输入表达式，如果 表达式不可以转成 JSON 数据则会报错。 -
path_literal：指定 Path 路径的字符串，输入其他类型数据或者非正确的 Path 路径信息则会报错。 -
column_list：指定所输入的列定义信息，在表达式中至少包含一个列定义参数，否则会报错。 -
column：指定具体的列定义信息，具体可以分为如下四类：-
column_name FOR ORDINALITYFOR子句用于为当前输出行提供行号，其中column_name是INT类型的列。 -
column_name data_type PATH path_literal [json_value_on_empty_clause] [json_value_on_error_clause]子句用于提取path_literal指定的数据为 JSON 并强制转换为列类型。如果缺失值会触发可选的json_value_on_empty_clause子句。 -
column_name data_type EXISTS PATH path_literal子句：如果path_literal指定的位置存在任何数据，则此列返回 1，否则返回 0。 -
NESTED [PATH] path_literal COLUMNS (column_list)子句表示重复的嵌套结构，可以在其中继续定义column的四种列类型。
-
-
data_type：指定数据类型，当前支持 seekdb下除ENUM和SET之外的所有数据类型。 -
alias：指定表别名。 -
json_value_on_empty_clause：如果经过 Path 筛选的数据是空值时用户可选的预期行为，可选行为包含三种：NULL、ERROR和默认值DEFAULT。NULL ON EMPTY：将该列设置为NULL，为默认行为。DEFAULT json_string：将json_string解析为默认值取代 JSON 对象或者数组。seekdb 允许行为默认值为任意类型的常量数据。ERROR ON EMPTY：抛出错误。
-
json_value_on_error_clause：如果表达式执行期间发生错误，用户可以使用预期的行为去覆盖该错误，可选择的行为包含三种：NULL、ERROR和默认值DEFAULT。可选行为和json_value_on_empty_clause一致。以下场景会触发可选的
json_value_on_error_clause子句。-
expr不是格式正确的 JSON 数据 -
使用 JSON 路径表达式计算 JSON 数据时发现非标量值
-
使用 JSON 路径表达式计算 JSON 数据时未找到匹配项。
-
所指定的返回值数据类型不够大，无法容纳返回值。
-
示例
解析用户输入数据
如下示例为将用户输入的 JSON 数据转为关系表。
SELECT * FROM
JSON_TABLE(
'[{"x":"3"},{"a":2},{"b":1},{"a":0},{"a":[1,2]}]',
'$[*]' COLUMNS (id FOR ORDINALITY,
jpath VARCHAR(100) PATH '$.a'
DEFAULT '33' ON EMPTY
DEFAULT '66' ON ERROR,
jsn_path JSON PATH '$.a' DEFAULT '{"x":33}' ON EMPTY,
jexst INT EXISTS PATH '$.b')
) AS tt;
示例的查询结果如下：
+------+-------+-----------+-------+
| id | jpath | jsn_path | jexst |
+------+-------+-----------+-------+
| 1 | 33 | {"x": 33} | 0 |
| 2 | 2 | 2 | 0 |
| 3 | 33 | {"x": 33} | 1 |
| 4 | 0 | 0 | 0 |
| 5 | 66 | [1, 2] | 0 |
+------+-------+-----------+-------+
5 rows in set (0.003 sec)
从表中获取数据
如下示例为从表中获取 JSON 数据，将其展开称为结构化数据。
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(id INT, jd JSON);
INSERT INTO t1 VALUES (1, '[1,3,5]'),(2,'[2,4,6]');
SELECT id, jt.* FROM t1,
JSON_TABLE(jd, '$[*]' COLUMNS (jid FOR ORDINALITY,
val INT PATH '$')) AS jt;
SELECT /*+ JOIN_ORDER(jt, t1) */ id, jt.*
FROM t1,
JSON_TABLE(jd, '$[*]' COLUMNS (jid FOR ORDINALITY,
val INT PATH '$')) AS jt;
示例的查询结果如下：
+------+------+------+
| id | jid | val |
+------+------+------+
| 1 | 1 | 1 |
| 1 | 2 | 3 |
| 1 | 3 | 5 |
| 2 | 1 | 2 |
| 2 | 2 | 4 |
| 2 | 3 | 6 |
+------+------+------+
6 rows in set (0.004 sec)
创建视图
如下示例为通过 JSON_TABLE() 函数的返回值生成视图。
CREATE VIEW v1 AS
SELECT * FROM JSON_TABLE('[{"a": 1, "b": 2}]',
'$[*]' COLUMNS ( a INT PATH '$.b')) AS jt;
SELECT * FROM v1;
示例的查询结果如下：
+------+
| a |
+------+
| 2 |
+------+
1 row in set (0.001 sec)
如下示例为查看视图定义。
SHOW CREATE VIEW v1;
DROP VIEW v1;
示例的查询结果如下：
+------+-----------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View | character_set_client | collation_connection |
+------+-----------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| v1 | CREATE VIEW `v1` AS select `jt`.`a` AS `a` from JSON_TABLE('[{\"a\": 1, \"b\": 2}]' , '$[*]' columns (a INTEGER path '$.b' )) jt | utf8mb4 | utf8mb4_general_ci |
+------+-----------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.001 sec)
向表中插入数据
如下示例为将 JSON_TABLE() 函数的返回值插入到表中。
DROP TABLE IF EXISTS t2;
CREATE TABLE t2(id INT, a TINYINT, b VARCHAR(5), c DATE, d DECIMAL);
INSERT INTO t2 SELECT * FROM
JSON_TABLE(JSON_ARRAY(JSON_OBJECT('a', 1, 'b', 'abc'),
JSON_OBJECT('a', 2, 'b', 'abcd'),
JSON_OBJECT('a', 1000, 'b', 'xyz'),
JSON_OBJECT('c', TIME'12:00:00', 'd', 1e308)),
'$[*]' COLUMNS (id FOR ORDINALITY,
a TINYINT PATH '$.a' DEFAULT '111' ON ERROR,
b VARCHAR(5) PATH '$.b' DEFAULT '"ERR"' ON ERROR,
c DATE PATH '$.c' DEFAULT '"2001-01-01"' ON ERROR,
d DECIMAL PATH '$.c' DEFAULT '999' ON ERROR)
) AS jt;
SELECT * FROM t2 ORDER BY id;
DROP TABLE t2;
示例的查询结果如下：
+------+------+------+------------+-------+
| id | a | b | c | d |
+------+------+------+------------+-------+
| 1 | 1 | abc | NULL | NULL |
| 2 | 2 | abcd | NULL | NULL |
| 3 | 111 | xyz | NULL | NULL |
| 4 | NULL | NULL | 2001-01-01 | 43200 |
+------+------+------+------------+-------+
4 rows in set (0.002 sec)