如何在单次MongoDB聚合中实现双字段Lookup与Group查询

Got it, let's figure out how to adjust your aggregation to handle two separate ticket fields in a single query. The key here is using MongoDB's $facet stage, which lets you run multiple parallel aggregation pipelines within one operation—perfect for your use case.

Here's a revised version of your function that supports two fields (we'll use typeA and typeB as placeholders for your actual field names) with lookups, filtering, and grouping all in one go:

userSchema.statics.getDualCounts = function (req, typeA, typeB) {
  const dateRange = {
    $gte: new Date(moment().subtract(4, 'd').startOf('day').utc()),
    $lt: new Date(moment().endOf('day').utc())
  };

  return this.aggregate([
    // Step 1: Filter users to only those in the current organization
    { $match: { organization: req.user.organization._id } },

    // Step 2: Use $facet to run two parallel pipelines for each ticket field
    { $facet: {
        [`${typeA}_stats`]: [
          { $lookup: {
              from: 'tickets',
              localField: `${typeA}Tickets`,
              foreignField: '_id',
              as: `${typeA}_tickets`,
            }
          },
          // Unwind the ticket array, skip users with no matching tickets
          { $unwind: { path: `$${typeA}_tickets`, preserveNullAndEmptyArrays: false } },
          // Filter tickets to the date range
          { $match: { [`${typeA}_tickets.createdAt`]: dateRange } },
          // Group results (adjust _id to your grouping needs—this example groups by user)
          { $group: {
              _id: '$_id',
              user_name: { $first: '$name' }, // Replace with your user identifier field
              [`${typeA}_total`]: { $sum: 1 }
            }
          }
        ],
        [`${typeB}_stats`]: [
          { $lookup: {
              from: 'tickets',
              localField: `${typeB}Tickets`,
              foreignField: '_id',
              as: `${typeB}_tickets`,
            }
          },
          { $unwind: { path: `$${typeB}_tickets`, preserveNullAndEmptyArrays: false } },
          { $match: { [`${typeB}_tickets.createdAt`]: dateRange } },
          { $group: {
              _id: '$_id',
              user_name: { $first: '$name' },
              [`${typeB}_total`]: { $sum: 1 }
            }
          }
        ]
      }
    },

    // Optional: Combine the two datasets into a single list of user stats
    { $project: {
        combined_stats: {
          $map: {
            input: `$${typeA}_stats`,
            as: 'a_stats',
            in: {
              $mergeObjects: [
                '$$a_stats',
                // Match the corresponding typeB stats for the same user
                { $arrayElemAt: [
                    { $filter: {
                        input: `$${typeB}_stats`,
                        cond: { $eq: ['$$this._id', '$$a_stats._id'] }
                      }
                    }, 0
                  ]
                }
              ]
            }
          }
        }
      }
    },
    { $unwind: '$combined_stats' },
    { $replaceRoot: { newRoot: '$combined_stats' } }
  ]);
}

Key Details: #

• $facet Stage: This is the backbone of the solution. It lets you run two separate aggregation pipelines (one for each ticket field) in parallel, avoiding the need for multiple database calls.
• Reusable Date Range: We extracted the date filter into a variable to keep code DRY and easy to adjust.
• Result Merging: The final stages combine the two separate stats lists into a single set of user records with both counts. If you don't need merged results, you can omit the last three stages and just return the raw $facet output.
• Flexible Grouping: Adjust the $group stage's _id field to match your needs—for example, if you want daily counts instead of user counts, use _id: { $dateToString: { format: "%Y-%m-%d", date: $${typeA}_tickets.createdAt } }.

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