How to Combine Data from Multiple Collections in MongoDB?

Combine Data from Multiple MongoDB Collections

In MongoDB, combining data from multiple collections into one collection can be achieved using the aggregate() method with the $lookup stage. The $lookup operator functions like a SQL JOIN, enabling you to pull in related documents from a secondary collection based on specified matching criteria.

Description:

To merge data from multiple collections into a single result set, MongoDB’s aggregation pipeline provides the $lookup stage, which allows you to join data between collections. This can be particularly useful when organizing related data stored across different collections into one comprehensive result.

Syntax:

To join two collections, use the $lookup stage within the aggregate() method:

db.<primary_collection>.aggregate([
  {
    $lookup: {
      from: "<secondary_collection>",
      localField: "<field_in_primary_collection>",
      foreignField: "<field_in_secondary_collection>",
      as: "<output_field<"
    }
  }
])

Explanation:

• <primary_collection>: The collection on which you’re performing the aggregation.
• <secondary_collection>: The collection from which you want to pull related data.
• <field_in_primary_collection>: The field in the primary collection used for matching.
• <field_in_secondary_collection>: The field in the secondary collection used for matching.
• <output_field>: The name of the new field that will store the joined data.

Example:

Suppose you have two collections, orders and customers, where each order references a customer by customerId. Here’s how to combine data from customers into orders:

Code:

// Perform aggregation on the orders collection
db.orders.aggregate([
  {
    $lookup: {
      from: "customers",                 // Join with customers collection
      localField: "customerId",          // Match orders' customerId field
      foreignField: "_id",               // with customers' _id field
      as: "customerDetails"              // Output field for joined data
    }
  }
])

Where -

• from: "customers": Specifies the customers collection as the source for related data.
• localField: "customerId": Matches customerId from orders.
• foreignField: "_id": Matches _id from customers to relate each order with the appropriate customer.
• as: "customerDetails": Names the output field for storing customer information in each orders document.

Explanation:

• $lookup Stage:
• $lookup is a pipeline stage in MongoDB’s aggregation framework that facilitates a left outer join between collections.
• The resulting documents will include a new field (customerDetails in this case), which contains an array of matched documents from the customers collection.
• Results Structure:
• The output will contain all documents from the orders collection, with an additional field (customerDetails) that holds matching customer data as an array of documents.
• If no match is found, the array will be empty; otherwise, it will contain one or more documents from the customers collection.

Additional Notes:

• Unwind Arrays: If you want to flatten the array created by $lookup into individual documents, you can use $unwind to expand each document in the array.
• Multiple Joins: For joining with more than two collections, you can chain multiple $lookup stages within the same aggregation pipeline.

Example Output:

If you have an orders document like:

{
  "_id": ObjectId("64b7a8d9c1b4"),
  "orderId": "1001",
  "customerId": ObjectId("64b7a7f9c1a2"),
  "items": ["item1", "item2"]
}

And a matching customers document:
{
  "_id": ObjectId("64b7a7f9c1a2"),
  "name": "Alice Johnson",
  "email": "[email protected]"
}

The $lookup operation will result in:
{
  "_id": ObjectId("64b7a8d9c1b4"),
  "orderId": "1001",
  "customerId": ObjectId("64b7a7f9c1a2"),
  "items": ["item1", "item2"],
  "customerDetails": [
    {
      "_id": ObjectId("64b7a7f9c1a2"),
      "name": "Alice Johnson",
      "email": "[email protected]"
    }
  ]
}

This output includes a customerDetails field with information about the customer who placed the order.