azure - Cosmos DB 分页提供倍增的页面记录

Question

我有一个场景，我需要根据文档内数组中存在的元素来过滤集合。任何人都可以建议如何在文档中使用 OFFSET 和 LIMIT 以及嵌套数组

{
  "id": "abcd",
  "pqrs": 1,
  "xyz": "UNKNOWN_594",
  "arrayList": [
    {
      "Id": 2,
      "def": true
    },
    {
      "Id": 302,
      "def": true
    }
  ]
}

现在我需要从集合中过滤并获取 10 10 条记录。我尝试了以下查询

SELECT * FROM collections c
WHERE ARRAY_CONTAINS(c.arrayList , {"Id":302 },true) or ARRAY_CONTAINS(c.arrayList , {"Id":2 },true)
ORDER BY c._ts DESC 
OFFSET 10 LIMIT 10

现在，当我运行此查询时，它返回 40 条记录

Answer 1

在下一个OFFSET的每一步，RU都会继续增加，你可以使用ContinuationToken

        private static async Task QueryWithPagingAsync(Uri collectionUri)
        {
            // The .NET client automatically iterates through all the pages of query results 
            // Developers can explicitly control paging by creating an IDocumentQueryable 
            // using the IQueryable object, then by reading the ResponseContinuationToken values 
            // and passing them back as RequestContinuationToken in FeedOptions.

            List<Family> families = new List<Family>();

            // tell server we only want 1 record
            FeedOptions options = new FeedOptions { MaxItemCount = 1, EnableCrossPartitionQuery = true };

            // using AsDocumentQuery you get access to whether or not the query HasMoreResults
            // If it does, just call ExecuteNextAsync until there are no more results
            // No need to supply a continuation token here as the server keeps track of progress
            var query = client.CreateDocumentQuery<Family>(collectionUri, options).AsDocumentQuery();
            while (query.HasMoreResults)
            {
                foreach (Family family in await query.ExecuteNextAsync())
                {
                    families.Add(family);
                }
            }

            // The above sample works fine whilst in a loop as above, but 
            // what if you load a page of 1 record and then in a different 
            // Session at a later stage want to continue from where you were?
            // well, now you need to capture the continuation token 
            // and use it on subsequent queries

            query = client.CreateDocumentQuery<Family>(
                collectionUri,
                new FeedOptions { MaxItemCount = 1, EnableCrossPartitionQuery = true }).AsDocumentQuery();

            var feedResponse = await query.ExecuteNextAsync<Family>();
            string continuation = feedResponse.ResponseContinuation;

            foreach (var f in feedResponse.AsEnumerable().OrderBy(f => f.Id))
            {
               
            }

            // Now the second time around use the contiuation token you got
            // and start the process from that point
            query = client.CreateDocumentQuery<Family>(
                collectionUri,
                new FeedOptions
                {
                    MaxItemCount = 1,
                    RequestContinuation = continuation,
                    EnableCrossPartitionQuery = true
                }).AsDocumentQuery();

            feedResponse = await query.ExecuteNextAsync<Family>();

            foreach (var f in feedResponse.AsEnumerable().OrderBy(f => f.Id))
            {
               
            }
        }

要跳过特定页面，请 pfb 代码

private static async Task QueryPageByPage(int currentPageNumber = 1, int documentNumber = 1)
    {
        // Number of documents per page
        const int PAGE_SIZE = 3 // configurable;

      

        // 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<Family>> currentPage = await QueryDocumentsByPage(currentPageNumber, PAGE_SIZE, continuationToken);

            foreach (Family celeryTask in currentPage.Value)
            {
               
                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 ---");
    }

和QueryDocumentsByPage函数如下

    private static async Task<KeyValuePair<string, IEnumerable<Family>>> QueryDocumentsByPage(int pageNumber, int pageSize, string continuationToken)
    {
        DocumentClient documentClient = new DocumentClient(new Uri("https://{CosmosDB/SQL Account Name}.documents.azure.com:443/"), "{CosmosDB/SQL Account Key}");

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

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

        IQueryable<Family> filter = documentClient.CreateDocumentQuery<Family>("dbs/{Database Name}/colls/{Collection Name}", feedOptions);
        IDocumentQuery<Family> query = filter.AsDocumentQuery();

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

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

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