Reputation: 547
I have the following collection:
article {
title: String,
slug: String,
published_at: Date,
...
}
MongoDB version: 4.4.10
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.
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.
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.
Upvotes: 3
Views: 1875
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
]
}
}
}
}
}
Upvotes: 6