高級要求：
我有一個特殊的要求，我想回傳在某些日期之間出售特定型別財產的特定專案。
詳細要求
我有專案集合，我們在其中存盤所有與專案相關的資料。該集合包含不同的部分，其中一個部分處理該專案中所有型別物業的銷售日期資訊。
所以現在我想拉取其“別墅”型別在“2018-10-01”（YYYY-mm-dd）和“2019-09-30”（YYYY-mm-dd）之間銷售的專案。
我的帶有示例資料的集合結構
db.getCollection('projects').find({})
{
"_id" : "11a2c36923fe24da490f81626742234f",
"project" : "highlands_1.0",
"project_sale_schedule" : [
{
"project_type" : "Condo",
"dates" : "2019-06-13"
},
{
"project_type" : "Apartment",
"dates" : "2019-07-11"
},
{
"project_type" : "Villa",
"dates" : "2019-07-11",
"comments" : "No issues"
},
{
"project_type" : "Open Plot"
},
{
"project_type" : "Fenced Plot",
"dates" : "2019-10-15"
}
],
"repay_schedule" : [
{
"project_type" : "Condo",
"installment1" : "2019-11-15",
"installment2" : "2019-11-01",
"installment3" : "2019-10-25",
"installment4" : "2019-10-14"
},
{
"project_type" : "Apartment",
"installment1" : "2019-12-20",
"installment2" : "2019-12-06",
"installment3" : "2019-11-29",
"installment4" : "2019-11-15"
},
{
"project_type" : "Villa",
"installment1" : "2020-03-10",
"installment2" : "2020-03-10",
"installment3" : "2020-03-10",
"installment4" : "2020-02-26"
},
{
"project_type" : "Fenced Plot",
"installment1" : "2020-03-27",
"installment2" : "2020-03-20"
}
],
"lease_pay_schedule" : [
{
"project_type" : "Condo",
"req_date_installment1" : "2019-10-21",
"req_date_installment2" : "2019-10-14"
},
{
"project_type" : "Apartment",
"req_date_installment1" : "2019-11-15",
"req_date_installment2" : "2019-11-01"
},
{
"project_type" : "Villa",
"req_date_installment1" : "2020-01-03",
"req_date_installment2" : "2019-12-20"
},
{
"project_type" : "Fenced Plot",
"req_date_installment1" : "2020-03-24",
"req_date_installment2" : "2020-03-10",
"comments" : "3 days before HM synth start"
}
]
},
{
"_id" : "11a2c36923fe24da490f81626742234f",
"project" : "green_fields_1.0",
"project_sale_schedule" : [
{
"project_type" : "Condo",
"dates" : "2019-06-13"
},
{
"project_type" : "Apartment",
"dates" : "2019-07-11"
},
{
"project_type" : "Villa",
"dates" : "2020-07-11"
},
{
"project_type" : "Open Plot"
},
{
"project_type" : "Fenced Plot",
"dates" : "2019-10-15"
}
],
"repay_schedule" : [
{
"project_type" : "Condo",
"installment1" : "2019-11-15",
"installment2" : "2019-11-01",
"installment3" : "2019-10-25",
"installment4" : "2019-10-14"
},
{
"project_type" : "Apartment",
"installment1" : "2019-12-20",
"installment2" : "2019-12-06",
"installment3" : "2019-11-29",
"installment4" : "2019-11-15"
},
{
"project_type" : "Villa",
"installment1" : "2020-03-10",
"installment2" : "2020-03-10",
"installment3" : "2020-03-10",
"installment4" : "2020-02-26"
},
{
"project_type" : "Fenced Plot",
"installment1" : "2020-03-27",
"installment2" : "2020-03-20"
}
],
"lease_pay_schedule" : [
{
"project_type" : "Condo",
"req_date_installment1" : "2019-10-21",
"req_date_installment2" : "2019-10-14"
},
{
"project_type" : "Apartment",
"req_date_installment1" : "2019-11-15",
"req_date_installment2" : "2019-11-01"
},
{
"project_type" : "Villa",
"req_date_installment1" : "2020-01-03",
"req_date_installment2" : "2019-12-20"
},
{
"project_type" : "Fenced Plot",
"req_date_installment1" : "2020-03-24",
"req_date_installment2" : "2020-03-10",
"comments" : "3 days before HM synth start"
}
]
},
{
"_id" : "11a2c36923fe24da490f81626742234f",
"project" : "green_fields_2.0",
"project_sale_schedule" : [
{
"project_type" : "Condo",
"dates" : "2019-06-13"
},
{
"project_type" : "Apartment",
"dates" : "2019-07-11"
},
{
"project_type" : "Villa"
},
{
"project_type" : "Open Plot"
},
{
"project_type" : "Fenced Plot",
"dates" : "2019-10-15"
}
],
"repay_schedule" : [
{
"project_type" : "Condo",
"installment1" : "2019-11-15",
"installment2" : "2019-11-01",
"installment3" : "2019-10-25",
"installment4" : "2019-10-14"
},
{
"project_type" : "Apartment",
"installment1" : "2019-12-20",
"installment2" : "2019-12-06",
"installment3" : "2019-11-29",
"installment4" : "2019-11-15"
},
{
"project_type" : "Villa",
"installment1" : "2020-03-10",
"installment2" : "2020-03-10",
"installment3" : "2020-03-10",
"installment4" : "2020-02-26"
},
{
"project_type" : "Fenced Plot",
"installment1" : "2020-03-27",
"installment2" : "2020-03-20"
}
],
"lease_pay_schedule" : [
{
"project_type" : "Condo",
"req_date_installment1" : "2019-10-21",
"req_date_installment2" : "2019-10-14"
},
{
"project_type" : "Apartment",
"req_date_installment1" : "2019-11-15",
"req_date_installment2" : "2019-11-01"
},
{
"project_type" : "Villa",
"req_date_installment1" : "2020-01-03",
"req_date_installment2" : "2019-12-20"
},
{
"project_type" : "Fenced Plot",
"req_date_installment1" : "2020-03-24",
"req_date_installment2" : "2020-03-10",
"comments" : "3 days before HM synth start"
}
]
},
{
"_id" : "11a2c36923fe24da490f81626742234f",
"project" : "high_value_1.0",
"project_sale_schedule" : [
{
"project_type" : "Condo",
"dates" : "2019-06-13"
},
{
"project_type" : "Apartment",
"dates" : "2019-07-11"
},
{
"project_type" : "Villa",
"dates" : "2019-09-11"
},
{
"project_type" : "Open Plot"
},
{
"project_type" : "Fenced Plot",
"dates" : "2019-10-15"
}
],
"repay_schedule" : [
{
"project_type" : "Condo",
"installment1" : "2019-11-15",
"installment2" : "2019-11-01",
"installment3" : "2019-10-25",
"installment4" : "2019-10-14"
},
{
"project_type" : "Apartment",
"installment1" : "2019-12-20",
"installment2" : "2019-12-06",
"installment3" : "2019-11-29",
"installment4" : "2019-11-15"
},
{
"project_type" : "Villa",
"installment1" : "2020-03-10",
"installment2" : "2020-03-10",
"installment3" : "2020-03-10",
"installment4" : "2020-02-26"
},
{
"project_type" : "Fenced Plot",
"installment1" : "2020-03-27",
"installment2" : "2020-03-20"
}
],
"lease_pay_schedule" : [
{
"project_type" : "Condo",
"req_date_installment1" : "2019-10-21",
"req_date_installment2" : "2019-10-14"
},
{
"project_type" : "Apartment",
"req_date_installment1" : "2019-11-15",
"req_date_installment2" : "2019-11-01"
},
{
"project_type" : "Villa",
"req_date_installment1" : "2020-01-03",
"req_date_installment2" : "2019-12-20"
},
{
"project_type" : "Fenced Plot",
"req_date_installment1" : "2020-03-24",
"req_date_installment2" : "2020-03-10",
"comments" : "3 days before HM synth start"
}
]
}
運行查詢時我感興趣的最終輸出是
**Result:**
{
"project" : "highlands_1.0"
},
{
"project" : "high_value_1.0"
}
or
["highlands_1.0", "high_value_1.0"]
注意：我不想要專案"green_fields_1.0","green_fields_2.0"，因為別墅型別的財產的銷售不在日期范圍內或日期不存在。
我在這里看到了這個帖子。它處理類似于我的問題的問題，但我的集合的資料結構使我無法聚合資料。
我的 Mongo 版本： 3.6.2
uj5u.com熱心網友回復：
這似乎有效。訣竅是使用$filterthen$match過濾陣列的大小。輸出比您要求的多一點，但您可以輕松添加另一個專案來洗掉X陣列。
var sdate = ISODate("2018-10-01");
var edate = ISODate("2019-09-30");
c = db.foo.aggregate([
{$project: {
project: true, // carry the project field into the output...
X: {$filter: {
input: "$project_sale_schedule",
as: "zz",
cond: {$and:[
{$eq: ['$$zz.project_type',"Villa"]}
,{$gte: [{$dateFromString: { dateString: "$$zz.dates", format: "%Y-%m-%d"}},sdate]}
,{$lt: [{$dateFromString: { dateString: "$$zz.dates", format: "%Y-%m-%d"}},edate]}
]
}
}}
}}
,{$match: {$expr: {$gt: [ {$size:"$X"}, 0 ] }}}
]);
有一個更高級的版本，$let它允許我們將字串轉換為日期一次。注意：如果您可以在檔案中使用實際日期時間型別而不是字串，這可能是個好主意。
X: {$filter: {
input: "$project_sale_schedule",
as: "zz",
cond: {$let: {
vars: {dd: {$dateFromString: { dateString: "$$zz.dates", format: "%Y-%m-%d"}}},
in: {$and:[
{$eq: ['$$zz.project_type',"Villa"]}
,{$gte: ["$$dd",sdate]}
,{$lt: ["$$dd",edate]}
]}
}}
}}
}}
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/377419.html