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

Itumeleng Manota 1 Reputation point
2021-02-04T00:43:49.827+00:00

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?

Azure Cosmos DB
Azure Cosmos DB
An Azure NoSQL database service for app development.
910 questions
Azure Spring Apps
Azure Spring Apps
An Azure platform as a service for running Spring Boot applications at cloud scale. Previously known as Azure Spring Cloud.
59 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Navtej Singh Saini 4,171 Reputation points
    2021-02-06T00:46:52.213+00:00

    @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