Node.js - MongoDB 連線

MongoDB 是一個 NoSQL 資料庫，它不支援關係型資料庫（如 MySQL）中的 JOIN 操作。但是，可以透過呼叫 Collection 物件的 aggregate() 方法和 $lookup 階段來實現類似的功能。

$aggregate() 函式

此函式對同一資料庫中的另一個集合執行左外連線，以篩選“連線”集合中的文件以進行處理。

$lookup: 對同一資料庫中的集合執行左外連線，以篩選“連線”集合中的文件以進行處理。$lookup 階段會向每個輸入文件新增一個新的陣列欄位。新陣列欄位包含來自“連線”集合的匹配文件。

要對輸入文件中的欄位與“連線”集合的文件中的欄位之間執行相等匹配，$lookup 階段具有以下語法：

{
   $lookup:
      {
         from: <collection to join>,
         localField: <field from the input documents>,
         foreignField: <field from the documents of the "from" collection>,
         as: <output array field>
      }
}

$lookup 階段中的引數如下：

以下 SQL 查詢對應於 $lookup 操作：

SELECT *, <output array field>
FROM collection
WHERE <output array field> IN (
   SELECT *
   FROM <collection to join>
   WHERE <foreignField> = <collection.localField>
);

示例

為了演示 MongoDB 中的 JOIN 操作，請建立兩個集合：inventory 和 orders。

inventory 集合

( [
   { prodId: 100, price: 20, quantity: 125 },
   { prodId: 101, price: 10, quantity: 234 },
   { prodId: 102, price: 15, quantity: 432 },
   { prodId: 103, price: 17, quantity: 320 }
] )

orders 集合

( [
   { orderId: 201, custid: 301, prodId: 100, numPurchased: 20 },
   { orderId: 202, custid: 302, prodId: 101, numPurchased: 10 },
   { orderId: 203, custid: 303, prodId: 102, numPurchased: 5 },
   { orderId: 204, custid: 303, prodId: 103, numPurchased: 15 },
   { orderId: 205, custid: 303, prodId: 103, numPurchased: 20 },
   { orderId: 206, custid: 302, prodId: 102, numPurchased: 1 },
   { orderId: 207, custid: 302, prodId: 101, numPurchased: 5 },
   { orderId: 208, custid: 301, prodId: 100, numPurchased: 10 },
   { orderId: 209, custid: 303, prodId: 103, numPurchased: 30 }
] )

以下程式碼在 Collection 物件和 $lookup 階段上呼叫 aggregate() 方法。

const {MongoClient} = require('mongodb');

async function main(){

   const uri = "mongodb://:27017/";
   const client = new MongoClient(uri);

   try {
      await client.connect();
      await joindocs(client, "mydb", "orders", "inventory");
   } finally {
      await client.close();
   }
}

main().catch(console.error);

async function joindocs(client, dbname, col1, col2){
   const result = await client.db(dbname).collection('orders').aggregate([
   { $lookup:
      {
         from: 'inventory',
         localField: 'prodId',
         foreignField: 'prodId',
         as: 'orderdetails'
      }
   }
   ]).toArray();
   result.forEach(element => {
      console.log(JSON.stringify(element));
   });
}

$lookup 階段允許您指定要與當前集合連線的集合以及應匹配的欄位。

輸出

{"_id":"658c4b14943e7a1349678bf3","orderId":201,"custid":301,"prodId":100,"numPurchased":20,"orderdetails":[{"_id":"658c4aff943e7a1349678bef","prodId":100,"price":20,"quantity":125}]}
{"_id":"658c4b14943e7a1349678bf4","orderId":202,"custid":302,"prodId":101,"numPurchased":10,"orderdetails":[{"_id":"658c4aff943e7a1349678bf0","prodId":101,"price":10,"quantity":234}]}
{"_id":"658c4b14943e7a1349678bf5","orderId":203,"custid":303,"prodId":102,"numPurchased":5,"orderdetails":[{"_id":"658c4aff943e7a1349678bf1","prodId":102,"price":15,"quantity":432}]}
{"_id":"658c4b14943e7a1349678bf6","orderId":204,"custid":303,"prodId":103,"numPurchased":15,"orderdetails":[{"_id":"658c4aff943e7a1349678bf2","prodId":103,"price":17,"quantity":320}]}
{"_id":"658c4b14943e7a1349678bf7","orderId":205,"custid":303,"prodId":103,"numPurchased":20,"orderdetails":[{"_id":"658c4aff943e7a1349678bf2","prodId":103,"price":17,"quantity":320}]}
{"_id":"658c4b14943e7a1349678bf8","orderId":206,"custid":302,"prodId":102,"numPurchased":1,"orderdetails":[{"_id":"658c4aff943e7a1349678bf1","prodId":102,"price":15,"quantity":432}]}
{"_id":"658c4b14943e7a1349678bf9","orderId":207,"custid":302,"prodId":101,"numPurchased":5,"orderdetails":[{"_id":"658c4aff943e7a1349678bf0","prodId":101,"price":10,"quantity":234}]}
{"_id":"658c4b14943e7a1349678bfa","orderId":208,"custid":301,"prodId":100,"numPurchased":10,"orderdetails":[{"_id":"658c4aff943e7a1349678bef","prodId":100,"price":20,"quantity":125}]}
{"_id":"658c4b14943e7a1349678bfb","orderId":209,"custid":303,"prodId":103,"numPurchased":30,"orderdetails":[{"_id":"658c4aff943e7a1349678bf2","prodId":103,"price":17,"quantity":320}]}