CODEWITHSUNDEEP
mongodbmongodb-query
user1814016
user1814016

Reputation: 2383

How do I find the most popular IDs ordered by row count in MongoDB?

I have a polling application, where, when a user votes, entries such as these are created into the votes collection:

{"vote": "apples", "voter_id": 54729, "poll_id": 1}
{"vote": "oranges", "voter_id": 48201, "poll_id": 1}
{"vote": "apples", "voter_id": 22810, "poll_id": 1}
{"vote": "oranges", "voter_id": 55901, "poll_id": 1}
{"vote": "oranges", "voter_id": 54321, "poll_id": 1}

I am trying to figure out the query which would allow me to find the most popular votes for a given poll_id. For the example data above, I want a result like:

{"vote": "oranges", "count": 3}
{"vote": "apples", "count": 2}

What query should I use to obtain this?

Upvotes: 0

Views: 63

Answers (1)

Hussein Awala
Hussein Awala

Reputation: 5110

You can use the aggregation pipeline:

db.collection.aggregate([{$match:{poll_id:1}},{$group:{_id: { vote: "$vote" },count:{$sum:1}}},{$project:{vote:"$_id.vote",count:1,_id:0}},{$sort:{count:-1}}])

Output:

{ "count" : 2, "vote" : "apples" }
{ "count" : 3, "vote" : "oranges" }

And you can sort by count and then return the top K:

db.collection.aggregate([{$match:{poll_id:1}},{$group:{_id: { vote: "$vote" },count:{$sum:1}}},{$project:{vote:"$_id.vote",count:1,_id:0}},{$sort:{count:-1}},{$limit:1}])

Output:

{ "count" : 3, "vote" : "oranges" }

Try it out: https://mongoplayground.net/p/Mp_c7SAxmYY

Upvotes: 2

Related Questions