MongoDB Aggregation - Divide array element by non-array element

Question

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?

Can we have some sample documents and the expected output pls? — ray
– ray, Commented Aug 23, 2022 at 23:49
@ray I should have provided a sample right from the start sorry. Thank you for noticing. Expected output is percentage_revenue in each element of projects array being cost / total_project_cost — Timothee W
– Timothee W, Commented Aug 24, 2022 at 4:13

Yong Shun · Accepted Answer · 2022-08-23 23:51:22Z

2

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

$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

answered Aug 23, 2022 at 23:51

Yong Shun

54.6k6 gold badges38 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MongoDB Aggregation - Divide array element by non-array element

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related