mongodb - find previous and next document in aggregation framework

user_10669723 0
Viewed 596

After applying a long pipeline to my collection I can obtain something like this:

{
    {
        "_id": "main1",
        "title": "First",
        "code": "C1",
        "subDoc": {
            "active": true,
            "sub_id": "main1sub1",
            "order": 1
        }
    },
        {
        "_id": "main2",
        "title": "Second",
        "code": "C2",
        "subDoc": {
            "active": true,
            "sub_id": "main2sub1",
            "order": 1
        }
    },
        {
        "_id": "main3",
        "title": "Third",
        "code": "C3",
        "subDoc": {
            "active": false,
            "sub_id": "main3sub1",
            "order": 1
        }
    }
}

The documents are already in the correct order. Now I have to find the document immediately preceding or following the one corresponding to a given parameter. For example, if I know { "code" : "C2" } I have to retrieve the previous document (example document with "code" : "C1").

I only need to get that document, not the others.

I know how to do it using the find () method and applying sort () and limit () in sequence, but I want to get the document directly in the aggregation pipeline, adding the necessary stages to do it.

I've tried some combinations of $ indexOfArray and $ arrayElemAt, but the first problem I encounter is that I don't have an array, it's just documents.

The second problem is that the parameter I know might sometimes be inside the subdocument, for example {"sub_id": "main3sub1"}, and again I should always get the previous or next parent document as a response (in the example, the pipeline should return document "main2" as previous document)

I inserted the collection in mongoplayground to be able to perform the tests quickly:

mongoplayground

Any idea?

aggregation-framework mongodb-query mongodb
1 Answers

If you want to retrieve only the previous document, use the following query:

First Approach:

Using $match,$sort,$limit

db.collection.aggregate([
  {
    $match: {
      code: {
        "$lt": "C2"
      }
    }
  },
  {
    "$sort": {
      code: -1
    }
  },
  {
    $limit: 1
  }
])

MongoDB Playground

Second Approach:

As specified by @ Wernfried Domscheit, Converting to array and then using $arrayElemAt

db.collection.aggregate([
  {
    $group: {
      _id: null,
      data: {
        $push: "$$ROOT"
      }
    }
  },
  {
    $addFields: {
      "index": {
        $subtract: [
          {
            $indexOfArray: [
              "$data.code",
              "C2"
            ]
          },
          1
        ]
      }
    }
  },
  {
    $project: {
      _id: 0,
      data: {
        $arrayElemAt: [
          "$data",
          "$index"
        ]
      }
    }
  },
  {
    $replaceRoot: {
      newRoot: "$data"
    }
  }
])

MongoDB Playground

user_77682870
Related