How to Perform the SQL Join Equivalent in MongoDB?

In database management, with the rise of NoSQL databases like MongoDB, the approach to handling data relationships has evolved. MongoDB’s document–oriented nature and schema–less design provide a different perspective on joins compared to SQL.

In this article, we’ll learn about How to Perform the SQL Join Equivalent in MongoDB by understanding their types and performing various queries using Aggregation pipelines.

How to Perform the SQL Join Equivalent in MongoDB?

• MongoDB, being a NoSQL database, is quite different from SQL databases. MongoDB is schema-less and document-oriented.
• It means it stores data in flexible BSON documents instead of predefined tables.
• As a result, the concept of joins in MongoDB doesn’t directly translate from SQL.
• In MongoDB, achieving joins as SQL joins is possible through aggregation pipelines and the use of the $lookup operator.
• Aggregation pipelines allow us to process data records and transform them using a series of stages.

Let’s set up an Environment:

To understand How to perform the SQL Join equivalent in MongoDB we need a collection and some documents on which we will perform various operations and queries. Here we will consider two collections called Books and Authors which contain various information shown in the below images.

Books Collection

Authors Collection:

Authors Collection

Below Synax used for performing Joins

db.modelName.aggregate([
    {
        $lookup: {
            from: "Collection to Join",
            localField: "Field from the input documents",
            foreignField: "Field from the documents of the 'from' collection",
            as: "Pick a field-name as output"
        }
    }
]);

Explanation: This MongoDB aggregation syntax utilizes the $lookup stage to perform a left join between two collections.

• from: This represents the foreign collection, which we want to join to our current collection.
• localField: LocalField specifies the field in the input documents to perform the join operation.
• foreignField: The foreignField specifies the field from the foreign collection to match with the localField of the input documents.
• as: It denotes the name of the field that will contain the join array data in the output documents.

Joins in MongoDB

1. Left Join

• A left join in MongoDB combines matching documents from the primary (“left“) collection with documents from the secondary (“right“) collection.
• It returns all documents from the primary collection along with related documents from the secondary collection.

db.books.aggregate([
    {
        $lookup: {
            from: "authors",
            localField: "authorId",
            foreignField: "_id",
            as: "author"
        }
    }
]);

Output:

Left Join (Authors join to Books)

Explanation:

• In the above Query, We have use aggregation pipeline $lookup stage to perform a left join between the books collection and the authors collection based on matching values between the authorId field in the books collection and the _id field in the authors collection.
• The result is an aggregation output where each document from the books collection includes an additional field named author containing an array of matching documents from the authors collection.

2. Right Join

• In a right join MongoDB reverses the typical roles of the primary and secondary collections.
• It is similar to the left join but we are reversing the input documents like in left-join we combine the authors collection to the books collection but in right-join we combine the books collection into the authors collection.

db.authors.aggregate([
    {
        $lookup: {
            from: "books",
            localField: "bookId",
            foreignField: "_id",
            as: "book"
        }
    }
]);

Output:

Right Join (Books join to Authors)

Explanation:

• In the above Query, It matches documents from the authors collection where the bookId field corresponds to the _id field in the books collection. The result is an array named book containing related book documents for each author entry.
• This output array represents authors along with their associated books. Each object in the array contains an authorId, name, and their bookId. If an author has a book, it appears in the book array within the object, showing the bookId, name, and the authorId. For authors without any associated books, the book array is empty ([]), as seen with “Peter Thiel” and “Kristina Chodorow” in the provided example.

3. Inner Join

• In MongoDB, an inner join operation combines matching documents from two collections based on specified conditions and resulting in a set of documents that intersect.
• This process involves matching documents from the primary collection with those from the secondary collection using a common field. The result includes only the documents where a match is found in both collections.

db.books.aggregate([
    {
        $lookup: {
            from: "authors",
            localField: "authorId",
            foreignField: "_id",
            as: "author"
        }
    },
    {
        $match: {
            "author": { $ne: [] }
        }
    }
]);

Output:

Inner Join (Authors join to Books)

Explanation:

• In the above Query, It basically brings together information from the books and authors collections by finding matches based on the authorId field in the books collection and the _id field in the authors collection.
• This way, you get details about books along with their respective authors. The output only includes books with valid authors. If there’s a book without a matching author, it simply won’t show up in the result.
• In this case, there are 4 documents in the books collection, but some of them does not have a corresponding author in the authors collection. As a result, only 3 documents are returned in the output.

4. Full Join

• In MongoDB, a full join is a database operation that combines data from two collections using a related field and bringing together all documents from both collections, regardless of matching related fields.
• If there’s no match for a document in one collection with another, both documents will still appear in the output..

db.books.aggregate([
    {
        $lookup: {
            from: "authors",
            localField: "authorId",
            foreignField: "_id",
            as: "author"
        }
    },
    {
        $unionWith: {
            coll: "authors",
            pipeline: [
                {
                    $lookup: {
                        from: "books",
                        localField: "bookId",
                        foreignField: "_id",
                        as: "book"
                    }
                }
            ]
        }
    }
]);

Output:

Full Join (8 Documents)

Explanation:

• In the above Query, it looks up related authors from the books collection using the authorId field. These details are saved in a field called author. After that, the $unionWith stage is used to merge this data with the authors collection and bringing together documents from both collections.
• In the output, there are a total of 8 documents, 4 from the books collection and another 4 from the authors collections.

Conclusion

Overall, MongoDB and SQL differ in their approach to data storage and querying, MongoDB provides powerful tools like aggregation pipelines and the $lookup operator to perform joins between collections. By understanding these concepts and applying them appropriately, you can effectively work with related data in MongoDB, providing flexibility and scalability to your database operations.