CODEWITHSUNDEEP
node.jsmongodbmongooseaggregation-framework
rickster
rickster

Reputation: 324

How to add the results of a group, to the grouped documents? MongoDB

I am creating some reports by passing this data: Reports Model

userId : String,
marketId : String,
marketName : String,
itemId : String,
minPricePerKg : Number,
maxPricePerKg : Number

creating 3 reports by POST request :

POST /reports 

request 1:
{
  "details": {
    "userId": "1",
    "marketId": "1",
    "marketName": "market1",
    "itemId": "1",
    "minPricePerKg": "10",
    "maxPricePerKg": "20",
  }
}

request 2:
{
  "details": {
    "userId": "2",
    "marketId": "1",
    "marketName": "market1",
    "itemId": "1",
    "minPricePerKg": "20",
    "maxPricePerKg": "40",
  }
}

request 3:
{
  "details": {
    "userId": "1",
    "marketId": "2",
    "marketName": "market2",
    "itemId": "1",
    "minPricePerKg": "80",
    "maxPricePerKg": "100",
  }
}

I want to get the average price of all reports of some particular itemId (which is received from query). So, for understanding we are filtering out all reports with itemId.. with $match : { itemId }

On Requesting GET /reports?itemId=1

Expected Output

[
  {
    "marketId": "1",
    "itemId": "1",
    "marketName": "market1",
    "users": ["1", "2"],
    "minPrice": 15,
    "maxPrice": 30
  },
  {
    "marketId": "2",
    "itemId": "1",
    "marketName": "market2",
    "users": ["1"],
    "minPrice": 80,
    "maxPrice": 100
  }
]

here minPrice is average of all minPricePerKg and maxPrice is average of all maxPricePerKg of there respective marketId's report. I want to get all the fields i.e(marketId, marketName, users, itemId) in result as well

output I am getting is:

[
  {
    "_id": {
      "marketId": "market1"
    },
    "minPrice": 15,
    "maxPrice": 30
  },
  {
    "_id": {
      "marketId": "market2"
    },
    "minPrice": 80,
    "maxPrice": 100
  }
]

my approach is something like this:

const res = await Report.aggregate([
  { $match: { itemId } },
  { $group: { _id: { marketId : "$marketId" }, minPrice: { $avg: '$minPricePerKg' }, maxPrice: { $avg: '$maxPricePerKg' } } },
  { $project: { "marketName": 1 } },
]);

Upvotes: 2

Views: 102

Answers (1)

Takis
Takis

Reputation: 8705

Query

  • group to keep those field that are common in the group
  • and accumulate those are not common users and MinPrice MaxPrice

Test code here

aggregate(
[{"$replaceRoot": {"newRoot": "$details"}},
  {"$group":  
    {"_id": 
      {"marketId": "$marketId",
        "itemId": "$itemId",
        "marketName": "$marketName"},
      "avgMinPrice": {"$avg": {"$toDouble": "$minPricePerKg"}},
      "avgMaxPrice": {"$avg": {"$toDouble": "$maxPricePerKg"}},
      "users": {"$push": "$userId"}}},
  {"$replaceRoot": {"newRoot": {"$mergeObjects": ["$_id", "$$ROOT"]}}},
  {"$project": {"_id": 0}}])

Upvotes: 1

Related Questions