根据 MongoDB 中的条件获取下一个和上一个文档 [英] Fetch immediate next and previous documents based on conditions in MongoDB

问题描述

背景

我有以下收藏:

文章 {标题:字符串，蛞蝓:字符串，发布时间:日期，...}

MongoDB版本:4.4.10

问题

给定一篇文章，我想根据该文章的 published_at 字段获取紧接的下一篇和上一篇文章.

假设我有一篇文章，published_at 为 100.并且有很多文章的 published_at 小于 100 并且很多文章的 published_at 大于 100.我希望管道/查询仅获取 published_at 值为 99 或 101 或最接近的文章.

尝试

这是我的聚合管道:

const article = await db.article.findOne({ ... });常量 nextAndPrev = db.article.aggregate([{$匹配:{$或:[{published_at: { $lt: article.published_at },published_at: { $gt: article.published_at },},],},},{$project: { slug: 1, 标题: 1 },},{$限制:2，},]);

它给出了错误的结果(提供的文章之后的两篇文章)，这是预期的，因为我知道它是不正确的.

可能的解决方案

• 我可以使用两个单独的 findOne 查询轻松做到这一点，如下所示:

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

  但我很想知道任何可用的方法可以在一次访问数据库中完成.

• 如果我对所有文章进行排序，将其偏移到时间戳，然后提取上一个和下一个条目，这可能会起作用.我不知道语法.

解决方案

从MongoDB v5.0开始，

您可以使用 $setWindowFields 根据一定的排序/排名来获取即时的上一个/下一个文档.

您可以通过操作 documents: [

, ] 字段获取当前和下一个文档的_id.同样，对于 OP 的场景，一次获取上一个、当前和下一个文档将是 [-1, 1].执行$lookup，通过nearIds数组中存储的_id取回文档.

<代码>{$setWindowFields":{partitionBy":空，排序依据":{published_at":1},输出":{附近ID:{$addToSet: "$_id",窗户: {文件:[-1,1]}}}}}

这里是 Mongo 游乐场供您参考.

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.

解决方案

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.

这篇关于根据 MongoDB 中的条件获取下一个和上一个文档的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！