Cosmos DB sort by a column coming from a scalar subquery used in join

Question

I have created the following custom query to sort documents in the 'posts' collection by the order of 'Priority' that I have defined in the kinda temp table thing that I have created inside the join clause. Now, the query itself works fine, but as soon as I order by Priority, it throws an error. So basically, order by using a variable that doesn't belong to the document doesn't work. Is there a work around for this? The reason I want to order by priority is so that I can get the documents in order and can use the continuation token to fetch paginated data from cosmos.

Do note that the inner query is dynamically generated by me in my web app code so I cannot pre-assign the priority in documents in the 'posts' container.

SELECT posts.id, posts.HairState, weights.Priority FROM posts
JOIN weights IN (
    SELECT VALUE [{Porosity:'Medium',Texture:'Wavy',Thickness:'Fine',Density:'High',Priority:0},{Porosity:'Medium',Texture:'Wavy',Thickness:'Fine',Density:'Medium',Priority:1},{Porosity:'Medium',Texture:'Wavy',Thickness:'Medium',Density:'High',Priority:2},{Porosity:'Medium',Texture:'Curly',Thickness:'Fine',Density:'High',Priority:3},{Porosity:'Medium',Texture:'Wavy',Thickness:'Medium',Density:'Medium',Priority:4},{Porosity:'Medium',Texture:'Curly',Thickness:'Fine',Density:'Medium',Priority:5},{Porosity:'Medium',Texture:'Curly',Thickness:'Medium',Density:'High',Priority:6},{Porosity:'Medium',Texture:'Curly',Thickness:'Medium',Density:'Medium',Priority:7},{Porosity:'Low',Texture:'Wavy',Thickness:'Fine',Density:'High',Priority:8},{Porosity:'Low',Texture:'Wavy',Thickness:'Fine',Density:'Medium',Priority:9},{Porosity:'Low',Texture:'Wavy',Thickness:'Medium',Density:'High',Priority:10},{Porosity:'Low',Texture:'Curly',Thickness:'Fine',Density:'High',Priority:11},{Porosity:'Low',Texture:'Wavy',Thickness:'Medium',Density:'Medium',Priority:12},{Porosity:'Low',Texture:'Curly',Thickness:'Fine',Density:'Medium',Priority:13},{Porosity:'Low',Texture:'Curly',Thickness:'Medium',Density:'High',Priority:14},{Porosity:'Low',Texture:'Curly',Thickness:'Medium',Density:'Medium',Priority:15}]
)
WHERE posts.HairState.Porosity = weights.Porosity
AND posts.HairState.Texture = weights.Texture
AND posts.HairState.Thickness = weights.Thickness
AND posts.HairState.Density = weights.Density
ORDER BY weights.Priority

Any ideas on how to achieve this will be really appreciated! One lead that I have found is in this documentation here (https://docs.microsoft.com/en-us/azure/cosmos-db/sql-query-order-by) that says <scalar_expression> are supported, but I'm not sure how this works as there are no examples given for the same.

Error I'm getting:

Failed to query item for container posts: {"code":400,"body":{"code":"BadRequest","message":"Gateway Failed to Retrieve Query Plan: Message: {"errors":[{"severity":"Error","message":"Unsupported ORDER BY clause. ORDER BY item expression could not be mapped to a document path."}]}

Should have added this in the post. Failed to query item for container posts: {"code":400,"body":{"code":"BadRequest","message":"Gateway Failed to Retrieve Query Plan: Message: {\"errors\":[{\"severity\":\"Error\",\"message\":\"Unsupported ORDER BY clause. ORDER BY item expression could not be mapped to a document path.\"}]} — Arvind Sasikumar

Noah Stahl Noah Stahl · Accepted Answer · 2020-08-18T02:37:26

It seems that the remarks section of the docs indicates the issue:

The ORDER BY clause requires that the indexing policy include an index for the fields being sorted. The Azure Cosmos DB query runtime supports sorting against a property name and not against computed properties.

Given that weights.Priority isn't a property in the document, it can't be indexed. This suggests that the ordering value must be incorporated into the persisted data model somehow.

Cosmos DB sort by a column coming from a scalar subquery used in join

1 Answers