Fetch immediate next and previous documents based on conditions in MongoDB

Question

Background

I have the following collection:

article {
  title: String,
  slug: String,
  published_at: Date,
  ...
}

MongoDB version: 4.4.10

The problem

Given an article, I want to fetch the immediate next and previous articles depending on the published_at field of that article.

Let's say I have an article with published_at as 100. And there are a lot of articles with published_at less than 100 and a lot having published_at more than 100. I want the pipeline/query to fetch only the articles with published_at values of 99 or 101 or the nearest possible.

Attempts

Here's my aggregation pipeline:

const article = await db.article.findOne({ ... });

const nextAndPrev = db.article.aggregate([
    {
        $match: {
            $or: [
                {
                    published_at: { $lt: article.published_at },
                    published_at: { $gt: article.published_at },
                },
            ],
        },
    },
    {
        $project: { slug: 1, title: 1 },
    },
    {
        $limit: 2,
    },
]);

It gives the wrong result (two articles after the provided article), which is expected as I know it's incorrect.

Possible solutions

I can do this easily using two separate findOne queries like the following:

const next = await db.article.findOne({ published_at: { $gt: article.published_at } });
const prev = await db.article.findOne({ published_at: { $lt: article.published_at } });

But I was curious to know of any available methods to do it in a single trip to the database.

If I sort all the articles, offset it to the timestamp, and pull out the previous and next entries, that might work. I don't know the syntax.

Hey @ray, I've updated the question with a scenario/example. Does that help? — Abhijit, Oct 30 '21 at 09:27
I think your solutions returns documents whose `published_at` is **between a range**. my problem is given a date, return two documents having `published_at` immediately next and previous to that. Here, next and previous mean chronologically next and previous. — Abhijit, Oct 30 '21 at 09:41
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/238723/discussion-between-ray-and-abhijit). — ray, Oct 30 '21 at 09:42

score 6 · Answer 1 · answered Oct 30 '21 at 10:00

Starting from MongoDB v5.0,

you can use $setWindowFields to fetch immediate prev/next documents according to certain sorting/ranking.

You can get the _id of current and next document through manipulating the documents: [<prev offset>, <next offset>] field. Similarly, for OP's scenario, it would be [-1, 1] to get the prev, current and next documents at once. Perform $lookup to fetch back the documents through the _id stored in the nearIds array.

{
    "$setWindowFields": {
      "partitionBy": null,
      "sortBy": {
        "published_at": 1
      },
      "output": {
        nearIds: {
          $addToSet: "$_id",
          window: {
            documents: [
              -1,
              1
            ]
          }
        }
      }
    }
  }

Here is the Mongo playground for your reference.