MongoDB C# Case Insensitive Sort and Index

Question

So far I've been using this code to find my documents and then sort them:

 var options = new FindOptions
 {
 Modifiers = new BsonDocument("$hint", "PathTypeFilenameIndex")
 };
 return await Collection
 .Find(f => f.Metadata["path"] == path, options)
 .SortBy(f => f.Metadata["type"])
 .ThenBy(f => f.Filename)
 .ToListAsync();

I have a class that has Metadata field with path and type fields, also the class has a Filename field. I want all documents with a given path inside the metadata sorted by type and then by Filename.

An example result would be a list of documents ordered by the Name field like this:

a, Ab, B, c, D

Unfortunately, I get something like this:

Ab, B, D, a, c

And that's because MongoDB sorts the data with a simple binary comparison, where 'A' < 'a' because of their ASCII codes.

So my question is: Is there a way to make a case insensitive sort and keep using the "$hint"?

That options I pass to the Find method should tell MongoDB which index to use. I found this post: MongoDB and C#: Case insensitive search but the method here doesn't work for sorting and I couldn't tell MongoDB which index to use.

Answer 1

I think you can use aggregation pipeline with $addFields, $toLower (to convert filename to lowercase in temporary field), and $sort to sort them irrespective of the case

In mongodb shell you would write something like this :

db.collection.aggregate([{
 $addFields : {
 "lowercaseFileName" : {
 $loLower : "$fileName"
 }
 },{
 $sort : {
 "metadata.type" : 1,
 lowercaseFileName : 1
 }
 }
}])

Please write the similar code in c#, and see if it works. I dont know c#, otherwise i would have given you the exact query, but i cant.

The idea is to transform the filename to lowercase, save it in temporary field, using addFields and sort by that field.

Hope this helps you out.

Read more about $addFields, $toLower here.

Update

For whoever wants a working code in C# , thanks to @kaloyan-manev

You can use this :

return await Collection.Aggregate()
 .Match(f => f.Metadata["path"] == path) 
 .AppendStage<BsonDocument>(new BsonDocument("$addFields", new BsonDocument("lowercaseFileName", new BsonDocument("$toLower", "$filename")))) 
 .AppendStage<GridFSFileInfo>(new BsonDocument("$sort", new BsonDocument { {"metadata.type", 1}, {"lowercaseFileName", 1} }))
 .ToListAsync();

• Kaloyan Manev: Thanks, I will try it and I will write back.
• Kaloyan Manev: Add this code to your answer if you want: return await Collection.Aggregate() .Match(f => f.Metadata["path"] == path) .AppendStage<BsonDocument>(new BsonDocument("$addFields", new BsonDocument("lowercaseFileName", new BsonDocument("$toLower", "$filename")))) .AppendStage<GridFSFileInfo>(new BsonDocument("$sort", new BsonDocument { {"metadata.type", 1}, {"lowercaseFileName", 1} })).ToListAsync()
• Kaloyan Manev: Also, there is no need to add the $hint, mongo is smart enough to use the right index.
• Ravi Shankar Bharti: Yes i would agree on that, mongodb is smart enough to use the right index

Answer 2

Did you try to set the CollationStrenght = 2?

Your code would be similar all you need is to set the Collation in the FindObject:

 var options = new FindOptions
 {
 Modifiers = new BsonDocument("$hint", "PathTypeFilenameIndex"),
 Collation = new Collation("en", strength: CollationStrength.Secondary)
 };

• Martin Perzl: here is the reference and explanation to the mentioned CollationStrength: mongodb.com/docs/manual/reference/collation/#collation-document