SQL JSON UPDATE 欄位值操作修改

學會了如何讀取 JSON 後，接下來我們要探討如何「動態產生」新的 JSON 文件，以及如何精確地更新已經儲存在資料庫中的 JSON 欄位。

動態建立 JSON 文件

在 SQL 中組裝 JSON 非常強大，因為你可以直接將資料表的欄位值「打包」成標準的 JSON 格式，這在產出 API 原始資料或進行資料遷移時非常實用。

JSON_OBJECT()：建立物件

將「鍵 (Key)」與「值 (Value)」配搭成物件。鍵必須是字串，值可以是任何型態（包括另一個 JSON）。

SELECT JSON_OBJECT(
    'user_id', id, 
    'is_active', TRUE, 
    'login_at', NOW()
) AS user_json 
FROM users;

JSON_ARRAY()：建立陣列

將一組值按順序封裝進陣列中。

SELECT JSON_ARRAY(name, email, age) FROM users;
-- 輸出範例: ["Mike", "mike@example.com", 25]

JSON 聚合函數：JSON_ARRAYAGG() 與 JSON_OBJECTAGG()

當你需要將「多列 (Multiple Rows)」資料合併成一個大的 JSON 陣列或物件時，這兩個彙總函數是利器。

-- 將所有產品名稱合併成一個 JSON 陣列
SELECT JSON_ARRAYAGG(name) FROM products;
-- 輸出: ["iPhone", "iPad", "MacBook"]

-- 將員工編號與姓名對應成一個大的 JSON 物件
SELECT JSON_OBJECTAGG(emp_id, emp_name) FROM employees;
-- 輸出: {"101": "張一", "102": "王二"}

修改既存 JSON 的三劍客

在執行 UPDATE 時，我們往往只想變更 JSON 中的某一小部分，而不是替換掉整塊資料。SQL 提供了三種邏輯截然不同的修改函數：

修改邏輯對照表

範例：靈活更新個人資訊

假設 profile 原本為 {"city": "Taipei"}：

-- 使用 JSON_SET: 更新城市名並新增新屬性 'vocation'
UPDATE users 
SET profile = JSON_SET(profile, '$.city', 'Tainan', '$.vocation', 'Engineer')
WHERE id = 1;
-- 結果: {"city": "Tainan", "vocation": "Engineer"}

-- 使用 JSON_INSERT: 因為 'city' 已存在，它不會被改為 'London'
UPDATE users 
SET profile = JSON_INSERT(profile, '$.city', 'London', '$.hobby', 'Golf')
WHERE id = 1;
-- 結果: {"city": "Tainan", "vocation": "Engineer", "hobby": "Golf"}

陣列的高階操作

針對 JSON 陣列，我們不只有追加，還能指定位置插入。

JSON_ARRAY_APPEND()

在陣列的「末端」追加元素。如果路徑原本不是陣列，它會自動將原值與新值封裝成陣列。

-- 在 tags 陣列最後加入 "Sale"
UPDATE products 
SET tags = JSON_ARRAY_APPEND(tags, '$', 'Sale') 
WHERE id = 101;

JSON_ARRAY_INSERT()

在「指定索引位置」插入元素。與 APPEND 不同，它會將指定位置後的元素依序往後移。

-- 在第二個位置 (索引 1) 插入 "Featured"
UPDATE products 
SET tags = JSON_ARRAY_INSERT(tags, '$[1]', 'Featured') 
WHERE id = 101;

JSON_REMOVE() 刪除指定內容

可以用來刪除物件中的某個 Key，或是陣列中的某個位置。

-- 1. 刪除物件屬性
UPDATE users SET profile = JSON_REMOVE(profile, '$.vocation') WHERE id = 1;

-- 2. 刪除陣列第三個元素
UPDATE products SET tags = JSON_REMOVE(tags, '$[2]') WHERE id = 101;

合併 JSON 文件的差異

當你需要將兩塊 JSON 合併（例如把使用者的舊設定與新預設值合併）時，有兩種主要方式：

JSON_MERGE_PATCH() (推薦：RFC 7396)

邏輯：後者完全覆寫前者。 如果兩邊都有同名的 Key，且值不是物件，則由後面的值勝出。

SELECT JSON_MERGE_PATCH('{"a": 1, "b": 2}', '{"a": 9, "c": 3}');
-- 結果: {"a": 9, "b": 2, "c": 3}

JSON_MERGE_PRESERVE()

邏輯：保留所有資料。 如果兩邊都有同名的 Key，它會將兩者的值「合併成陣列」保留下來。

SELECT JSON_MERGE_PRESERVE('{"a": 1}', '{"a": 9}');
-- 結果: {"a": [1, 9]}

透過這些靈活的建立與修改工具，你可以將 SQL 操作提升到類似 NoSQL 的開發效率。