Skip to content

#13 Complex Query with Aggregation Pipeline

An aggregation pipeline consists of one or more stages that process documents:

  • Each stage performs an operation on the input documents. For example, a stage can filter documents, group documents, and calculate values.
  • The documents that are output from a stage are passed to the next stage.
  • An aggregation pipeline can return results for groups of documents. For example, return the total, average, maximum, and minimum values.

Create a new collection called fruits

db.fruits.insertMany( [
   { _id: 0, name: "mango", size: "small", price: 2.59,
     quantity: 100 },
   { _id: 1, name: "mango", size: "big", price: 3.19,
     quantity: 50 },
   { _id: 2, name: "banana", color: "yellow", price: 1.99,
     quantity: 100 },
   { _id: 3, name: "banana", color: "green", price: 1.79,
     quantity: 100 },
   { _id: 4, name: "ananas", size: "small", price: 2.49,
     quantity: 80 },
   { _id: 5, name: "ananas", size: "big", price: 3.59,
     quantity: 50 },
   { _id: 6, name: "grape", color: "green", price: 1.99,
     quantity: 30 },
   { _id: 7, name: "grape", color: "purple", price: 2.19,
     quantity: 30 }
])

Example 1

I will build a simple pipeline containing two stages.

  • stage 1: Filter fruit documents by its size
  • stage 2: Group remaining documents by fruit name and calculate total quantity
db.fruits.aggregate([
    { "$match": { name: { $in: ["banana", "grape"] } } },
    { $group: { _id: "$name", totalQuantity: { $sum: "$quantity" } } }
]);

# the result
{
  _id: 'banana',
  totalQuantity: 200        # 100 + 100
}
{
  _id: 'grape',
  totalQuantity: 60         # 30 + 30
}

Example 2

I will build a simple pipeline containing three stages.

  • stage 1: Filter fruit documents by its size
  • stage 2: Group remaining documents by fruit name and calculate the total value
  • stage 3: Sort the result by the total value in descending order.
db.fruits.aggregate([
    { $match: { size: "small" } },
    { $group: { _id: "$name", totalValue: { $sum: { $multiply: ["$price", "$quantity"] } } } },
    { $sort: { totalValue: -1 } }
])

# the result
{
  _id: 'mango',
  totalValue: 259            #2.59*100
}
{
  _id: 'ananas',
  totalValue: 199.20         #2.49*80
}

Tags: