Search⌘ K
AI Features
Home
Courses
MongoDB Interview Preparation

Solution: Grouping Across Collections

The aggregation pipeline for the orders collection begins with filtering documents using $match to include only orders from January 1, 2025, onward. It then uses $unwind to separate items into individual documents, followed by $lookup to join product details from the products collection. The $project stage shapes the output, selecting relevant fields and calculating revenue. Finally, $group aggregates data by productId to sum quantities and revenue, and $sort orders the results by revenue in descending order. Key concepts include filtering, unwinding arrays, joining collections, projecting fields, aggregating data, and sorting results.

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