0

I'm working on MongoDB and I have a document like below structure. I am trying to calculate total services cost and each services lane cost. Here services are sub document.

My document structure:

{
 user:'John Papa',active:true,
 proposals:[
 {
  company:'Radical LLC',
  owner:ObjectId(564546...),active:true,
  services:[
     {model:'oneTime',price:3.5, numOfEmp:12, duration:6, dates:''},
     {model:'perMonthly',price:4.5, numOfEmp:8, duration:5, dates:''},
     {model:'perYearly',price:5.5, numOfEmp:6, duration:3, dates:''}
        ]
 },
{
  company:'Terminal & Co',
  owner:ObjectId(564546...),active:true,
  services:[
      {model:'oneTime',price:6.2, numOfEmp:6, duration:4, dates:''},
      {model:'perMonthly',price:6.5, numOfEmp:2, duration:4, dates:''},
      {model:'perYearly',price:5.2, numOfEmp:3, duration:7, dates:''}
        ]
 }
]
}

Expecting output after aggregation like below structure:

{
 user:'John Papa'
 proposals:[
      {
        company:'Radical LLC', total_service_cost:321
        owner:ObjectId(564546...),active:true,
        services:[
        {service_cost:42, model:'oneTime',price:3.5, numOfEmp:12, duration:6, dates:''}, // service_cost=(price X numOfEmp)
        {service_cost:180, model:'perMonthly',price:4.5, numOfEmp:8, duration:5, dates:''}, // service_cost=(price X numOfEmp X duration)
        {service_cost:99, model:'perYearly',price:5.5, numOfEmp:6, duration:3, dates:''} // service_cost=(price X numOfEmp X duration)
        ]
      },{
        company:'Terminal & Co', total_service_cost:198.4
        owner:ObjectId(564546...),active:true.
        services:[
        {service_cost:37.2, model:'oneTime',price:6.2, numOfEmp:6, duration:4, dates:''},
        {service_cost:52, model:'perMonthly',price:6.5, numOfEmp:2, duration:4, dates:''},
        {service_cost:109.2, model:'perYearly',price:5.2, numOfEmp:3, duration:7, dates:''}
        ]
      }
      ]
    }

I tried with the below code, but output is not showing as expected.

db.users.aggregate([
{ $match: { active: true} },
 {
    $lookup: {
   from: "proposals", let: { ownerId: '$_id', active: true }, 
   pipeline: [
      { $match: { $expr: { $and: [{ $eq: ['$active', '$$active'] }, { $eq: ['$owner', '$$ownerId'] }] } } },
             { $unwind: '$services' },                            
                    {
                        $addFields: {
                            'services.value': {
                                $switch: {
                                    branches: [
                                        { case: { $eq: ['$services.model', 'perFte'] }, then: { $multiply: ['$services.numOfEmp', '$services.price'] } },
                                        { case: { $eq: ['$services.model', 'oneTime'] }, then: { $multiply: ['$services.numOfEmp', '$services.price'] } },
                                        { case: { $eq: ['$services.model', 'perMonth'] }, then: { $multiply: ['$services.numOfEmp', '$services.price', '$services.duration'] } },
                                        { case: { $eq: ['$services.model', 'perYear'] }, then: { $multiply: ['$services.numOfEmp', '$services.price', '$services.duration'] } }
                                    ], default: 0
                                }
                            }
                        }
                    },                                                      
                ], as: "proposals"
            }
        }
    ])

Any Idea? How can i achieve the above?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.