使用带条件关联的$lookup筛选特定用户的实现问题求助

Looks like the core issue here is that you're trying to nest a $lookup directly inside a $match condition with $exists—that's not how MongoDB aggregation stages work. $lookup needs to be a top-level stage, and we'll use the results from that lookup to check if a user has no associated projects.

Let's break down the fix step by step:

1. First, add a $lookup stage to pull in all projects the user is part of. We'll use a left join so users with no projects get an empty array.
2. Then, use $match to combine your two target user groups, leveraging the lookup results to check for users with no projects.

Here's the corrected aggregation pipeline:

return await this.collection
  .aggregate([
    // Step 1: Look up all projects the user is associated with
    {
      $lookup: {
        from: _.get(Config, `env.collection.projects`),
        let: { userUuid: "$uuid" },
        pipeline: [
          {
            $match: {
              $expr: {
                $in: ["$$userUuid", "$users.uuid"]
              }
            }
          },
          { $project: { _id: 1 } } // Only keep project IDs to reduce data size
        ],
        as: "associatedProjects"
      }
    },
    // Step 2: Filter the two user groups
    {
      $match: {
        $or: [
          // Group 1: Inactive users who haven't logged in (or created) in 2 weeks
          {
            $and: [
              { isActive: false },
              {
                $expr: {
                  $allElementsTrue: {
                    $map: {
                      input: ["$lastLogin", "$createdOn"],
                      in: { $lt: ["$$this", twoWeeksAgo] }
                    }
                  }
                }
              }
            ]
          },
          // Group 2: Users who haven't logged in (or created) in 5 weeks AND have no associated projects
          {
            $and: [
              {
                $expr: {
                  $allElementsTrue: {
                    $map: {
                      input: ["$lastLogin", "$createdOn"],
                      in: { $lt: ["$$this", fiveWeeksAgo] }
                    }
                  }
                }
              },
              { $expr: { $eq: ["$associatedProjects", []] } } // Check if lookup returned empty array
            ]
          }
        ]
      }
    }
  ])
  .toArray();

Key Fixes & Explanations: #

• $lookup as a top-level stage: We moved $lookup out of $match—this is mandatory because aggregation stages run sequentially, and $lookup needs to execute before we can use its results in filtering.
• Simplified project matching: Instead of using $filter in the lookup pipeline, we use $in to check if the user's UUID exists in any project's users.uuid array. This is more efficient and cleaner.
• Check for empty projects array: Instead of $exists, we use $expr: { $eq: ["$associatedProjects", []] } to verify that the user has no associated projects—this works because a left join will return an empty array for users with no matches in the Projects collection.
• Cleaner condition grouping: We restructured the $or to clearly separate the two user groups, making the pipeline easier to read and maintain.

Additional Notes: #

• If your lastLogin field can be null (e.g., for users who never logged in), you might want to adjust the $map logic to handle that (e.g., treat null as "older than 5 weeks").
• Adding a $project stage at the end to exclude the associatedProjects array if you don't need it in your final results can help reduce data transfer:

  { $project: { associatedProjects: 0 } }
  

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