CODEWITHSUNDEEP
azureazure-cosmosdb
pookie
pookie

Reputation: 4142

Azure CosmosDb: Order-by item requires a range index

I'm performing a simple query via the Azure Portal "Query Explorer".

Here is my query:

SELECT * FROM c
WHERE c.DataType = 'Fruit' 
AND c.ExperimentIdentifier = 'prod' 
AND c.Param = 'banana' 
AND Contains(c.SampleDateTime, '20171029')
ORDER BY c.SampleDateTime DESC

However, I get the exception:

Order-by item requires a range index to be defined on the corresponding index path.

There is no link to help regarding the error and I cannot make heads from tails, from that error message.

What does it mean, why is my query failing and how can I fix it?

P.S. the _ts property is no good to me as I do not want to order by the time the records were inserted.

Upvotes: 0

Views: 3299

Answers (1)

Samer Boshra
Samer Boshra

Reputation: 919

ORDER BY is served directly from the index and thus it requires the order by item to be Range indexed (as opposed to Hash indexed).

While you could only index the order-by item as range (for both numbers and string), my advice is to index all paths as range with precision of -1.

Basically, you'd need to update the indexing policy of your collection to be something like this:

    {
        "automatic": true,
        "indexingMode": "consistent",
        "includedPaths": [
            {
                "path": "/", 
                "indexes": [ 
                    { "kind": "Range", "dataType": "Number", "precision": -1 }, 
                    { "kind": "Range", "dataType": "String", "precision": -1 }
                ]
            }
        ]
    }

Upvotes: 6

Related Questions