Tables API - returning results sorted by RowKey

[freakyfriday]

I need to return my results in lexicographical order as per the current Azure Tables Storage API. Currently this document states that in CosmosDB using the Tables API "Query results returned by the Table API aren't sorted in partition key/row key order as they're in Azure Table storage" - https://docs.microsoft.com/en-us/azure/cosmos-db/faq#where-is-table-api-not-identical-with-azure-table-storage-behavior

Is this a mistake in the documentation as currently there is a Cosmos DB Tables API document that states "The Table service returns entities sorted in ascending order based on PartitionKey and then by RowKey. " - https://docs.microsoft.com/en-us/azure/cosmos-db/table-storage-design-guide#sorting-data-in-the-table-service

There is also a recommendation that uses the Log Tail Pattern which suggests results are returned in order of RowKey? - https://docs.microsoft.com/en-us/azure/cosmos-db/table-storage-design-guide#log-tail-pattern

---

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

• ID: 75154d08-47ff-fa53-1dab-73f67208a864
• Version Independent ID: dcb50b3c-b00b-cee8-3dfe-7f5636483f0b
• Content: Azure Cosmos DB frequently asked questions
• Content Source: articles/cosmos-db/faq.md
• Service: cosmos-db
• GitHub Login: @SnehaGunda
• Microsoft Alias: sngun

[Mike-Ubezzi-MSFT]

@freakyfriday Thank you very much for your interest in Azure Cloud. We are currently investigating and will update you with our findings.

[SnehaGunda]

@freakyfriday Query results returned by the Table API aren't sorted in partition key/row key order as they're in Azure Table storage is the right statement.

The https://docs.microsoft.com/en-us/azure/cosmos-db/table-storage-design-guide doc was initially written for Table storage only. Later when Table API in Cosmos DB and Azure Table storage are merged, the docs were brought to Cosmos DB space. I think there are some issues with the existing Table API docs and we are in the process of revamping our docs, will get to the Table API docs soon.

[freakyfriday]

@SnehaGunda Thanks for confirming this as there's a lot of confusion around the internet on this topic. People are under the impression that results returned by the CosmosDB Tables API are sorted lexicographically as they are in Azure Table Storage.

Are there any plans to address this basic functionality in the near future as not being able to return results in at least ascending order is a deal breaker? Sorting of some kind is a pretty basic requirement of any modern database.

I would like to suggest that if an index is applied to the PartitionKey/RowKey then results are automatically sorted lexicographically when a PartitionKey or range query is returned by CosmosDB using the Tables API.

[SnehaGunda]

@freakyfriday we will fix the doc to remove confusion.

Wrt to addressing the functionality, can you please vote or submit a request in the uservoice: https://feedback.azure.com/forums/263030-azure-cosmos-db it will help us prioritize developing the feature.

[CHEEKATLAPRADEEP-MSFT-zz]

@freakyfriday We will now proceed to close this thread. If there are further questions regarding this matter, please comment and we will gladly continue the discussion.

[marxxxx]

@CHEEKATLAPRADEEP-MSFT So in fact, if i need sorted data there is no way to achieve this with Cosmos DB Table API, making it intrinsically unsuitable for time series data. Is this assumption correct?

[freakyfriday]

@marxxxx according to the following comment It looks like the latest Dot Net CosmosDB Tables SDK version 0.11.0-preview has an OrderBy property: #26228 (comment)

Sample code:
https://github.com/Azure-Samples/azure-cosmos-table-dotnet-core-getting-started/blob/f5b352fec591a4ce702cfc7ef333480bdbd4b366/CosmosTableSamples/AdvancedSamples.cs#L390

Hopefully the other libraries like the Storage SDK for Javascript will be updated soon. Good to know it's now available. Shame the info is buried so deep in comments!

@CHEEKATLAPRADEEP-MSFT can you please start the process of updating the documentation to include this feature? Thanks

[senthilkjeya]

Is OrderBy available in Java client library?
i checked the latest version (8.1.0) of azure-storage artifact but can't see the OrderBy function.

[marxxxx]

@freakyfriday @CHEEKATLAPRADEEP-MSFT I tried the samples provided with Version 1.0.0.0 of the library https://www.nuget.org/packages/Microsoft.Azure.Cosmos.Table.

However, as soon as i apply the OrderBy method, i get the following error:

Request Information
RequestID:082bba8a-5d9c-44ba-a3db-fe691f3981fc
RequestCharge:0
RequestDate:
StatusMessage:BadRequest
ErrorCode:
ErrorMessage:Message: {"errors":[{"severity":"Error","location":{"start":26,"end":28},"code":"SC1001","message":"Syntax error, incorrect syntax near 'by'."}]}
ActivityId: 082bba8a-5d9c-44ba-a3db-fe691f3981fc, Microsoft.Azure.Documents.Common/2.2.0.0, Windows/10.0.17134 documentdb-netcore-sdk/2.1.3

When i omit the OrderBy method, the result set is returned without error.

My CosmosDB Table API Instance is running in the West Europe region. Could it be the case that the server side updates are not yet rolled out here?

(Please let me know if there is another issue to post my comment, as this one is already closed).

[Mike-Ubezzi-MSFT]

@marxxxx The Microsoft.Azure.Cosmos.Table library is based upon .NETStandard 2.0 and not .NETCore, which looks to be the runtime your client is using?!

[marxxxx]

@Mike-Ubezzi-MSFT exactly, the log was coming from a. NET core 2.2 based console application. I don't fully understand your comment though. .NET Core 2.2 fully implements .NET Standard 2.0, so i don't see why this should be an issue.

[Mike-Ubezzi-MSFT]

@marxxxx Can you open a new issue. This specific GitHub issue was opened back in November and this issue needs to be revisited I see.

[marxxxx]

@Mike-Ubezzi-MSFT Would love to do so but could you point me to an appropriate place? I think it's not really a docs issue.

[freakyfriday]

@Mike-Ubezzi-MSFT What about OrderBy in the azure-storage-node library. I've raised it with the team but they said to email askcosmosdb@microsoft.com (whom haven't replied to my query sent on March 26 2019). Would love to migrate my Table Storage over to CosmosDB but without any ability to sort the way Tables Storage does on RowKey it's useless for my needs.

Azure/azure-storage-node#571

[Mike-Ubezzi-MSFT]

@freakyfriday Much confusion and may questions around the Cosmos Table API versus Table Storage API. To see what are the currently query support operators for Cosmos Table API. The Uservoice forum is the appropriate channel for requesting feature requests and there are quite a few OrberBy requests that have been completed. If there is no existing entry that meets your requirements, please create a new entry so others can comment and upvote as a means to communicate to the Product Group that specific functionality is desired. There are likely other
Another possible option is to migrate to Cosmos DB for MongoDB or MongoDB on an Azure VM and use cursor.sort().

[freakyfriday]

Thanks for your reply @Mike-Ubezzi-MSFT I did create one (https://feedback.azure.com/forums/263030-azure-cosmos-db/suggestions/36124006-table-api-sort) which was marked as complete for the .NET library. I'm using NodeJS so I need it for the azure-storage javascript library or even a REST api. I've created another one (https://feedback.azure.com/forums/263030-azure-cosmos-db/suggestions/37885435-table-api-sort-javascript-storage-library) asking for this functionality specifically for Javascript users. Fingers crossed!