mysql的jsonTable使用及說明

更新時間：2026年01月04日 15:36:22 作者：jpq+

MySQL 8.0引入JSON_TABLE函數,用于將JSON數據轉換為關系表,便于查詢,JSON_TABLE支持嵌套路徑,解決復雜JSON嵌套數據轉換問題,根據SQL標準,MySQL將帶有表函數的連接視為使用LATERAL,8.0.14及以上版本LATERAL關鍵字隱式

以下是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為其所屬的子句中的每個匹配項生成一組記錄?？梢越鉀Q復雜的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';