Let's break down how to build the MongoDB aggregation pipeline to meet your exact requirements. We need to filter users who match both your profile criteria and possess all the specified talent categories, then return their full details along with the media from those matching talents.

Full Aggregation Pipeline #

Here's the complete pipeline that ties everything together:

db.user.aggregate([
  // Stage 1: Filter users by profile attributes
  {
    $match: {
      gender: { $regex: "^male$", $options: "i" }, // Case-insensitive match for gender
      complexion: { $regex: "^white$", $options: "i" },
      $expr: {
        $and: [
          // Calculate age from dob and check range
          { $gte: [{ $dateDiff: { startDate: "$dob", endDate: "$$NOW", unit: "year" } }, 18] },
          { $lte: [{ $dateDiff: { startDate: "$dob", endDate: "$$NOW", unit: "year" } }, 25] },
          // Convert height/weight from string to number and check range
          { $gte: [{ $toDouble: "$height" }, 5] },
          { $lte: [{ $toDouble: "$height" }, 6] },
          { $gte: [{ $toDouble: "$weight" }, 50] },
          { $lte: [{ $toDouble: "$weight" }, 80] }
        ]
      }
    }
  },
  // Stage 2: Join with talent collection and filter matching categories
  {
    $lookup: {
      from: "talent",
      localField: "_id",
      foreignField: "userId",
      let: { targetCategories: ["5f19359250bcf9158c6be573", "5f19357b50bcf9158c6be572"] },
      pipeline: [
        { $match: { $expr: { $in: ["$category", "$$targetCategories"] } } },
        // Optional: Join with category collection to include category details
        {
          $lookup: {
            from: "category",
            localField: "category",
            foreignField: "_id",
            as: "categoryDetails"
          }
        },
        { $unwind: "$categoryDetails" } // Flatten category details array
      ],
      as: "matchingTalents"
    }
  },
  // Stage 3: Ensure the user has ALL required talent categories
  {
    $match: {
      $expr: {
        $eq: [
          { $size: { $setUnion: ["$matchingTalents.category", []] } }, // Count unique matching categories
          { $size: ["5f19359250bcf9158c6be573", "5f19357b50bcf9158c6be572"] } // Count required categories
        ]
      }
    }
  },
  // Stage 4: Format output to match your expected structure
  {
    $project: {
      _id: 0,
      user: {
        // Exclude the matchingTalents field from the user object (optional)
        $mergeObjects: [
          "$$ROOT",
          { matchingTalents: "$$REMOVE" }
        ]
      },
      medias: {
        // Combine all media arrays from matching talents into a single list
        $reduce: {
          input: "$matchingTalents.media",
          initialValue: [],
          in: { $concatArrays: ["$$value", "$$this"] }
        }
      }
    }
  }
])

Key Explanations #

1. Profile Filtering:

   • We use $dateDiff to calculate the user's age from their dob field and validate it against your min/max range.
   • $toDouble converts string values for height and weight to numbers (since your sample data has empty strings, you might want to add a fallback with $cond if needed, e.g., $cond: [{ $eq: ["$height", ""] }, 0, { $toDouble: "$height" }]).
   • Case-insensitive regex matches for gender and complexion make the filter more flexible.

2. Talent & Category Joining:

   • The $lookup with a sub-pipeline filters talents to only those in your target categories, and optionally joins with the category collection to include category details if you need them.

3. Enforce All Required Categories:

   • $setUnion removes duplicate category IDs from the matched talents, then we check if the count matches the number of required categories. This ensures the user has all the specified talents, not just one.

4. Output Formatting:

   • $mergeObjects cleans up the user object by removing the temporary matchingTalents field.
   • $reduce concatenates all media arrays from the matching talents into a single medias array, matching your expected output structure.

Edge Cases to Consider #

• If some users don't have a dob, height, or weight field, add $exists: true checks to the $match stage to avoid errors.
• If empty strings for height/weight should be excluded, add $ne: ["$height", ""] to the $expr conditions.

内容的提问来源于stack exchange，提问作者Lijomon C John