How do I write a query to return all my TTL Indexes only

Working with Data
1

Hi all,
I want to return all the TTL Indexes in my schema but cannot find a way to restrict to TTL only.
I found this for hashed Indexes but it doesn’t help. Tried using idxValues.hasProperty as well any ideas?

// The following finds all hashed indexes

db.adminCommand("listDatabases").databases.forEach(function(d){
   let mdb = db.getSiblingDB(d.name);
   mdb.getCollectionInfos({ type: "collection" }).forEach(function(c){
     let currentCollection = mdb.getCollection(c.name);
     currentCollection.getIndexes().forEach(function(idx){
       let idxValues = Object.values(Object.assign({}, idx.key));

        if (idxValues.includes("hashed")) {
        print("Hashed index: " + idx.name + " on " + idx.ns);
          printjson(idx);
        };
      });
   });
});
3

Hi @Claire_Moore1,

Do you have some details regarding the use case for this? In the meantime, I’ve created an example version which looks for the field expireAfterSeconds in mongosh:

DB> db.log_events.getIndexes() /// Get indexes for reference
[
  { v: 2, key: { _id: 1 }, name: '_id_' },
  {
    v: 2,
    key: { createdAt: 1 },
    name: 'createdAt_1',
    expireAfterSeconds: 10
  },
  {
    v: 2,
    key: { lastModifiedDate: 1 },
    name: 'lastModifiedDate_1',
    expireAfterSeconds: 3600
  },
  { v: 2, key: { a: 1 }, name: 'a_1' }
]
DB> db.log_events.getIndexes().forEach(function(idx){ if (Object.hasOwn(idx, 'expireAfterSeconds')){ print(idx)}}) /// Look for 'expireAfterSeconds' field
{
  v: 2,
  key: { createdAt: 1 },
  name: 'createdAt_1',
  expireAfterSeconds: 10
}
{
  v: 2,
  key: { lastModifiedDate: 1 },
  name: 'lastModifiedDate_1',
  expireAfterSeconds: 3600
}

If you are considering on running this script or similar then please test it thoroughly to verify it meets all your use case(s) and requirements.

If you’re not planning to run this via the mongosh then perhaps you could alter it accordingly for listIndexes as the getIndexes() command is a helper for mongosh.

Regards,
Jason

4

Hi Jason,

Firstly thank you for taking time to help with this.
I tried running your command in Mongo shell and I get an error

Type error:Object.hasOwn is not a function
I am using MongoDB Compass Version 1.28.4
Db is Mongo 4.2.6

The reason I want to return these Indexes is because we are planning to introduce more TTL Indexes and I want to keep track of how many we have, how much space they are taking up in RAM - we are using them to purge older data.

5

Hello,

The snippet you shared works for hashed indexes as it searches for “hashed” in the “key” field, while the attribute you are searching for “expireAfterSeconds” for TTL indexes is a top level field.

I modified the snippet you shared to search for indexes that has the field “expireAfterSeconds” and returning only those.

I tested it in my environment and confirmed it returns only TTL indexes:

db.adminCommand("listDatabases").databases.forEach(function(d){
   let mdb = db.getSiblingDB(d.name);
   mdb.getCollectionInfos({ type: "collection" }).forEach(function(c){
     let currentCollection = mdb.getCollection(c.name);
     currentCollection.getIndexes().forEach(function(idx){
     if(idx.expireAfterSeconds){
        printjson(idx);
     }
      });
   });
});

I hope you find this helpful.

1 Like
6

Mohamed,

This worked perfectly. While I had tried to supplement hashed with expireAfterSeconds I did it slightly different and I think it was returning all the indexes for a collection that had a TTL (so if collection eventlog had 3 indexes , 1 was TTL they all 3 indexes returned)
Thanks so much for your help made my day :smiley:

1 Like
7

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