Mongo Aggregation$减法:总是为两个非空日期提供null结果

Question

我想按员工工作周年纪念日的顺序获得一份员工名单。joiningDate在员工记录中可用。在该查询中，首先计算今年的周年纪念日期，然后尝试查找今天的日期和周年纪念日期之间的差异。但是$subtract对于所有记录集都返回false。

db.employees.aggregate([{
"$match": { "deleted": false }
},
{
"$project": {
"id": 1, "firstName": 1, "lastName": 1, "empId": 1, "joiningDate": 1,
"aniversaryDate":
{
"$dateFromParts":
{
"year": { "$year": "$$NOW" },
"day": { "$dayOfMonth": "$joiningDate" },
"month": { "$month": "$joiningDate" }
}
},
"dateDifference":
{ "$subtract": ["$aniversaryDate", "$$NOW"] }

}
}, { "$sort": { "dateDifference": 1 } }]);

结果给出了这样的对象的数组：

{
"_id" : ObjectId("61a4bf6b5362437b668db2b5"),
"empId" : "1",
"firstName" : "Olive",
"lastName" : "Gunter",
"joiningDate" : ISODate("2021-10-03T16:16:58.997+05:30"),
"aniversaryDate" : ISODate("2021-10-03T05:30:00.000+05:30"),
"dayssince" : null,
"dateDifference" : null
}

Answer 1

您需要另一个$project阶段来获得从上一个$project阶段生成的aniversaryDate。

db.collection.aggregate([
{
"$match": {
"deleted": false
}
},
{
"$project": {
"id": 1,
"firstName": 1,
"lastName": 1,
"empId": 1,
"joiningDate": 1,
"aniversaryDate": {
"$dateFromParts": {
"year": {
"$year": "$$NOW"
},
"day": {
"$dayOfMonth": "$joiningDate"
},
"month": {
"$month": "$joiningDate"
}
}
},
}
},
{
$project: {
"dateDifference": {
"$subtract": [
"$aniversaryDate",
"$$NOW"
]
}
}
},
{
"$sort": {
"dateDifference": 1
}
}
])

Mongo游乐场示例

---

如果您使用的是MongoDB版本5，则可以替换为$dateDiff。

{
$project: {
"dateDifference": {
"$dateDiff": {
"startDate": "$aniversaryDate",
"endDate": "$$NOW",
"unit": "day"
}
}
}
}

Mongo游乐场示例(带$dateDiff(