Spring Data Mongodb findBy In case insensitive

Question

I'm trying to create a Spring Data MongoDB repository method that could perform case insensitive search for fields from a given list, that is functionality similar to SQL 'IN' operator.

In my repository I have following methods:

User findByUsername(@Param("username") String username);
User findByUsernameIgnoreCase(@Param("username") String username);
Page<User> findByUsernameIn(@Param("username") List<String> username, Pageable pageable);
Page<User> findByUsernameIgnoreCaseIn(@Param("username") List<String> username, Pageable pageable);
Page<User> findByUsernameInIgnoreCase(@Param("username") List<String> username, Pageable pageable);
Page<User> findByUsernameInAllIgnoreCase(@Param("username") List<String> username, Pageable pageable);

I'm also exposing repository as REST resource using @RepositoryRestResource

The first three methods work very well as I expect. The case gets ignored by search when using findByUsernameIgnoreCase. The users are correclty found using the given string list in findByUsernameIn.

My problem is that I cannot combine In and IgnoreCase suffixes. The last two methods also work, but they don't ignore case of the string as I want them to do.

Is there a way to perform case insensitive IN search not falling back to explicitly specifying the MongoDB @Query?

(Tried that with spring-data-mongodb:1.8.4, spring-data-rest-core:2.4.4)

Sherysherye

Have you tried findByUsernameInAllIgnoreCase()? Not sure it will work, but it is worth a try. – Stockade

Yes, same as others, In clause works, 'AllIgnoreCase' is getting ignored. (I'll update the question) – Sherysherye

Are you able to do a standard mongodb query using $in that is case insensitive? I just wonder if mongoDB supports it directly or not, in case it is mongodb that is ignoring it, rather than Spring data. Sorry for not trying it myself. – Stockade

Didn't try to compose direct MongoDb query with 'In' but a simple case insensitive Mongo query I was able to get working using $regex – Sherysherye

I just stumbled upon the same problem. Were you able to solve it? – Phlegm

did anyone got the solution i am also facing the same issue... pls help – Lois

I am also facing same issue . did you get the ans – Scalp

Answer 1

One way to force case insensitive search is by changing the collation settings. By default strength is 3. You need to set it to 2, to make it case insensitive.

@Query(collation = "{'locale': 'en_US', 'strength': 2}")
Page<User> findByUsernameIn(@Param("username") List<String> username, Pageable pageable);

Baa

Thank you! The collation support was apparently released in Mongodb 3.4 several months after my question. :) – Sherysherye