java - 通过将两个集合与 spring data mongodb 数据中的条件合并来获取计数

Question

我有两个名为广告和包含的集合。我需要在该广告的当前持续月份中包含 containState 为“ACTIVE”和 lastModifiedDate 的包含数。

此外，我还有一个广告条件，即 adState 为“ACTIVE”。

advertisement document
{

    "_id" : ObjectId("1231321321321"),
    "adNumber":1
    "adState" : "ACTIVE"
    "company" : "companyABC",
    "address" : "123 new road",
    "agentName": "Agent_2"
    "lastpublish" : ISODate("2018-01-18T20:20:02.262Z"),
    "createdDate" : ISODate("2018-01-10T16:03:06.694Z"),
    "lastModifiedDate" : ISODate("2018-01-18T20:20:02.262Z"),
    "createdBy" : "yasa",
    "lastModifiedBy" : "System",   
}

    contains documents

{   

    "_id" : ObjectId("465465456456"),
    "adNumber":1
    "containId": 10
    "containState" : "ACTIVE",
    "title" : "Increase sales by 10%",
    "detials" : "buy this solution to incresce ur sale by 10%",
    "agentName": "Agent_2"
    "createdDate" : ISODate("2018-01-11T15:03:06.694Z"),
    "lastModifiedDate" : ISODate("2018-01-11T15:03:06.694Z"),
    "createdBy" : "yasa",
    "lastModifiedBy" : "System",   
}

{    

    "_id" : ObjectId("56565656555"),
    "adNumber":1
    "containId": 11
    "containState" : "IN-ACTIVE",
    "title" : "Land for sale",
    "detials" : "Land near water park for sale",
    "agentName": "Agent_2"
    "createdDate" : ISODate("2018-01-11T15:30:01.694Z"),
    "lastModifiedDate" : ISODate("2018-01-12T15:03:06.694Z"),
    "createdBy" : "yasa",
    "lastModifiedBy" : "System",   
}

{


    "_id" : ObjectId("56887423587"),
    "adNumber":1
    "containId": 12
    "containState" : "ACTIVE",
    "title" : "car for sale",
    "detials" : "BMW for sale",
    "agentName": "Agent_2"
    "createdDate" : ISODate("2018-01-11T15:45:01.690Z"),
    "lastModifiedDate" : ISODate("2018-01-11T15:45:01.690Z"),
    "createdBy" : "yasa",
    "lastModifiedBy" : "System",   
}

给出在 spring-data 和 java 中的 mongodb 上合并这个的代码示例。

还建议获得结果聚合或 map reduce 的最佳方式

聚合查询

db.advertisement.aggregate([
  {$match:{"adState": "ACTIVE", "agentName": "Agent_2"}},
  {$lookup:
   {
     from: "contains",
     localField: "adNumber",
     foreignField: "adNumber",
     as: "result_ad"
   }},
{$project:{result_ad:1}},
{$match:{"result_ad": {'$ne': []}}},  
{$unwind:"$result_ad"},
{$match:{"result_ad.containState" : "ACTIVE"}},
{$group: { _id: null, count: { $sum: 1 } }}])

Answer 1

你可以在spring mongo 2.x和Mongo 3.4版本中尝试下面的spring聚合查询。

Instant startofMonth = LocalDate.now()
                .with( TemporalAdjusters.firstDayOfMonth() ).atStartOfDay().toInstant(ZoneOffset.UTC);
Instant endofMonth = LocalDate.now()
                .with( TemporalAdjusters.lastDayOfMonth() ).atTime(LocalTime.MAX).toInstant(ZoneOffset.UTC);

MatchOperation matchOperation = Aggregation.match(Criteria.where("adState").is("ACTIVE").and("agentName").is("Agent_2"));
LookupOperation lookupOperation = LookupOperation.newLookup().
                from("contains").
                localField("adNumber").
                foreignField("adNumber").
                as("result_ad");
UnwindOperation unwindOperation = Aggregation.unwind("result_ad");
MatchOperation matchOperation2 = Aggregation.match(Criteria.where("result_ad.containState").is("ACTIVE").and("result_ad.lastModifiedDate").gte(startofMonth).lte(endofMonth));
CountOperation countOperation = Aggregation.count().as("count");

Aggregation aggregation = Aggregation.newAggregation(matchOperation, lookupOperation, unwindOperation, matchOperation2, countOperation);
Integer results = mongoOperations.aggregate(aggregation, colname, Document.class).getUniqueMappedResult().getInteger("count");