How do I perform the SQL Join equivalent in MongoDB?

Question

How do I perform the SQL Join equivalent in MongoDB?

For example say you have two collections (users and comments) and I want to pull all the comments with pid=444 along with the user info for each.

comments   { uid:12345, pid:444, comment="blah" }   { uid:12345, pid:888, comment="asdf" }   { uid:99999, pid:444, comment="qwer" }  users   { uid:12345, name:"john" }   { uid:99999, name:"mia"  } 

Is there a way to pull all the comments with a certain field (eg. ...find({pid:444}) ) and the user information associated with each comment in one go?

At the moment, I am first getting the comments which match my criteria, then figuring out all the uid's in that result set, getting the user objects, and merging them with the comment's results. Seems like I am doing it wrong.

Answer 1

As of Mongo 3.2 the answers to this question are mostly no longer correct. The new $lookup operator added to the aggregation pipeline is essentially identical to a left outer join:

https://docs.mongodb.org/master/reference/operator/aggregation/lookup/#pipe._S_lookup

From the docs:

{    $lookup:      {        from: <collection to join>,        localField: <field from the input documents>,        foreignField: <field from the documents of the "from" collection>,        as: <output array field>      } } 

Of course Mongo is not a relational database, and the devs are being careful to recommend specific use cases for $lookup, but at least as of 3.2 doing join is now possible with MongoDB.

Answer 2

We can merge/join all data inside only one collection with a easy function in few lines using the mongodb client console, and now we could be able of perform the desired query. Below a complete example,

.- Authors:

db.authors.insert([     {         _id: 'a1',         name: { first: 'orlando', last: 'becerra' },         age: 27     },     {         _id: 'a2',         name: { first: 'mayra', last: 'sanchez' },         age: 21     } ]); 

.- Categories:

db.categories.insert([     {         _id: 'c1',         name: 'sci-fi'     },     {         _id: 'c2',         name: 'romance'     } ]); 

.- Books

db.books.insert([     {         _id: 'b1',         name: 'Groovy Book',         category: 'c1',         authors: ['a1']     },     {         _id: 'b2',         name: 'Java Book',         category: 'c2',         authors: ['a1','a2']     }, ]); 

.- Book lending

db.lendings.insert([     {         _id: 'l1',         book: 'b1',         date: new Date('01/01/11'),         lendingBy: 'jose'     },     {         _id: 'l2',         book: 'b1',         date: new Date('02/02/12'),         lendingBy: 'maria'     } ]); 

.- The magic:

db.books.find().forEach(     function (newBook) {         newBook.category = db.categories.findOne( { "_id": newBook.category } );         newBook.lendings = db.lendings.find( { "book": newBook._id  } ).toArray();         newBook.authors = db.authors.find( { "_id": { $in: newBook.authors }  } ).toArray();         db.booksReloaded.insert(newBook);     } ); 

.- Get the new collection data:

db.booksReloaded.find().pretty() 

.- Response :)

{     "_id" : "b1",     "name" : "Groovy Book",     "category" : {         "_id" : "c1",         "name" : "sci-fi"     },     "authors" : [         {             "_id" : "a1",             "name" : {                 "first" : "orlando",                 "last" : "becerra"             },             "age" : 27         }     ],     "lendings" : [         {             "_id" : "l1",             "book" : "b1",             "date" : ISODate("2011-01-01T00:00:00Z"),             "lendingBy" : "jose"         },         {             "_id" : "l2",             "book" : "b1",             "date" : ISODate("2012-02-02T00:00:00Z"),             "lendingBy" : "maria"         }     ] } {     "_id" : "b2",     "name" : "Java Book",     "category" : {         "_id" : "c2",         "name" : "romance"     },     "authors" : [         {             "_id" : "a1",             "name" : {                 "first" : "orlando",                 "last" : "becerra"             },             "age" : 27         },         {             "_id" : "a2",             "name" : {                 "first" : "mayra",                 "last" : "sanchez"             },             "age" : 21         }     ],     "lendings" : [ ] } 

I hope this lines can help you.