CODEWITHSUNDEEP
azure-cosmosdbazure-cosmosdb-mongoapi
Channa
Channa

Reputation: 3737

Azure Cosmos db Timestamp index fails saying `order-by item is excluded`

I have a collection that contains documents similar to the following:

{
  "_id": ObjectId("5cde558555555"),
  "liftId": "b227eb28a555",
  "timestamp": 1558108800000
}

I applied following index-policy via azure CLI to the above collection:

{
  "indexingMode": "consistent",
  "automatic": true,
  "includedPaths": [
    {
      "path": "/liftId/?",
      "indexes": [
        {
          "kind": "Hash",
          "dataType": "String",
          "precision": 3
        }
      ]
    },
    {
      "path": "/timestamp/?",
      "indexes": [
        {
          "kind": "Range",
          "dataType": "Number",
          "precision": -1
        }
      ]
    }
  ],
  "excludedPaths": [
    {
      "path": "/"
    }
  ]
}

But when I try to query the database using sort for timestamp it fails with the following error:

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:    }

Upvotes: 1

Views: 4007

Answers (2)

Animalnots
Animalnots

Reputation: 51

Answer:

It seems you're encountering an issue specific to Azure Cosmos DB's MongoDB API when dealing with dynamically structured documents. A potent solution to this is leveraging Wildcard Indexes.

Wildcard indexes help you to index fields that are not consistent across all documents, serving ideally for collections with varied fields.

To solve your problem, create a wildcard index that encompasses all fields:

db.CollectionName.createIndex( { "$**" : 1 } )

By doing so, all fields within your collection will be indexed, mitigating the sort() method issue you're facing. It's a broad solution and it's recommended to review your indexing strategy periodically for optimal performance according to your application's querying patterns.

Learn more about wildcard indexes in Azure Cosmos DB's MongoDB API from the official documentation.

Upvotes: 3

Mohammad Tarem
Mohammad Tarem

Reputation: 201

I have a similar problem and created the index using mondo shell. Use Azure portal to get the connection string for mongo shell and create an index in the shell for timestamp.

Upvotes: 1

Related Questions