$text and case sensitive sorting

Working with Data
1

We are trying to do an aggregate query with a $text command. This works, but then when we try to sort the results on one of the string fields we end up with case sensitive sorting, which is not what we are hoping for. If we add collation to the aggregation, then the query fails with an error.

Is there any way to do this, or is this impossible with mongodb?

Thanks

2

I think you will need to specify the collation for the sort; documented at https://docs.mongodb.com/manual/reference/collation/. You will see that the default strength item is 3 which includes case so you will need to change it to 1.

3

I don’t see a way to specify a collation for the $sort. In fact I just found:

You cannot specify multiple collations for an operation. For example, you cannot specify different collations per field, or if performing a find with a sort, you cannot use one collation for the find and another for the sort.

:frowning: I think collation for $text has to be “simple” therefore any query that involves $text can only do a case sensitive sort. Can anyone confirm this?

I guess we’ll have to do the old hack of adding an extra field with a lowercase version of the column we’re sorting on and then sorting on that.