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

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?

CodePudding user response：

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.

$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
$set the "$$ROOT" in order to use it latter
$lookup twice in order to get your requested documents from the original collection
For each document create an array of documents, in order to get the before and after out of the current documents
$unwind to separate into documents
$group by _id in order to remove the duplicates of the before and after documents
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

CodePudding user response：

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

db.collection.aggregate([
  {
    "$facet": {
      "smaller": [
        {
          $match: {
            datetime: {
              $lt: ISODate("2022-10-18")
            }
          }
        },
        {
          $sort: {
            datetime: -1
          }
        },
        {
          $limit: 1
        }
      ],
      "within": [
        {
          $match: {
            datetime: {
              $gte: ISODate("2022-10-18"),
              $lte: ISODate("2022-10-19")
            }
          }
        }
      ],
      "larger": [
        {
          $match: {
            datetime: {
              $gt: ISODate("2022-10-19")
            }
          }
        },
        {
          $sort: {
            datetime: 1
          }
        },
        {
          $limit: 1
        }
      ]
    }
  },
  // wrangling
  {
    $project: {
      all: {
        "$setUnion": [
          "$smaller",
          "$within",
          "$larger"
        ]
      }
    }
  },
  {
    "$unwind": "$all"
  },
  {
    "$replaceRoot": {
      "newRoot": "$all"
    }
  }
])

Here is the Mongo Playground for your reference.