Question 116492 in Mongodb Question posted in
The official documentation can be found here.

Mongodb – 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?

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search