Creating Query like "A is "b" and C is '' " (Spring Boot/Spring Data w/Mongo)

user_768058 0
Viewed 384

I'm making a simple application with Spring Boot (2.3.4) using MongoDB with Spring Data for MongoDB. I usually create queries for the app using the @Query annotation and it works very fine. But for an Aggregation I want to use, I built a query with the Criteria class. The criteria I need is like

where("primary").is(value).and("secondary").is("").

I need all entries where primary is equal to 'value' and secondary is empty. The query entered in MOngoDB Compass

{ $and: [ { primary: 'value' }, { secondary: ''} ] }

works as expected, but when I try to use the Criteria with Spring, it looks like the and part with the secondary is completely dropped. I get any results with 'value' in primary and with anything in secondary. This means an empty fields or anything else. Replacing the .is("") part with .regex("^$") didn't help.

This looks pretty basic to me, so what am I missing here? I don't want to replace the empty secondary with an "empty flag", because that feels wrong.

Update:

This is the code in question

Criteria crit;
if(!primary.equals(secondary)) {
    crit = where("primary").is(primary.name()).and("secondary").is(secondary.name());
} else {
    crit = where("primary").is(primary.name()).and("secondary").is("");
}
MatchOperation matchStage = Aggregation.match(crit);
GroupOperation groupStage = Aggregation.group("grouping").count().as("sum");
SortOperation sortStage = new SortOperation(Sort.by("_id"));

Aggregation aggregation = Aggregation.newAggregation(matchStage, groupStage, sortStage);
AggregationResults<TypePerGroup> results =  mongoTemplate.aggregate(aggregation, "dataCollection",  TypePerGroup.class);
spring-data-mongodb spring-boot mongodb spring java
3 Answers

This works with mongodb - Not sure what abstraction compass adds. Both queries don't generate the same json query but they are equal.

Generated query

where("primary").is(value).and("secondary").is("").

is

{"primary":value, "secondary": ""}

Perhaps compass doesn't like this variant ?

Anyways to generate query similar to what you have you input in compass you can use below code

Criteria criteria = new Criteria();
criteria.andOperator(Criteria.where("primary").is("hello"), Criteria.where("secondary").is(""));
Query query = Query.query(criteria);
user_26838140

You are not missing anything. where("primary").is(value).and("secondary").is("") is correct and is functionally equivalent to { $and: [ { primary: 'value' }, { secondary: ''} ] }. You should turn on debug level logging for MongoTemplate to see the generated query.

user_44933520

A have connected to Atlas using Mongo DBCompas and added 4 records to collection:

[{
  "primary": "A",
  "secondary": "A"
},{
  "primary": "A",
  "secondary": ""
},{
  "primary": "B",
  "secondary": "B"
},{
  "primary": "B",
  "secondary": ""
}]

both queries:

    List<Data> firstResults = mongoTemplate.query(Data.class)
            .matching(Query.query(Criteria.where("primary").is("B").and("secondary").is("")))
            .all();
    System.out.println(firstResults);

    Criteria criteria = new Criteria();
    criteria.andOperator(Criteria.where("primary").is("B"), Criteria.where("secondary").is(""));
    List<Data> secondResults = mongoTemplate.query(Data.class)
            .matching(Query.query(criteria))
            .all();
    System.out.println(secondResults);

gave the same result:

[Data{primary='B', secondary=''}]

Campfire can you please provide example of your code to analyze?

user_81610040
Related