Multiple collections joins

About the Community Welcome
1

I have 5 collections viz. claim, document, complaint, acknowledge, responses

relationship are as follows

  1. claimid of claim collection is a foreign key in the document collection
  2. documentid of the document is a foreign key in complaint collection
  3. complaintid of complaint collection is a foreign key in acknowledge & responses collections

I need data from document collection and all its claims, complaint, responses, and acknowledgment data

query used so far, pls help in making a query to fetch data from acknowledge & responses collections.

db.document.aggregate([ { $lookup: { from: "complaint", localField: "documentid ", foreignField: "documentid", as: "ComplaintDetail" } }, { $lookup:{ from: "Claim", localField: "claimId", foreignField: "claimId", as: "Claims" } } ])
3

Hey @bharat_phartyal,

Welcome to the MongoDB Community :sparkles:

Based on your schema design, you can use the following aggregation pipeline consisting of 4 $lookup stages:

db.document.aggregate([
  {
    $lookup: {
      from: "complaint",
      localField: "documentid",
      foreignField: "documentid",
      as: "ComplaintDetail"
    }
  },
  {
    $lookup: {
      from: "claim",
      localField: "claimid",
      foreignField: "claimid",
      as: "Claims"
    }
  },
  {
    $lookup: {
      from: "acknowledge",
      localField: "ComplaintDetail.complaintid",
      foreignField: "complaintid",
      as: "Acknowledgements"
    }
  },
  {
    $lookup: {
      from: "responses",
      localField: "ComplaintDetail.complaintid",
      foreignField: "complaintid",
      as: "Responses"
    }
  }
]);

However, I’d recommend reconsidering your schema design to make the query more efficient and optimize it for better performance. Depending on your use case, you may need to use appropriate indexes and possibly denormalize some data to reduce the need for multiple lookups in the aggregation pipeline.

Note: I recommend evaluating the above aggregation pipeline with different data loads to ensure it meets your use case and requirements.

Hope it helps!

Regards,
Kushagra

2 Likes