CODEWITHSUNDEEP
azureazure-cosmosdb
mawtex
mawtex

Reputation: 436

Azure CosmosDB: how to ORDER BY id?

Using a vanilla CosmosDB collection (all default), adding documents like this:

{
    "id": "3",
    "name": "Hannah"
}

I would like to retrieve records ordered by id, like this:

SELECT c.id FROM c
ORDER BY c.id

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

I expect this is because /id is hash indexed and not range indexed. I've tried to change the Indexing Policy in various ways, but any change I make which would touch / or /id gets wiped when I save.

How can I retrieve documents ordered by ID?

Upvotes: 3

Views: 16229

Answers (2)

Aravind Krishna R.
Aravind Krishna R.

Reputation: 8003

The best way to do this is to store a duplicate property e.g. id2 that has the same value of id, and is indexed using a range index, then use that for sorting, i.e. query for SELECT * FROM c ORDER BY c.id2.

PS: The reason this is not supported is because id is part of a composite index (which is on partition key and row key; id is the row key part) The Cosmos DB team is working on a change that will allow sorting by id.

EDIT: new collections now support ORDER BY c.id as of 7/12/19

Upvotes: 5

jatal
jatal

Reputation: 840

I found this page CosmosDB Indexing Policies , which has the below Note that may be helpful:

Azure Cosmos DB returns an error when a query uses ORDER BY but doesn't have a Range index against the queried path with the maximum precision.

Some other information from elsewhere in the document:

Range supports efficient equality queries, range queries (using >, <, >=, <=, !=), and ORDER BY queries. ORDER By queries by default also require maximum index precision (-1). The data type can be String or Number.

Some guidance on types of queries assisted by Range queries:

Range Range over /prop/? (or /) can be used to serve the following queries efficiently:

SELECT FROM collection c WHERE c.prop = "value"

SELECT FROM collection c WHERE c.prop > 5

SELECT FROM collection c ORDER BY c.prop

And a code example from the docs also:

var rangeDefault = new DocumentCollection { Id = "rangeCollection" };

// Override the default policy for strings to Range indexing and "max" (-1) precision
rangeDefault.IndexingPolicy = new IndexingPolicy(new RangeIndex(DataType.String) { Precision = -1 });

await client.CreateDocumentCollectionAsync(UriFactory.CreateDatabaseUri("db"), rangeDefault);

Hope this helps,

J

Upvotes: 0

Related Questions