Limit with ties in MongoDB

user_17381218 0
Viewed 111

I have a database with movie data: title and year. I need a query to perform a count of movies by year and return the year with the highest count. My struggle is that I need it to be dynamic, so if there is a tie, it should return all the "tied" years.

Currently my query looks like this:

db.movies.aggregate({$group : {_id : "$year", movies: {$sum : 1}}}, {$sort : {"movies" : -1}}, {$limit : 3})

Right now I have a tie between three movies, hence the limit 3 but I would like it to be dynamic. Any help would be appreciated! Thanks in advance!

aggregation-framework mongodb limit
2 Answers

use double $group, the second one group by count.

db.collection.aggregate({
  $group: {
    _id: "$year",
    movies: {
      $sum: 1
    }
  }
},
{
  "$group": {
    "_id": "$movies",
    "yearList": {
      "$push": "$$ROOT._id"
    }
  }
},
{
  $sort: {
    "_id": -1
  }
},
{
  "$limit": 1
})

mongoplayground

user_166952450

Query

  • uses $setWindowFields, requires MongoDB >= 5
  • sort by movies (it has the movie count)
  • $denseRank will rank the members but same movie count will get the same rank
  • we filter max rank 3 (if duplicates it might be more like in the example bellow)

PlayMongo

aggregate(
[{"$setWindowFields": 
    {"output": {"dense-rank": {"$denseRank": {}}},
     "sortBy": {"movies": -1}}},
  {"$match": {"$expr": {"$lte": ["$dense-rank", 3]}}},
  {"$unset": ["dense-rank"]}])
user_48826920
Related