Cosmos DB查询-当所有文档中都不存在属性时使用ORDER BY [英] Cosmos DB queries - using ORDER BY when a property does not exist in all documents

问题描述

在为Cosmos Document DB编写查询时，我们遇到一个问题，我们想创建一个新的文档属性并在ORDER BY子句中使用它

We are experiencing an issue in when writing queries for Cosmos Document DB and we want to create a new document property and use it in an ORDER BY clause

例如，如果我们有一组文档，例如:

If, for example, we had a set of documents like:

{
  "Name": "Geoff",
  "Company": "Acme"
},
{
  "Name": "Bob",
  "Company": "Bob Inc"
}

...，我们编写了一个类似SELECT * FROM c ORDER BY c.Name的查询，它可以正常工作并返回两个文档

...and we write a query like SELECT * FROM c ORDER BY c.Name this works fine and returns both documents

但是，如果我们要添加一个具有附加属性的新文档:

However, if we were to add a new document with an additional property:

{
  "Name": "Geoff",
  "Company": "Acme"
},
{
  "Name": "Bob",
  "Company": "Bob Inc"
},
{
  "Name": "Sarah",
  "Company": "My Company Ltd",
  "Title": "President"
}

...并且我们编写一个类似于SELECT * FROM c ORDER BY c.Title的查询，它将仅返回Sarah的文档，并排除不带Title属性的2.

...and we write a query like SELECT * FROM c ORDER BY c.Title it will only return the document for Sarah and excludes the 2 without a Title property.

这意味着ORDER BY子句的行为类似于过滤器，而不仅仅是一种排序，这似乎是意外的.

This means that the ORDER BY clause is behaving like a filter rather than just a sort, which seems unexpected.

似乎所有文档架构都可能随着时间的推移添加属性.除非我们返回并将这些属性添加到容器中所有现有的文档记录中，否则我们将无法在不排除记录的情况下在ORDER BY子句中使用它们.

It seems that all document schemas are likely to add properties over time. Unless we go back and add these properties to all existing document records in the container then we can never use them in an ORDER BY clause without excluding records.

是否有人有解决方案允许ORDER BY仅影响结果集的排序顺序?

Does anyone have a solution to allow the ORDER BY to only effect the Sort order of the result set?

推荐答案

当前，ORDER BY可以处理索引属性，并且使用ORDER BY的查询结果中不包含缺少的值.

Currently, ORDER BY works off of indexed properties, and missing values are not included in the result of a query using ORDER BY.

作为一种解决方法，您可以执行两个查询并合并结果:

As a workaround, you could do two queries and combine the results:

• 您正在使用ORDER BY进行的当前查询，返回所有包含Title属性的文档，已排序
• 第二个查询，返回所有未定义Title的文档.

• The current query you're doing, with ORDER BY, returning all documents containing the Title property, ordered
• A second query, returning all documents that don't have Title defined.

第二个查询类似于:

SELECT * FROM c
WHERE NOT IS_DEFINED(c.Title)

还要注意，根据EF中的本说明核心回购问题列表，使用复合索引(返回缺少属性 的文档)时的行为有些不同.

Also note that, according to this note within the EF Core repo issue list, behavior is a bit different when using compound indexes (where documents with missing properties are returned).

这篇关于Cosmos DB查询-当所有文档中都不存在属性时使用ORDER BY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！