I am looking for a query that finds documents that lower than the specific date and also will give me the row that has a ref key equals to that id
for the following data (1 collection) and the date param is "01-10-2019" (Oct 2019):(so will search first only lte : 01-10-2020
{
{
"id": 1,
"ref": null,
"date": "20-09-2020" (SEP 20)
},
{
"id": 2,
"ref": 1,
"date": "today"
},
{
"id": 3,
"ref" null,
"date": "today"
}
}
the result will be : id 1 and 2 , because 1 is meet the date criteria and id 2 has ref key that equal to id 1.
id 3 won't be received because it's not meet the date criteria
{
{
"id": 1,
"ref": 2,
"date": "one month before"
},
{
"id": 2,
"ref": 1,
"date": "today"
},
}
the issue is that I need to go on above 100-200k rows and every time work on let's say 1000 docs
how can I do that in an efficient way?
I thought about something like: 1-
col.find({date:{$lte:given_Date}}
2- run on each of the result and find
col.findOne({ref:given_id}}
You can do it using aggregation framework
db.collection.aggregate([
{
"$addFields": {//Add a field to convert your dates
"date1": {
"$cond": {
"if": {
"$eq": [
"$date",
"today"
]
},
"then": new Date(),
"else": {
"$toDate": "$date"
}
}
}
}
},
{
"$lookup": {//Join
"from": "collection",
"localField": "id",
"foreignField": "ref",
"as": "output"
}
},
{
"$match": {//match condition
"date1": {
"$lte": ISODate("2020-10-01T00:00:00Z")
}
}
}
])
$addFields.