Use a View to Join Two Collections
On this page
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.
Example
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 } ] )
Create a Joined View
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 theprodId
field in theorders
collection to "join" documents in theinventory
collection that have matchingprodId
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 theprice
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 }
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 } ]