Requirement in High level:
I have a peculiar requirement where I want to return the specific project in which specific type of property got sold between some dates.
Detailed Requirement
I have projects collection in which we store all projects related data. That collection contains different sections in which one of the section deal with sale date information of all types of properties in that project.
So now I want to pull the projects whose "Villa" types are sold between "2018-10-01" (YYYY-mm-dd) and "2019-09-30" (YYYY-mm-dd).
My Collection Structure with Sample Data
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"
}
]
}
The final output I'm interested in when I run the query is
**Result:**
{
"project" : "highlands_1.0"
},
{
"project" : "high_value_1.0"
}
or
["highlands_1.0", "high_value_1.0"]
Note: I don't want the Projects "green_fields_1.0","green_fields_2.0", since either the sale of Villa type of property not falls between the date range or date is not present.
I came across the post here. which handles something similar to my question but the data structure of my collection made it not possible for me to aggregate the data.
My Mongo Version: 3.6.2
This seems to work. The trick is to use $filter then $match on the size of the filtered array. The output is a bit more than you asked for but you can easily add another project to remove the X arrray.
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 ] }}}
]);
There is a fancier version that uses $let which allows us to convert the string to a date just once. Note: Probably a good idea if you could use actual datetime types in your docs instead of strings.
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]}
]}
}}
}}
}}