👐 $lookup (JOINs in MongoDB)

$lookup in MongoDB serves a similar purpose to JOINs in SQL. It enables combining data from multiple collections by performing an equality match between fields.

---

🔹 Understanding $lookup

The $lookup stage is used to fetch related documents from another collection, similar to INNER JOIN or LEFT JOIN in SQL.

Syntax

{
  $lookup: {
    from: "<foreign_collection>",  // Collection to join
    localField: "<local_field>",   // Field in current collection
    foreignField: "<foreign_field>", // Field in foreign collection
    as: "<output_array>"             // Output field
  }
}

• from: The target collection for joining.
• localField: The field in the current collection.
• foreignField: The field in the target collection.
• as: The output field containing the joined data as an array.

---

🔹 Joining 2 collections

👐 Example 1: Joining Authors collection with Books

🔍 Get book details along with their authors

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

Equivalent SQL query

SELECT author.* , books_written.*
FROM authors
LEFT OUTER JOIN author_books ON authors.id = author_books.author_id
LEFT OUTER JOIN books as books_written ON author_books.book_id = books_written._id;

info

The result in MongoDB will have an array (authorDetails) instead of flat columns.

---

🔹 Handling unwinding ($unwind)

Since $lookup produces an array, we can flatten it using $unwind.

Example 2: Get only book titles and single author name

db.authors.aggregate([
  {
    $lookup: {
      from: "books",
      localField: "books",
      foreignField: "_id",
      as: "booksWritten",
    },
  },
  { $unwind: "$booksWritten" },
  { $project: { name: 1, "booksWritten.title": 1, _id: 0 } },
]);

info

The $lookup operation creates an array within each book document. Using $unwind then flattens this array, resulting in a separate document for every single book-author pair.

---

👐 Challenge

👐 1. Fetch books with their associated reviews

Answer

JavaScript

 await books.aggregate([
  {
    $lookup:
    {
      from: "reviews",
      localField: "_id",
      foreignField: "bookId",
      as: "reviews"
    }
  }
]).toArray();

mongosh

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

C#

var pipeline = booksCollection.Aggregate()
  .Lookup<Book, Review, Book>(
    foreignCollection: reviewsCollection,
    localField: b => b.Id,
    foreignField: r => r.BookId,
    @as: b => b.Reviews
  );

var results = pipeline.ToList();

Python

books_with_reviews = books.aggregate([
  {
    "$lookup":
    {
      "from": "reviews",
      "localField": "_id",
      "foreignField": "bookId",
      "as": "reviews"
    }
  }
])

Java

AggregateIterable<Document> result = books.aggregate(
    List.of(
        Aggregates.lookup("reviews", "_id", "bookId", "reviewsResult"),
        Aggregates.project(
            Projections.include("title", "reviewsResult.name", "reviewsResult.rating")
        ),
        limit(5)         
    )
);

// Iterate through the results
for (Document doc : result) {
    System.out.println("book: " + doc.toJson());
}