MongoDB $merge 教學 (如同 SQL 的 SELECT INTO! )

MongoDB, out, merge

本篇接續 MongoDB $out 教學，介紹的是 $out 語法的進階版本 $merge，客製化程度更高了，能夠處理衝突時該使用覆蓋還是略過等。

$merge 僅能在 aggregation pipeline 中使用，且必須是最後一個執行的階段，也就是說在前面可以執行任何的資料篩選、操作和轉型，最終輸入到目的地。

語法與參數

{ $merge: {
     into: [collection]  // 或是 { db: [db], coll: [collection] },
     on: [field] // 或是 [ field1, field2 ], 指定唯一的欄位，選填參數
     let: [variables],        // 選填參數
     whenMatched: [ replace | keepExisting | merge | fail | pipeline ],  // 選填參數
     whenNotMatched: [ insert | discard | fail ]         // 選填參數
} }

從上述語法上可以得知

into 就是匯入的集合
on 指定唯一的欄位，用來做文件比對是否相符，以進行 whenMatched 或 whenNotMatched 動作，可以是一個或多個欄位
let 可以讓使用者自定義的變數 $$xxx
whenMatched 當遇到衝突時該..
- replace 直接取代
- keepExisting 略過
- merge 嘗試合併(預設選項)
- fail 直接回傳失敗
- pipeline 若有自定義的 user variable，就必須使用 pipeline 參數來客製整個操作細節
whenNotMatched
- insert 直接寫入(預設選項)
- discard 忽略此資料
- fail 直接報錯

注意事項

$merge 語法會 自動建立db 與 自動建立collection，唯獨 cluster 狀態下，需要目的的 db 已經存在
一但成功建立第一筆文件後，db 與 collection 就能夠立即看到
若遇到錯誤，則錯誤之前寫入的資料都還是會存在，並不會被rollback

輸出與來源是相同集合

在 4.4 版本之後就支援這個功能啦！
官方的文件上看起來是不建議這樣做，因為文件可能會被多次更新，甚至是造成無限迴圈，且修改行為很有可能造成著名的 Halloween Problen，在效能上也可能無法預期。綜合以上，如果要做這種舉動，可以用更新或者寫到新集合之類的方案來解決。

Halloween Problem

相傳於萬聖節，有間公司想替薪水低於 50000 的員工加薪 10%，於是寫了以下 sql

1	UPDATE EMP_TABLE SET SALARY=SALARY*1.1 WHERE SALARY<50000

結果發生什麼事？就是所有薪水低於 50000 的員工都會不斷地被執行更新 SALARY 欄位，直到高於 50000 為止。

當然啦～現在資料庫都能夠處理這種低級錯誤了，但還是要了解一下 Update 機制以及 Cursor 機制。

基本語法

最簡單的莫過於單純 merge 啦！

1	{ $merge: <collection> }

重新建立新的 _id

db.source.aggregate([
 { $project: {_id: 0} },
 { $merge: { $into: 'dest' } }
])

測試

首先我們來準備一下測試資料

db.src.insertMany([
   { "_id" : 1, customer: "Baron", orderAmount: 1000, year: 2019, orderNo:1 },
   { "_id" : 2, customer: "Baron", orderAmount: 5000, year: 2020, orderNo:2 },
   { "_id" : 3, customer: "Baron", orderAmount: 5000, year: 2021, orderNo:3 },
   { "_id" : 4, customer: "Mango", orderAmount: 8000, year: 2021, orderNo:1 },
   { "_id" : 5, customer: "Mango", orderAmount: 12000, year: 2021, orderNo:2 },
   { "_id" : 6, customer: "Prime", orderAmount: 3000, year: 2019, orderNo:1 },
   { "_id" : 7, customer: "Prime", orderAmount: 3000, year: 2019, orderNo:2 },
   { "_id" : 8, customer: "Prime", orderAmount: 9000, year: 2020, orderNo:1 },
   { "_id" : 9, customer: "Prime", orderAmount: 12000, year: 2021, orderNo:1 }
])

Case1 直接複製

Syntax:

1	db.src.aggregate([ { $merge: 'test1col'} ])

> db.src.aggregate([ { $merge: 'test1col'} ])
> show collections
src
test1col
> db.test1col.find()
{ "_id" : 1, "customer" : "Baron", "orderAmount" : 1000, "orderNo" : 1, "year" : 2019 }
{ "_id" : 2, "customer" : "Baron", "orderAmount" : 5000, "orderNo" : 2, "year" : 2020 }
{ "_id" : 3, "customer" : "Baron", "orderAmount" : 5000, "orderNo" : 3, "year" : 2021 }
{ "_id" : 4, "customer" : "Mango", "orderAmount" : 8000, "orderNo" : 1, "year" : 2021 }
{ "_id" : 5, "customer" : "Mango", "orderAmount" : 12000, "orderNo" : 2, "year" : 2021 }
{ "_id" : 6, "customer" : "Prime", "orderAmount" : 3000, "orderNo" : 1, "year" : 2019 }
{ "_id" : 7, "customer" : "Prime", "orderAmount" : 3000, "orderNo" : 2, "year" : 2019 }
{ "_id" : 8, "customer" : "Prime", "orderAmount" : 9000, "orderNo" : 1, "year" : 2020 }
{ "_id" : 9, "customer" : "Prime", "orderAmount" : 12000, "orderNo" : 1, "year" : 2021 }
>

看起來沒什麼難度

Case2 調整一下欄位吧

移除 _id 與 year 欄位
Syntax:

db.src.aggregate([
 { $project: { _id: 0, customer:1, orderAmount:1, orderNo:1 } } ,
 { $merge: 'test2col'}
])

可以看到新的集合內 _id 變成預設寫入的 ObjectId 以及 year 不見了

> db.test2col.find()
{ "_id" : ObjectId("61cdad0f8d7a0ffa2943dd8d"), "customer" : "Baron", "orderAmount" : 1000, "orderNo" : 1 }
{ "_id" : ObjectId("61cdad0f8d7a0ffa2943dd8e"), "customer" : "Baron", "orderAmount" : 5000, "orderNo" : 2 }
{ "_id" : ObjectId("61cdad0f8d7a0ffa2943dd8f"), "customer" : "Baron", "orderAmount" : 5000, "orderNo" : 3 }
{ "_id" : ObjectId("61cdad0f8d7a0ffa2943dd90"), "customer" : "Mango", "orderAmount" : 8000, "orderNo" : 1 }
{ "_id" : ObjectId("61cdad0f8d7a0ffa2943dd91"), "customer" : "Mango", "orderAmount" : 12000, "orderNo" : 2 }
{ "_id" : ObjectId("61cdad0f8d7a0ffa2943dd92"), "customer" : "Prime", "orderAmount" : 3000, "orderNo" : 1 }
{ "_id" : ObjectId("61cdad0f8d7a0ffa2943dd93"), "customer" : "Prime", "orderAmount" : 3000, "orderNo" : 2 }
{ "_id" : ObjectId("61cdad0f8d7a0ffa2943dd94"), "customer" : "Prime", "orderAmount" : 9000, "orderNo" : 1 }
{ "_id" : ObjectId("61cdad0f8d7a0ffa2943dd95"), "customer" : "Prime", "orderAmount" : 12000, "orderNo" : 1 }
>

Case3 嘗試合併欄位

這次要用目的地已經存在該資料，且欄位略有重複，看看會發生什麼事。

首先要在目的資料庫建立一些資料，值用 -1 是方便查看改變，而多了一個 vip 欄位。

db.test3col.insertMany([
   { "_id" : 1, customer: "Baron", orderAmount: -1, vip: 15, orderNo:-1 },
   { "_id" : 2, customer: "Baron", orderAmount: -1, vip: 15, orderNo:-1 },
   { "_id" : 3, customer: "Baron", orderAmount: -1, vip: 15, orderNo:-1 }
])

Syntax:

db.src.aggregate([
  { $match: { _id: { $lte: 5 } } },
  { $merge: {
      into: {db: "test", coll: "test3col" },
      on: "_id",
      whenMatched: "merge",
      whenNotMatched: "insert"
 }}
])

可以看到新的集合內 _id 為 1,2,3 的資料都合併成功，已經存在的欄位覆蓋，多的 vip 則保持不變。
另外 _id 為 4,5 就是單純的 insert

> db.test3col.find()
{ "_id" : 1, "customer" : "Baron", "orderAmount" : 1000, "vip" : 15, "orderNo" : 1, "year" : 2019 }
{ "_id" : 2, "customer" : "Baron", "orderAmount" : 5000, "vip" : 15, "orderNo" : 2, "year" : 2020 }
{ "_id" : 3, "customer" : "Baron", "orderAmount" : 5000, "vip" : 15, "orderNo" : 3, "year" : 2021 }
{ "_id" : 4, "customer" : "Mango", "orderAmount" : 8000, "orderNo" : 1, "year" : 2021 }
{ "_id" : 5, "customer" : "Mango", "orderAmount" : 12000, "orderNo" : 2, "year" : 2021 }
>

$merge 的介紹差不多到這就可以了，比較深入的語法若工作有用到可以再私下問，寫太細節應該是沒人會看XD