complex aggregate in mongodb-CodePudding

I have the following 2 schemas, that should be joined and the following calculations to be done.

a { 
   academic_year:  { type:String} 
} 
b { 
       b1: {type:Number, default:0  },
      b2: {type:Number, default:0  },
      b3: [{
        b3_1: {type:Number, default:0  },
        b3_2: {type:Number, default:0  }, 
        b3_3: {type:Number, default:0 }
          }]
       b4: {type:mongoose.Schema.ObjectId,  ref: 'a'}
                    
}

Let's suppose we have below example

a { 
   academic_year: "2021-2022"
   _id:6345659fd06188306de0deae
} 


b { 
       b1:1,
      b2: 2,
      b3: [
      {  b3_1: 5,  b3_2: 4,  b3_3: 4, },
      {  b3_1: 1,  b3_2: 4,  b3_3: 2 }
      {  b3_1: 5,  b3_2: 1,  b3_3: 2 }
              ]
       b4: "6345659fd06188306de0deae"        
}

the result to return would below where field total has been added for every object inside array and bigtotal= b1 b2 sum of total field

c { 
 academic_year: "2021-2022",
 b1:1,
 b2: 2,
 b3: [
      {  b3_1: 5,  b3_2: 4,  b3_3: 4,total:13  },
      {  b3_1: 1,  b3_2: 4,  b3_3: 2 ,total:7},
      {  b3_1: 5,  b3_2: 1,  b3_3: 2,total:8 }
      ],
              
    BigTotal:31,
    
    
}

I first tried with unwind to add a total field for each array object but I don't know how to go back before $unwind. I think it is a complex aggregation

CodePudding user response：

Use $reduce to iterate through the array and sum the 3 fields. Perform another $sum with b1 and b2 to get the final BigTotal.

db.b.aggregate([
  {
    "$addFields": {
      "b3": {
        "$map": {
          "input": "$b3",
          "as": "b",
          "in": {
            "$mergeObjects": [
              "$$b",
              {
                total: {
                  $sum: [
                    "$$b.b3_1",
                    "$$b.b3_2",
                    "$$b.b3_3"
                  ]
                }
              }
            ]
          }
        }
      }
    }
  },
  {
    $addFields: {
      BigTotal: {
        $sum: [
          "$b1",
          "$b2",
          {
            "$reduce": {
              "input": "$b3",
              "initialValue": 0,
              "in": {
                $sum: [
                  "$$value",
                  "$$this.total"
                ]
              }
            }
          }
        ]
      }
    }
  },
  {
    "$lookup": {
      "from": "a",
      "localField": "b4",
      "foreignField": "_id",
      "as": "aLookup"
    }
  },
  {
    "$unwind": "$aLookup"
  },
  // data wrangling/cosmetics
  {
    "$replaceRoot": {
      "newRoot": {
        "$mergeObjects": [
          "$$ROOT",
          "$aLookup"
        ]
      }
    }
  },
  {
    "$unset": [
      "aLookup",
      "b4"
    ]
  }
])

Mongo Playground