Perform SQL Join Equivalent in MongoDB with $lookup

How to Perform an SQL-Like Join in MongoDB?

In MongoDB, performing the equivalent of an SQL JOIN is achieved using the $lookup aggregation stage. While MongoDB is a NoSQL database and doesn’t support direct joins like SQL, $lookup allows you to perform left outer joins between collections during aggregation. Here’s a guide to using $lookup to combine data from multiple collections.

Description:

To perform an SQL-like join in MongoDB, you can use the $lookup stage in an aggregation pipeline. The $lookup operator allows you to join data from different collections, providing a way to combine related information across collections into a single query result. This approach is helpful in situations where you need data from multiple collections in one query result.

Syntax:

The syntax for using $lookup in MongoDB is:

{
  $lookup: {
    from: "<foreign_collection>",
    localField: "<local_field>",
    foreignField: "<foreign_field>",
    as: "<output_array_field>"
  }
}

Explanation:

• from: Specifies the name of the foreign collection to join with.
• localField: The field in the current collection that is used for matching.
• foreignField: The field in the foreign collection to match with localField.
• as: The name of the output array field where matched documents will be added.

Example Code:

In this example, we’ll join a customers collection with an orders collection, where we want each customer document to include an array of their orders.

Code:

// Aggregation query on the 'customers' collection
db.customers.aggregate([
  {
    // Use $lookup to perform a left outer join with 'orders' collection
    $lookup: {
      // Specify the foreign collection to join
      from: "orders",
      // The field in 'customers' collection to match on
      localField: "_id",
      // The field in 'orders' collection to match with 'customers._id'
      foreignField: "customerId",
      // Name of the array field to hold matched orders
      as: "customerOrders"
    }
  }
])

Explanation:

• db.customers.aggregate([ ... ])
  This initiates an aggregation pipeline on the customers collection.
• $lookup
  The $lookup stage is used here to perform a left outer join with the orders collection.
• from: "orders"
  Specifies the orders collection as the foreign collection for the join.
• localField: "_id"
  Uses the _id field from the customers collection as the joining field.
• foreignField: "customerId"
  Matches the customerId field in the orders collection to customers._id.
• as: "customerOrders"
  The resulting joined documents from orders are added to a new field called customerOrders in each customer document.

This query returns each document from the customers collection, with an added customerOrders field containing an array of matching orders for each customer.