MongoDB C# Driver: Nested Lookups - How do I "join" nested relations?

Question

I have 3 MongoDB collections that are related to each other:

1. Company
2. Store: a Company can have multiple Stores
3. Product: a Store can have multiple Products

Company

{
  "_id": { "$oid": "1388445c0000000000000001" },
  "name": "Company A",
  "stores": [
    { "$oid": "1388445c0000000000000011" },
    { "$oid": "1388445c0000000000000012" }
  ]
}

Store

{
  "_id": { "$oid": "1388445c0000000000000011" },
  "name": "Store A",
  "products": [
    { "$oid": "1388445c0000000000000021" },
    { "$oid": "1388445c0000000000000022" },
    { "$oid": "1388445c0000000000000023" }
  ]
}

Product

{
  "_id": { "$oid": "1388445c0000000000000021" },
  "name": "Product A"
}

If I use Lookup to "join" the first two collections, then the ObjectIds of the Stores are replaced with their corresponding objects from the Store collection:

db.GetCollection<BsonDocument>("Company")
    .Aggregate()
    .Lookup("Store", "stores", "_id", "stores")
    .ToList();

{
   "_id": { "$oid": "1388445c0000000000000001" },
   "name": "Company A",
   "stores": [
     {
       "_id": { "$oid": "1388445c0000000000000011" },
       "name": "Store A",
       "products": [
         { "$oid": "1388445c0000000000000021" },
         { "$oid": "1388445c0000000000000022" },
         { "$oid": "1388445c0000000000000023" }
       ]
     },
     ...
   ]
}

But I'm struggling to "join" the Products on the nested Stores.

First I tried:

db.GetCollection<BsonDocument>("Company")
    .Aggregate()
    .Lookup("Store", "stores", "_id", "stores")
    .Lookup("Product", "products", "_id", "products")
    .ToList();

but obviously, it doesn't work as simple as that. Because the field products doesn't exist on Company, nothing happens.

If I try:

db.GetCollection<BsonDocument>("Company")
    .Aggregate()
    .Lookup("Store", "stores", "_id", "stores")
    .Lookup("Product", "stores.products", "_id", "stores.products")
    .ToList();


{
   "_id": { "$oid": "1388445c0000000000000001" },
   "name": "Company A",
   "stores": {
     "products": [
       {
         "_id": { "$oid": "1388445c0000000000000021" },
         "name": "Product A"
       },
       ...
     ]
   }
}

then the products are "joined", but all other fields of the Store are gone. Furthermore the field stores is not an array anymore, but an object.

How do I correctly setup the aggregate pipeline with the MongoDB C# Driver to get the 3 collections "joined" so that I receive the following result:

{
   "_id": { "$oid": "1388445c0000000000000001" },
   "name": "Company A",
   "stores": [
     {
       "_id": { "$oid": "1388445c0000000000000011" },
       "name": "Store A",
       "products": [
         {
           "_id": { "$oid": "1388445c0000000000000021" },
           "name": "Product A"
         },
         ...
       ]
     }
   ]
}

Side note: I'm working with BsonDocument and not a concrete C# type.

Answer 1

Thanx to @Yong Shun I have found the correct answer.

You can build the query also with MongoDB C# types as follows:

PipelineStageDefinition<BsonDocument, BsonDocument> stage = PipelineStageDefinitionBuilder.Lookup<BsonDocument, BsonDocument, BsonDocument, IEnumerable<BsonDocument>, BsonDocument>(
  db.GetCollection<BsonDocument>("Store"),
  new BsonDocument("stores", new BsonDocument("$ifNull", new BsonArray { "$stores", new BsonArray() })),
  new PipelineStagePipelineDefinition<BsonDocument, BsonDocument>(new List<PipelineStageDefinition<BsonDocument, BsonDocument>>
  {
    PipelineStageDefinitionBuilder.Match(new BsonDocumentFilterDefinition<BsonDocument>(new BsonDocument("$expr", new BsonDocument("$in", new BsonArray { "$_id", "$$stores" })))),              
    PipelineStageDefinitionBuilder.Lookup<BsonDocument, BsonDocument, BsonDocument, IEnumerable<BsonDocument>, BsonDocument>(
      db.GetCollection<BsonDocument>("Product"),
      new BsonDocument("products", new BsonDocument("$ifNull", new BsonArray { "$products", new BsonArray() })),
      new PipelineStagePipelineDefinition<BsonDocument, BsonDocument>(new List<PipelineStageDefinition<BsonDocument, BsonDocument>>
        {
          PipelineStageDefinitionBuilder.Match(new BsonDocumentFilterDefinition<BsonDocument>(new BsonDocument("$expr", new BsonDocument("$in", new BsonArray { "$_id", "$$products" })))),
      }),
      "products"
    )
  }),
  "stores"
);

List<BsonDocument> result = db.GetCollection<BsonDocument>("Entity").Aggregate().AppendStage(stage).ToList();

Answer 2

I think you should achieve with nested $lookup pipeline as below:

db.Company.aggregate([
  {
    "$lookup": {
      "from": "Store",
      "let": {
        stores: "$stores"
      },
      "pipeline": [
        {
          $match: {
            $expr: {
              $in: [
                "$_id",
                "$$stores"
              ]
            }
          }
        },
        {
          $lookup: {
            "from": "Product",
            let: {
              products: { products: { $ifNull: [ "$products", [] ] } }
            },
            pipeline: [
              {
                $match: {
                  $expr: {
                    $in: [
                      "$_id",
                      "$$products"
                    ]
                  }
                }
              }
            ],
            as: "products"
          }
        }
      ],
      "as": "stores"
    }
  }
])

Sample Mongo Playground

And convert the query to BsonDocument with MongoDB Compass.

var pipeline = new[]
{
    new BsonDocument("$lookup",
        new BsonDocument
        {
            { "from", "Store" },
            { "let",
                new BsonDocument("stores", "$stores") 
            },
            { "pipeline",
                new BsonArray
                {
                    new BsonDocument("$match",
                        new BsonDocument("$expr",
                            new BsonDocument("$in",
                                new BsonArray
                                {
                                    "$_id",
                                    "$$stores"
                                }
                            )
                        )
                    ),
                    new BsonDocument("$lookup",
                        new BsonDocument
                        {
                            { "from", "Product" },
                            { "let",
                                new BsonDocument("products", 
                                    new BsonDocument("$ifNull", 
                                        new BsonArray 
                                        { 
                                            "$products", 
                                            new BsonArray() 
                                        }
                                    ) 
                                ) 
                            },
                            { "pipeline",
                                new BsonArray
                                {
                                    new BsonDocument("$match",
                                        new BsonDocument("$expr",
                                            new BsonDocument("$in",
                                                new BsonArray
                                                {
                                                    "$_id",
                                                    "$$products"
                                                }
                                            )
                                        )
                                    )
                                } 
                            },
                            { "as", "products" }
                        }
                    )
                } 
            },
            { "as", "stores" }
        }
    )
};

var result = _db.GetCollection<BsonDocument>("Company")
    .Aggregate<BsonDocument>(pipeline)
    .ToList();

Result