CODEWITHSUNDEEP
mongodbaggregation-framework
bsguedes
bsguedes

Reputation: 801

Find documents between two dates, but include documents before first and after last result

We have a collection with multiple documents ordered with respect to a given timestamp. We want to aggregate documents between two timestamps (let's say startTime and stopTime): that is a simple match stage in our aggregation that has a query such as timestamp: {$gte: startTime, $lte: stopTime}. However, we'd like to include two extra documents in the result of this step: the closest document right before startTime, no matter how far back in time we would need to look, and also the closest document right after stopTime. Is there a way to achieve this with the aggregation framework in MongoDB?

Upvotes: 2

Views: 201

Answers (2)

ray
ray

Reputation: 15287

Chain up $unionWith with $sort and $limit: 1 to get the documents out of range.

db.collection.aggregate([
  {
    $match: {
      datetime: {
        $gte: ISODate("2022-10-18"),
        $lte: ISODate("2022-10-19")
      }
    }
  },
  {
    "$unionWith": {
      "coll": "collection",
      "pipeline": [
        {
          $match: {
            datetime: {
              $lt: ISODate("2022-10-18")
            }
          }
        },
        {
          $sort: {
            datetime: -1
          }
        },
        {
          $limit: 1
        }
      ]
    }
  },
  {
    "$unionWith": {
      "coll": "collection",
      "pipeline": [
        {
          $match: {
            datetime: {
              $gt: ISODate("2022-10-19")
            }
          }
        },
        {
          $sort: {
            datetime: 1
          }
        },
        {
          $limit: 1
        }
      ]
    }
  }
])

Here is the Mongo Playground for your reference.

Upvotes: 2

nimrod serok
nimrod serok

Reputation: 16033

One option if you are already after filtering out these documents, is using a $lookup step with a pipeline. It looks a bit clumsy after the $lookups, but I could not think about another way to continue without grouping all the documents, which is not the best way to go.

  1. $match - This is a "fake" step in order to level up with your situation. You already have it in your current pipeline, thus don't need it here
  2. $set the "$$ROOT" in order to use it latter
  3. $lookup twice in order to get your requested documents from the original collection
  4. For each document create an array of documents, in order to get the before and after out of the current documents
  5. $unwind to separate into documents
  6. $group by _id in order to remove the duplicates of the before and after documents
  7. Format
db.collection.aggregate([
  {$match: {timestamp: {$gte: startTime, $lte: stopTime}}},
  {$set: {data: "$$ROOT"}},
  {$lookup: {
      from: "collection",
      let: {},
      pipeline: [
        {$match: {timestamp: {$lt: startTime}}},
        {$sort: {timestamp: -1}},
        {$limit: 1}
      ],
      as: "before"
  }},
  {$lookup: {
      from: "collection",
      let: {},
      pipeline: [
        {$match: {timestamp: {$gt: stopTime}}},
        {$sort: {timestamp: 1}},
        {$limit: 1}
      ],
      as: "after"
  }},
  {$project: {_id: 0, data: {$concatArrays: ["$after", "$before", ["$data"]]}}},
  {$unwind: "$data"},
  {$group: {_id: "$data._id", data: {$first: "$data"}}},
  {$replaceRoot: {newRoot: "$data"}},
  {$sort: {timestamp: 1}}
])

See how it works on the playground example

Upvotes: 1

Related Questions