How to find prev/next document after sort in MongoDB

Question

I want to find prev/next blog documents whose publish date is closest to the input document.

Below is the document structure.

Collection Examples (blog)

{
    blogCode: "B0001",
    publishDate: "2020-09-21"
},
{
    blogCode: "B0002",
    publishDate: "2020-09-22"
},
{
    blogCode: "B0003",
    publishDate: "2020-09-13"
},
{
    blogCode: "B0004",
    publishDate: "2020-09-24"
},
{
    blogCode: "B0005",
    publishDate: "2020-09-05"
}

If the input is blogCode = B0003

Expected output

{
    blogCode: "B0005",
    publishDate: "2020-09-05"
},
{
    blogCode: "B0001",
    publishDate: "2020-09-21"
}

How could I get the output result? In sql, it seems using ROW_NUMBER can solve my problem, however I can't find a solution to achieve the feature in MongoDB. The alternate solution may be reference to this answer (But, it seems inefficient). Maybe using mapReduce is another better solutions? I'm confused at the moment, please give me some help.

varman · Accepted Answer · 2020-09-21T06:15:22.603

You can go like following.

We need to compare existing date with given date. So I used $facet to categorize both dates
The original data should be one Eg : B0003. So that I just get the first element of the origin[] array to compare with rest[] array
used $unwind to flat the rest[]
Substract to get the different between both dates
Again used $facet to find previous and next dates.
Then combined both to get your expected result

NOTE : The final array may have 0<elements<=2. The expected result given by you will not find out whether its a prev or next date if there is a one element. So my suggestion is add another field to say which date it is as the mongo playground shows

[{
    $facet: {
        origin: [{
            $match: { blogCode: 'B0001' }
        }],
        rest: [{
            $match: {
                $expr: {
                    $ne: ['$blogCode','B0001']
                }
            }
        }]
    }
}, {
    $project: {
        origin: {
            $arrayElemAt: ['$origin',0]
        },
        rest: 1
    }
}, {
    $unwind: {path: '$rest'}
}, {
    $project: {
        diff: {
            $subtract: [{ $toDate: '$rest.publishDate' },{ $toDate: '$origin.publishDate'}]
        },
        rest: 1,
        origin: 1
    }
}, {
    $facet: {
        prev: [{
                $sort: {diff: -1}
            },
            {
                $match: {
                    diff: {$lt: 0 }
                }
            },
            {
                $limit: 1
            },
            {
                $addFields:{"rest.type":"PREV"}
            }
        ],
        next: [{
                $sort: { diff: 1 }
            },
            {
                $match: {
                    diff: { $gt: 0 }
                }
            },
            {
                $limit: 1
            },
            {
                $addFields:{"rest.type":"NEXT"}
            }
        ]
    }
}, {
    $project: {
        combined: {
            $concatArrays: ["$prev", "$next"]
        }
    }
}, {
    $unwind: {
        path: "$combined"
    }
}, {
    $replaceRoot: {
        newRoot: "$combined.rest"
    }
}]

Working Mongo playground

Thanks for your explanation so clearly. It solve my problems well. I also find another method which by applying [includeArrayIndex](https://docs.mongodb.com/manual/reference/operator/aggregation/unwind/#includearrayindex) — bcjohn, Sep 22 '20 at 10:49

score 1 · Answer 2 · answered Sep 22 '20 at 10:58

Inspire for the solution of varman proposed. I also find another way to solve my problem by using includeArrayIndex.

[
  {
    $sort: {
      "publishDate": 1
    },
    
  },
  {
    $group: {
      _id: 1,
      root: {
        $push: "$$ROOT"
      }
    },
    
  },
  {
    $unwind: {
      path: "$root",
      includeArrayIndex: "rownum"
    }
  },
  {
    $replaceRoot: {
      newRoot: {
        $mergeObjects: [
          "$root",
          {
            rownum: "$rownum"
          }
        ]
      }
    }
  },
  {
    $facet: {
      currRow: [
        {
          $match: {
            blogCode: "B0004"
          },
          
        },
        {
          $project: {
            rownum: 1
          }
        }
      ],
      root: [
        {
          $match: {
            blogCode: {
              $exists: true
            }
          }
        },
        
      ]
    }
  },
  {
    $project: {
      currRow: {
        $arrayElemAt: [
          "$currRow",
          0
        ]
      },
      root: 1
    }
  },
  {
    $project: {
      rownum: {
        prev: {
          $add: [
            "$currRow.rownum",
            -1
          ]
        },
        next: {
          $add: [
            "$currRow.rownum",
            1
          ]
        }
      },
      root: 1
    }
  },
  {
    $unwind: "$root"
  },
  {
    $facet: {
      prev: [
        {
          $match: {
            $expr: {
              $eq: [
                "$root.rownum",
                "$rownum.prev"
              ]
            }
          }
        },
        {
          $replaceRoot: {
            newRoot: "$root"
          }
        }
      ],
      next: [
        {
          $match: {
            $expr: {
              $eq: [
                "$root.rownum",
                "$rownum.next"
              ]
            }
          }
        },
        {
          $replaceRoot: {
            newRoot: "$root"
          }
        }
      ],
      
    }
  },
  {
    $project: {
      prev: {
        $arrayElemAt: [
          "$prev",
          0
        ]
      },
      next: {
        $arrayElemAt: [
          "$next",
          0
        ]
      },
    }
  },
]

Working Mongo playground

How to find prev/next document after sort in MongoDB

2 Answers2