JSON_TABLE 是 MySQL 8.0 中一个新的 JSON 函数。它也是一个表函数，返回值不是标量值而是结果集。JSON_TABLE 将 JSON 文档(部分或全部)转换为关系表，以便您可以像使用普通表一样使用它。JSON_TABLE 函数从一个指定的 JSON 文档中提取数据并返回一个具有指定列的关系表。可以像普通的表一样使用 JSON_TABLE () 返回的表。
json-table官方文档地址
https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html#function_json-table
以下是jsontable的语法
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
使用示例
假设我们有一个表包含ID和名称：
CREATE TABLE t1
(
id varchar(32),
name JSON
);
添加如下数据：
INSERT INTO t1
VALUES (uuid_short(), json_array('John'));
INSERT INTO t1
VALUES (uuid_short(), json_array('toma','Smith'));
INSERT INTO t1
VALUES (uuid_short(), json_array('aa','bb','cc','tt'));
现在，我们想要从 t1 表中查询所有名称和id，进行查询（在 MySQL 中，JSON_TABLE 函数的 JSON 路径表达式可以使用 $ 符号来指定。$[*] 表示提取 JSON 文档中的所有属性）：
SELECT value,id
from t1
,
JSON_TABLE(name, '$[*]' COLUMNS (
value VARCHAR(255) PATH '$'
)) AS jt
数据将如下：
NESTED PATH 嵌套路径
NESTED PATH为其所属的子句中的每个匹配项生成一组记录。可以解决复杂的json嵌套数据转为行。
添加一条数据：
INSERT INTO t1
VALUES ('1', json_array(json_object('a',1,'b',json_array(11,111)),
json_object('a',2, 'b',json_array(22,222)),
json_object('a',3)));
使用 NESTED PATH：
SELECT jt.*
FROM t1,
JSON_TABLE(
name,
'$[*]' COLUMNS (
a INT PATH '$.a',
NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$')
)
) AS jt
WHERE id = '1';
我们发现每次jsontable后依然会对应所在的ID，比如：
上面需求的a,b列的ID就是1而不是其他ID。这其实是遵从了sql标准。
横向派生表
根据SQL标准，MySQL总是将带有表函数（如JSON_TABLE（））的连接视为使用LATERAL无论MySQL发布版本如何，都是如此，这就是为什么即使在8.0.14之前的MySQL版本中也可以加入此函数的原因。在MySQL 8.0.14及更高版本中，LATERAL 关键字是隐式的，不允许在 JSON_TABLE（） 之前使用。这也是根据SQL标准。
