Aggregate results based on different fields in MongoDB

db.collection.aggregate([
  {
    $facet: {
      budgets: [
        {
          "$group": {
            "_id": {
              "owner": "$owner",
              "budget": "$budget"
            },
            budgetCount: {$sum: 1}
          }
        },
        {
          "$group": {
            "_id": {"owner": "$_id.owner"},
            budgets: {
              "$addToSet": {
                budget: "$_id.budget",
                count: "$budgetCount"
              }
            },
            totalBudgets: {$sum: "$budgetCount"},
          }
        },
        {
          $project: {
            owner: "$_id.owner",
            _id: 0,
            budgets: 1,
            totalBudgets: 1,
            locations: null,
            projects: null
          }
        }
      ],
      locations: [
        {
          "$group": {
            "_id": {
              "owner": "$owner",
              "location": "$location"
            },
            locationsCount: {$sum: 1}
          }
        },
        {
          "$group": {
            "_id": {"owner": "$_id.owner"},
            locations: {
              "$addToSet": {
                location: "$_id.location",
                count: "$budgetCount"
              }
            },
            totalLocations: {$sum: "$locationsCount"},
          }
        },
        {
          $project: {
            owner: "$_id.owner",
            _id: 0,
            locations: 1,
            totalLocations: 1,
            budgets: null,
            projects: null
          }
        }
      ],
      projects: [
        {
          "$group": {
            "_id": {
              "owner": "$owner",
              "project": "$project"
            },
            projectsCount: {$sum: 1}
          }
        },
        {
          "$group": {
            "_id": {"owner": "$_id.owner"},
            projects: {
              "$addToSet": {
                project: "$_id.project",
                count: "$projectsCount"
              }
            },
            totalProjects: {$sum: "$projectsCount"},
          }
        },
        {
          $project: {
            owner: "$_id.owner",
            _id: 0,
            projects: 1,
            totalProjects: 1,
            budgets: null,
            locations: null
          }
        }
      ],
    }
  },
  {
    $project: {
      data: {
        "$concatArrays": [
          "$budgets",
          "$locations",
          "$projects"
        ]
      }
    }
  },
  {
    $unwind: "$data"
  },
  {
    $group: {
      _id: "$data.owner",
      locations: {
        $push: {
          $ifNull: ["$data.locations", "$$REMOVE"]
        }
      },
      budgets: {
        $push: {
          $ifNull: ["$data.budgets", "$$REMOVE"]
        }
      },
      projects: {
        $push: {
          $ifNull: ["$data.projects", "$$REMOVE"]
        }
      },
      totalBudgets: {$sum: "$data.totalBudgets"},
      totalLocations: {$sum: "$data.totalLocations"},
      totalProjects: {$sum: "$data.totalProjects"},   
    }
  },
  {
    "$project": {
      "_id": 0,
      "owner": "$_id",
      "locations": {"$arrayElemAt": ["$locations", 0]},
      "projects": {"$arrayElemAt": ["$projects", 0]},
      "budget": {"$arrayElemAt": ["$budgets", 0]},
      totalBudgets: 1,
      totalLocations: 1,
      totalProjects: 1
    }
  }
])
Topics: MongoDB

Related Code Examples