Cosmos DB queries - using ORDER BY when a property does not exist in all documents

Question

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

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

...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.

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

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.

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

David Makogon David Makogon · Accepted Answer · 2019-11-27T13:36:42

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:

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.

The second query would look something like:

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

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 queries - using ORDER BY when a property does not exist in all documents

1 Answers