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}]}