Spring data mongo query with regex within an array

Question

I have a collection with structure somewhat like this :

{  
  "organization" : "Org1",  
  "active" : true,  
  "fields" : [  
  {  
    "key" : "key1",  
    "value" : "table"   
  },  
  {  
    "key" : "key2",  
    "value" : "Harrison"  
  }  
  ]  
}

I need to find all documents with organization : "Org1", active : true, and regex match the 'value' in fields.

In mongo shell, it works perfectly. I tried the query:

db.collection.find({"organization" : "Org1", "active" : true, "fields" : {$elemMatch : {"key" : "key2","value" : {$regex : /iso/i}}}}).pretty()

But when I tried to convert it to a Java code with Spring, it gives wrong results.
1. This one will give documents even if it didn't match the pattern:

@Query("{'organization' : ?0, 'active' : true, 'fields' : {$elemMatch : {'key' : ?1, 'value' : {$regex : ?2}}}}")
List<ObjectCollection> findFieldDataByRegexMatch(String org, String key, String pattern);

This one doesn't give any documents even though it should.

        MongoTemplate MONGO_TEMPLATE = null;
        try {
            MONGO_TEMPLATE = multipleMongoConfig.secondaryMongoTemplate();
        } catch (Exception e) {
            e.printStackTrace();
        }

        List<Criteria> criteriaListAnd = new ArrayList<Criteria>();
        Criteria criteria = new Criteria();
        String pattern = "/iso/i";

        criteriaListAnd.add(Criteria.where("organization").is("Org1"));
        criteriaListAnd.add(Criteria.where("active").is(true));
        criteriaListAnd.add(Criteria.where("fields").elemMatch(Criteria.where("key").is(key).and("value").regex(pattern)));
        criteria.andOperator(criteriaListAnd.toArray(new Criteria[criteriaListAnd.size()]));

        Query query = new Query();
        query.addCriteria(criteria);

        List<ObjectCollection> objects = MONGO_TEMPLATE.find(query, ObjectCollection.class);

What am I missing here and how should I form my query?

I think the value of active attribute "active": "true" is stored as string i.e "true" and in the query, you are passing as the Boolean value as true. — krishna Prasad
– krishna Prasad, Commented Nov 23, 2019 at 12:44
The active attribute is a boolean in my database. I will edit the post. — Ira Sinha
– Ira Sinha, Commented Nov 23, 2019 at 12:49
The issue is very strange, I have just checked the query printed by enabling DEBUG log is different than the string you are running on mongo shell, the issue is in the regex part i.e regex part is a string. i.e { "$regex" : "/iso/i" } which is causing the issue, once you remove the string notation i.e from "/iso/i" to /iso/i it work fine on mongo shell terminal as well as spring application. — krishna Prasad
– krishna Prasad, Commented Nov 23, 2019 at 13:42

krishna Prasad · Accepted Answer · 2019-11-23 14:46:20Z

4

You are making a very small mistake, in the pattern you are passing / which is the mistake, it took me half an hour to identify it, finally, I got it after enabling the debug log of spring boot.

For the first query, it should be called as below:

springDataRepository.findFieldDataByRegexMatch("Org1", "key2", "iso")

And the query should be modified in the Repository as to hanlde the case sensetivity:

@Query("{'organization' : ?0, 'active' : true, 'fields' : {$elemMatch : {'key' : ?1, 'value' : {$regex : ?2, $options: 'i'}}}}")
List<Springdata> findFieldDataByRegexMatch(String org, String key, String pattern);

The same issue in your second query also, just change String pattern = "/iso/i"; to String pattern = "iso" or String pattern = "iso.*" ;

Both will start working, For details please check the my GitHub repo https://github.com/krishnaiitd/learningJava/blob/master/spring-boot-sample-data-mongodb/src/main/java/sample/data/mongo/main/Application.java#L60

I hope this will resolve your problem.

edited Nov 23, 2019 at 14:46

answered Nov 23, 2019 at 14:24

krishna Prasad

3,8521 gold badge36 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ira Sinha Over a year ago

1. The first query still giving the wrong result. However, it is working in shell correctly 2. The second query works perfectly.

krishna Prasad Over a year ago

@IraSinha You could please try with "iso.*" in the first query.

Collectives™ on Stack Overflow

Spring data mongo query with regex within an array

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related