CODEWITHSUNDEEP
azureazure-cosmosdb
tklepzig
tklepzig

Reputation: 638

Azure DocumentDB: order by and filter by DateTime

I have the following query:

SELECT * FROM c 
WHERE c.DateTime >= "2017-03-20T10:07:17.9894476+01:00" AND c.DateTime <= "2017-03-22T10:07:17.9904464+01:00"
ORDER BY c.DateTime DESC

So as you can see I have a WHERE condition for a property with the type DateTimeand I want to sort my result by the same one. The query ends with the following error:

Order-by item requires a range index to be defined on the corresponding index path.

I have absolutely no idea what this error message is about :(

Has anybody any idea?

Upvotes: 2

Views: 5527

Answers (2)

tklepzig
tklepzig

Reputation: 638

I think I found a possible solution, thanks for pointing out the issue with the index.

As stated in the following article https://learn.microsoft.com/en-us/azure/documentdb/documentdb-working-with-dates#indexing-datetimes-for-range-queries I changed the index for the datatype string to RangeIndex, to allow range queries:

DocumentCollection collection = new DocumentCollection { Id = "orders" };
collection.IndexingPolicy = new IndexingPolicy(new RangeIndex(DataType.String) { Precision = -1 });
await client.CreateDocumentCollectionAsync("/dbs/orderdb", collection);

And it seems to work! If there are any undesired side effects I will let you know.

Upvotes: 1

Ankur Lathiya
Ankur Lathiya

Reputation: 85

You can also do one thing that don't require indexing explicitly. Azure documentBD is providing indexing on numbers field by default so you can store the date in long format. Because you are already converting date to string, you can also convert date to long an store, then you can implement range query.

Upvotes: 1

Related Questions