How do I perform the SQL Join equivalent in MongoDB?

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.

This page on the official mongodb site addresses exactly this question:

https://mongodb-documentation.readthedocs.io/en/latest/ecosystem/tutorial/model-data-for-ruby-on-rails.html

When we display our list of stories, we'll need to show the name of the user who posted the story. If we were using a relational database, we could perform a join on users and stores, and get all our objects in a single query. But MongoDB does not support joins and so, at times, requires bit of denormalization. Here, this means caching the 'username' attribute.

Relational purists may be feeling uneasy already, as if we were violating some universal law. But let’s bear in mind that MongoDB collections are not equivalent to relational tables; each serves a unique design objective. A normalized table provides an atomic, isolated chunk of data. A document, however, more closely represents an object as a whole. In the case of a social news site, it can be argued that a username is intrinsic to the story being posted.

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.