Query to find missing sequence

Working with Data
,
2

Hello @Mamatha_Damodharan, welcome to the MongoDB Community forum!

Assuming the sample data with sequence numbers from 1 thru 10 as follows:

{ id: 1 },
{ id: 2 },
{ id: 4 },
{ id: 7 },
{ id: 9 },
{ id: 10 }

And, note the missing numbers are 3, 5, 6 and 8. You can use the following aggregation to find them:

db.collection.aggregate([
  { 
      $group: { 
          _id: null, 
          nos: { $push: "$id" } 
      }
  },
  { 
      $addFields: { 
          missing: { $setDifference: [ { $range: [ 1, 11 ] }, "$nos" ] } 
      } 
  }
])
2 Likes