Database Manual / Introduction / Databases & Collections / Views

Use a View to Join Two Collections使用视图连接两个集合

You can use $lookup to create a view over two collections and then run queries against the view. Applications can query the view without having to construct or maintain complex pipelines.您可以使用$lookup在两个集合上创建一个视图，然后对该视图运行查询。应用程序可以查询视图，而无需构建或维护复杂的管道。

Example示例

Create two sample collections, inventory and orders:创建两个样本集合，inventory和orders：

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

db.orders.insertMany( [
   { 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 }
] )

Create a Joined View创建联接视图

This command uses db.createView() to create a new view named sales based on the orders collection:此命令使用db.createView()基于orders集合创建一个名为sales的新视图：

db.createView( "sales", "orders", [
   {
      $lookup:
         {
            from: "inventory",
            localField: "prodId",
            foreignField: "prodId",
            as: "inventoryDocs"
         }
   },
   {
      $project:
         {
           _id: 0,
           prodId: 1,
           orderId: 1,
           numPurchased: 1,
           price: "$inventoryDocs.price"
         }
   },
      { $unwind: "$price" }
] )

In the example:在示例中：

  • The $lookup stage uses the prodId field in the orders collection to "join" documents in the inventory collection that have matching prodId fields.$lookup阶段使用orders集合中的prodId字段来“连接”inventory集合中具有匹配的prodId字段的文档。
  • The matching documents are added as an array in the inventoryDocs field.匹配的文档将作为数组添加到investoryDocs字段中。
  • The $project stage selects a subset of the available fields.$project阶段选择可用字段的子集。

  • The $unwind stage converts the price field from an array to a scalar value.$unwind阶段将价格字段从数组转换为标量值。

The documents in the sales view are:sales视图中的文档包括：

{ orderId: 201, prodId: 100, numPurchased: 20, price: 20 },
{ orderId: 202, prodId: 101, numPurchased: 10, price: 10 },
{ orderId: 203, prodId: 102, numPurchased: 5, price: 15 },
{ orderId: 204, prodId: 103, numPurchased: 15, price: 17 },
{ orderId: 205, prodId: 103, numPurchased: 20, price: 17 },
{ orderId: 206, prodId: 102, numPurchased: 1, price: 15 },
{ orderId: 207, prodId: 101, numPurchased: 5, price: 10 },
{ orderId: 208, prodId: 100, numPurchased: 10, price: 20 },
{ orderId: 209, prodId: 103, numPurchased: 30, price: 17 }

Query the View查询视图

To find the total amount sold of each product, query the view:要查找每种产品的总销售额，请查询视图：

db.sales.aggregate( [
   {
      $group:
         {
            _id: "$prodId",
            amountSold: { $sum: { $multiply: [ "$price", "$numPurchased" ] } }
         }
   }
] )

The output is:输出为：

[
  { _id: 102, amountSold: 90 },
  { _id: 101, amountSold: 150 },
  { _id: 103, amountSold: 1105 },
  { _id: 100, amountSold: 600 }
]