MongoDB 透過 lookup pipeline 實踐 Left Join

很久以前寫過一篇基本的 mongodb group 語法的基本介紹,在這些年間也持續跟 mongodb 持續纏鬥無法自拔…,對他又愛又恨,雖然超想拔掉他,但是從用途/功能性上卻無法找到他的取代品。

如今已經面臨要開始用他來 JOIN 了,感覺這毒是越吸越重了..XD 讓我們繼續看下去..

我們今天試著從 3.2 開始 aggregate 新支援的語法 $lookup 搭配 3.6 開始的 lookup pipeline 來解決我們需要 LEFT JOIN 的問題。

先複習一下,所有 mysql 跟 mongodb syntax 的 mapping,官方已經有一個不錯的文件快速幫助參照要怎麼把 SQL 轉換成 mongodb query

先來一個範例資料吧,現在有兩個 collection 使用者 user 跟訂單 order

user
| _id | name   |
|-----|--------|
| 1   | mlwmlw |
| 2   | wawa   |
| 3   | hap    |

order
| _id | user_id | amount |
|-----|---------|--------|
| 1   | 1       | 300    |
| 2   | 2       | 123    |
| 3   | 1       | 500    |

如果用 SQL 的語法,要取得使用者的每筆訂單會這樣查詢

SELECT user._id, user.name, sum(order.amount) from user
LEFT JOIN order on user._id = order.user_id

如果使用 mongodb 的 aggregate framework 的話則是透過從 3.2 開始支援的  lookup 

db.getCollection('user').aggregate([
   {$lookup:
      {
         as: 'orders',
         from: 'order',
         localField: "_id",
         foreignField: "user_id"
     }
  }])

對應到 SQL 來看就蠻容易理解的,只是查詢出來的結構會是有多一個 orders 的 array 出現,不像是 SQL 被拆解成數筆資料,如果想要得到跟 SQL 一樣的結果也是可以透過 $unwind 來達成

//result 
[
    {
        "_id" : 1,
        "name" : "mlwmlw",
        "orders" : [ 
            {
                "_id" : 1,
                "user_id" : 1,
                "amount" : "300"
            }, 
            {
                "_id" : 3,
                "user_id" : 1,
                "amount" : 500
            }
        ]
    },
    {
        "_id" : 2.0,
        "name" : "wawa",
        "orders" : [ 
            {
                "_id" : 2,
                "user_id" : 2,
                "amount" : 123
            }
        ]
    },
    {
        "_id" : 3.0,
        "name" : "hap",
        "orders" : []
    }
]

這樣連帶造成的問題是 JOIN 起來的資料量有可能會因為 order 的數量不斷增加而變多,超過 BSON 單筆 16MB 的限制,資料就會查不出來了,會噴出如下錯誤

Aggregate $lookup Total size of documents in matching pipeline exceeds maximum document size

這個時候 3.6 開始支援的 $lookup pipeline 就可以解決這個問題了,就像在 SQL 中,你可以透過 JOIN 的 on 多增加一些條件,來減少需要被 JOIN 的資料量,pipeline 就是讓你在 lookup 重新獲得 aggregate 的能力

例如想要取得每一個使用者最新一筆訂單,可以改成

db.getCollection('user').aggregate([
   {$lookup:
      {
         as: 'orders',
         from: 'order',
         let: { 
            //order.user_id
            order_user_id: "$user_id"
         },
         pipeline: [{
            $match: {
               $expr: {
                  // on user._id = order.user_id
                  $eq: [ "$_id", "$$order_user_id" ]
               }
            },
         }, {
           $sort: {"_id": -1}
         },{
           $limit: 1
        }]

     }
  }])

透過 pipeline 的 $match 來作為 on 的條件,搭配 pipeline 的 aggregate 能力,可以在做任何 $sort, $group 等語法,來讓最後 JOIN 進 document 的內容選擇只要你需要用到的資料

結論

就如同 SQL 有子查詢的能力一般,在這個版本上,幾乎可以滿足 90% 以上 SQL 的查詢能力了,Pipeline 的彈性甚至超越 SQL( Declarative programming),因為更偏向指令、流程(Imperative programming) 的描述方式一些, 只是這種混種品寫起來會很想死而已XD

之前有試用過 Mongodb 的 bi-connector,他就是幫你把 SQL 轉成 mongodb 的 aggregate,解決這種痛苦的窘境,但是當初試用的時候還在 3.2 的版本的關係,其隨便一句 SQL 就能跑到要崩潰都還出不來,也不見得有辦法建立索引改進。

如今看來 Mongodb 為了朝向大眾市場,看得出來很努力在新的版本解決這些問題,說不定有一天 Mongodb 預設裝起來就能透過 SQL 來 Query 也說不定呢,就像 BigQuery 一樣,因為原本語法不是標準 SQL,所以改進起來也沒有包袱,一個版本一個版本都可以有革命性的變革,只是看起來很像是數十年前 SQL 剛被標準化時的發展軌跡呀

參考資料

官方的 lookup 說明文件,有更多範例喔 https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *