MongoDB: get document immediately preceding matched document

Question

I have a collection of documents each of which have a timestamp (let's call it 'Time').

I need to get the document immediately preceding a matched document, when ordered by 'Time'.

I know I can do this with another query per matched document, but is there any way to do it in one query?

Example:

{ _id: ObjectId("621e6abb0f91a6d1ba18d48f"),
  type: 'Warning',
  time: 2022-03-02T12:58:31.984Z }
{ _id: ObjectId("621e6abc0f91a6d1ba18d490"),
  type: 'Error',
  time: 2022-03-02T12:59:32.253Z }

So given this data I want to query for documents with type 'Error', and then also retrieve the document immediately preceding it so:

    { _id: ObjectId("621e6abc0f91a6d1ba18d490"),
      type: 'Error',
      time: 2022-03-02T12:59:32.253Z,
precedingLog:     { _id: ObjectId("621e6abb0f91a6d1ba18d48f"),
      type: 'Warning',
      time: 2022-03-02T12:58:31.984Z }
 }

Answer 1

Here is an approach you can use:

• Get all documents less than the time filter - matching document.
• Sort by time descending.
• The first document, is the the result.

Sample documents and the query:

{ "time" : ISODate("2022-03-02T00:00:00Z") }
{ "time" : ISODate("2022-03-01T00:00:00Z") }
{ "time" : ISODate("2022-02-21T00:00:00Z") }
{ "time" : ISODate("2022-02-28T00:00:00Z") }
{ "time" : ISODate("2022-02-09T00:00:00Z") }

matching_time = ISODate("2022-03-01T00:00:00Z")

db.collection.find({ time: { $lt: matching_time } })
             .sort({ time: -1 })
             .limit(1)

This returns the document with "time" : ISODate("2022-02-28T00:00:00Z") - the one preceding the matching_time.