The index path corresponding to the specified order-by item is excluded.

Question

Hi 🙂

I run a Spring Boot app on Azure, it connects to CosmosDB through ReactiveMongoTemplate. I can save to the collection but my query fails with the following error message:

Caused by: com.mongodb.MongoQueryException: Query failed with error code 2 and error message 'Error=2, Details='Response status code does not indicate success: BadRequest (400); Substatus: 0; ActivityId: 2f155ed8-383f-4153-8bf8-8b1fbe78ad04; Reason: (Response status code does not indicate success: BadRequest (400); Substatus: 0; ActivityId: 2f155ed8-383f-4153-8bf8-8b1fbe78ad04; Reason: (Response status code does not indicate success: BadRequest (400); Substatus: 0; ActivityId: 2f155ed8-383f-4153-8bf8-8b1fbe78ad04; Reason: (Message: {"Errors":["The index path corresponding to the specified order-by item is excluded."]}
ActivityId: 2f155ed8-383f-4153-8bf8-8b1fbe78ad04, Request URI: /apps/75202cca-ae74-443d-8d6f-d2223cd7bb8a/services/27a66028-a812-436c-bb19-4c3a566b4504/partitions/8685be1e-1385-415f-9c2d-ba15f873269b/replicas/132566649399112422s/, RequestStats: Please see CosmosDiagnostics, SDK: Windows/10.0.17763 cosmos-netstandard-sdk/3.3.2);););' on server trend-follow-strategy-brazilsouth.mongo.cosmos.azure.com:10255
at com.mongodb.internal.operation.FindOperation$3.onResult(FindOperation.java:748) ~[mongodb-driver-core-4.1.1.jar:na]

Document:

@alrt
@Builder
@ram (collection = "trades")
@CompoundIndexes({
@CompoundIndex(name = "symbol_type", def = "{'symbol' : 1, 'type': 1}")
})
public class Trade {

public enum Type {OPEN, UPDATE, CLOSED}  
  
@Id  
private String id;  
  
@Indexed(name = "symbol_index", direction = IndexDirection.ASCENDING)  
private String symbol;  
  
@Indexed(name = "date_index", direction = IndexDirection.DESCENDING)  
private Date date;  
  
//more instance variables  

}

Query:

public Mono<Trade> findLastBySymbol(String symbol) {  
	Query query = new Query();  
	query.fields().include("id", "symbol", "date", "type");  
	query.addCriteria(Criteria.where("symbol").is(symbol));  
	query.with(Sort.by(Sort.Direction.DESC, "date"));  
	query.limit(1);  
	  
 	return reactiveMongoTemplate.findOne(query, Trade.class);  
}  

Since the Index defining annotations do not seem to work, I tried the following commands on Azure Mongo Shell; they too didn't work:

use 'mydb'

db.trades.createIndex({date: -1})
db.trades.createIndex({symbol: 1})
db.trades.createIndex({'symbol' : 1, 'type': 1})

Please help?

Answer 1

@Itumeleng Manota

We checked some of the previous issues in line with this error. Here is Product team response for the same:

An index must match exactly the field being sorted on. The direction of the index is not relevant. Here your initial index was a composite index on three fields so could not be used for the sort.

Also adding the index on the field you are sorting on may resolve the issue. Its required to index the properties that we use in the sort query. If you are planning to sort on multiple fields, you need to create a compound index. Also go through these documents( Document1, Document2 ) for finding more information regarding the indexing.

Regards
Navtej S