Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Getting Distinct values of multiple values in Mongodb

Let's imagine I have the following collection of books (Edited out the ID field for compactness):

{ "author" : "Steve Jones", "title" : "Fun Stuff",    "edition" : "2010" }
{ "author" : "Steve Jones", "title" : "Fun Stuff",    "edition" : "2011" }
{ "author" : "Steve Jones", "title" : "Boring Stuff", "edition" : "2010" }
{ "author" : "Ben Johnson", "title" : "Other Stuff",  "edition" : "2010" }

What should I do I want to find only the latest edition of each book? In other words: I want to get a result which omits the 2010 edition of Steve Jones' Fun Stuff, and only has the 2011 edition:

{ "author" : "Steve Jones", "title" : "Fun Stuff",    "edition" : "2011" }
{ "author" : "Steve Jones", "title" : "Boring Stuff", "edition" : "2010" }
{ "author" : "Ben Johnson", "title" : "Other Stuff",  "edition" : "2010" }

If this was a relational database and I were writing a query, I'd probably write something like SELECT DISTINCT title, DISTINCT author, edition WHERE 1 SORT BY edition DESC. So my instinct coming from a SQL background is to look up db.collection.distinct() but it seems to work pretty differently than what I'm used to; it only returns an array with each distinct value for that a single field.

Is this something that I would need to tackle programmatically? Or can I do this entirely with mongo functions and bson?

like image 761
dotVezz Avatar asked Mar 14 '23 23:03

dotVezz


1 Answers

Try something like this to start:

db.textbooks.aggregate({ $group: {
    _id: { title: "$title", author: "$author" },
    latestEdition: { $max: "$edition" }
}})

That will give you each unique book by title & author and the latest edition. Play around with the query and aggregation to get exactly what you want.

like image 183
ICDevin Avatar answered Mar 17 '23 16:03

ICDevin