sort array of object, then sort array of collection

Question

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

can you add example document about how you expect the final document to look like? — R2D2
– R2D2, Commented Mar 31, 2022 at 19:24
order of collection is different and reportTypes object's order is also different. Collection is sorted in descending order of total no. of counts & reportTypes objects are also sorted in descending order of count. — Shubham Maheshwari
– Shubham Maheshwari, Commented Apr 1, 2022 at 7:31

R2D2 · Accepted Answer · 2022-04-01 08:13:31Z

1

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:

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

playground

edited Apr 1, 2022 at 8:13

answered Apr 1, 2022 at 7:30

R2D2

10.9k2 gold badges20 silver badges41 bronze badges

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

6 Comments

Shubham Maheshwari Over a year ago

thanks man for so much effort i can't ask more. But it is not what i am looking. I am really grateful to you.

R2D2 Over a year ago

Let me know what different is expected?

Shubham Maheshwari Over a year ago

yeah, i have added in question's comment section

R2D2 Over a year ago

check the pg it is same resulting doc as expected: mongoplayground.net/p/LmzUHTDcL8m

R2D2 Over a year ago

add to the question

|

Collectives™ on Stack Overflow

sort array of object, then sort array of collection

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related