question

ItumelengManota-8672 avatar image
0 Votes"
ItumelengManota-8672 asked NavtejSaini-MSFT commented

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

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:

@Data
@Builder
@Document(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-dbazure-spring-cloud
· 1
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,

This could possibly be a defect on Azure Cosmos DB since I just created a MongoDB Atlas cluster, and chose to have it hosted on Google Cloud; the same Java code I posted above can successfully read and write to this cluster. The only two things that I have changed are my mongodb connection properties spring.data.mongodb.database and spring.data.mongodb.uri.

0 Votes 0 ·

1 Answer

NavtejSaini-MSFT avatar image
1 Vote"
NavtejSaini-MSFT answered NavtejSaini-MSFT commented

@ItumelengManota-8672

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

· 3
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.

@ItumelengManota-8672

Please let us know if you have any further questions.

Regards
Navtej S

0 Votes 0 ·

Hi,

Thank you for your response.

So, based on my document class above, should the Single Field Index be individually defined on field symbol, and on field date, or just on field date; currently it is just on field date but it doesn't work. Also, the Composite Index is defined between fields symbol, and type. It looks as though I have a Single Field Index on the field I am sorting on already - field date.









0 Votes 0 ·

@ItumelengManota-8672

Can you confirm what error you get when you run the following commands:

use 'mydb'

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

Regards
Navtej S

0 Votes 0 ·