Azure DocumentDB: order by and filter by DateTime

2
votes

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?

2
azureazure-cosmosdb
Have you added an index on the DateTime attribute in your collection? You would need to add that in order to use Order By clause in your query as the error indicates. - Gaurav Mantri
Ok, thanks for the hint. I never used indexes in documentdb before, I will try it and let you know the result. Edit: not all my documents has the property DateTime, is indexing still possible? - tklepzig
not all my documents has the property DateTime, is indexing still possible - Yes. - Gaurav Mantri

2 Answers

1
votes

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.

1
votes

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

As stated in the following article https://docs.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.