Cosmic sorting in Cosmos DB

When you use Cosmos DB and want to perform multiple properties ordering you have to use a special extended index. Instead of classic MSSQL Cosmos DB sorting doesn’t support various properties ordering by default.

Cosmos DB price is based on RUs you are using and space your database occupies. To support all kind of sorting variants it is necessary to store all combinations of properties to sort. In e.g. we need to have a Cartesian product inside Cosmos DB engine to perform such sort.

To eliminate this Cosmos DB doesn’t construct any N > 1 sorting indexes, where N – a property in the document. To allow Cosmos DB perform sorting by several properties you need to prepare a compose index.

Assume we have the following document structure

{
    "id" : string,
    "time" : string,
    "priority" : int
}

If we try to select documents with composite ordering we will receive the following error:

select * from c order by c.time, c.priority

"Errors":[ "The order by query does not have a corresponding composite index that it can be served from." ... ]

To solve this problem, add the following composite index definition in the collection settings.

Cosmos DB two properties composite index example

Keep in mind you need to enumerate all possible ordering cases you are going to use:

time desc, priority asc
time asc, priority asc
time asc, priority desc
time desc, priority desc
priority desc, time asc
priority asc, time asc
priority asc, time desc
priority desc, time desc

As you can see it is the cartesian product N^2. All combinations are stored and occupied disc space so the cost will be increased accordingly.

Links


Read other Azure related articles by tag Azure

Leave a Reply