Abhijit
Abhijit

Reputation: 547

Fetch immediate next and previous documents based on conditions in MongoDB

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

Upvotes: 3

Views: 1875

Answers (1)

ray
ray

Reputation: 15257

Starting from MongoDB v5.0, you can use $setWindowFields to fetch immediate prev/next documents according to certain sorting/ranking.

You can get the current and next document by manipulating the documents: [<prev offset>, <next offset>] field. Similarly, for OP's scenario, it would be [-1, 1] to get the previous, current and next documents at once.

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

Mongo playground

Upvotes: 6

Related Questions