1

I have the following query:

db.pmusers.aggregate([
    {
        $unwind: '$preferableUsersIds'
    },
    {
        $group:{_id: '$preferableUsersIds', number:{$sum: 1}}
    },
    {
        $sort:{number:-1}
    },
    {
        $limit:1
    }
])

I understand that it is not the optimal solution because I sort all rntries instead of find only one.

Does mongoDb support to rewrite it in more efficient way?

P.S.

I know aboout $max but don't see how it can help me.

this one works at least not faster:

db.pmusers.aggregate([
      {
          $unwind: '$preferableUsersIds'
      },
      {
          $sortByCount: "$preferableUsersIds"
      },
      {
          $limit:1
      }
  ])
gstackoverflow
  • 36,709
  • 117
  • 359
  • 710

1 Answers1

0

See the answer here

MongoDB coalesces the sort and limit in an aggregation to optimise the query. You can also add an index if you're doing this a lot to make sure it's a covered query.

Text from linked answer:

According to the MongoDB documentation:

When a $sort immediately precedes a $limit, the optimizer can coalesce the $limit into the $sort. This allows the sort operation to only maintain the top n results as it progresses, where n is the specified limit, and MongoDB only needs to store n items in memory.

Chris Trott
  • 52
  • 5
  • which index can help at the moment ? – gstackoverflow Aug 08 '17 at 07:47
  • In the case of this query specifically, no index will help. Unfortunately anything after a mutation like $unwind is no longer working with your collection directly, but a modified result set, so the index can't be used. I would suggest looking into doing a $match before the $unwind and setting up an index based on that match. Of course, this depends on your use case, as you haven't really provided much context. – Chris Trott Aug 09 '17 at 01:29