Recursive Nested Arrays - Applying query to multiple array

Question

{
  "array": [
    {
      "date1": "date1",
      "date2": "date2",
      "childArray": [
        {
          "date1": "date1",
          "date2": "date2",
          "childArray": [
            {
              "date1": "date1",
              "date2": "date2"
            }
          ]
        }
      ]
    }
  ]
}

I have document that is of the format above - I need to be able to query give a date and fetch all recursively here if the date falls between date1 and date2.

Could use a bit of help -- I've looked into aggregation and a way of potentially flattening this structure out via the aggregation and then applying the query but I haven't gotten anywhere with it.

This is a sample -- the childArray can be any number of layers deep, so need something recursive.

Answer 1

You can do this with a find and $or:

var date = ISODate("2020-08-12T23:45:00Z")
db.collection.find({
    $or: [
          {array: { $elemMatch:{
                date1: {$lte: date},
                date2: {$gte: date}
          }}},
          {"array.childArray": { $elemMatch:{
                date1: {$lte: date},
                date2: {$gte: date}
          }}},
          {"array.childArray.childArray": { $elemMatch:{
                date1: {$lte: date},
                date2: {$gte: date}
          }}}
    ]
})