Join and aggregate

[Genti_P]

Hi, I am new to mongodb. Till now I have worked with SQL queries. 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:234lkjlk2342432
} 


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: "234lkjlk2342432"        
}

the result to return would bebelow where 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,
	
	
}

[Aasawari (Aasawari Sahasrabuddhe)]

Hi @Genti_P and welcome to the MongoDB community forum!!

Based on the sample example and the expected response , the following aggregation query would be helpful.

[
  {
    '$lookup': {
      'from': 'a', 
      'localField': '_id', 
      'foreignField': '_id', 
      'as': 'c'
    }
  }, {
    '$addFields': {
      'academic_year': {
        '$arrayElemAt': [
          '$c', 0
        ]
      }
    }
  }, {
    '$addFields': {
      'academic_year': '$academic_year.academic_year'
    }
  }, {
    '$unset': [
      'c', 'b4'
    ]
  }, {
    '$addFields': {
      'bigtotal': {
        '$map': {
          'input': '$b3', 
          'as': 'obj', 
          'in': {
            'total': {
              '$add': [
                '$$obj.b3_1', '$$obj.b3_2', '$$obj.b3_3'
              ]
            }
          }
        }
      }
    }
  }, {
    '$addFields': {
      'b3': {
        '$zip': {
          'inputs': [
            '$b3', '$bigtotal'
          ]
        }
      }
    }
  }, {
    '$addFields': {
      'b3': {
        '$map': {
          'input': '$b3', 
          'in': {
            '$mergeObjects': '$$this'
          }
        }
      }
    }
  }, {
    '$addFields': {
      'bigtotal': {
        '$sum': [
          '$b1', '$b2', {
            '$sum': '$bigtotal.total'
          }
        ]
      }
    }
  }
]

The output for the above aggregation would look like:

[
  {
    _id: ObjectId("636cbcf1e1c238226bb5a421"),
    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 }
    ],
    academic_year: '2011-2022',
    bigtotal: 31
  }
]

However, please note the following recommendations:

1. Depending on your use case, the aggregation works but may be difficult to maintain in the long term.
2. The first four stages can be avoided if the field academic_year is stored in the document in collection b , thus shortening the aggregation pipeline by 50%
3. Alternatively you may consider to do this processing in the application code since I think the aggregation output just adds extra information in the documents in collection b

Also, note that, the above aggregation is dependent on the sample example provided and was not optimised for performance by any means, I would recommend you to throughly test before applying it to the application for entire documents in collection.

Let us know if you have any further queries.

Best Regards
Aasawari