I have a Java project and a MongoDB database. I use Morphia to query data. I need to order these data based on one field which may start with lowercase or uppercase letters (or even digits or symbols but it is not relevant here). My problem is that Morphia seems to order them based on their ASCII/Unicode value, so I get first all the lowercase rows and then all the uppercase rows. Is there a way to tell Morphia to ignore case during sorting ?
My (simplified) code so far :
Query<MyObject> query = store.query(MyObject.class);
query = query.field("myfield").equal("some value");
query.order("myfield");
query.asList();
By referring to Case insensitive sorting in MongoDB, we can set the collation with {locale: "en"}. This is a little bit counter intuitive at first glance. How come setting the locale can make the query ignore case?
So let's check the documentation of collation, the case sensitivity is actually determined by caseLevel.
Optional. Flag that determines whether to include case comparison at strength level 1 or 2.
...
If false, do not include case comparison at level 1 or 2. The default is false.
The default value is false and hence sorting will ignore case just by setting the locale.
In Morphia, we can set the Collation through FindOptions, as below.
Query<MyObject> query = store.query(MyObject.class);
query = query.field("myfield").equal("some value");
query.order("myfield");
FindOptions options = new FindOptions();
options.collation(Collation.builder().locale("en").build());
query.asList(options);