I am new to Mongodb, been working on an aggregation pipeline, lets say I have the following books collections :

{ "bname" : "BOOKA-2020", "PRICE" : 10 , "REF":"A" }
{ "bname" : "BOOKA-2020", "PRICE" : 20 , "REF":"A" }
{ "bname" : "BOOKB-2020", "PRICE" : 30 , "REF":"B" }
{ "bname" : "BOOKB-2020", "PRICE" : 40 , "REF":"B" }

{ "bname" : "BOOKA-2021", "PRICE" : 50 , "REF":"A" }
{ "bname" : "BOOKA-2021", "PRICE" : 60 , "REF":"A" }
{ "bname" : "BOOKB-2021", "PRICE" : 70 , "REF":"B" }
{ "bname" : "BOOKB-2021", "PRICE" : 80 , "REF":"B" }

I want the make two separate $match to output the sum of the books price based on the year so I ended up with someting like this in the pipeline :

{
  $match: {
    bname: /2020/ 
}, {
  $group: {
    _id: '$REF',
    PriceSum2020: {
      $sum: '$PRICE' ,
    },
  },
}, .........

(I dont have direct access to the year, plus I am not allowed to change the schema so I ended up using regex)

this works as expected! it show the book's name and the price sum of the year 2020, but I want to do the same for 2021 and have it show too in the result, the thing is when I do the second $match it overrides the previous $match only show 2021,

I Think it may be achieved by using $faucet or $bucket but I couldn't figure it out..

my expected output is :

{ "REF" : "A", "PriceSum2020" : 30, "PriceSum2021" : 110 }
{ "REF" : "B", "PriceSum2020" : 70, "PriceSum2021" : 150 }

Thank you in advance!

CodePudding user response：

Query

• add a year field
• group by REF year and sum the prices
• group by REF and push the total prices in each year
• the rest is to produce the exact output you need, i think you dont need them but i added them
• map to turn the prices array to k,v pairs and convert into {} with the field names price year
• and then move that to root (merge with root)

Playmongo

aggregate(
[{"$set": 
   {"year": 
     {"$toInt": {"$arrayElemAt": [{"$split": ["$bname", "-"]}, 1]}}}},
 {"$group": 
   {"_id": {"REF": "$REF", "year": "$year"}, "price": {"$sum": "$PRICE"}}},
 {"$group": 
   {"_id": "$_id.REF",
    "prices": {"$push": {"year": "$_id.year", "price": "$price"}}}},
 //Maybe you dont need the bellow 
 {"$project": 
   {"REF": "$_id",
    "_id": 0,
    "prices": 
     {"$arrayToObject": 
       [{"$map": 
           {"input": "$prices",
            "in": 
             {"k": {"$concat": ["price", {"$toString": "$$this.year"}]},
              "v": "$$this.price"}}}]}}},
 {"$replaceRoot": {"newRoot": {"$mergeObjects": ["$prices", "$$ROOT"]}}},
 {"$project": {"prices": 0}}])