Cosmos DB (NO SQL API) - ordering by Index_of

Question

I want to search some string in my column and i want to return the data in the order of index where they are found. Something like this:

SELECT c.myData INDEX_OF(c.myData , "ABC") AS Ordered FROM c WHERE INDEX_OF(c.searchablePartNum, "ABC") >= 0 ORDER BY c.Ordered```

This returns the result but it does not sort the result based on the output of the INDEX_OF, what am i missing here?

Answer 1

ORDER BY is applicable only on property or expression. In the query it is applied on Ordered which is computed at runtime only. Therefore create computed property using .NET SDK V3 or Java SDK V4 and apply order by on that computed property only.

Below is the computed property:

{
  "name": "abcIndex", 
  "query": SELECT VALUE INDEX_OF(c.searchablePartNum, "ABC") FROM c
}

Before you can ORDER BY abcIndex you must add it to your indexing policy. After your indexing policy is updated, you can order by the computed property.

SELECT * FROM c ORDER BY c.abcIndex

For more information on ORDER BY refer this link.