Cosmos db REST API order by with partitioning

2
votes

I'm using the REST API with SQL for Cosmos db and need to return a query's results ordered by a timestamp (which is stored as UNIX numeric timestamp). I'm trying to do this with a simple ORDER BY.

e.g. SELECT * FROM requests c ORDER BY c.timestamp

However with partitioning I get the error:

"Cross partition query with TOP/ORDER BY or aggregate functions is not supported."

In the collection settings the indexing precision for strings is set to -1, which was a suggestion from elsewhere, however the error is still thrown.

If I remove or set to false the x-ms-documentdb-query-enablecrosspartition header then I get:

"Cross partition query is required but disabled. Please set x-ms-documentdb-query-enablecrosspartition to true, specify x-ms-documentdb-partitionkey, or revise your query to avoid this exception."

Has anyone had any success doing this via the SQL REST API?

Thanks.

1
azureazure-cosmosdb
Make sure you disable cross partition query, the error is saying that you cannot use OrderBy with it enabled – Joshua Krstic
If I disable cross partition query then I get: "Cross partition query is required but disabled. Please set x-ms-documentdb-query-enablecrosspartition to true, specify x-ms-documentdb-partitionkey, or revise your query to avoid this exception." – Dom Robinson
Is this a very old collection? This used to be an issue before they added support for Order by and Top. – Nick Chapsas

1 Answers

4
votes

I reproduce your issue on my side.

enter image description here

However , based on this official statement, java sdk and node.js sdk are support for TOP and ORDER BY queries on partitioned collections. I test the same query via sdk and it works.

enter image description here

Update Answer:

I used the Flidder tool to observe the requests from the SDK, and I found three requests included.

One:

When I run the above sdk code, the first request as below and the exactly the same error that rest received. However , sdk will do retry for me to get the _rid property of partition.

enter image description here

Two:

enter image description here

I did not find any clear official explanation about this.However , after reading this article, I suppose the cross partition here refers to physical partitions , not logical partitions. So the request help us to get the "rid" of the physical partition which your data stored in and PartitionKeyRanges.

Three:

enter image description here

Then sdk send update request for me with additional property :x-ms-documentdb-partitionkeyrangeid. And query results returned correctly. Please notice the update sql in the last request.

I think you could emulate the requests SDK did for us to fulfill your needs.