DEV Community

Arjun Kumar
Arjun Kumar

Posted on

🧠 Mastering MongoDB Aggregation

In this post, we dive deep into MongoDB’s aggregation framework using a practical dataset of users and orders. Below are 8 real-world questions and how you can solve them using stages like $lookup, $unwind, $group, and $project.

📦 Sample Data
📄 Users Collection

[ { "_id": 1, "name": "Alice", "country": "USA" }, { "_id": 2, "name": "Bob", "country": "India" }, { "_id": 3, "name": "Charlie", "country": "India" }, { "_id": 4, "name": "Diana", "country": "USA" }, { "_id": 5, "name": "Eve", "country": "UK" } ]
Enter fullscreen mode Exit fullscreen mode

📄 Orders Collection

[ { "_id": 1, "userId": 1, "amount": 200 }, { "_id": 2, "userId": 2, "amount": 350 }, { "_id": 3, "userId": 1, "amount": 150 }, { "_id": 4, "userId": 3, "amount": 400 }, { "_id": 5, "userId": 3, "amount": 100 }, { "_id": 6, "userId": 4, "amount": 300 }, { "_id": 7, "userId": 5, "amount": 50 } ]
Enter fullscreen mode Exit fullscreen mode

🔍 Question 1: Join users with their orders

[ { $lookup: { from: "orders", localField: "_id", foreignField: "userId", as: "orders" } } ]
Enter fullscreen mode Exit fullscreen mode

✅ Result: Each user now has an orders array.

🔍 Question 2: Unwind orders array

[ { $lookup: { from: "orders", localField: "_id", foreignField: "userId", as: "orders" } }, { $unwind: "$orders" } ]
Enter fullscreen mode Exit fullscreen mode

✅ Result: Each user-order combination is flattened into individual documents.

🔍 Question 3: Total amount spent by each user

[ { $lookup: { from: "orders", localField: "_id", foreignField: "userId", as: "orders" } }, { $unwind: "$orders" }, { $group: { _id: "$_id", totalSpent: { $sum: "$orders.amount" }, name: { $first: "$name" } } }, { $project: { _id: 0, name: 1, totalSpent: 1 } } ]
Enter fullscreen mode Exit fullscreen mode

🔍 Question 4: Average order amount per country

[ { $lookup: { from: "orders", localField: "_id", foreignField: "userId", as: "orders" } }, { $unwind: "$orders" }, { $group: { _id: "$country", avgSpent: { $avg: "$orders.amount" } } }, { $project: { country: "$_id", avgSpent: 1, _id: 0 } } ]
Enter fullscreen mode Exit fullscreen mode

🔍 Question 5: Most expensive user (by total spent)

[ { $lookup: { from: "orders", localField: "_id", foreignField: "userId", as: "orders" } }, { $unwind: "$orders" }, { $group: { _id: "$name", totalSpent: { $sum: "$orders.amount" } } }, { $sort: { totalSpent: -1 } }, { $limit: 1 } ]
Enter fullscreen mode Exit fullscreen mode

✅ Result: The user who spent the most.

🔍 Question 6: Count how many orders each user made

[ { $lookup: { from: "orders", localField: "_id", foreignField: "userId", as: "orders" } }, { $project: { name: 1, orderCount: { $size: "$orders" } } } ]
Enter fullscreen mode Exit fullscreen mode

🔍 Question 7: User with highest average order value

[ { $lookup: { from: "orders", localField: "_id", foreignField: "userId", as: "orders" } }, { $project: { name: 1, avgOrderValue: { $avg: "$orders.amount" } } }, { $sort: { avgOrderValue: -1 } }, { $limit: 1 } ]
Enter fullscreen mode Exit fullscreen mode

🔍 Question 8: Total revenue per country

[ { $lookup: { from: "orders", localField: "_id", foreignField: "userId", as: "orders" } }, { $unwind: "$orders" }, { $group: { _id: "$country", revenue: { $sum: "$orders.amount" } } }, { $project: { country: "$_id", revenue: 1, _id: 0 } } ]
Enter fullscreen mode Exit fullscreen mode

🚀 Conclusion

These examples reflect real scenarios you might encounter while analyzing user and order data. MongoDB's aggregation pipeline is extremely flexible for joining, transforming, and summarizing data — no need to switch to SQL for analytics anymore.

Top comments (0)