Cosmos DB按来自联接中使用的标量子查询的列排序 [英] Cosmos DB sort by a column coming from a scalar subquery used in join

问题描述

我创建了以下自定义查询，以按照我在join子句中创建的临时表中定义的优先级"的顺序对帖子"集合中的文档进行排序.现在，查询本身可以正常工作，但是只要我按优先级排序，它就会引发错误.因此，基本上，使用不属于文档的变量进行排序是行不通的.有没有解决的办法?我想按优先顺序排序的原因是，我可以按顺序获取文档，并可以使用延续令牌从波斯菊中提取分页数据.

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.

请注意，内部查询是由我在我的Web应用程序代码中动态生成的，因此我无法在帖子"容器中的文档中预先分配优先级.

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.

我得到的错误:

无法查询项目以获取容器帖子:{代码":400，正文":{代码":"BadRequest"，消息":网关无法检索查询计划:消息:: {错误":[{严重性":错误"，消息":不支持的ORDER BY子句. ORDER BY项目表达式无法映射到文档路径."}]}

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."}]}

推荐答案

似乎备注部分指出了该问题:

ORDER BY子句要求索引策略包括索引 对于要排序的字段. Azure Cosmos DB查询运行时 支持对属性名称进行排序，而对不进行计算 属性.

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.

鉴于weights.Priority不是文档中的属性，因此无法对其进行索引.这表明必须将排序值以某种方式合并到持久数据模型中.

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按来自联接中使用的标量子查询的列排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！