Get distinct values in Cosmos DB

Question

I need to get the distinct value. The query is SELECT DISTINCT c.Column1 FROM c. This query applies distinct with in the page instead of the entire collection. If i run the same above query again, it returns the next set of distinct values with continuation Token.

I want the distinct needs to be applied to the entire collection, then the results can be paged. But it applies distinct with in the page and returns the results with continuation token. I want to achieve this without increasing the RU/s.

Solution

I want the distinct needs to be applied to the entire collection, then the results can be paged.I want to achieve this without increasing the RU/s.

Paging-Search in cosmos db is optimized for query performance.Page size is also limited by Rus settings. Regardless of whether your sql contains distinct, it will eventually scan the entire collection. Furthermore, limited by RUs settings , you can't traverse the entire collection at once.

So, even if you have to find the entire collection first, you have to page. Unless you set the RUS to the ideal value which is you tried to avoid.

Hope it helps you.