CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
Boat
Boat

Reputation: 535

Grouping by array elements and find sum of common array fields

The document has an array field and each of them has a unique Id field. After doing unwind on the array, I need to add a count field on each array document instead of Id. The count should be the sum of similar array fields (grouped by fields other than Id).

Sample document - https://mongoplayground.net/p/LUPqVw07unP

Expected result:

{
    "originId": 123,
    "taskMetric": {
        "count": 2,
        "status": "OPEN",
        "assignee": "ABC"
    }
}, {
    "originId": 1,
    "taskMetric": {
        "count": 1,
        "status": "COMPLETED",
        "assignee": "CDE"
    }
}, {
    "originId": 1,
    "taskMetric": {
        "count": 1,
        "status": "COMPLETED",
        "assignee": "EFG"
    }
}

Upvotes: 0

Views: 37

Answers (1)

Gibbs
Gibbs

Reputation: 22964

Play

You have to add one more projection stage to avoid _id and add id to group stage as other fields to the following query.

db.collection.aggregate([
  {
    $unwind: "$tasks"
  },
  {
    $group: {
      "_id": {
        "status": "$tasks.status",
        "assignee": "$tasks.assignee"
      },
      "count": {
        $sum: 1
      },
      "status": {
        $first: "$tasks.status"
      },
      "assignee": {
        $first: "$tasks.assignee"
      }
    }
  }
])

Upvotes: 1

Related Questions