MongoDB SQL Join with Examples

You can use the $lookup to perform SQL join two MongoDB collections as mongo doesn’t have a join operator to perform traditional SQL-like joins. Moreover, a $lookup stage is used to perform a left outer join between two collections.

1. Syntax of $lookup Operator to Join

Following is the syntax for the $lookup operator in MongoDB:


# Syntax of $lookup
{
  $lookup: {
    from: ,
    localField: ,
    foreignField: ,
    as: 
  }
}

Let’s discuss the syntax’s components.

$lookup: The name of the operator that performs the join between two collections.
from: The name of the foreign collection that you want to join with the input collection.
localField: Likewise, the name of the field in the input collection that you want to use for the join. Also, this field should contain values that match values in the foreign collection’s field specified in the foreignField parameter.
foreignField: The name of the field in the foreign collection that you want to use for the join. Also, this field should contain values that match values in the input collection’s field specified in the localField parameter.
as: The name of the output array field that will contain the joined documents. However, each document in this array field will be the result of the join between the input and foreign collections.

2. MongoDB Join Example with $lookup stage

MongoDB doesn’t have a join operator hence, use the $lookup operator to perform SQL-like join in MongoDB, note that this operator is used to perform only the left outer join as MongoDB doesn’t support other join types like right outer join, inner join e.t.c.

let’s create two collections to perform the join operation using the $lookup stage in MongoDB.

First, create the collection Books with the following documents:


#Create 'Books' collection
db.Books.insertMany([
 { "_id" : 1, "author_id" : 1, "title": "The Alchemist", "price" : 100 },
 { "_id" : 2, "author_id" : 2, "title": "Charles Dicken", "price" : 200 },
 { "_id" : 3, "author_id" : 1, "title": "The Pilgrimage", "price" : 300 },
 { "_id" : 4, "author_id" : 3, "title": "Othello", "price" : 400 }
 ])

And, another collection as Authors


#Create 'Authors' collection
db.Authors.insertMany([
 { "_id" : 1, "name" : "william shakespeare" },
 { "_id" : 2, "name" : "David copperfield" },
 { "_id" : 3, "name" : "Paulo coelho" }
 ])

To join these two collections, we have performed the following pipeline. Similarly, the query will return all documents from the Books collection, along with the matching documents from the Authors collection, where the author_id field in the Books collection matches the _id field in the Authors collection.

Therefore, the result will include an additional field as AuthorDetail, which is an array of matching documents from the Authors collection.


# Join two collections using $lookup operator
db.Books.aggregate([
   {
      $lookup:
         {
           from: "Authors",
           localField: "author_id",
           foreignField: "_id",
           as: "AuthorDetail"
         }
   }
])

Following is the output from the above pipeline:

3. MongoDB Recursive Join Example with $graphlookup stage

We can perform a recursive join on a collection using the $graphlookup stage of MongoDB that represents a tree or graph structure. Also, we have performed the query that will return all documents from the Books collection, along with a new field called Book_Details, which is an array of documents that represent the hierarchy for each book, including themselves.

Note that the startWith parameter is set to $_id, which means that we start the traversal from the _id field of each document in the collection.

Moreover, the connectFromField is assigned with _id, which is the field in the Books collection that connects to the connectToField, which is book_id. Also, the as parameter sets the name of the field that will contain the result of the $graphLookup operation.


#$graphLookup usage
db.Books.aggregate([
   {
      $graphLookup:
         {
           from: "Books",
           startWith: "$_id",
           connectFromField: "book_id",
           connectToField: "_id",
           as: "Book_Details"
         }
   }
])

The following example of the output for recursive join in MongoDB:

4. Conclusion

In summary, while MongoDB does not support traditional SQL joins, the $lookup aggregation pipeline stage provides a way to combine data from multiple collections in MongoDB. However, the join-like operation can be performed in another way which is discussed above. It’s important to note that MongoDB’s approach to combining documents is different from traditional SQL JOIN operations, and it requires a different way of thinking about data modeling and querying.