有一個架構
const schemaDB = mongoose.Schema({
product:{
type: String,
},
DateOfInstallation: {
type: Date
},
standardWarranty: {
type: Number
},
extendedWarranty: {
type: Number
},
AMC:{
type: Number
}
})
我需要獲取從今天起 1 個月內到期的所有產品的串列。
total_years_to_Add = standardWarranty extendedWarranty AMC
date_exp = DateOfInstallation addYears(total_years_to_Add)
date_diff = date_exp - today's Date
如果 date_diff 小于月份，則回傳資料
請幫助除錯給定的 mongodb 聚合查詢（在 mongoose 中實作）
const datas = await DBModel.aggregate([
{
$match: {
total: { $add: ['$standardWarranty','$extendedWarranty','$AMC']},
newdate: { $dateAdd: {
startDate: '$DateOfInstallation',
unit: "year",
amount: "$$total"
}},
$lte: [{$dateDiff:{
startDate: "$$newdate",
endDate: new Date(),
unit: "month"
}},1]
}},
{ $project:{
"DateOfInstallation":1, "standardWarranty":1, "extendedWarranty":1,"AMC":1
}},
{$sort:{
"DateOfInstallation": 1
}}
])
我知道 $add 不能在 $match 中使用，誰能幫忙查詢一下
uj5u.com熱心網友回復：
你的方向是正確的。但是，您必須使用$expr：
db.collection.aggregate([
{
$match: {
$expr: {
$lte: [
{
$dateDiff: {
startDate: {
$dateAdd: {
startDate: "$DateOfInstallation",
unit: "year",
amount: {
$add: [
"$standardWarranty",
"$extendedWarranty",
"$AMC"
]
}
}
},
endDate: new Date(),
unit: "month"
}
}, 1
]
}
}
},
{
$project: {
"DateOfInstallation": 1,
"standardWarranty": 1,
"extendedWarranty": 1,
"AMC": 1
}
},
{ $sort: { "DateOfInstallation": 1 } }
])
或者更容易理解：
db.collection.aggregate([
{
$set: {
total: {
$add: [
"$standardWarranty",
"$extendedWarranty",
"$AMC"
]
}
}
},
{
$set: {
newdate: {
$dateAdd: {
startDate: "$DateOfInstallation",
unit: "year",
amount: "$total"
}
}
}
},
{
$set: {
dateDiff: {
$dateDiff: {
startDate: "$newdate",
endDate: new Date(),
unit: "month"
}
}
}
},
{ $match: { "$dateDiff": { $lt: 1 } } },
{
$project: {
"DateOfInstallation": 1,
"standardWarranty": 1,
"extendedWarranty": 1,
"AMC": 1
}
},
{ $sort: { "DateOfInstallation": 1 } }
])
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/525891.html
標籤：节点.jsmongodb猫鼬mongodb查询聚合框架