主旨

最近幾天工作上都在使用 MongoDB，但常常忘記一些指令的格式

因此特別紀錄一下，並說明一下怎麼使用

Lookup

在 MongoDB 初期並沒有所謂的 Join

只有單純的 Collection Search

但由於單一文本最大限制 16MB 的存在 And 物件管理上的設計

而衍伸出了 Lookup 這個 Operator

可以理解成他就是 Sql 的 Join

{
   $lookup:
      {
         from: <foreign collection>,
         localField: <field from local collection's documents>,
         foreignField: <field from foreign collection's documents>,
         let: { <var_1>: <expression>, …, <var_n>: <expression> },
         pipeline: [ <pipeline to run> ],
         as: <output array field>
      }
}

from: 關聯到哪個Collection
localField: 當前Collection的欄位名稱
foreignField: 關聯到Collection的欄位名稱
let: 相當於 Sql 的要甚麼欄位並且命名
pipeline: 可以在關聯到的資料庫多做一次Pipline Ex: $match、$gt(e)….
as: 查詢出來的新欄位名稱

select <let> as where [currentCollection] a , <from> b
a.localField = b.foreignField

實際例子

Orders

db.orders.insertMany( [
   { "_id" : 1, "item" : "almonds", "price" : 12, "quantity" : 2 },
   { "_id" : 2, "item" : "pecans", "price" : 20, "quantity" : 1 },
   { "_id" : 3  }
] )

inventory

db.inventory.insertMany( [
   { "_id" : 1, "sku" : "almonds", "description": "product 1", "instock" : 120 },
   { "_id" : 2, "sku" : "bread", "description": "product 2", "instock" : 80 },
   { "_id" : 3, "sku" : "cashews", "description": "product 3", "instock" : 60 },
   { "_id" : 4, "sku" : "pecans", "description": "product 4", "instock" : 70 },
   { "_id" : 5, "sku": null, "description": "Incomplete" },
   { "_id" : 6 }
] )

Aggregate

db.orders.aggregate( [
   {
     $lookup:
       {
         from: "inventory",
         localField: "item",
         foreignField: "sku",
         as: "inventory_docs"
       }
  }
] )

結果

{
   "_id" : 1,
   "item" : "almonds",
   "price" : 12,
   "quantity" : 2,
   "inventory_docs" : [
      { "_id" : 1, "sku" : "almonds", "description" : "product 1", "instock" : 120 }
   ]
}
{
   "_id" : 2,
   "item" : "pecans",
   "price" : 20,
   "quantity" : 1,
   "inventory_docs" : [
      { "_id" : 4, "sku" : "pecans", "description" : "product 4", "instock" : 70 }
   ]
}
{
   "_id" : 3,
   "inventory_docs" : [
      { "_id" : 5, "sku" : null, "description" : "Incomplete" },
      { "_id" : 6 }
   ]
}

如果要將 inventory_docs 轉成陣列單一物件屬性可以透過 $unwind