Cosmos db OFFSET LIMIT clause issue

Question

I am querying a Cosmos DB using the REST API. I am having problems with the 'OFFSET LIMIT' clause. I have tested this both with my code (Dart) and Postman with the same results:

1. This query works ok:

   SELECT * FROM Faults f WHERE CONTAINS(f.Key, 'start', true)
   

2. This query does not work. Same as 1 but using OFFSET and LIMIT to get a subset:

   SELECT * FROM Faults f 
   WHERE CONTAINS(f.Key, 'start', true) 
   OFFSET 10 LIMIT 10
   

3. This query works ok. Same as 2. but with an additional filter

   SELECT * FROM Faults f 
   WHERE CONTAINS(f.Key, 'start', true) 
     AND f.Node = 'XI' 
   OFFSET 10 LIMIT 10
   

I don't get why if 1 and 3 are working 2 is not.

This is the response from query 2:

{
    "code": "BadRequest",
    "message": "The provided cross partition query can not be directly served by the gateway. This is a first chance (internal) exception that all newer clients will know how to handle gracefully. This exception is traced, but unless you see it bubble up as an exception (which only happens on older SDK clients), then you can safely ignore this message.\r\nActivityId: 5918ae0e-71ab-48a4-aa20-edd8427fe21f, Microsoft.Azure.Documents.Common/2.11.0",
    "additionalErrorInfo": "{\"partitionedQueryExecutionInfoVersion\":2,\"queryInfo\":{\"distinctType\":\"None\",\"top\":null,\"offset\":10,\"limit\":10,\"orderBy\":[],\"orderByExpressions\":[],\"groupByExpressions\":[],\"groupByAliases\":[],\"aggregates\":[],\"groupByAliasToAggregateType\":{},\"rewrittenQuery\":\"SELECT *\\nFROM Faults AS f\\nWHERE CONTAINS(f.Key, \\\"start\\\", true)\\nOFFSET 0 LIMIT 20\",\"hasSelectValue\":false},\"queryRanges\":[{\"min\":\"\",\"max\":\"FF\",\"isMinInclusive\":true,\"isMaxInclusive\":false}]}"
}

Thanks for your help

Answer 1

It seems that you can't execute cross partition query through REST API.

Probably, you have to use the official SDKs.

Cosmos DB : cross partition query can not be directly served by the gateway

Answer 2

Thanks decoy for putting me in the right direction.
OFFSET LIMIT is not supported by the REST API.
Pagination though can be achieved with the headers without using the SDK.

Set on your first request:

• x-ms-max-item-count to the amount of records you want to retrieve at a time e.g. 10.

With the response you get the header:

• x-ms-continuation String that points to the next document.

To get the next 10 documents send a new request with the headers:

• x-ms-max-item-count = 10. Just like the first one.
• x-ms-continuation set to the value you got from the response.

So it is very easy to get the next documents but it is not straightforward to get the previous ones. I had to save the document nº and 'x-ms-continuation' strings as key-value pairs and use them to implement a 'search previous' pagination. I don't know if there is an easier way to do it.