MongoDB嵌套同键对象与另一集合聚合的实现需求

Got it, let's tackle this problem where we need to fetch the progress percentage for all direct sub-modules of a specified parent module, matching a student and course. We'll return the actual progress if it exists in the Course activity collection, otherwise default to 0.

Here's the complete aggregation pipeline you can use in your API, with explanations for each stage:

db.getCollection("Course").aggregate([
  // 1. Match the target course using the incoming courseId parameter
  { $match: { _id: "courseId1" } },
  
  // 2. Recursively find the target module within the nested modules structure
  {
    $addFields: {
      targetModule: {
        $function: {
          body: function(modules, targetId) {
            function findModule(arr) {
              for (let mod of arr) {
                if (mod._id === targetId) return mod;
                if (mod.modules) {
                  let found = findModule(mod.modules);
                  if (found) return found;
                }
              }
              return null;
            }
            return findModule(modules);
          },
          args: ["$modules", "id3"], // Pass the moduleId parameter here
          lang: "js"
        }
      }
    }
  },
  
  // 3. Extract only the direct sub-modules of the target module
  {
    $project: {
      subModules: "$targetModule.modules",
      _id: 0
    }
  },
  
  // 4. Unwind the sub-modules array to process each module individually
  { $unwind: "$subModules" },
  
  // 5. Look up matching progress records from the Course activity collection
  {
    $lookup: {
      from: "Course activity",
      let: {
        studentId: "std1", // Pass the studentId parameter here
        courseId: "courseId1", // Pass the courseId parameter here
        modId: "$subModules._id"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                { $eq: ["$studentId", "$$studentId"] },
                { $eq: ["$courseId", "$$courseId"] }
              ]
            }
          }
        },
        { $unwind: "$mProgress" },
        {
          $match: {
            $expr: { $eq: ["$mProgress.modId", "$$modId"] }
          }
        },
        { $project: { progress: "$mProgress.progress", _id: 0 } }
      ],
      as: "progressData"
    }
  },
  
  // 6. Set progress to the matched value or 0 if no record exists
  {
    $addFields: {
      progress: {
        $cond: {
          if: { $gt: [{ $size: "$progressData" }, 0] },
          then: { $arrayElemAt: ["$progressData.progress", 0] },
          else: 0
        }
      }
    }
  },
  
  // 7. Format each module document to the desired output structure
  {
    $project: {
      modId: "$subModules._id",
      name: "$subModules.name",
      progress: 1,
      _id: 0
    }
  },
  
  // 8. Group all modules back into an array
  {
    $group: {
      _id: null,
      modules: { $push: "$$ROOT" }
    }
  },
  
  // 9. Remove the unnecessary _id field from the final result
  {
    $project: {
      _id: 0,
      modules: 1
    }
  }
])

Key Pipeline Stages Explained #

• $match: Filters the Course collection to only the course matching the incoming courseId parameter.
• $addFields with $function: Uses a custom JavaScript function to recursively search the nested modules array and retrieve the exact module specified by moduleId. This handles any depth of nested modules.
• $project: Extracts just the direct sub-modules of the target module (since we only need its immediate children's progress).
• $unwind: Breaks down the sub-modules array into individual documents so we can process each module's progress separately.
• $lookup: Joins with the Course activity collection to find progress records matching the student, course, and current module. The inner pipeline filters and extracts only the relevant progress value.
• $addFields: Checks if a progress record was found. If yes, uses that value; if not, defaults to 0.
• $project: Shapes each document into the required modId, name, progress structure.
• $group: Reassembles the individual module documents back into a single modules array for the final output.
• $project: Cleans up the result by removing the auto-generated _id field from the group stage.

Parameter Replacement #

Make sure to replace the hardcoded values ("courseId1", "id3", "std1") in the pipeline with the actual parameters received from your API request. Most MongoDB drivers let you pass variables into the aggregation pipeline for this purpose.

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