here is the collection and query code. Now i want to do two things.

i) sort reportTypes array objects by counts in descending order then,

ii) sort the collection by total no. of counts in reportTypes array in descending order.

iii) then group by managerId

i want resultant doc to like this.

[
  {
    "_id": ObjectId("62441917d12596f96de163a3"),
    "managerId": 2,
    "reportTypes": [
      {
        "reasonId": 100,
        "count": 20
      }
    ]
  },
  {
    "_id": ObjectId("62441917d12596f96de163a5"),
    "managerId": 3,
    "reportTypes": [
      {
        "reasonId": 200,
        "count": 10
      },
      {
        "reasonId": 100,
        "count": 5
      },
      {
        "reasonId": 300,
        "count": 0
      }
    ]
  },
  {
    "_id": ObjectId("62441917d12596f96de163a2"),
    "managerId": 1,
    "reportTypes": [
      {
        "reasonId": 300,
        "count": 4
      },
      {
        "reasonId": 200,
        "count": 3
      },
      {
        "reasonId": 100,
        "count": 2
      }
    ]
  }
]

CodePudding user response：

Maybe something like this:

db.collection.aggregate([
{
  $unwind: "$reportTypes"
},
{
  $sort: {
   "managerId": 1,
   "reportTypes.count": -1
 }
},
{
 $group: {
  _id: "$managerId",
  reportTypes: {
    $push: "$reportTypes"
  },
  cnt: {
    $sum: "$reportTypes.count"
   }
 }
},
{
  $addFields: {
    managerId: "$_id"
 }
},
{
 $sort: {
   cnt: -1
}
},
{
  $project: {
    managerId: 1,
     reportTypes: 1
  }
 }
])

Explained:

1. Unwind the reportTypes
2. Sort by managerId and descending by reportTypes.count
3. group with push to form the same objects with sorted arrays per managerId and generate summary count per managerId.
4. addFileds managerId
5. Sort by total count ( cnt)
6. Project only the needed fields

playground