How to build aggregation pipeline to receive data in this certain way?

user_14355349 0
Viewed 31

I got a MongoDB collection looking like this:

const data = [
  { name: "eggplant", price: 3.42, quantity: 1},
  { name: "eggplant", price: 3.17, quantity: 3},
  { name: "potato", price: 2.12, quantity: 5},
  { name: "potato", price: 1.99, quantity: 10},
  { name: "eggplant", price: 3.33, quantity: 3},
  { name: "cucumber", price: 5.02, quantity: 4},
  { name: "lettuce", price: 3.42, quantity: 1.5},
  { name: "cucumber", price: 4.45, quantity: 4},
]

And I want to filter this data using aggregation framework on my server, to receive one document of a kind by name, and calculate other fields. Basically I need to calculate average price of each specific item, and total quantity. So I need somehow to summarize price of each specific item, divide it by the number of documents with that name and set it to new field, and also take the sum of quantity. So I want to have data like this in my output:

[
  { name: "eggplant", averagePrice: 3.30, quantitySum: 7},
  { name: "potato", averagePrice: 2.05, quantitySum: 15},
  { name: "cucumber", averagePrice: 4.73, quantitySum: 8},
  { name: "lettuce", averagePrice: 3.42, quantity: 1.5},
]

My question is that are there in aggregation framework any special operator which could help me with that in easy way and I could dive into? Is it even possible to do such calculation in aggregation framework?

aggregation-framework mongodb-query mongodb javascript
1 Answers

Demo - https://mongoplayground.net/p/GtvyGysBNM6

Use $group

Groups input documents by the specified _id expression and for each distinct grouping outputs a document. The _id field of each output document contains the unique group by value.

$avg

$sum

db.collection.aggregate([
    $group: {
      _id: "$name", // group by name
      averagePrice: { $avg: "$price" },
      quantitySum: { $sum: "$quantity" }
    }
  }
])

If you want to get name instead of _id add $project

Demo - https://mongoplayground.net/p/bSfdYgg9lUo

{
    $project: {
      _id: 0,
      name: "$_id",
      averagePrice: 1,
      quantitySum: 1,
      
    }
  }
user_22242650
Related