Spring boot mongoDB like query on numeric fields-Integer / Double

Question

I want to make LIKE query on Integer / Double field in spring boot.
Collection name : players

{
    "firstName" : "Lionel",
    "lastName" : "messi",
    "team" : "FC Barcelona",
    "salary" : 40000,
    "type" : "football"
},{
    "firstName" : : "Cristiano",
    "lastName" : "Ronaldo",
    "team" : "Real Madrid C.F.",
    "salary" : 35000,
    "type" : "football"
},{
    "firstName" : : "Neymar",
    "lastName" : "Jr",
    "team" : "Paris Saint-Germain F.C.",
    "salary" : 25000,
    "type" : "football"
},{
    "firstName" : "Luis",
    "lastName" : "Alberto",
    "team" : "FC Barcelona",
    "salary" : 25000,
    "type" : "football"
},{
    "firstName" : "Virat",
    "lastName" : "Kohali",
    "team" : "Indian Cricket Team",
    "salary" : 40000,
    "type" : "cricket"
}

And my spring java code as follows which generate query.

String game = "football";
String team = "barcelona";
Double salary = 250;

Query query = new Query();
Set<String> gameType = new HashSet<>();
List<Criteria> andCriteria = new ArrayList<>();

gameType.add(game);

andCriteria.add(Criteria.where("type").in(gameType));
andCriteria.add(Criteria.where("team").regex(team,"i"));

Criteria[] criteriaArray = new Criteria[andCriteria.size()];
criteriaArray = andCriteria.toArray(criteriaArray);
query.addCriteria(new Criteria().andOperator(criteriaArray));

List<Players> players = mongoTemplate.find(query, Players.class);

return players;

Query :

db.players.find({
  $and: [{
      "type": {
        $in: ["football"]
        }
    },
    {
      "team": {
        $regex: "barcelona",
        $options: "i"
      }
    }]
})

Above query returns me 2 documents for "type" as "football" and "team" like barcelona

{"firstName" : "Lionel", "lastName" : "messi", "team" : "FC Barcelona", "salary" : 40000, "type" : "football"},
{"firstName" : "Luis", "lastName" : "Alberto", "team" : "FC Barcelona", "salary" : 25000, "type" : "football"}

But I want query "type" as "football" and "salary" like 250 in it

db.players.find({
  $and: [{
      "type": {
        $in: ["football"]
      }
    },
    {
      $where : "/^250.*/.test(this.salary)"
    }]
})

and the returned result will be as follow.

{"firstName" : : "Neymar", "lastName" : "Jr", "team" : "Paris Saint-Germain F.C.", "salary" : 25000, "type" : "football"},
{"firstName" : "Luis", "lastName" : "Alberto", "team" : "FC Barcelona", "salary" : 25000, "type" : "football"}

Thank you in advance.

Regex works on text fields. So there is no regex "like" on numeric fields. Btw, How about using range max and min ? Something like between 250 - 250 ( fill zeros - as many zeros up to max double type value ) or store the salary field as string type. — s7vr, Jan 31 '18 at 13:21
I can't use range because when I type 250 then I want all matching records in the range of 250, 2500, 25000.. so on. And even I can't make salary as String because of some arithmetic operations. Btw thanks for the comment. Is there any other possible solution? — Rahul Ghadage, Jan 31 '18 at 13:33
oh I see. Np. You can pass all the combination of 250 as $in criteria. I don't see any other solution. — s7vr, Jan 31 '18 at 13:38
@Sumanth Varada you can use "$eq" operator for exact match. Ex. db.players.find( { salary: { $eq: 25000 } } ); — Rahul Ghadage, Jul 17 '19 at 19:00
@RahulGhadage I'm using spring data criteria like following. Can you please correct the following criteria. query.addCriteria(Criteria.where("slary").is(salary)); — Sumanth Varada, Jul 17 '19 at 19:39
@SumanthVarada you can use this approach, replace T with your class name ---> CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder(); CriteriaQuery criteriaQuery = criteriaBuilder.createQuery(T.class); Root rootObj = criteriaQuery.from(T.class); CriteriaQuery selectQuery = criteriaQuery.select(rootObj); selectQuery.where(criteriaBuilder.and(rootObj.get("salary").eq(salary))); TypedQuery typedQuery = entityManager.createQuery(selectQuery); List list = typedQuery.getResultList(); — Rahul Ghadage, Jul 18 '19 at 08:22

Rahul Ghadage · Accepted Answer · 2020-09-08T05:17:27.310

I managed to build LIKE query on double field.

Query query = new Query();
Set<String> gameType = new HashSet<>();
List<Criteria> andCriteria = new ArrayList<>();

String game = "football";
int salary = 250;   

gameType.add(game);

andCriteria.add(Criteria.where("type").in(gameType));
andCriteria.add(Criteria.where("$where").is("/^" + salary + ".*/.test(this.salary)"));

Criteria[] criteriaArray = new Criteria[andCriteria.size()];
criteriaArray = andCriteria.toArray(criteriaArray);
query.addCriteria(new Criteria().andOperator(criteriaArray));

List<Players> players = mongoTemplate.find(query, Players.class);

return players;

This code snippet generate LIKE as I posted above.
Also you can do it in simple way.

Query query = new Query();
Set<String> gameType = new HashSet<>();
        
String game = "football";
int salary = 250;
        
gameType.add(game);
        
query.addCriteria(Criteria.where("type").in(gameType));
query.addCriteria(Criteria.where("$where").is("/^" + salary + ".*/.test(this.salary)"));

List<Players> players = mongoTemplate.find(query, Players.class);

return players;

Query:

db.players.find({
  $and: [{
      "type": {
        $in: ["football"]
      }
    },
    {
      $where : "/^250.*/.test(this.salary)"
    }]
})

I hope this will help.

score 0 · Answer 2 · edited Sep 29 '20 at 22:44

0

Base on @Rahul Ghadage answer, I was able to get this working:

query.addCriteria(Criteria.where("$where").is("/" + salary + "/.test(this.salary)"));

Note that the salary in the code sample is your Java variable populated with the number that you want to match. The second salary is the name of the field in your mongo structure.

Be careful using regex in mongo with user inputs!

edited Sep 29 '20 at 22:44

Dharman

30,962
25
85
135

answered Sep 29 '20 at 22:38

Tales Kerschner

141
1
6

Spring boot mongoDB like query on numeric fields-Integer / Double

2 Answers2

Linked