MongoDB - previous and next inside subdocument

user_10669723 0
Viewed 668

I'm trying to obtain 3 subdocuments from my collection using aggregation framework (I'd like to use only aggregation because this is part of a long pipeline). I know only one property of one subdocument. I need to retrieve that subdoc and the previous and next one too. Example:

[
  {
    "title": "Title 34",
    "subDoc": {
      "subtitle": "subtitle 8",
      "order": 8
    }
  },
  {
    "title": "Title 34",
    "subDoc": {
      "subtitle": "subtitle 6",
      "order": 6
    }
  },
  {
    "title": "Title 34",
    "subDoc": {
      "subtitle": "subtitle 11",
      "order": 11
    }
  },
  {
    "title": "Title 34",
    "subDoc": {
      "subtitle": "subtitle 13",
      "order": 13
    }
  },
  {
    "title": "Title 34",
    "subDoc": {
      "subtitle": "subtitle 7",
      "order": 7
    }
  }
]

I knew only one subtitle order (for example, "11"), and I need to obtain something like this:

[
  {
    "title": "Title 34",
    "subDoc": [
      {
        "subtitle": "subtitle 8",
        "order": 8
      },
      {
        "subtitle": "subtitle 11",
        "order": 11
      },
      {
        "subtitle": "subtitle 13",
        "order": 13
      }
    ]
  }
]

I've tried with unwind, order, limit, but I can get only the previous OR the next document, not both.

Playground here: https://mongoplayground.net/p/0228GD-b6oZ

aggregation-framework mongodb
3 Answers

You can achieve your use case using the following stages:

  1. $sort the subDoc.order
  2. $group all the documents into an array
  3. Find the index of the previous and current array elements
  4. $group based on title and use $addToSet to append required docs
  5. $project stages to transform data into expected output

Edge cases to consider based on the sample doc:

  1. No previous document Eg: subDoc.order=6 ,return docs [6,7]
  2. No current document Eg: subDoc.order= 14, return empty
  3. No next document Eg: subDoc.order= 13 ,return docs [11,13]
  4. Current document exists, but no previous or next document, when document count=1

You can use the following aggregation query:

db.collection.aggregate([
  {
    "$sort": {
      "subDoc.order": 1
    }
  },
  {
    $project: {
      "title": 1,
      "order": "$subDoc.order",
      "subtitle": "$subDoc.subtitle"
    }
  },
  {
    $group: {
      _id: null,
      data: {
        $push: "$$ROOT"
      },
      count: {
        "$sum": 1
      }
    }
  },
  {
    $addFields: {
      "previousDoc": {
        $subtract: [
          {
            $indexOfArray: [
              "$data.order",
              11
            ]
          },
          1
        ]
      },
      "nextDoc": {
        $add: [
          {
            $indexOfArray: [
              "$data.order",
              11
            ]
          },
          1
        ]
      },
      currentDoc: {
        $indexOfArray: [
          "$data.order",
          11
        ]
      }
    }
  },
  {
    "$addFields": {
      "next": {
        $arrayElemAt: [
          "$data",
          "$nextDoc"
        ]
      },
      previous: {
        $arrayElemAt: [
          "$data",
          "$previousDoc"
        ]
      },
      current: {
        $arrayElemAt: [
          "$data",
          "$currentDoc"
        ]
      }
    }
  },
  {
    $unwind: "$data"
  },
  {
    $group: {
      _id: "$data.title",
      p: {
        "$addToSet": {
          $cond: [
            {
              $and: [
                {
                  $lt: [
                    "$currentDoc",
                    1
                  ]
                },
                
              ]
            },
            "$$REMOVE",
            "$previous"
          ]
        }
      },
      c: {
        "$addToSet": {
          $cond: [
            {
              $and: [
                {
                  $lt: [
                    "$currentDoc",
                    0
                  ]
                },
                {
                  $ne: [
                    "$count",
                    1
                  ]
                }
              ]
            },
            "$$REMOVE",
            "$current"
          ]
        }
      },
      n: {
        "$addToSet": {
          $cond: [
            {
              $lt: [
                "$nextDoc",
                1
              ]
            },
            "$$REMOVE",
            "$next"
          ]
        }
      },
      
    }
  },
  {
    $project: {
      subDoc: {
        "$concatArrays": [
          "$p",
          "$c",
          "$n"
        ]
      }
    }
  },
  {
    $project: {
      "subDoc.subtitle": 1,
      "subDoc.order": 1
    }
  }
])

MongoDB Playground

user_77682870

You can try,

  • $sort by order in ascending order
  • $group by title and make subDoc array of object and subDocOrder array of order field
  • $addFields to add three fields prevOrder previous order number ($indeOfArray to get previous index of 11 and subtract 1) and nextOrder next order number ($indeOfArray to get next index of 11 and add 2), sizeSubDoc size of subDoc array
let order = 11;
db.collection.aggregate([
  { $sort: { "subDoc.order": 1 } },
  {
    $group: {
      _id: "$title",
      subDoc: { $push: "$subDoc" },
      subDocOrder: { $push: "$subDoc.order" }
    }
  },
  {
    $addFields: {
      prevOrder: {
        $subtract: [{ $indexOfArray: ["$subDocOrder", order] }, 1]
      },
      nextOrder: {
        $add: [{ $indexOfArray: ["$subDocOrder", order] }, 2]
      },
      sizeSubDoc: { $size: "$subDoc" }
    }
  },
  • $project to show required fields, and get subDoc array
  • $range to get the range from 2 conditions, first check prevOrder is less than 0 then return 0 otherwise preOrder, second check nextOrder is greater than size of subDoc array then return size otherwise return nextOrder, finally we got start number and end number of range
  • $map to iterate loop of above created range array that we are going to input in map, and check in will return $arrayElemAt form range array
  {
    $project: {
      _id: 0,
      title: "$_id",
      subDoc: {
        $cond: [
          { $in: [order, "$subDocOrder"] },
          {
            $map: {
              input: {
                $range: [
                  { $cond: [{ $lt: ["$prevOrder", 0] }, 0, "$prevOrder"] },
                  { $cond: [{ $gt: ["$nextOrder", "$sizeSubDoc"] }, "$sizeSubDoc", "$nextOrder"] }
                ]
              },
              in: { $arrayElemAt: ["$subDoc", "$$this"] }
            }
          },
          []
        ]
      }
    }
  }
])

Playground

Test cases covered:

  1. What if current element is first element means don't have previous element? (this will return current and next elements only)
  2. What if current element is last element means don't have next element? (this will return current and previous elements only)
  3. What if subDoc only have one element that is current? (this will return only single current element)
  4. What is your input 11 not found? (this will return blank array)
user_89871280

I think this is the solution if you really want to do it with aggregation but the structure is modified you can map it at your convenience

db.collection.aggregate([
  {
    "$sort": {
      "subDoc.order": 1
    }
  },
  {
    "$group": {
      "_id": "$title",
      "next": {
        "$push": {
          "$cond": [
            {
              "$gt": [
                "$subDoc.order",
                11
              ]
            },
            {
              "id": "$_id",
              "item": "$$CURRENT.subDoc"
            },
            "$$REMOVE"
          ]
        }
      },
      "prev": {
        "$push": {
          "$cond": [
            {
              "$lt": [
                "$subDoc.order",
                11
              ]
            },
            {
              "id": "$_id",
              "item": "$$CURRENT.subDoc"
            },
            "$$REMOVE"
          ]
        }
      },
      "current": {
        "$push": {
          "$cond": [
            {
              "$eq": [
                "$subDoc.order",
                11
              ]
            },
            {
              "id": "$_id",
              "item": "$$CURRENT.subDoc"
            },
            "$$REMOVE"
          ]
        }
      }
    }
  },
  {
    $project: {
      _id: 1,
      next: {
        $slice: [
          "$next",
          1
        ]
      },
      prev: {
        $slice: [
          "$prev",
          -1
        ]
      },
      current: 1
    }
  }
])

The output looks like this

[
  {
    "_id": "Title 34",
    "current": [
      {
        "id": "33",
        "item": {
          "order": 11,
          "subtitle": "subtitle 11"
        }
      }
    ],
    "next": [
      {
        "id": "15",
        "item": {
          "order": 12,
          "subtitle": "subtitle 12"
        }
      }
    ],
    "prev": [
      {
        "id": "34",
        "item": {
          "order": 8,
          "subtitle": "subtitle 8"
        }
      }
    ]
  }
]
user_123779720
Related