[Closed] Is it possible to use ORDER BY with both a field and VectorDistance in Azure Cosmos DB?

Question

[Closed] This is no longer relevant to my use case.

The documentation states that a query sorting on two or more fields requires a composite index in Cosmos DB. How can I perform sorting first by Price and then by vector distance, especially if vector embedding fields are recommended to be excluded using excludedPaths? For example, the following query will not work due to these limitations:

SELECT TOP 5 c.Name, c.Price 
FROM c 
WHERE c.Price >= 35.02 
ORDER BY c.Price DESC, VectorDistance(c.DescriptionEmbedding, [...])

Answer 1

@Viktor
Thank you for the question and for using Microsoft Q&A platform.
You're correct that sorting on multiple fields in Azure Cosmos DB requires a composite index.

To achieve sorting by Price and then by vector distance, you can try the below steps:

1. Define a composite index on the Price field. This will allow you to sort by Price efficiently.

2)Instead of calculating the vector distance on the fly, you can precompute the vector distance and store it in a separate field. This way, you can include this precomputed field in your composite index.

3. Use the below query:

SELECT TOP 5 c.Name, c.Price 
FROM c 
WHERE c.Price >= 35.02 
ORDER BY c.Price DESC, c.PrecomputedVectorDistance

By following these steps, you can effectively sort by Price and then by the precomputed vector distance.

You can refer to the below links for more help:
https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/query/vectordistance
https://devblogs.microsoft.com/cosmosdb/new-ways-to-use-composite-indexes/
https://dev.to/willvelida/understanding-indexing-in-azure-cosmos-db-21kc

Hope this helps. Please let us know if you have any further questions.