Search⌘ K
AI Features
Home
Courses
MongoDB Interview Preparation

Solution: Grouping Across Collections

We'll cover the following...
Query
db.orders.aggregate([
  // (Optional) limit to 2025
  {
    $match: {
      orderDate: { $gte: ISODate("2025-01-01T00:00:00Z") }
    }
  },
  // One row per product name in the order
  { 
    $unwind: "$items" 
  },
  // Join to products by name
  {
    $lookup: {
      from: "products",
      localField: "items",      // product name in orders
      foreignField: "name",     // product name in products
      as: "product"
    }
  },
  { 
    $unwind: "$product" 
  },
  // Assume quantity = 1 per occurrence; take price from products
  {
    $project: {
      productName: "$product.name",
      productId: "$product._id",
      price: "$product.price",
      quantity: { $literal: 1 },
      revenue: { $multiply: [ "$product.price", 1 ] }
    }
  },
  // Summaries per product
  {
    $group: {
      _id: "$productId",
      totalQuantity: { $sum: "$quantity" },
      totalRevenue: { $sum: "$revenue" }
    }
  },
  { 
    $sort: { totalRevenue: -1 } 
  }
])

The explanation of the query is given below:

  • Line 1: This begins an aggregation pipeline on the orders collection.

  • Lines 4–7: $match filters documents before processing further. This keeps only orders placed on or after January 1, 2025. Whereas, $gte means “greater than or equal to.”

  • Lines 10–12: $unwind takes an array field (items) and creates a separate document for each element. If one order has three items, this stage creates three separate documents, one per item.

  • Lines 16–21: $lookup performs a left join between collections. It looks in the products collection to find documents where:

    • orders.items (product name in the order) matches

    • products.name (product name in the ...