I have an users collection like this...

{
  "_id": "614443e922b33330542a2645",
  "userName": "John",
  "code": 1,
},
{
  "_id": "614443e922b33330542a2646",
  "userName": "Adam",
  "code": 2,
},
{
  "_id": "614443e922b33330542a2647",
  "userName": "Lily",
  "code": 3,
},
{
  "_id": "614443e922b33330542a2649",
  "userName": "Brad",
  "code": 5,
}

In this collection, the document with code 4 was deleted... Now i would like to create a new document and fill the sequence. Is there any function or operator that will detect the missing code and create it with code 4?

Thanks in advance.

CodePudding user response：

You can calculate the new code with an aggregation and insert the new document afterwards:

db.collection.aggregate([
{
 $group: {
  _id: "Test",
  arr: {
    $push: "$code"
  },
  ma: {
    $max: "$code"
  },
  mi: {
    $min: "$code"
   }
  }
  },
  {
   $project: {
    _id: 0,
    code: {
      $min: {
      "$setDifference": [
        {
          $range: [
            "$mi",
            "$ma",
            1
          ]
        },
        "$arr"
      ]
     }
    }
   }
  }
])

1. Group all codes in an array with $push and find max & min
2. Calculate the difference between the array and auto-generated array based on max and min and get the min value available for the new document to be inserted. Please, note that if your application is multithreaded if you execute at the same time this code it will provide the same result so you may think about some concurrency mechanism here or chosing random code from list of available ...

playground