question

CarloSergioCachoPacheco-9193 avatar image
0 Votes"
CarloSergioCachoPacheco-9193 asked NavtejSaini-MSFT answered

The index path corresponding to the specified order-by item is excluded. Cosmos DB (MONGO)

I'm using GridFsTemplate to store files on mongoDB, but when I do a search I get the error "The index path corresponding to the specified order-by item is excluded"
In local this works fine but not in Cosmos DB.

Example of my code

@Autowired
private GridFsTemplate fsTemplate;

.....
.....
....

GridFSFile f= fsTemplate.findOne(new Query(Criteria.where("filename").is(filename))); <----- Error in this line only in cosmos db (azure)

By default the collection fs.files has two index => _id (unique, regular) , filename uploadDate (compound , regular)

I test it in the Open Mongo Shell and I'm getting the same error

db.fs.files.find({"filename":"filename"}).sort({"filename":1})
Error: error: {
"ok" : 0,
"errmsg" : "Error=2, Details='Response status code does not indicate success: BadRequest (400); Substatus: 0; ActivityId: b65fca6d-ef9a-4966-83cf-b7f4f7a263b1; Reason: (Response status code does not indicate success: BadRequest (400); Substatus: 0; ActivityId: b65fca6d-ef9a-4966-83cf-b7f4f7a263b1; Reason: (Response status code does not indicate success: BadRequest (400); Substatus: 0; ActivityId: b65fca6d-ef9a-4966-83cf-b7f4f7a263b1; Reason: (Message: {\"Errors\":[\"The order by query does not have a corresponding composite index that it can be served from.\"]}\r\nActivityId: b65fca6d-ef9a-4966-83cf-b7f4f7a263b1, Request URI: /apps/950b32f2-6902-4b2e-9311-eca73a7e886f/services/d42e30e2-ff77-4a04-a474-2fce0e42fcb2/partitions/39edd886-7a4b-40a2-9c59-377ce7d13dd6/replicas/132640599096005533s/, RequestStats: Please see CosmosDiagnostics, SDK: Windows/10.0.17763 cosmos-netstandard-sdk/3.3.2);););",
"code" : 2,
"codeName" : "BadValue"
}
globaldb:PRIMARY> db.fs.files.find({"filename":"filename"}).sort({"filename":1}).count()
1


best regards

azure-cosmos-db
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@CarloSergioCachoPacheco-9193 We are checking this and will get back to you.

0 Votes 0 ·

Hi,

Have you found anything?

0 Votes 0 ·

1 Answer

NavtejSaini-MSFT avatar image
0 Votes"
NavtejSaini-MSFT answered

@CarloSergioCachoPacheco-9193 One of the thing we have found out is CosmosDB does not use a new composite index that is added to the policy until re-indexing is complete in the service side.

You can track the Re-indexing progress by passing a RequestOptions object that sets the PopulateQuotaInfo property to true and the retrieve the value from the "x-ms-documentdb-collection-index-IndexTransformation-progress" header.

Can you please check if the reindexing is complete and you are still getting this error.

Regards
Navtej S

· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi thanks for your response:

I did the steps to check the reindexing and I get the value of 100 So that is not the problem.

0 Votes 0 ·

Hi
The database was created two weeks ago and the collection only has two records.
I test again my app I'm getting the same error "The index path corresponding to the specified order-by item is excluded.".

best regards

0 Votes 0 ·
NavtejSaini-MSFT avatar image NavtejSaini-MSFT CarloSergioCachoPacheco-9193 ·

@CarloSergioCachoPacheco-9193 We have got response from our team:

"You are sorting on filename, you must add an index on filename like createIndex({filename:1}) or add a wildcard index. This is required for sorting in Cosmos DB.

Please check this and get back to us.

Regards
Navtej S

0 Votes 0 ·

So with that you said, Cosmosdb doesn't works like mongoDB , mongoDB doesn't get me any error about it? so is better to change it for a native mongodb instead of using CosmosDb.

Extra information: I don't call the sort, the sort is part of the findOne method implementation for (gridFsTemplate)

0 Votes 0 ·
Show more comments