Cosmos DB paginated query with custom order by clause

Question

I want to do a select query in Cosmos DB that returns a maximum number of results (say 50) and then gives me the continuation token so I can continue the search where I left off.

Now let's say my query has 2 equality conditions in my where clause, e.g.

where prop1 = "a" and prop2 = "w" and prop3 = "g"

In the results that are returned, I want the records that satisfy prop1 = "a" to appear first, followed by the results that have prop2 = "w" followed by the ones with prop3 = "g".

Why do I need it? Because while I could just get all the data to my application and sort it there, I can't pull all records obviously as that would mean pulling in too much data. So if I can't order it this way in cosmos itself, in the results that I get, I might only have those records that don't have prop1 = "a" at all. Now I could keep retrying this till I get the ones with prop1 = "a" (I need this because I want to show the results with prop1 = "a" as the first set of results to the user) but I might have to pull like a 100 times to get the first record since I have a huge dataset sitting in my Cosmos DB.

How can I handle this scenario in Cosmos? Thanks!

Do you know about: docs.microsoft.com/en-us/azure/cosmos-db/sql-query-offset-limit — Andy
Yes I do, but that doesn't let me custom sort right? Like how could I sort for my scenario above to apply the order by and then use offset limit? — Arvind Sasikumar
I'll add an answer. This is possible, but there is a little work you have to do. — Andy
@Andy thank you! Any help on this will be extremely appreciated! — Arvind Sasikumar

Andy Andy · Accepted Answer · 2020-08-17T20:45:13

So if I am understanding your question correctly, you want to accomplish this:

SELECT * FROM c
WHERE
        c.prop1 = 'a'
    AND
        c.prop2 = 'b'
    AND
        c.prop3 = 'c'
ORDER BY
        c.prop1, c.prop2, c.prop3
OFFSET 0 LIMIT 25

Now, luckily you can now do this in CosmosDB SQL. But, there is a caveat. You have to set up a composite index in your collection to allow for this.

So, for this collection, my composite index would look like this:

Now, if I wanted to change it to this:

SELECT * FROM c
WHERE
        c.prop1 = 'a'
    AND
        c.prop2 = 'b'
    AND
        c.prop3 = 'c'
ORDER BY
        c.prop1 DESC, c.prop2, c.prop3
OFFSET 0 LIMIT 25

I could add another composite index to cover that use-case. You can see in your settings it's an array of arrays so you can add as many combinations as you'd like.

This should get you to where you need to be if I understood your question correctly.

Cosmos DB paginated query with custom order by clause

1 Answers