MongoDB创建集合与$Lookup使用：层级一对多关系建模问询

Great question! Let's break this down into two key parts: setting up your collections with proper constraints to avoid orphaned documents, and then querying across the hierarchy using $lookup.

Step 1: Create Collections with Referential Integrity #

MongoDB doesn’t have native foreign key constraints, but we can enforce data consistency using document validation, indexes, and transactions. Here’s how to set up your collections:

1.1 Create Top-Level Collection A #

Start with the parent collection, defining any required fields and validation rules:

db.createCollection("A", {
  validator: {
    $jsonSchema: {
      bsonType: "object",
      required: ["_id", "name"], // Adjust fields to match your needs
      properties: {
        _id: { bsonType: "objectId" },
        name: { bsonType: "string", description: "Name of the A document" }
      }
    }
  }
});

1.2 Create Child Collection B #

For B, we’ll add a validation rule to ensure Aid is a valid ObjectId, and create an index on Aid for faster lookups:

db.createCollection("B", {
  validator: {
    $jsonSchema: {
      bsonType: "object",
      required: ["_id", "Aid", "name"],
      properties: {
        _id: { bsonType: "objectId" },
        Aid: { bsonType: "objectId", description: "Must reference an existing A._id" },
        name: { bsonType: "string", description: "Name of the B document" }
      }
    }
  }
});

// Index for faster lookups and query performance
db.B.createIndex({ Aid: 1 });

1.3 Create Grandchild Collection C #

Repeat the pattern for C, linking to B via Bid:

db.createCollection("C", {
  validator: {
    $jsonSchema: {
      bsonType: "object",
      required: ["_id", "Bid", "name"],
      properties: {
        _id: { bsonType: "objectId" },
        Bid: { bsonType: "objectId", description: "Must reference an existing B._id" },
        name: { bsonType: "string", description: "Name of the C document" }
      }
    }
  }
});

db.C.createIndex({ Bid: 1 });

Enforce No Orphaned Documents #

Since MongoDB can’t natively check cross-collection references in validation, use transactions when inserting parent-child pairs to ensure atomicity:

const session = db.getMongo().startSession();
session.startTransaction();

try {
  // Insert a parent A document
  const aId = db.A.insertOne({ name: "Project Alpha" }, { session }).insertedId;
  
  // Insert child B documents linked to A
  const b1Id = db.B.insertOne({ Aid: aId, name: "Task 1" }, { session }).insertedId;
  db.B.insertOne({ Aid: aId, name: "Task 2" }, { session });
  
  // Insert grandchild C documents linked to B
  db.C.insertOne({ Bid: b1Id, name: "Subtask 1a" }, { session });
  
  // Commit the transaction if all inserts succeed
  await session.commitTransaction();
} catch (error) {
  // Rollback if any step fails
  await session.abortTransaction();
  console.error("Transaction failed:", error);
  throw error;
} finally {
  session.endSession();
}

Step 2: Query Hierarchical Data with $lookup #

Use MongoDB’s $lookup aggregation stage to join collections. You can nest $lookup stages to traverse multiple levels of the hierarchy.

2.1 Single-Level Join (A → B) #

Get all A documents along with their associated B documents:

db.A.aggregate([
  {
    $lookup: {
      from: "B",          // Collection to join
      localField: "_id",  // Field in A to match
      foreignField: "Aid",// Field in B to match
      as: "tasks"         // Name of the array field to store results
    }
  }
]);

2.2 Nested Join (A → B → C) #

To include C documents under their parent B entries, use a nested $lookup inside a pipeline:

db.A.aggregate([
  {
    $lookup: {
      from: "B",
      localField: "_id",
      foreignField: "Aid",
      as: "tasks",
      pipeline: [
        // Nested lookup to get C documents for each B
        {
          $lookup: {
            from: "C",
            localField: "_id",
            foreignField: "Bid",
            as: "subtasks"
          }
        }
      ]
    }
  }
]);

2.3 Filtered Hierarchical Query #

You can add $match stages to filter results at any level. For example, get a specific A document with only its completed B tasks and their C subtasks:

db.A.aggregate([
  // Filter to get a specific A document
  { $match: { _id: ObjectId("your-a-document-id") } },
  {
    $lookup: {
      from: "B",
      localField: "_id",
      foreignField: "Aid",
      as: "completed_tasks",
      pipeline: [
        // Filter B documents to only completed ones
        { $match: { status: "completed" } },
        {
          $lookup: {
            from: "C",
            localField: "_id",
            foreignField: "Bid",
            as: "subtasks"
          }
        }
      ]
    }
  }
]);

Key Tips #

• Indexing: Always index foreign key fields (like Aid in B, Bid in C) to speed up $lookup operations.
• Transaction Safety: Use transactions when inserting related documents to avoid orphaned records if an insert fails.
• Pipeline Flexibility: Nested pipelines in $lookup let you filter, sort, or transform child documents before including them in the parent result.

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