mongodb get the document/subdocument having highest value

Question

I have a comment collection like this

{
  _id: 'c1',
  text: 'comment 1',
  votes: 1,
  replies: [
    {
      _id: 'r1',
      text: 'reply 1',
      isReply: true,
      votes: 3
    },
    {
      _id: 'r2',
      text: 'reply 2',
      isReply: true,
      votes: 0
    }
  ]
},
{
  _id: 'c2',
  text: 'comment2',
  votes: 2,
  replies: []
}

The idea is a comment can have many replies. All comments and replies have id, text, votes. How can I get the best 2 comment or reply having the most votes. In the given case, this will be 'reply 1' and 'comment 2'.

I can send 1 request to mongodb to have the best 2 comments and another one to have the best 2 replies, then compare them to get what I want.

But, can it be possible with only 1 request to mongodb, I can have the result like this?

 [
   {
     _id: 'r1',
     text: 'reply 1',
     isReply: true,
     votes: 3
   },
   {
     _id: 'c2',
     text: 'comment 2',
     votes: 2
   }
 ]

Or better can I flatten the comment/replies to have a list of comment or reply sorted by votes? In this case it will be [r1, c2, c1, r2] with respectively their properties

Thanks.

---

Update: I tried aggregate({$unwind: '$replies'}) but I still have comments and replies on 2 different levels that I cannot compare by using the aggregation framework. Maybe be there is a way to flatten these 2 levels, I am very new to mongodb.

Answer 1

A little change of the schema to denormalize the votes would make it easier to sort. If there is another array of votes embedded in the comment document, like:

votes: [ 
  { "type" : "c", "_id": "c1", v: 1},
  { "type" : "r", "_id": "r1", v: 3},
  { "type" : "r", "_id": "r2", v: 2}
]

querying and sorting could be straight-forward.

db.playground.aggregate(
[
  {$project: { votes: 1 }},
  {$unwind: "$votes"},
  {$sort: {"votes.v": -1}},
  {$limit: 2}
])

It gives the following result.

{
  "result" : [
    {
      "_id" : "c1",
      "votes" : {
        "type" : "c",
        "_id" : "c1",
        "v" : 3
      }
    },
    {
      "_id" : "c1",
      "votes" : {
        "type" : "r",
        "_id" : "r1",
        "v" : 2
      }
    }
  ],
  "ok" : 1
}

An index on votes.v is needed because it seems like a read heavy use case. When updating the comments, just update votes array in the same update request.