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 struggeling 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.

CodePudding user response：

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"
    }
  }
])