Azure Cosmos Db, select after row?

Question

I'm trying to select some rows after x rows, something like:

SELECT * from collection WHERE ROWNUM >= 235 and ROWNUM <= 250

Unfortunately it looks like ROWNUM isn't resolved in azure cosmos db.

Is there another way to do this? I've looked at using continuation tokens but it's not helpful if a user skips to page 50, would I need to keep querying with continuation tokens to get to page 50?

I've tried playing around with the page size option but that has some limitations in terms of how many things it can return at any one time.

What do you mean it looks like ROWNUM isn't resolved in azure cosmos db? Any error? Any more details? — Jay Gong, Jul 10 '18 at 01:31
I get an error saying rownum does not exist when I make the SQL query — meds, Jul 10 '18 at 01:39
Generally,continuation token is used combined with maxItemCount parameter. You mean your continuation token doesn't works after 50 page? What's your page size?And how many items you have? — Jay Gong, Jul 10 '18 at 02:17
For example I have 1,000,000 records in Azure. I want to query rows 500,000 to 500,010. I can't do SELECT * from collection WHERE ROWNUM >= 500,000 and ROWNUM <= 500,010 so how do I achieve this? — meds, Jul 10 '18 at 02:19

Jay Gong · Accepted Answer · 2018-07-11T06:44:58.840

For example I have 1,000,000 records in Azure. I want to query rows 500,000 to 500,010. I can't do SELECT * from collection WHERE ROWNUM >= 500,000 and ROWNUM <= 500,010 so how do I achieve this?

If you don't have any filters, you can't retrieve items in specific range via query sql direcly in cosmos db so far. So, you need to use pagination to locate your desire items. As I know, pagination is supported based on continuation token only so far.

Please refer to the function as below:

using JayGongDocumentDB.pojo;
using Microsoft.Azure.Documents.Client;
using Microsoft.Azure.Documents.Linq;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace JayGongDocumentDB.module
{
    class QuerySample1
    {
        public static async void QueryPageByPage()
        {
            // Number of documents per page
            const int PAGE_SIZE = 2;

            int currentPageNumber = 1;
            int documentNumber = 1;

            // Continuation token for subsequent queries (NULL for the very first request/page)
            string continuationToken = null;

            do
            {
                Console.WriteLine($"----- PAGE {currentPageNumber} -----");

                // Loads ALL documents for the current page
                KeyValuePair<string, IEnumerable<Student>> currentPage = await QueryDocumentsByPage(currentPageNumber, PAGE_SIZE, continuationToken);

                foreach (Student student in currentPage.Value)
                {
                    Console.WriteLine($"[{documentNumber}] {student.Name}");
                    documentNumber++;
                }

                // Ensure the continuation token is kept for the next page query execution
                continuationToken = currentPage.Key;
                currentPageNumber++;
            } while (continuationToken != null);

            Console.WriteLine("\n--- END: Finished Querying ALL Dcuments ---");
        }


        public static async Task<KeyValuePair<string, IEnumerable<Student>>> QueryDocumentsByPage(int pageNumber, int pageSize, string continuationToken)
        {
            DocumentClient documentClient = new DocumentClient(new Uri("https://***.documents.azure.com:443/"), "***");

            var feedOptions = new FeedOptions
            {
                MaxItemCount = pageSize,
                EnableCrossPartitionQuery = true,

                // IMPORTANT: Set the continuation token (NULL for the first ever request/page)
                RequestContinuation = continuationToken
            };

            IQueryable<Student> filter = documentClient.CreateDocumentQuery<Student>("dbs/db/colls/item", feedOptions);
            IDocumentQuery<Student> query = filter.AsDocumentQuery();

            FeedResponse<Student> feedRespose = await query.ExecuteNextAsync<Student>();

            List<Student> documents = new List<Student>();
            foreach (Student t in feedRespose)
            {
                documents.Add(t);
            }

            // IMPORTANT: Ensure the continuation token is kept for the next requests
            return new KeyValuePair<string, IEnumerable<Student>>(feedRespose.ResponseContinuation, documents);
        }
    }
}

Output:

Hope it helps you.

Update Answer:

No such function like ROW_NUMBER() [How do I use ROW_NUMBER()? ] in cosmos db so far. I also thought skip and top.However, top is supported and skip yet(feedback).It seems skip is already in processing and will be released in the future.

I think you could push the feedback related to the paging function.Or just take above continuation token as workaround temporarily.

Hmm this still means I will have to go through all the pages before reaching the page I want to see. Is there a way I can skip to that page using filters? Like 'select * where c.tags.mytag = true' or perhaps if I order by an index value then jump to 500,000 to 500,010? It just seems inefficientt o constantly ask the database for entries to reach the records I want. — meds, Jul 10 '18 at 09:51

Azure Cosmos Db, select after row?

1 Answers1