Unused indexes in MongoDB-CodePudding

I have the following code snippet to list unused indexes in Mongo but how can I exclude TTL indexes from the results?

db.getMongo().getDBNames().forEach(function (dbname) {
    if (dbname != "admin") {
    db.getSiblingDB(dbname).getCollectionNames().forEach(function (cname) {
        output = db.getSiblingDB(dbname)[cname].aggregate({$indexStats:{} });  
        output.forEach(function(findUnused) { 
        if (findUnused.accesses.ops == 0 && findUnused.name != "_id_") {  
            print(dbname   " \t"   cname   " \t"   JSON.stringify(findUnused) );  
        } 
        })
    })
}})

Typical outoput is below am not sure how to exclude the TTL indexes denoted by the expireAfterSeconds field.

dbname  collection {"name":"collection","key":{"backupTime":1},"host":"server:27017","accesses":{"ops":0,"since":"2001-01-12T10:01:03.338Z"},"spec":{"v":2,"key":{"backupTime":1},"name":"collection","expireAfterSeconds":1234567}}

CodePudding user response：

In your aggregation, after the $indexStats stage, you can add a $match with "spec.expireAfterSeconds": {$exists: false} to check for TTL indexs. That is a field unique to TTL indexs.

db.orders.aggregate([
  {
    $indexStats: {}
  },
  {
    $match: {
      "spec.expireAfterSeconds": {
        $exists: false
      },
      "accesses.ops": 0,
      "name": {
        $ne: "_id_"
      }
    }
  }
])

Mongo Playground