Spring MongoTemplate, issue with Criteria and Regex

Question

I'm having issue whereby i'm trying to query my mongo collection (called Projects) which contains an array. I have a query method which will check to see if the ListArray passed to method matches any of the entries in the mongo array. Now this works until there is a mismatch in the case sensitivity in the db.

I wont know if the values in the document are Upper or Lower case so I'd like to add a regex to ignore the case. However I can't figure out how to do it.

Here is the code for my query method:

 @Override
    public List<Project> findAllProjectsForShowCaseNewestFirst(Integer offset, Integer limit, List<String> techStack) {
        Query query = new Query();
        query.with(Sort.by(Sort.Direction.DESC, "createdDate"));
        if(!techStack.isEmpty()) {
            query.addCriteria(Criteria.where("projectTechStack").in(techStack));
        }
        return mongoTemplate.find(query, Project.class);
    }

I've tried the following but it throws an error:

query.addCriteria(Criteria.where("projectTechStack").in(techStack).regex("i"));

I'd be very grateful if someone can help me here.

Thanks

Edit: its worth noting I can see how this works for a simple String as seen here BUT I can't for the life of me see how that would apply to a List

Does this answer your question? [Spring Data mongo case insensitive like query](https://stackoverflow.com/questions/41746370/spring-data-mongo-case-insensitive-like-query) — Felipe, Mar 18 '21 at 22:00
Thanks for the response @Felipe, the problem is, I dont know how to apply that logic to a List of Strings i.e. $n. In my code example :query.addCriteria(Criteria.where("projectTechStack").in(techStack).regex("i")); techStack is a list array — MetaCoder, Mar 18 '21 at 22:09
If you want to apply regex to a list within a document, then use an aggregation query with [$filter](https://docs.mongodb.com/manual/reference/operator/aggregation/filter/index.html) operator. This array operator will allow you to iterate the list and you can apply the regex condition for each of the array elements. I think this will work. — prasad_, Mar 19 '21 at 04:15

score 0 · Answer 1 · answered Aug 12 '21 at 19:36

There is an alternative way for your requirement, where to yield the expected output you have to convert your IN condition to multiple OR conditions in MongoDB context as below:

MongoDB query

db.getCollection('project').find({
  "$or":[
    {
      "projectTechStack":{
        $regex:".*AVA.*",
        $options:"i"
      }
    },
    {
      "projectTechStack":{
        $regex:".*SCRIPT.*",
        $options:"i"
      }
    },
    {
      "projectTechStack":{
        $regex:".*LANG.*",
        $options:"i"
      }
    }
  ]
})

We can convert the above query in java as below

    @Override
    public List<Project> findAllProjectsForShowCaseNewestFirst(Integer offset, Integer limit, 
                                                       List<String> techStack) {
        Query query = new Query();
        query.with(Sort.by(Sort.Direction.DESC, "createdDate"));
        Criteria outerCriteria = new Criteria();
        if(!techStack.isEmpty()) {
           // We are adding each regex as criteria to list which will be used in 
           // main query with OR operator
            List<Criteria> regexCriteria = new ArrayList<>();
            for (String techStackTemp : techStack) {
                regexCriteria.add(Criteria.where("projectTechStack").regex(techStackTemp, "i"));
            }
            outerCriteria.orOperator(regexCriteria.toArray(new Criteria[0]));
            query.addCriteria(outerCriteria);
        }
        return mongoTemplate.find(query, Project.class);
    }

NOTE: The option "i" indicates case-insensitive regex search

score -1 · Answer 2 · answered Aug 12 '21 at 16:58

-1

Try thisquery.addCriteria(Criteria.where("projectTechStack").regex(".*"+techStack+".*","i"));

answered Aug 12 '21 at 16:58

O.Ndiaye

1
1

The question is to query results using a list of regex patterns but using your answer we can only query with a single regex pattern – Prasanth Rajendran Aug 12 '21 at 19:38

Spring MongoTemplate, issue with Criteria and Regex

2 Answers2