多个文档之间的MongoDB dateDiff

本文介绍了多个文档之间的MongoDB dateDiff的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我的mongoDB中有一个集合，其中存储了提供给客户的服务以及他们的电子邮件地址，如下所示:

I have collection in my mongoDB which stores service given to customer along with their email address something like below

{ "_id" : ObjectId("56a84627f8fd4a136c0e944a"), "Vehicle" : "Honda", "ServiceSelected" : "FULL SERVICE", "FullName" : "xyz", "Email" : "xyz@xyz", "BookingTime" : ISODate("2015-12-27T06:00:00.000Z") }, { "_id" : ObjectId("56a84627f8fd4a136c0e944b"), "Vehicle" : "AUDI", "ServiceSelected" : "FLAT TYRE", "FullName" : "abc", "Email" : "abc@abc", "BookingTime" : ISODate("2015-12-26T06:00:00.000Z") }, { "_id" : ObjectId("56a84627f8fd4a136c0e944c"), "Vehicle" : "BMW", "ServiceSelected" : "OTHERS", "FullName" : "def", "Email" : "def@def", "BookingTime" : ISODate("2015-12-25T06:00:00.000Z") }, { "_id" : ObjectId("56a84627f8fd4a136c0e944d"), "Vehicle" : "BMW", "ServiceSelected" : "OTHERS", "FullName" : "def", "Email" : "def@def", "BookingTime" : ISODate("2015-12-30T06:00:00.000Z") }, { "_id" : ObjectId("56a84627f8fd4a136c0e944a"), "Vehicle" : "Honda", "ServiceSelected" : "FULL SERVICE", "FullName" : "xyz", "Email" : "xyz@xyz", "BookingTime" : ISODate("2016-01-27T06:00:00.000Z") }

我要从上述集合中获取所有已使用我们服务的文档，至少要间隔30天，即应从以上集合电子邮件"中返回:"xyz@xyz"，但不应返回电子邮件":"def@def"，因为第二个服务是在5天内获得的.

From the above collection I want to fetch all the documents that have taken our service with a gap of at-least 30 days i.e. from the above collection "Email" : "xyz@xyz" should be returned but not "Email" : "def@def" as the second service was taken with in 5 days.

我知道设计存在缺陷，可以在从应用程序插入记录时设置其他标志，但是我需要获取现有记录的数据.

I know there is flaw in the design and an additional flag can be set while inserting the record from the application but I need to fetch the data for the existing records.

推荐答案

您需要使用 $min 和 $max 运算符，它们分别在您的 $group 阶段.管道的最后一个阶段是 $redact 使用 $divide 和 $subtract 算术运算符.返回第一个服务"到最后一个服务"之间的天数大于30的那些文档.

You need to use the $min and $max operators which respectively return the minimum and maximum value for "BookingTime" in your $group stage. The last stage in the pipeline is the $redact stage where you use a simple "date" math using the $divide and $subtract arithmetic operators.to return those documents where the number of days between first "service" and last "service" is greater than 30

db.collection.aggregate( [ { "$group": { "_id": "$Email", "date1": { "$min": "$BookingTime" }, "date2": { "$max": "$BookingTime" } }}, { "$redact": { "$cond": [ { "$gte": [ { "$divide": [ { "$subtract": [ "$date2", "$date1" ] }, 1000 * 60 * 60 * 24 ]}, 30 ]}, "$$KEEP", "$$PRUNE" ] }} ])

哪个返回:

{ "_id" : "xyz@xyz", "date1" : ISODate("2015-12-27T06:00:00Z"), "date2" : ISODate("2016-01-27T06:00:00Z") }

另一种方法是使用 $cond 运算符在$project阶段可避免进行集合扫描.

Another way to do this is by using the $cond operator in a $project stage to avoid a collection scan.

db.collection.aggregate( [ { "$group": { "_id": "$Email", "date1": { "$min": "$BookingTime" }, "date2": { "$max": "$BookingTime" }, "count": { "$sum": 1 } }}, { "$match": { "count": { "$gte": 2 } } }, { "$project": { "emails": { "$cond": [ { "$gte": [ { "$divide": [ { "$subtract": [ "$date2", "$date1" ] }, 1000 * 60 * 60 * 24 ]}, 30 ] }, "$_id", false ] } }}, { "$match": { "emails": { "$ne": false } } } ])