Spring MongoDB query documents if days difference is x days

Question

I have a collection that has two date fields and I am trying to query all records that have a difference of 15 days:

{
   "_id" : "someid",
   "factoryNumber" : 123,
   "factoryName" : "some factory name",
   "visitType" : "audit",
   "personelId" : "somePersonel",
   "lastVisit": ISODate("2018-10-30T00:00:00.000+0000"),
   "acceptedDate" : ISODate("2018-11-16T00:00:00.000+0000")
}

Now in some cases acceptedDate will not be there so i need to evaluate it against the current date. Not complete sure how to write this type of query in spring to obtain the desire result.

    Criteria.where("acceptedDate"). 
(is 15 days past last visit or current date if last visit not present)

Answer 1

Starting 3.6 you have to use new operator $expr which allows use of aggregation expressions inside match queries or in regular queries.

You can create the json query and pass it directly as $expr is not supported in spring yet in regular query.

15 days = 15 * 24 * 60 * 60 * 1000 = 1296000000 millis

Query query = new BasicQuery("{'$expr':{'$gte':[{'$subtract':[{'$ifNull':['$acceptedDate',{'$date':" + System.currentTimeMillis() + "}]},'$lastVisit']},1296000000]}}");
List<Document> results = mongoTemplate.find(query, Document.class);

3.4 version

If you like to use spring mongo methods you have to use projection to add new field which holds comparison and followed by match operation and extra projection to drop the comparison field. Unfortunately $addFields is still not supported so you have to use the AggregationOperation to create a new stage manually.

AggregationOperation addFields = new AggregationOperation() {
    @Override
    public Document toDocument(AggregationOperationContext aggregationOperationContext) {
        Document document = new Document("comp", Document.parse("{'$gte':[{'$subtract':[{'$ifNull':['$acceptedDate', {'$date':" + System.currentTimeMillis() + "}]},'$lastVisit']},1296000000]}}"));      
        return new Document("$addFields", document);
    }
};

Aggregation aggregation = Aggregation.newAggregation(
        addFields,
        Aggregation.match(Criteria.where("comp").is(true))
        Aggregation.project().andExclude("comp");
);

List<Document> results = mongoTemplate.aggregate(aggregation, collection name, Document.class).getMappedResults();

3.2 version

AggregationOperation redact = new AggregationOperation() {
    @Override
    public DBObject toDBObject(AggregationOperationContext aggregationOperationContext) {
    Map<String, Object> map = new LinkedHashMap<>();
    map.put("if",  BasicDBObject.parse("{'$gte':[{'$subtract':[{'$ifNull':['$acceptedDate', {'$date':" + System.currentTimeMillis() + "}]},'$lastVisit']},1296000000]}}"));
    map.put("then", "$$KEEP");
    map.put("else", "$$PRUNE");
    return new BasicDBObject("$redact", new BasicDBObject("$cond", map));
};

Aggregation aggregation = Aggregation.newAggregation(redact);

List<FactoryAcceptance> results = mongoTemplate.aggregate(aggregation, FactoryAcceptance.class, FactoryAcceptance.class).getMappedResults();