mongodb - Multiple join conditions using the $lookup operator

Mongodb - Multiple join conditions using the $lookup operator

In MongoDB, the $lookup stage in the aggregation pipeline is used to perform a left outer join between documents from the input collection and documents from another collection. The $lookup stage supports multiple join conditions using the let and pipeline options.

Here's an example of performing a $lookup with multiple join conditions:

Assuming you have two collections, orders and products, and you want to join them based on the fields product_id and category_id:

// Sample data in the 'orders' collection db.orders.insertMany([ { _id: 1, product_id: 101, quantity: 2 }, { _id: 2, product_id: 102, quantity: 1 }, { _id: 3, product_id: 103, quantity: 3 }, ]); // Sample data in the 'products' collection db.products.insertMany([ { _id: 101, name: 'Product A', category_id: 1 }, { _id: 102, name: 'Product B', category_id: 2 }, { _id: 103, name: 'Product C', category_id: 1 }, ]); // Perform a $lookup with multiple join conditions db.orders.aggregate([ { $lookup: { from: 'products', let: { product_id: '$product_id', category_id: '$category_id' }, pipeline: [ { $match: { $expr: { $and: [ { $eq: ['$product_id', '$$product_id'] }, { $eq: ['$category_id', '$$category_id'] }, ], }, }, }, ], as: 'joinedData', }, }, { $unwind: '$joinedData', // Optional: If you want to unwind the result array }, { $project: { _id: 1, quantity: 1, 'joinedData.name': 1, 'joinedData.category_id': 1, }, }, ]);

In this example:

  • The $lookup stage uses the let option to define variables (product_id and category_id) representing the fields to be used in the join conditions.
  • The pipeline option specifies the conditions for the join using $match and $expr.
  • The $and expression is used to specify multiple join conditions.

After the $lookup stage, you may optionally use $unwind to flatten the result array, and then use $project to shape the final output.

Adjust the field names and collection names based on your actual data model.

Examples

  1. "MongoDB $lookup with multiple conditions"

    • Code:
      db.orders.aggregate([ { $lookup: { from: "products", let: { orderId: "$_id", productCode: "$productCode" }, pipeline: [ { $match: { $expr: { $and: [ { $eq: ["$orderId", "$$orderId"] }, { $eq: ["$code", "$$productCode"] } ] } } } ], as: "orderDetails" } } ])
    • Description: This MongoDB aggregation pipeline uses the $lookup operator with multiple conditions. It performs a join between the "orders" and "products" collections based on both the order ID (_id) and the product code (productCode).

  2. "MongoDB $lookup with nested conditions"

    • Code:
      db.orders.aggregate([ { $lookup: { from: "products", let: { orderId: "$_id", productCode: "$productCode" }, pipeline: [ { $match: { $expr: { $and: [ { $eq: ["$orderId", "$$orderId"] }, { $eq: ["$code", "$$productCode"] }, { $gt: ["$quantity", 0] } ] } } } ], as: "validProducts" } } ])
    • Description: This MongoDB aggregation example extends the $lookup with an additional condition, filtering products based on a quantity greater than 0.

  3. "MongoDB $lookup with multiple fields"

    • Code:
      db.orders.aggregate([ { $lookup: { from: "products", let: { orderId: "$_id", productCode: "$productCode", quantity: "$quantity" }, pipeline: [ { $match: { $expr: { $and: [ { $eq: ["$orderId", "$$orderId"] }, { $eq: ["$code", "$$productCode"] }, { $gte: ["$stock", "$$quantity"] } ] } } } ], as: "matchingProducts" } } ])
    • Description: This MongoDB query demonstrates a $lookup operation with multiple fields, joining "orders" and "products" based on order ID, product code, and a quantity condition.

  4. "MongoDB $lookup with nested array conditions"

    • Code:
      db.orders.aggregate([ { $lookup: { from: "products", let: { items: "$items" }, pipeline: [ { $match: { $expr: { $in: ["$code", "$$items.productCodes"] } } } ], as: "matchingProducts" } } ])
    • Description: This MongoDB aggregation query demonstrates using $lookup with nested array conditions. It matches products based on the inclusion of their codes in the "items.productCodes" array.

  5. "MongoDB $lookup with array and value conditions"

    • Code:
      db.orders.aggregate([ { $lookup: { from: "products", let: { itemCode: "$itemCode", quantities: "$quantities" }, pipeline: [ { $match: { $expr: { $and: [ { $eq: ["$code", "$$itemCode"] }, { $gte: ["$quantity", { $arrayElemAt: ["$$quantities", 0] }] } ] } } } ], as: "matchedProducts" } } ])
    • Description: This MongoDB $lookup example incorporates both array and value conditions. It matches products based on the equality of their codes and having a quantity greater than or equal to the first element in the "quantities" array.

  6. "MongoDB $lookup with multiple equality conditions"

    • Code:
      db.orders.aggregate([ { $lookup: { from: "products", let: { orderId: "$_id", productCode: "$productCode", status: "$status" }, pipeline: [ { $match: { $expr: { $and: [ { $eq: ["$orderId", "$$orderId"] }, { $eq: ["$code", "$$productCode"] }, { $eq: ["$status", "$$status"] } ] } } } ], as: "matchedProducts" } } ])
    • Description: This MongoDB query involves $lookup with multiple equality conditions, joining "orders" and "products" based on order ID, product code, and status.

  7. "MongoDB $lookup with multiple OR conditions"

    • Code:
      db.orders.aggregate([ { $lookup: { from: "products", let: { orderType: "$type", orderStatus: "$status" }, pipeline: [ { $match: { $expr: { $or: [ { $and: [{ $eq: ["$type", "$$orderType"] }, { $eq: ["$status", "Shipped"] }] }, { $eq: ["$status", "$$orderStatus"] } ] } } } ], as: "matchedProducts" } } ])
    • Description: This MongoDB $lookup example involves multiple OR conditions. It matches products based on a combination of order type and status conditions.

  8. "MongoDB $lookup with nested and array conditions"

    • Code:
      db.orders.aggregate([ { $lookup: { from: "products", let: { orderItems: "$items" }, pipeline: [ { $match: { $expr: { $and: [ { $in: ["$code", "$$orderItems.productCodes"] }, { $gte: ["$quantity", { $arrayElemAt: ["$$orderItems.quantities", 0] }] } ] } } } ], as: "matchingProducts" } } ])
    • Description: This MongoDB query demonstrates $lookup with both nested and array conditions. It matches products based on the inclusion of their codes in the "items.productCodes" array and having a quantity greater than or equal to the first element in the "items.quantities" array.

  9. "MongoDB $lookup with multiple conditions and projection"

    • Code:
      db.orders.aggregate([ { $lookup: { from: "products", let: { orderId: "$_id", productCode: "$productCode" }, pipeline: [ { $match: { $expr: { $and: [ { $eq: ["$orderId", "$$orderId"] }, { $eq: ["$code", "$$productCode"] } ] } } }, { $project: { _id: 0, code: 1, name: 1 } } ], as: "matchedProducts" } } ])
    • Description: This MongoDB query uses $lookup with multiple conditions and includes a projection stage to specify the fields to include in the result from the "products" collection.

  10. "MongoDB $lookup with multiple conditions and array unwind"

    • Code:
      db.orders.aggregate([ { $lookup: { from: "products", let: { orderId: "$_id", productCodes: "$productCodes" }, pipeline: [ { $match: { $expr: { $and: [ { $eq: ["$orderId", "$$orderId"] }, { $in: ["$code", "$$productCodes"] } ] } } } ], as: "matchedProducts" } }, { $unwind: "$matchedProducts" } ])
    • Description: This MongoDB query combines $lookup with multiple conditions and the $unwind stage to match products based on order ID and product codes, and then unwinds the resulting array for further processing.

More Tags

observable dosbox asp.net-core-2.0 key-value pygame2 mmap sap-fiori keyerror dapper pygal

More Programming Questions

More Transportation Calculators

More Investment Calculators

More Chemical reactions Calculators

More Financial Calculators