Looping through MongoDB collections and joining them in C#

Question

I have a collection in MongoDB which has document with names of collections I need to work. I need to query this collection, get all the collection names from the document inside this collection and then query those collections and join them based on ParentId references. Following is the collection which stores the name of other collection

db.AllInfoCollection.find()
{
    "_id" : ObjectId("5b83b982a5e17c383c8424f3"),
    "CollName" : "Collection1",
},
{
    "_id" : ObjectId("5b83b9aaa5e17c383c8424f7"),
    "CollName" : "Collection2",
},
{
    "_id" : ObjectId("5b83b9afa5e17c383c8424f8"),
    "CollName" : "Collection3",
},
{
    "_id" : ObjectId("5b83b9b5a5e17c383c8424f9"),
    "CollName" : "Collection4",
},
{
    "_id" : ObjectId("5b83b9b9a5e17c383c8424fa"),
    "CollName" : "Collection5",
},
{
    "_id" : ObjectId("5b84f41bc5eb3f1f7c291f94"),
    "CollName" : "Collection6",
}

All the above collections (Collection1, Collection2,.... Collection6) are created on run time with empty documents. They are connected to each other with Id and ParentId fields. Now I need to query this AllInfoCollection, get the collection names and join them and generate the final joined ($lookup) output. I am able to query and get the collection list, but I am not sure how to add lookup projection inside the for loop. Any help would be appreciated.

public void RetrieveDynamicCollection()
    {
        IMongoDatabase _db = client.GetDatabase("MyDb");
        var collectionList = _db.GetCollection<AllInfoCollection>("AllInfoCollection").AsQueryable().Distinct().Select(x => x.CollectionName).ToList();

        for(int i = 0; i < collectionList.Count; i++)
        {
            var collectionName = collectionList[i];
            IMongoCollection<BsonDocument> collection = _db.GetCollection<BsonDocument>(collectionName);
            var options = new AggregateOptions()
            {
                AllowDiskUse = false
            };
            //not able to proceed here
        }

    }

Answer 1

Finally I was able to retrieve collections dynamically with all required joins(lookup aggregation) as below, hope it helps someone:

public async Task<string> RetrieveDynamicCollection()
    {
        try
        {
            IMongoDatabase _db = client.GetDatabase("MyDB");
            var list = _db.GetCollection<HazopCollectionInfo>("AllCollectionInfo").AsQueryable().ToList();
            var collectionList = list.OrderBy(x => x.CollectionOrder).Select(x => x.CollectionName).Distinct().ToList();

            var listOfJoinDocuments = new List<BsonDocument>();

            var firstCollection = _db.GetCollection<BsonDocument>(collectionList[0]);
            var options = new AggregateOptions()
            {
                AllowDiskUse = false
            };
            var previousCollectionName = "";
            for (int i = 0; i < collectionList.Count; i++)
            {
                var collectionName = collectionList[i];
                IMongoCollection<BsonDocument> collection = _db.GetCollection<BsonDocument>(collectionName);
                if (i == 0)
                {
                    firstCollection = collection;
                    var firstarray = new BsonDocument("$project", new BsonDocument()
                        .Add("_id", 0)
                        .Add(collectionName, "$$ROOT"));

                    listOfJoinDocuments.Add(firstarray);
                }
                else
                {
                    var remainingArray = new BsonDocument("$lookup", new BsonDocument()
                            .Add("localField", previousCollectionName + "." + "Id")
                            .Add("from", collectionName)
                            .Add("foreignField", "ParentId")
                            .Add("as", collectionName));
                    listOfJoinDocuments.Add(remainingArray);

                    remainingArray = new BsonDocument("$unwind", new BsonDocument()
                            .Add("path", "$" + collectionName)
                            .Add("preserveNullAndEmptyArrays", new BsonBoolean(true)));
                    listOfJoinDocuments.Add(remainingArray);
                }
                previousCollectionName = collectionName;
            }
            // Project the columns 
            list.OrderBy(x => x.ColumnOrder);
            var docProjection = new BsonDocument();
            for(int i=0;i<list.Count;i++)
            {
                docProjection.Add(list[i].ColumnName, "$"+list[i].CollectionName + "." + list[i].FieldName);
            }

            listOfJoinDocuments.Add(new BsonDocument("$project", docProjection));


            PipelineDefinition<BsonDocument, BsonDocument> pipeline = listOfJoinDocuments;

            var listOfDocs = new List<BsonDocument>();
            using (var cursor = await firstCollection.AggregateAsync(pipeline, options))
            {
                while (await cursor.MoveNextAsync())
                {
                    var batch = cursor.Current;
                    foreach (BsonDocument document in batch)
                    {
                        listOfDocs.Add(document);
                    }
                }
            }

            var jsonString = listOfDocs.ToJson(new MongoDB.Bson.IO.JsonWriterSettings { OutputMode = MongoDB.Bson.IO.JsonOutputMode.Strict });

            return jsonString;
        }
        catch(Exception ex)
        {
            throw ex;
        }
    }