mongodb inner loop on same collection

Question

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}}

user_2694184 · Answer

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")
      }
    }
  }
])

Store a proper date than string format
Do not find current day by today - how do you change it when day passes!!
If the above two suggestions incorporated, you could avoid $addFields.

playground

mongodb inner loop on same collection

1 Answers