mysql的jsonTable使用及說(shuō)明

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

MySQL 8.0引入JSON_TABLE函數(shù),用于將JSON數(shù)據(jù)轉(zhuǎn)換為關(guān)系表,便于查詢,JSON_TABLE支持嵌套路徑,解決復(fù)雜JSON嵌套數(shù)據(jù)轉(zhuǎn)換問(wèn)題,根據(jù)SQL標(biāo)準(zhǔn),MySQL將帶有表函數(shù)的連接視為使用LATERAL,8.0.14及以上版本LATERAL關(guān)鍵字隱式

以下是jsontable的語(yǔ)法

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

使用示例

假設(shè)我們有一個(gè)表包含ID和名稱：

CREATE TABLE t1
(
    id       varchar(32),
    name JSON
);

添加如下數(shù)據(jù)：

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'));

現(xiàn)在，我們想要從 t1 表中查詢所有名稱和id，進(jìn)行查詢（在 MySQL 中，JSON_TABLE 函數(shù)的 JSON 路徑表達(dá)式可以使用 $ 符號(hào)來(lái)指定。

$[*] 表示提取 JSON 文檔中的所有屬性）：

SELECT value,id
from t1
        ,
     JSON_TABLE(name, '$[*]' COLUMNS (
         value VARCHAR(255) PATH '$'
         )) AS jt

數(shù)據(jù)將如下：

NESTED PATH 嵌套路徑

NESTED PATH為其所屬的子句中的每個(gè)匹配項(xiàng)生成一組記錄。可以解決復(fù)雜的json嵌套數(shù)據(jù)轉(zhuǎn)為行。

添加一條數(shù)據(jù)：

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';