Query to find missing sequence

Working with Data
,
#1

Need help to write a query to find the missing sequence no,I have collection in which ids are in sequence but sometimes due to some malfunction,few ids are missing.Hence need help to have a query which can display the missed sequence nos in mongodb collection

#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
#3

thank you for your help

#4

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.