MongoDB：基于starred字段关联School双重嵌套subjects的$lookup实现

Got it, let's figure out how to pull those matched subject objects using MongoDB's aggregation framework. The tricky part here is that your subjects are nested inside the faculties array in the School collection—so a basic $lookup won't cut it directly. We need to use a correlated subquery in the $lookup pipeline to unpack those nested structures and match the IDs from the User's starred array.

Here's the complete aggregation pipeline for the User collection:

db.User.aggregate([
  {
    $lookup: {
      from: "School",
      // Define a variable to reference the current user's starred subject IDs
      let: { starredSubjectIds: "$starred" },
      pipeline: [
        // First, unpack the faculties array to access nested subjects
        { $unwind: "$faculties" },
        // Then unpack the subjects array to work with individual subject documents
        { $unwind: "$faculties.subjects" },
        // Match subjects where the ID is in the user's starred list
        {
          $match: {
            $expr: {
              $in: ["$faculties.subjects.id", "$$starredSubjectIds"]
            }
          }
        },
        // Extract only the subject fields we need, flattening the nested structure
        {
          $project: {
            _id: 0,
            id: "$faculties.subjects.id",
            name: "$faculties.subjects.name"
          }
        }
      ],
      // Name the field where matched subjects will be stored
      as: "starredSubjects"
    }
  },
  // Optional: Shape the final output to only include the fields you care about
  {
    $project: {
      id: 1,
      name: 1,
      starredSubjects: 1
    }
  }
])

Breakdown of what this does: #

1. $lookup with correlated subquery: We use let to pass the current user's starred array into the pipeline that runs against the School collection.
2. $unwind stages: These temporarily flatten the nested faculties and subjects arrays, so we can access individual subject documents for matching.
3. $match with $expr: This checks if a subject's ID exists in the user's starred array using the $in operator.
4. $project stages: The inner pipeline reshapes the matched subjects into the flat {id, name} structure you want, and the outer pipeline cleans up the final output.

Testing with your sample data: #

For the User document { id: 1, name: "Michael", starred: [1, 2] } and School document { id: 1, name: "Uni", faculties: [{ id:1000, name: "faculty1", subjects: [ {id: 1, name: "sub1"}, {id: 2, name: "sub2"}, {id: 3, name: "sub3"} ] }] }, the output will be:

{
  "id": 1,
  "name": "Michael",
  "starredSubjects": [
    { "id": 1, "name": "sub1" },
    { "id": 2, "name": "sub2" }
  ]
}

Quick notes: #

• If you only need to match subjects from a specific School (e.g., the one with id:1), add a $match: { id: 1 } at the start of the inner pipeline to make the query more efficient.
• If a user's starred array is empty, starredSubjects will return an empty array.
• The $unwind stages only affect the aggregation pipeline—they don't modify your actual collection data.

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