MongoDB聚合查询：关联matter的contactId匹配参数报错问题

First, let's address the immediate error you're hitting: when you try to set query.matter.contactId, query.matter doesn't exist yet (your initial query only contains the firmId key). To fix that error alone, you'd need to initialize the matter object first:

if (contactId) {
  contactId = new mongoose.Types.ObjectId(contactId);
  // Initialize the matter object in query if it doesn't exist
  if (!query.matter) {
    query.matter = {};
  }
  query.matter.contactId = contactId;
}

But this won't solve your actual requirement—because the matter.contactId field doesn't exist in your transactions collection. That field lives in the separate matters collection, which you join later via $lookup. The initial $match stage can't filter on a field that hasn't been joined yet.

Correct Approach: Filter After Joining Matters #

To filter transactions where the associated matter's contactId matches your input parameter, you need to add a $match stage after you've joined and unwound the matters collection. Here's how to adjust your code properly:

1. Track the ContactId Filter in Your Function #

First, store the converted contactId (to ObjectId) if it's provided:

exports.getJoinedTransactions = function(firmId, page, pageSize, billerId, contactId, matterId, words, billingStatus, dateBeginning, dateEnding, transactionType, cb) {
  let firmIdToObjId = new mongoose.Types.ObjectId(global.user.firmId);
  let query = { firmId: firmIdToObjId };
  let matterContactFilter = null; // Track this filter for later

  if ( typeof billerId === "object" ) {
    billerId = new mongoose.Types.ObjectId(billerId);
    query.contactId = billerId;
  }
  if ( typeof matterId === "object" ) {
    matterId = new mongoose.Types.ObjectId(matterId);
    query.matterId = matterId;
  }
  // Convert contactId to ObjectId if provided
  if (contactId && typeof contactId === "object") {
    matterContactFilter = new mongoose.Types.ObjectId(contactId);
  }

  // Rest of your code to build the aggregation pipeline...
}

2. Add the Filter to Your Aggregation Pipeline #

Insert a conditional $match stage after the $unwind: "$matter" step to filter on the joined matter's contactId:

myAggregate = [
  { $sort: { date: -1 } },
  { $match: query },
  { $lookup: { from: "contacts", localField: "contactId", foreignField: "_id", as: "contact" } },
  { $unwind: "$contact" },
  { $lookup: { from: "matters", localField: "matterId", foreignField: "_id", as: "matter" } },
  { $unwind: "$matter" },
  // Conditionally add the matter contact filter if it exists
  ...(matterContactFilter ? [{ $match: { "matter.contactId": matterContactFilter } }] : [])
];

Alternative: Optimized Lookup with Filtered Pipeline #

For better performance (avoid joining all matters just to filter them out later), modify the $lookup for matters to only fetch entries matching the contactId upfront:

// Replace your existing matters $lookup with this:
{ 
  $lookup: { 
    from: "matters", 
    let: { transactionMatterId: "$matterId" },
    pipeline: [
      { $match: { 
        $expr: { $eq: ["$_id", "$$transactionMatterId"] },
        ...(matterContactFilter ? { contactId: matterContactFilter } : {})
      }}
    ],
    as: "matter" 
  } 
},
{ $unwind: "$matter" },
// Optional: Exclude transactions that have no matching matter
...(matterContactFilter ? [{ $match: { "matter": { $ne: null } } }] : [])

This way, you only join matters that already meet your contactId criteria, reducing the data processed in later stages.

Why Your Original Approach Failed #

Your initial code had two key issues:

1. query.matter wasn't initialized, causing the "Cannot set property 'contactId' of undefined" error.
2. Even if you fixed that, the transactions collection doesn't have a matter.contactId field—this field resides in the matters collection, so you can't filter on it until after the $lookup and $unwind stages complete.

内容的提问来源于stack exchange，提问作者Jeff Matthews