13

With Cosmos DB for MongoDB API (Version 3.4), the following find query in combination with the method cursor sort seems to behave incorrectly:

db.test.find({"field1": "value1"}).sort({"field2": 1})

The error occurs, if all of the following conditions are met:

  • the default indexing policy were discarded - regardless of whether custom indexes were created afterwards using createIndex().
  • The find() query does not return any documents (Find(filter).Count() == 0)
  • The Sort document defining the sort order contains only one field. It doesn't matter, whether this field exists or has been indexed. Using two fields in the sort document returns 0 hits which is the correct behavior.

The error also occurs, if all of the following conditions are met:

  • the default indexing policy were discarded
  • The find() query returns one or more documents
  • The Sort document contains exactly one field. This field has not been indexed.

The error message:

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

The malfunction occurs only when using the CosmosDB, with native MongoDB (mongoDB Atlas, v4.0) it behaves correctly.

Azure Cosmos DB for MongoDB API with MongoDB 3.4 wire protocol (preview feature) is used. The problem occurs with both a MongoDB C#/.NET driver and the mongo shell.

In addition, the problem only occurs with find(). An equivalent aggregation pipeline containing $match and $sort behaves correctly.

Reproduction

  1. Create an Azure Cosmos DB Account with the "Azure Cosmos DB for MongoDB API". Enable the preview feature MongoDB 3.4 (Version 3.2 has not been tested).
  2. Create a new database
  3. Create a new collection, define a shard key
  4. Drop the default indexing policy (using db.test.dropIndexes() )
  5. (Optional) Create new custom indexes
  6. (Optional) Insert documents

Execute command in mongo shell (or the equivalent code with mongoDB C#/.NET driver):

    db.test.find({"field1": "value1"}).sort({"field2": 1})

Expected result

All documents that match the query criteria. If there are none, no documents should be returned.

Actual result

Error: error: { "_t" : "OKMongoResponse", "ok" : 0, "code" : 2, "errmsg" : "Message: {\"Errors\":[\"The index path corresponding to the specified order-by item is excluded.\"]}\r\nActivityId: c50cc751-0000-0000-0000-000000000000, Request URI: /apps/[...]/, RequestStats: \r\nRequestStartTime: 2019-07-11T08:58:48.9880813Z, RequestEndTime: 2019-07-11T08:58:49.0081101Z, Number of regions attempted: 1\r\nResponseTime: 2019-07-11T08:58:49.0081101Z, StoreResult: StorePhysicalAddress: rntbd://[...]/, LSN: 359549, GlobalCommittedLsn: 359548, PartitionKeyRangeId: 0, IsValid: True, StatusCode: 400, SubStatusCode: 0, RequestCharge: 1, ItemLSN: -1, SessionToken: -1#359549, UsingLocalLSN: True, TransportException: null, ResourceType: Document, OperationType: Query\r\n, SDK: Microsoft.Azure.Documents.Common/2.4.0.0", [...]

Workaround

Adding an additional "dummy" field to the sort document prevents the error:

db.test.find({"field1": "value1"}).sort({"field2": 1, "dummyfield": 1}).count()

The workaround is not satisfactory. It could falsify the result.

Am I doing something wrong, or is Cosmos DB behaving flawed here?

Mathias
  • 211
  • 3
  • 10

3 Answers3

8

According to Microsoft support, an index needs to be created on the field being sorted. The default indexes can be dropped and custom indexes created. As for the issue of not modifying the index every time a new field is added, there is no other alternative other than performing a client side sort. Unfortunately, client side sorting would take lot of CPU memory on the client side and the sort on index would take work when you would get more fields to index.

Thus I did not find a really satisfying solution:

  • Using the Default Indexing Policy. However, this can lead to a huge index.
  • Indexing all elements that need to be sorted. Every time a new element has to be indexed, this leads to a manual modification of the indexing policy.
  • Only use Client-side sort. In my opinion this leads to a strong limitation of MongoDB functionality.
  • Using aggregation frameworks instead of the find method. This leads to increased complexity and traffic.
  • Migrating to native MongoDB.
Mathias
  • 211
  • 3
  • 10
4
db.collection.createIndex ({ "$**" : 1 });
Sabito stands with Ukraine
  • 4,271
  • 8
  • 34
  • 56
Abner Matheus
  • 49
  • 1
  • 9
    Please add some explanation to your answer such that others can learn from it – Nico Haase Mar 11 '21 at 09:23
  • what happens, is that the AzureCosmoDB api, cannot do everything that mongoDB has of functionality, it needs an index to do the searches. – Abner Matheus Mar 12 '21 at 05:53
  • 5
    Please add all clarification **to your answer** by editing it – Nico Haase Mar 12 '21 at 06:34
  • 1
    I tried to add the two indexes stated in https://docs.mongodb.com/manual/core/gridfs/ , namely: ```db.fs.chunks.createIndex( { files_id: 1, n: 1 }, { unique: true } );``` and ```db.fs.files.createIndex( { filename: 1, uploadDate: 1 } );``` but this doesn't work. Your answer on the other hand did work. If someone knows why, please share your knowledge – Dirk N. Jul 06 '21 at 13:44
  • 1
    Can you please provide a *full* explanation of your answer (e.g. what it does)? – Eldar B. Jun 13 '22 at 11:38
0

CosmosDB does not fully implement the MongoDB API. Sorting is one of the limitations of using CosmosDB for MongoDB. For you to sort a collection you must index the field you are sorting with.

Emekarr
  • 111
  • 2
  • 7