Azure Cosmos DB container sorting of records

Question

Azure Cosmos DB container sorting of records

Chris Buley 156

Hi everyone,

We have an Azure Cosmos DB container that gets populated with batched entries and currently they get sorted by the "c_ts" JSON value. I would like them to be sorted by "creationDate" value in Oldest to Newest order.

Is there a way I can do that, maybe with the Indexing Policy for that container? Kind regards Chris.

Oury Ba-MSFT 20,911 Reputation points Microsoft Employee Moderator

2024-02-20T22:51:51.33+00:00

@Chris Buley
Thank you for reaching out. My understanding is that you are looking to sort the entries in your Azure Cosmos DB container by the “creationDate” value in Oldest to Newest order. Please let me know if my understanding is not correct. You can use the ORDER BY clause in your queries. Use the following query:

SELECT * FROM c

ORDER BY c.creationDate ASC https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/query/order-by https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/model-partition-example
Chris Buley 156 Reputation points

2024-02-21T00:17:29.5866667+00:00

Hi Oury,

As the container gets filled up by entries, I want them to be automatically sorted by Creation Date (Oldest to Newest) rather than the "_ts" value.

So would the following code in the indexing policy solve that? {
"automatic":true, "indexingMode":"Consistent", "includedPaths":[
{
"path":"/*" } ], "excludedPaths":[], "compositeIndexes":[
[
{
"path":"/creationDate", "order":"ascending" } ] ] } Regards Chris.

Accepted answer

0 additional answers

Your answer

Oury Ba-MSFT 20,911 Reputation points Microsoft Employee Moderator

2024-02-20T22:51:51.33+00:00

@Chris Buley
Thank you for reaching out. My understanding is that you are looking to sort the entries in your Azure Cosmos DB container by the “creationDate” value in Oldest to Newest order. Please let me know if my understanding is not correct. You can use the ORDER BY clause in your queries. Use the following query:

SELECT * FROM c

ORDER BY c.creationDate ASC https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/query/order-by https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/model-partition-example
Chris Buley 156 Reputation points

2024-02-21T00:17:29.5866667+00:00

Hi Oury,

As the container gets filled up by entries, I want them to be automatically sorted by Creation Date (Oldest to Newest) rather than the "_ts" value.

So would the following code in the indexing policy solve that? {
"automatic":true, "indexingMode":"Consistent", "includedPaths":[
{
"path":"/*" } ], "excludedPaths":[], "compositeIndexes":[
[
{
"path":"/creationDate", "order":"ascending" } ] ] } Regards Chris.

Answer 1

Oury Ba-MSFT 20,911 Microsoft Employee Moderator

Chris Buley Thank you for clarifying this. Yes, the indexing policy you’ve provided aims to sort the entries in your Azure Cosmos DB container by the “creationDate” value in Oldest to Newest order. { "automatic":true, "indexingMode":"Consistent", "includedPaths":[ { "path":"/*" } ], "excludedPaths":[], "compositeIndexes":[ [ { "path":"/creationDate", "order":"ascending" } ] } Manage indexing policies in Azure Cosmos DB Regards, Oury

Oury Ba-MSFT 20,911 Reputation points Microsoft Employee Moderator

2024-02-21T18:27:29.99+00:00

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Chris Buley 156 Reputation points

2024-02-21T19:06:21.0766667+00:00

Awesome, many thanks for your clarification Oury.
Oury Ba-MSFT 20,911 Reputation points Microsoft Employee Moderator

2024-02-21T20:08:14.3166667+00:00

It was a pleasure helping you Chris Buley Regards, Oury
Priya Ponnuram 0 Reputation points

2024-04-02T15:50:24.5733333+00:00

{ "automatic":true, "indexingMode":"Consistent", "includedPaths":[ { "path":"/*" } ], "excludedPaths":[], "compositeIndexes":[ [ { "path":"/creationDate", "order":"ascending" } ] }

The above won't work as composite indexes require 2 paths!
Oury Ba-MSFT 20,911 Reputation points Microsoft Employee Moderator

2024-04-29T20:06:39.7866667+00:00
Priya Ponnuram

The provided indexing policy indeed has a single composite index on the “creationDate” field in ascending order. However, you are correct that composite indexes typically require at least two paths.

Here’s an updated indexing policy that includes both the “creationDate” field and another field (e.g., “id” or “partitionKey”):

JSON

{ "automatic": true, "indexingMode": "Consistent", "includedPaths": [ { "path": "/creationDate/?", "indexes": [ { "kind": "Range", "dataType": "Number", "precision": -1, "min": "-9223372036854775808", "max": "9223372036854775807" }, { "kind": "Hash", "dataType": "String", "precision": 3 } ] }, { "path": "/id/?" } ], "excludedPaths": [] }

You can adjust the second path according to your specific requirements.Regards,

Oury

Share via

Azure Cosmos DB container sorting of records

0 additional answers

Your answer