MongoDB lookup example

 

To join three tables (or collections) in MongoDB, you can use the $lookup aggregation stage multiple times within an aggregation pipeline. This allows you to perform multiple left outer joins. Here’s an example scenario and how you can implement it.

Example Scenario

Let's assume we have three collections: orders, customers, and products.

orders Collection:

json

[
  { "_id": 1, "order_id": "ORD001", "customer_id": 1, "product_id": 101, "amount": 500 },
  { "_id": 2, "order_id": "ORD002", "customer_id": 2, "product_id": 102, "amount": 200 },
  { "_id": 3, "order_id": "ORD003", "customer_id": 1, "product_id": 103, "amount": 300 },
  { "_id": 4, "order_id": "ORD004", "customer_id": 3, "product_id": 101, "amount": 700 }
]

customers Collection:

json

[
  { "_id": 1, "name": "John Doe", "email": "john@example.com" },
  { "_id": 2, "name": "Jane Smith", "email": "jane@example.com" },
  { "_id": 3, "name": "Mike Johnson", "email": "mike@example.com" }
]

products Collection:

json

[
  { "_id": 101, "product_name": "Laptop", "price": 1000 },
  { "_id": 102, "product_name": "Phone", "price": 500 },
  { "_id": 103, "product_name": "Tablet", "price": 300 }
]

Joining the Collections

We want to create a report that includes orders along with the customer details and product details.

Aggregation Pipeline with Multiple $lookup Stages

Here’s how to use multiple $lookup stages to join the three collections:

javascript

db.orders.aggregate([
  {
    $lookup: {
      from: "customers",           // First join with the 'customers' collection
      localField: "customer_id",   // Field from 'orders' collection
      foreignField: "_id",         // Field from 'customers' collection
      as: "customer_info"          // Name of the array field to add
    }
  },
  {
    $unwind: "$customer_info"      // Unwind the 'customer_info' array
  },
  {
    $lookup: {
      from: "products",            // Second join with the 'products' collection
      localField: "product_id",    // Field from 'orders' collection
      foreignField: "_id",         // Field from 'products' collection
      as: "product_info"           // Name of the array field to add
    }
  },
  {
    $unwind: "$product_info"       // Unwind the 'product_info' array
  },
  {
    $project: {
      order_id: 1,
      amount: 1,
      "customer_info.name": 1,
      "customer_info.email": 1,
      "product_info.product_name": 1,
      "product_info.price": 1
    }
  }
])

Explanation:

• First $lookup: Joins the orders collection with the customers collection using the customer_id field.
• First $unwind: Deconstructs the resulting customer_info array so that each document contains a single customer object.
• Second $lookup: Joins the resulting documents with the products collection using the product_id field.
• Second $unwind: Deconstructs the resulting product_info array so that each document contains a single product object.
• $project: Specifies the fields to include in the final output, creating a concise and readable result.

Result:

The result of the above aggregation will be:

json

[
  {
    "_id": 1,
    "order_id": "ORD001",
    "amount": 500,
    "customer_info": {
      "name": "John Doe",
      "email": "john@example.com"
    },
    "product_info": {
      "product_name": "Laptop",
      "price": 1000
    }
  },
  {
    "_id": 3,
    "order_id": "ORD003",
    "amount": 300,
    "customer_info": {
      "name": "John Doe",
      "email": "john@example.com"
    },
    "product_info": {
      "product_name": "Tablet",
      "price": 300
    }
  },
  {
    "_id": 2,
    "order_id": "ORD002",
    "amount": 200,
    "customer_info": {
      "name": "Jane Smith",
      "email": "jane@example.com"
    },
    "product_info": {
      "product_name": "Phone",
      "price": 500
    }
  },
  {
    "_id": 4,
    "order_id": "ORD004",
    "amount": 700,
    "customer_info": {
      "name": "Mike Johnson",
      "email": "mike@example.com"
    },
    "product_info": {
      "product_name": "Laptop",
      "price": 1000
    }
  }
]

In this result:

• Each order document now includes the customer details (customer_info) and the product details (product_info).
• The project stage ensures that only the relevant fields are included in the output.

Additional Tips:

• Complex Joins: For more complex joins, you can use additional aggregation stages like $group, $match, or nested $lookup stages.
• Optimizing Performance: Ensure that the fields used in joins are indexed to improve performance.
• Pipeline Customization: Customize the pipeline stages to fit specific requirements, such as filtering, sorting, or additional calculations.

By using multiple $lookup stages in the aggregation pipeline, you can effectively join multiple collections and create comprehensive reports or views in MongoDB.