Spring MongoDB查询文档，如果天差是x天 [英] Spring MongoDB query documents if days difference is x days

问题描述

我有一个具有两个日期字段的集合，我正在尝试查询所有相差15天的记录:

  {"_id":"someid"，"factoryNumber":123，"factoryName":一些工厂名称"，"visitType":"audit"，"personelId":"somePersonel"，"lastVisit":ISODate("2018-10-30T00:00:00.000 + 0000")，"acceptedDate":ISODate("2018-11-16T00:00:00.000 + 0000")} 

现在在某些情况下 acceptedDate 将不存在，因此我需要针对当前日期进行评估.还不完全确定如何在spring中编写此类查询以获得所需结果.

  Criteria.where("acceptedDate").(距离上次访问时间是15天，如果没有上次访问，则是当前日期) 

解决方案

从3.6开始，您必须使用新的运算符 $ expr ，该运算符允许在匹配查询或常规查询中使用聚合表达式.

您可以创建json查询，并将其直接作为 $ expr 传递春季仍不支持.

15天= 15 * 24 * 60 * 60 * 1000 = 1296000000毫秒

 查询查询= new BasicQuery("{'$ expr':{'$ gte':[{'$ subtract':[{'$ ifNull':['$ acceptedDate'，{'$ date':"+ System.currentTimeMillis()+"}]}，'$ lastVisit']}，1296000000]}})));列出<文档>结果= mongoTemplate.find(query，Document.class); 

3.4版本

如果您想使用spring mongo方法，则必须使用投影来添加用于保存比较的新字段，然后进行匹配操作和额外的投影来删除比较字段.不幸的是，仍然不支持 $ addFields AggregationOperation 手动创建一个新阶段.

  AggregationOperation addFields = new AggregationOperation(){@Override公共文档toDocument(AggregationOperationContext AggregationOperationContext){Document document = new Document("comp"，Document.parse("{'$ gte':[{'$ subtract':[{'$ ifNull':['$ acceptedDate'，{'$ date':" + System.currentTimeMillis()+}]}，'$ lastVisit']}，1296000000]}}"))));返回新的Document("$ addFields"，document);}};聚合聚合= Aggregation.newAggregation(addFields，Aggregation.match(Criteria.where("comp").is(true))Aggregation.project().Exclude("comp"););列出<文档>结果= mongoTemplate.aggregate(集合，集合名称，Document.class).getMappedResults(); 

3.2版本

  AggregationOperation编辑=新的AggregationOperation(){@Override公共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");返回new BasicDBObject("$ redact"，new BasicDBObject("$ cond"，map));};聚合聚合= Aggregation.newAggregation(redact);列表< FactoryAcceptance>结果= mongoTemplate.aggregate(aggregation，FactoryAcceptance.class，FactoryAcceptance.class).getMappedResults(); 

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)

解决方案

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();

这篇关于Spring MongoDB查询文档，如果天差是x天的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！