SQL JSON_TABLE JSON 轉換為資料表

在之前的章節中，我們學會了如何從 JSON 提取單一值或更新屬性。但在實務上，JSON 欄位常存放「物件陣列」(Array of Objects)。如果你想針對這些陣列資料進行統計、分組，或者將其與實體資料表進行 JOIN，單靠 JSON_EXTRACT 會變得極其緩慢且難以維護。

這時，JSON_TABLE() 就是處理「行轉列 (Row-to-Column)」的終極武器。

什麼是 JSON_TABLE？

JSON_TABLE() 是一個資料表函數 (Table Function)，它能將 JSON 的層級結構「攤平 (Flattening)」成一組虛擬的關係型資料列。你可以像操作傳統資料表一樣，對它的結果進行 WHERE、GROUP BY 或 ORDER BY。

基本語法解析

SELECT columns 
FROM source_table, 
JSON_TABLE(
    json_doc,
    '$.path_to_array' COLUMNS(
        alias data_type PATH '$.sub_path' [default_error_clause],
        ...
    )
) AS virtual_table_alias;

核心欄位類型與範例

在定義 COLUMNS 時，除了對應屬性路徑，還有幾個強大的特殊類型：

FOR ORDINALITY (自動編號)

當你需要知道元素在陣列中的順序時，這個子句會自動生成一個從 1 開始的遞增整數。

PATH '$' (抓取當前元素)

如果你的陣列不是物件陣列，而是簡單的數值或字串陣列（如 ["A", "B", "C"]），我們使用 $ 來代表當前元素。

綜合範例：處理簡單標籤陣列

假設有一張 articles 表，tags 欄位存著字串陣列：["SQL", "JSON", "MySQL"]

SQL 查詢：

SELECT jt.*
FROM articles,
JSON_TABLE(
    tags,
    '$[*]' COLUMNS(
        tag_id FOR ORDINALITY,
        tag_name VARCHAR(20) PATH '$'
    )
) AS jt;

結果表：

實戰教學：拆解物件陣列

這是最常見的用法，將 JSON 物件中的屬性轉化為標準欄位。

範例：拆解訂單細項

假設 orders 表的 items 欄位如下： [{"p_name": "手機", "price": 20000}, {"p_name": "耳機", "price": 3000}]

SQL 查詢：

SELECT 
    o.order_id, 
    jt.p_name, 
    jt.p_price
FROM orders o,
JSON_TABLE(
    o.items,
    '$[*]' COLUMNS(
        p_name VARCHAR(50) PATH '$.p_name',
        p_price INT PATH '$.price'
    )
) AS jt;

解析結果：

處理複雜結構：NESTED PATH

當你的 JSON 陣列內還嵌套了子陣列時（例如：員工資料包含多項技能），我們可以使用 NESTED PATH 將所有層級攤開。

範例：員工與技能

JSON 結構：{"name": "Mike", "skills": ["SQL", "Java"]}

SQL 查詢：

SELECT jt.emp_name, jt.skill
FROM company,
JSON_TABLE(
    employee_json,
    '$[*]' COLUMNS(
        emp_name VARCHAR(50) PATH '$.name',
        NESTED PATH '$.skills[*]' COLUMNS(
            skill VARCHAR(50) PATH '$'
        )
    )
) AS jt;

結果表：

容錯機制：ON EMPTY 與 ON ERROR

在處理來源不明的 JSON 時，資料缺失或型別錯誤非常常見。

• ON EMPTY：當路徑在 JSON 中找不到時觸發。
• ON ERROR：當解析失敗（如字串無法轉成數字）時觸發。

範例：設定預設值

假設某筆資料遺漏了 age 屬性，或 age 被誤填為 "N/A" 字串。

COLUMNS(
    user_name VARCHAR(20) PATH '$.name',
    user_age INT PATH '$.age' 
        DEFAULT 18 ON EMPTY 
        DEFAULT 0 ON ERROR
)

行為說明：

1. 如果沒有 age 這個 Key $\rightarrow$ 輸出 18。
2. 如果 age 是 "ABC" (無法轉成 INT) $\rightarrow$ 輸出 0。

應用：與傳統資料表 JOIN

攤平後的 JSON 資料可以像普通表一樣與其他表關聯。

-- 找出所有購買了「手機」的訂單，並關聯使用者資料表
SELECT u.user_name, o.order_date, jt.price
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN JSON_TABLE(o.items, '$[*]' COLUMNS(
    name VARCHAR(50) PATH '$.p_name',
    price INT PATH '$.price'
)) AS jt ON jt.name = '手機';

跨平台技術對比

總結

JSON_TABLE 讓你可以將靈活的 NoSQL 資料即時轉換成結構化的關係型資料。這不僅簡化了查詢邏輯，更讓你能在同一個 SQL 語句中無縫結合兩種不同格式的資料優勢。