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" }
]

📄 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 }
]

🔍 Question 1: Join users with their orders

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

✅ Result: Each user now has an orders array.

🔍 Question 2: Unwind orders array

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

✅ 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
    }
  }
]

🔍 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
    }
  }
]

🔍 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 }
]

✅ 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" }
    }
  }
]

🔍 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 }
]

🔍 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
    }
  }
]

🚀 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.