Azure CosmosDB API for MongoDB TTL index failed to create

Priyanka P 21

Hi,

I have created an index on a collection in Azure Cosmos DB API for MongoDB 3.6 version using Shell available with in the azure cosmos as below:

db.coll.createIndex( { "_ts": 1 }, { expireAfterSeconds: 3600 } )

ERROR: createIndex is not implemented on this tool, please use your favorite Mongo framework or third party tools.

Please help what is the correct syntax to create ttl index in Azure Cosmos DB API for MongoDB.

And also it is observed that _ts field is not available in the documents of MongoDB API whereas in SQL API it is available.

GeethaThatipatri-MSFT 27,007 Reputation points Microsoft Employee

2022-01-18T22:50:03.9+00:00

Hi, @Priyanka P Thanks for posting your question in the Microsoft forum,
I would recommend referring to the MongoDB per-document TTL feature in Azure Cosmos DB | Microsoft Learn
as per internal discussion with the product team, the recommendation is to use client tools such as native mongo shell or mongo 3t.You can also try using Robo 3T or some other similar tool to connect and create TTL index.

_ts is a Cosmos DB-specific field and is not accessible from MongoDB clients. It is a reserved (system) property that contains the timestamp of the document's last modification.

please let me know if you need any additional information.
Regards
Geetha
Priyanka P 21 Reputation points

2022-01-19T11:31:25.747+00:00

Hi Geetha,

Thanks for the quick reply.
As suggested I have gone through the above link and tried to create ttl index in mongo shell:
globaldb:PRIMARY> db.coll.createIndex({"_ts":1}, {expireAfterSeconds: 10})
{
"_t" : "CreateIndexesResponse",
"ok" : 1,
"createdCollectionAutomatically" : true,
"numIndexesBefore" : 1,
"numIndexesAfter" : 4
}
but could see some unexpected token error.
I have tried to create ttl index through Mongoshell which is available in cosmos then Mongodb Compass and Studio 3T but can see below error everywhere:
"errmsg" : "The 'expireAfterSeconds' option is supported on '_ts' field only.",

But _ts is not available in the column list. Attached the error screenshots for the same.

Can you please help what are the other ways to create ttl index on cosmos mongo and also provide any links available.

Thanks
Priyanka

1 answer

GeethaThatipatri-MSFT 27,007 Reputation points Microsoft Employee

2022-01-19T23:48:54.27+00:00

Hi, @Priyanka P the above-mentioned screenshot is from the portal the recommended one is native mongo shell or mongo3t tool, not the portal. can you please give it a try using the recommended one.
globaldb:PRIMARY> db.testttl01.createIndex({"_ts":1}, {expireAfterSeconds: 10})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 2,
"numIndexesAfter" : 3,
"ok" : 1
}
globaldb:PRIMARY>
Regards
Geetha
Please sign in to rate this answer.
Priyanka P 21 Reputation points

2022-01-20T06:32:38.453+00:00

Hi Geetha,

We could not find any documentation for native Mongo shell or mongo 3T, So tried with other options.
It would be helpful if you can share any links on how to connect to Azure Cosmos DB account using native Mongo shell or mongo 3T.

Thanks
Priyanka

GeethaThatipatri-MSFT 27,007 Reputation points Microsoft Employee

2022-01-20T15:34:38.84+00:00

Hi, @Priyanka P sorry that you could not find the links.
Here you go with The mongo Shell — MongoDB Manual

Use Studio 3T to connect to Azure Cosmos DB's API for MongoDB | Microsoft Learn

Connect a MongoDB application to Azure Cosmos DB | Microsoft Learn

Regards
Geetha

GeethaThatipatri-MSFT 27,007 Reputation points Microsoft Employee

2022-01-23T18:47:37.01+00:00

Hi, @Priyanka P Did you have the chance to check the above reply, Please let us know if you are still facing the same issue?

Regards
Geetha

Priyanka P 21 Reputation points

2022-01-24T08:02:36.93+00:00

Hi Geetha,

I tried creating ttl on one collection using native Mongoshell as suggested in above link.
But could not see any index created in Azure cosmos and the data in the collection also not purged after given time in ttl.
Please find the attached screenshot for the details.

and Studio 3T we had tried before which has _ts issue, the same has been communicated in the earlier comments.

GeethaThatipatri-MSFT 27,007 Reputation points Microsoft Employee

2022-01-25T20:57:35.257+00:00

Hi @Priyanka P For a deeper investigation and immediate assistance on this issue, if you have a support plan you may file a support ticket, else we can create a one-time-free support ticket for you to work closely on this.

Regards
Geetha

Priyanka P 21 Reputation points

2022-01-26T13:10:53.337+00:00

Hi Geetha,

Can you please create a one-time-free support ticket for me asap.

Thanks
Priyanka
Sign in to comment