In MongoDB, you can use the aggregation pipeline to calculate totals from deeply nested sub-documents while filtering the main documents. Here's how you can achieve this:
Match Stage: Use the $match stage to filter the main documents based on your criteria.
Unwind Stage: Use $unwind to deconstruct arrays within your documents. This is useful if your nested sub-documents are within arrays.
Project Stage: Use $project to shape your documents, focusing on the fields necessary for your calculations, including the nested sub-document fields.
Group Stage: Use $group to aggregate the necessary fields and calculate totals.
Here's a basic example. Suppose you have a orders collection where each order document contains an array of items, and you want to calculate the total quantity ordered for each productId:
[
{
"_id": 1,
"customer": "John Doe",
"items": [
{ "productId": "A", "quantity": 2 },
{ "productId": "B", "quantity": 1 }
]
},
{
"_id": 2,
"customer": "Jane Doe",
"items": [
{ "productId": "A", "quantity": 1 },
{ "productId": "C", "quantity": 5 }
]
}
]
To calculate the total quantity for each productId, follow the steps:
db.orders.aggregate([
{
// Filter main documents as needed
$match: { "customer": { $exists: true } }
},
{
// Deconstruct items array
$unwind: "$items"
},
{
// Prepare necessary fields
$project: {
productId: "$items.productId",
quantity: "$items.quantity"
}
},
{
// Group by productId to calculate total quantities
$group: {
_id: "$productId",
totalQuantity: { $sum: "$quantity" }
}
}
])
This pipeline does the following:
Filters documents based on whether the customer field exists ($match).
Unwinds the items array to work with each item independently.
Projects the fields necessary for calculation, extracting productId and quantity.
Groups the documents by productId while summing the quantity field to calculate the total quantity for each product.
