SQL JSON SELECT 讀取與搜尋

將資料以 JSON 格式存入資料庫後，最重要的任務就是如何精確且高效地將我們需要的資訊讀取出來。這篇文章將介紹從基礎到進階的 JSON 讀取函數、運算子與搜尋技巧。

核心函數：JSON_EXTRACT()

JSON_EXTRACT() 是最標準的函數，它能根據你提供的 JSON 路徑 從 JSON 文件中提取資料。它支援同時提取多個路徑。

基本語法

JSON_EXTRACT(json_doc, '$.path1', '$.path2', ...)

範例：從複雜物件中讀取資料

假設有一張 products 資料表，其中的 metadata 欄位存放產品詳細規格：

SQL 語句：

SELECT 
    name, 
    JSON_EXTRACT(metadata, '$.brand') AS brand,
    JSON_EXTRACT(metadata, '$.specs.cpu') AS cpu,
    JSON_EXTRACT(metadata, '$.colors[0]') AS primary_color
FROM products;

結果：

MySQL 特色運算子：-> 與 ->>

為了讓 SQL 語法更簡潔，MySQL 參考了 PostgreSQL 的做法，提供了專用的箭頭運算子。

-> (JSON 提取運算子)

等同於 JSON_EXTRACT()，回傳的資料仍保存在 JSON 型態中（因此字串外會有雙引號）。

SELECT metadata->'$.brand' FROM products; 
-- 輸出: "BrandA"

->> (外掛去引號運算子)

這是實務中最常用的運算子。它等同於執行 JSON_UNQUOTE(JSON_EXTRACT(...))，它會移除字串兩側的引號，並將值轉換為一般的 VARCHAR。

SELECT metadata->>'$.brand' FROM products; 
-- 輸出: BrandA (純文字)

進階路徑尋找：萬用字元與遞迴

當 JSON 結構非常深層或不固定時，我們可以使用萬用字元：

• $.*：代表該物件下的所有屬性。
• $[*]：代表該陣列下的所有元素。
• $**：遞迴式搜尋，可以找到所有層級中符合後續路徑定義的資料。

範例：

-- 找出 metadata 下所有層級中名為 "cpu" 的值
SELECT JSON_EXTRACT(metadata, '$**.cpu') FROM products;

高效搜尋 JSON 內容

除了讀取，我們也常需要在 WHERE 子句中進行篩選：

JSON_CONTAINS() 檢查包含關係

語法：JSON_CONTAINS(target_json, candidate_json, [path])

-- 檢查顏色陣列中是否包含 "Black"
SELECT * FROM products 
WHERE JSON_CONTAINS(metadata, '"Black"', '$.colors');

MEMBER OF 運算子

在 MySQL 8.0.17+ 之後更推薦的寫法，專門用於陣列元素檢查：

SELECT * FROM products 
WHERE "Silver" MEMBER OF (metadata->'$.colors');

JSON_SEARCH() 尋找值的位置

如果你想知道某個字串在 JSON 中的具體路徑，可以使用 JSON_SEARCH()。

-- 尋找 "i7" 字串在 metadata 中的路徑
SELECT JSON_SEARCH(metadata, 'one', 'i7') FROM products;
-- 輸出: "$.specs.cpu"

讀取結構資訊

• JSON_KEYS()：取得物件中所有的鍵。
• JSON_LENGTH()：取得陣列長度。
• JSON_TYPE()：檢查資料型態 (如 OBJECT, ARRAY, NULL, BOOLEAN)。

效能與最佳化 (Indexing)

重點！！ JSON 欄位本身無法直接建立普通索引（B-Tree），這會導致搜尋大表時效能極差。

最佳實踐：使用 Generated Columns (產生欄位) 你可以將 JSON 中的某個頻繁搜尋的屬性「提取」出來作為一個虛擬欄位，並對該欄位建立索引：

-- 1. 建立一個提取 brand 屬性的產生欄位
ALTER TABLE products 
ADD COLUMN brand_virtual VARCHAR(50) 
GENERATED ALWAYS AS (metadata->>'$.brand') STORED;

-- 2. 對該產生欄位建立索引
CREATE INDEX idx_brand ON products(brand_virtual);

建立了索引後，即使 metadata 內有數百萬筆資料，搜尋特定品牌的速度也會瞬間提升。

跨資料庫語法比較

透過這些進階工具與索引技巧，你可以確保在操作大型 JSON 資料集時，依然保持優異的查詢效能與開發彈性。