mysql 的jsonTable使用

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标准。