Azure CosmosDB REST API omits results that data explorer returns

Question

I have a reasonably large cosmos db collection (circa 1.5million records). If I run a query on an indexed field in the data explorer such as:

SELECT * FROM c WHERE ARRAY_CONTAINS(c.InnerArray, { "MyValue": 1234 }, true)

I receive the result as I expect.

If I perform the same query over the REST API I receive:

{
    "_rid": "...",
    "Documents": [],
    "_count": 0
}

This difference varies. For some documents I receive it within the portal data explorer AND the REST API for others only the Data Explorer.

I've narrowed down one difference but I'm not sure what the 'fix' is (assuming this is the problem).

If I export the 'Per-partition query metrics (CSV)' from the portal I can see that documents that return over the REST api are found within 'partition key range id' 0. Those that do not appear over the REST api (but do appear in the portal) are in 'partition key range id' 1.

Is there some additional configuration or header that I need to set over the REST api? I set request headers including x-ms-documentdb-query-enablecrosspartition and receive null continuation tokens in the response.

Additional Note: The C# SDK (in both Gateway and Direct) connection mode will return the documents that the REST API does not.

Are you consuming the Continuation Token (x-ms-continuation) response header? docs.microsoft.com/rest/api/cosmos-db/… — Matias Quaranta
@MatiasQuaranta This would appear to be the case. I was reading {{token":null, ...}} to mean there was no continuation but actually there is an only the omission of the x-ms-continuation header implies that. If you want to write that as an answer i'll set it as correct / maybe add some content — Cargowire

Matias Quaranta Matias Quaranta · Accepted Answer · 2020-01-21T15:55:12

When executing queries using the REST API, make sure you are consuming and verifying the x-ms-continuation header. Reference: https://docs.microsoft.com/rest/api/cosmos-db/common-cosmosdb-rest-response-headers

You can iterate (send more requests) until the Continuation Token is not being returned in the response.

The SDKs will have this behavior and that is the reason you were seeing this difference. It is similar to iterating over a query checking for HasMoreResults or the ContinuationToken attribute in the FeedResponse.

Azure CosmosDB REST API omits results that data explorer returns

1 Answers