I have an aggregation pipeline, with this group as a stage.

I've put arrows at lines of interest

stage_group_month = {
        "$group" : { 
            "_id": { 
                "year": "$_id.year", 
                "month": "$_id.month"
            },
--->        "total_project_cost" : { "$sum": "$project_cost" },
            "total_hours": { "$sum": "$project_durationNumber"},
            "total_salaries": {"$sum": "$salaries"},
            "projects": { 
                "$push":  {
                    "_id": "$_id.projectId",
                    "name": "$projectName",
--->                "cost": "$project_cost",
                    "duration": "$project_durationNumber",
                    "salaries" : "$salaries",
                    "gross_profit": {"$subtract": [ "$project_cost", "$salaries" ]}
                }
            }
        }
    }

and I just want to do

stage_add_percentage_revenue = {
        "$addFields":
        {
            "projects.percentage_revenue" : {"$divide": ["$projects.cost", "$total_project_cost" ]}
        }
    }

If I replace $projects.cost with some number like 100 I have a result. But I can't access project.cost.

Here's an example sample before trying to do the division

{
    "total_project_cost": 13200.0,
    "total_hours": 120.0,
    "total_salaries": 0,
    "projects": [
        {
            "_id": "id1",
            "name": "Project1",
            "cost": 5610.0,
            "duration": 51.0,
            "salaries": 0,
            "gross_profit": 5610.0
        },
        {
            "_id": "id2",
            "name": "Project2",
            "cost": 7590.0,
            "duration": 69.0,
            "salaries": 0,
            "gross_profit": 7590.0
        }
    ],
    "total_gross_profit": 13200.0,
    "date": {
        "year": 2022,
        "month": 8
    }
}

How can I do that?

CodePudding user response：

As projects is an array, you need to use $map to iterate each array element in $project stage.

1. $map - Iterates the element in the array and returns a new array.

   1.1. $mergeObjects - Merge current iterate element ($$this) with the document in 1.1.1.

   1.1.1. A document with percentage_revenue field which performs the divison.

{
  $project: {
    projects: {
      $map: {
        input: "$projects",
        in: {
          $mergeObjects: [
            "$$this",
            {
              percentage_revenue: {
                "$divide": [
                  "$$this.cost",
                  "$total_project_cost"
                ]
              }
            }
          ]
        }
      }
    }
  }
}

Demo @ Mongo Playground