I have a mongodb collection where each document in it represent a “window” with a device_id (string), a start_time and possibly an end_time (both timestamps).

Now, given a javascript array containing objects with each object having a device_id and a timestamp, I want to construct a MongoDb query that outputs the same array but only containing objects for which there exists at least one document in the collection matching the device_id and with the timestamp being inside of its “window” (i.e. greater than the start_time and less than the end_time if end_time exists).

So given the following documents in a collection “windows”:

{
  "start_time": "2024-05-12T12:00:00Z",
  "end_time": "2024-05-12T14:00:00Z",
  "device_id:": "1"
},
{
  "start_time": "2024-05-12T07:00:00Z",
  "end_time": "2024-05-12T09:00:00Z",
  "device_id:": "2"
},
{
  "start_time": "2024-05-12T01:00:00Z",
  "end_time": "2024-05-12T03:00:00Z",
  "device_id:": "3"
}

and an array like:

const timestamps = [
   { device_id: 1, timestamp: "2024-05-13T13:00:00Z"}, 
   { device_id: 3, timestamp: "2024-05-13T13:00:00Z"},
   { device_id: 4, timestamp: "2024-05-13T13:00:00Z"}
];

would result in [{ device_id: 1, timestamp: "2024-05-13T13:00:00Z"}] since this is the only item matching the criteria.

Note that I don’t want to iterate over the array making one database query for each item but rather see if this can be done with only one database query.