Docs Menu

Docs HomeDevelop ApplicationsMongoDB Manual

Use a View to Join Two Collections

On this page

  • Example
  • Create a Joined View
  • Query the View

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.

Create two sample collections, inventory and 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 }
] )

This command uses db.createView() to create a new view named sales based on the orders collection:

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.

  • The matching documents are added as an array in the inventoryDocs field.

  • The $project stage selects a subset of the available fields.

  • The $unwind stage converts the price field from an array to a scalar value.

The documents in the sales view are:

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

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 }
]
←  Create and Query a ViewCreate a View with Default Collation →