如何用MongoDB Aggregate Lookup实现三级集合嵌套层级查询

First off, I notice your Subject collection example is missing a key link: there's no facultyId field to connect each Subject to its parent Faculty. Without that, we can't nest Subjects under Faculties properly. I'll assume we add this field to Subject documents (like { "id": { "$numberLong": "100000" }, "name": "Subject1", "facultyId": { "$numberLong": "100000" } }) for the solutions below.

Approach 1: Database-Side Aggregation (Recommended for Large Datasets) #

This uses MongoDB's aggregation framework to handle joins and nesting directly in the database, which is way more efficient for large datasets—it minimizes data transfer between your database and application.

Raw MongoDB Aggregation Query #

If you're querying for a school with ID 100000, here's the pipeline you'd use:

db.school.aggregate([
  // First, grab the target school
  { $match: { id: { $numberLong: "100000" } } },
  // Join with the Faculty collection to get all related faculties
  {
    $lookup: {
      from: "faculty",
      localField: "id",
      foreignField: "schoolId",
      as: "faculties",
      // Nest Subjects inside each Faculty using a sub-pipeline
      pipeline: [
        {
          $lookup: {
            from: "subject",
            localField: "id",
            foreignField: "facultyId",
            as: "subjects"
          }
        }
      ]
    }
  }
])

Node.js + Mongoose Implementation #

If you're using Mongoose in a Node.js API, here's how you'd wrap this into a reusable function and endpoint:

async function getNestedSchoolData(schoolId) {
  // Run the aggregation pipeline
  const schoolResults = await School.aggregate([
    { $match: { id: NumberLong(schoolId) } },
    {
      $lookup: {
        from: "faculties", // Mongoose maps model names to pluralized collections
        localField: "id",
        foreignField: "schoolId",
        as: "faculties",
        pipeline: [
          {
            $lookup: {
              from: "subjects",
              localField: "id",
              foreignField: "facultyId",
              as: "subjects"
            }
          }
        ]
      }
    }
  ]);
  // Aggregation returns an array—we just need the first (only) result
  return schoolResults[0] || null;
}

// Example Express API endpoint
app.get('/schools/:id', async (req, res) => {
  try {
    const schoolData = await getNestedSchoolData(req.params.id);
    if (!schoolData) {
      return res.status(404).json({ message: "School not found" });
    }
    res.json(schoolData);
  } catch (err) {
    res.status(500).json({ message: "Failed to fetch school data", error: err.message });
  }
});

Approach 2: Application-Side Nesting (Simpler for Small Datasets) #

If you prefer to handle the nesting logic in your application code instead of the database, you can fetch each level sequentially and build the nested structure manually. This is simpler for small datasets but less efficient for large volumes.

Node.js + Mongoose Implementation #

async function getNestedSchoolData(schoolId) {
  // Step 1: Fetch the base school document
  const school = await School.findOne({ id: NumberLong(schoolId) });
  if (!school) return null;

  // Step 2: Fetch all faculties linked to this school
  const faculties = await Faculty.find({ schoolId: school.id });

  // Step 3: Fetch subjects for each faculty (parallelize with Promise.all to save time)
  const facultiesWithSubjects = await Promise.all(
    faculties.map(async (faculty) => {
      const subjects = await Subject.find({ facultyId: faculty.id });
      // Convert Mongoose document to plain object and add subjects
      return { ...faculty.toObject(), subjects };
    })
  );

  // Step 4: Nest faculties into the school object
  return { ...school.toObject(), faculties: facultiesWithSubjects };
}

// Express endpoint can reuse the same code from Approach 1

Key Optimizations to Keep in Mind #

• Indexes: Add these indexes to speed up your queries drastically:
  • db.school.createIndex({ id: 1 })
  • db.faculty.createIndex({ schoolId: 1 })
  • db.subject.createIndex({ facultyId: 1 })
• Projection: Only fetch the fields you need (e.g., exclude _id if you don't use it) to reduce data transfer.
• Validation: Add checks for invalid ID formats to avoid unnecessary database calls.

Sample API Response #

Your endpoint will return a clean nested structure like this:

{
  "id": 100000,
  "name": "School1",
  "faculties": [
    {
      "id": 100000,
      "schoolId": 100000,
      "name": "Faculty1",
      "subjects": [
        {
          "id": 100000,
          "name": "Subject1",
          "facultyId": 100000
        }
      ]
    }
  ]
}

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