Grouping by array elements and find sum of common array fields

Question

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"
    }
}

Gibbs · Accepted Answer · 2020-08-26 02:54:29Z

1

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"
      }
    }
  }
])

answered Aug 26, 2020 at 2:54

Gibbs

23.1k14 gold badges84 silver badges147 bronze badges

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

1 Comment

Boat Over a year ago

mongoplayground.net/p/YX8AFpFwOst - this gave me the expected. Thanks @Gibbs

Collectives™ on Stack Overflow

Grouping by array elements and find sum of common array fields

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related