Spring Mongo criteria query with dates

Question

I have a Product pojo with online and offline date

i use the following query to get all the Products where either online date is Null or either offline date is Null . But the following query only returns me when both online and offline date are null.

query.addCriteria(Criteria.where(Product.IS).is(is).andOperator( Criteria.where(Product.OFFLINE_DATE).is(null).orOperator(Criteria.where(Product.ONLINE_DATE).is(null))));

Any clues ?

Answer 1

With the above Query object you are creating mongo query similar to

{ "productId" : "1" , "$and" : [ { "offlineDate" :  null  , "$or" : [ { "onlineDate" :  null }]}]}

where as you want to query something similar to { "productId" : "1" , "$and" : [ { "$or" : [ { "offlineDate" : null } , { "onlineDate" : null }]}]}

Create a Criteria object for orOperator and put it in to andOperator:

 Criteria orOperator = new Criteria().orOperator(Criteria.where(Product.OFFLINE_DATE).is(null),
            Criteria.where(Product.ONLINE_DATE).is(null));
    query.addCriteria(Criteria.where(Product.IS).is(is).andOperator(orOperator));

I hope this helps.