Getting the documents in the specified order from MongoDB using C# driver

Question

I am new to MongoDB and I wanted to know whether it is possible to fetch the documents by Id in the order I have specified in the array. I am using official c# driver.

Problem Description

I have some documents in the collection like this

{
    "_id" : "1",
    "Name" : "Assignment 1"
}

to

{
    "_id" : "100",
    "Name" : "Assignment 100"
}

I have a array of Ids { "4", "19", "10", "6" } and what I need is to fetch the documents specified in the same order as in the array.

These are the queries that I tried

{ "_id" : { "$in" : ["4", "19", "10", "6"] } }

{ "$or" : [{ "_id" : "4" }, { "_id" : "19" }, { "_id" : "10" }, { "_id" : "6" }] }

But both of these queries are returning the documents in the following order

But my expected result is

The only option in front of me right now is to make separate db requests for each elements in the array which I think is a bad option.

Can anyone of you please steer me on fetching the documents in the order that I have specified in the array. I am using MongoDB C# driver and expecting an answer based on C#.

Thanks,
Kapil

Answer 1

Well I have finally managed to use the Aggregation framework to make this work.

This is the way I have achieved it. But I would be happy if somebody can suggest a better way than this.

string[] fetchingIds = { "4", "19", "10", "6" };

IMongoQuery fetchQuery = Query<Assignment>.In(x => x.Id, fetchingIds);
BsonDocument match = new BsonDocument { { "$match", fetchQuery.ToBsonDocument() } };

BsonDocument currentDocument = null;
BsonDocument finalDocument = null;
BsonValue processingDocument = null;
for (int i = 0; i < fetchingIds.Length; i++)
{
    BsonElement ifStatement = new BsonElement("$eq", new BsonArray(new[] { "$_id", fetchingIds[i] }));
    BsonArray conditionStatement = new BsonArray(new[] { new BsonDocument(ifStatement), BsonValue.Create(i + 1), -1 });
    currentDocument = new BsonDocument("$cond", conditionStatement);

    if (finalDocument == null)
    {
        finalDocument = currentDocument;
    }
    else
    {
        processingDocument = null;
        BsonValue tempDoc = finalDocument["$cond"][2] as BsonDocument;

        if (tempDoc == null)
        {
            processingDocument = finalDocument["$cond"];
        }

        while (tempDoc != null)
        {
            if ((tempDoc["$cond"][2] as BsonDocument) == null)
            {
                processingDocument = tempDoc["$cond"];
                tempDoc = null;
            }
            else
            {
                tempDoc = tempDoc["$cond"][2];
            }
        }

        processingDocument[2] = currentDocument;
    }
}


BsonDocument project = new BsonDocument { { "$project", new BsonDocument 
                                                        { 
                                                           // this will return whose document
                                                            {"obj","$$ROOT"},
                                                            {"weight",finalDocument},
                                                        } 
                                         } };

BsonDocument sort = new BsonDocument { { "$sort", new BsonDocument { { "weight", 1 } } } };

var result = assignmentCollection.Aggregate(new AggregateArgs
{
    Pipeline = new[] { match, project, sort },
}).ToList();