CODEWITHSUNDEEP
azureazure-cosmosdb
Tristan Rhodes
Tristan Rhodes

Reputation: 393

DocumentDB ORDER BY removing undefined

I have a data set containing legacy data. We are sorting by a field which is only present in newer documents.

When I sort by this field, only the objects which have this value are returned, even when they pass the WHERE clause.

How do we get a sorted set at the top for objects which have the field, and all those that don't are un-sorted at the end?

I've been trying something like this, but it's not valid:

SELECT T.id, IS_DEFINED(T.createdDate) ? T.createdDate : "2000-01-01T00:00:00" AS createdDate FROM Themes T 
WHERE T.customerId = 43333
AND T.isDefault = false
AND (NOT IS_DEFINED(T.isArchived) OR T.isArchived = false)
ORDER BY createdDate ASC

I have also tried:

SELECT T.id FROM Themes T 
WHERE T.customerId = 43333
AND T.isDefault = false
AND (NOT IS_DEFINED(T.isArchived) OR T.isArchived = false)
ORDER BY IS_DEFINED(T.createdDate) ? T.createdDate : "2000-01-01T00:00:00" ASC

Upvotes: 3

Views: 746

Answers (2)

Larry Maccherone
Larry Maccherone

Reputation: 9533

Short of populating that field in documents where it's missing, the only way I know to do it would be to return all of the data and do the sort client side.

I wonder if you could ORDER BY a projected field and/or if you can use SQL's case and value substitution features in DocumentDB's SQL subset... and if so, are they efficient?

The bottleneck for my system is DocumentDB RUs so I try to do as much as possible client-side, but I'd love to know if this is possible and efficient in DocumentDB's SQL.

Upvotes: 2

Shireesh Thota
Shireesh Thota

Reputation: 303

In the schema-less databases, there is no defined convention on the type order, let alone on the undefined entries when sorting. Your requirement is still valid nonetheless.

Currently the best solution for this in DocumentDB, is to do two part queries -

  1. Look for NOT IS_DEFINED documents
  2. Query for ORDER BY

If the number of documents without the isArchived property are few and fixed, then it might be a good idea to prepare that list and keep it in a separate document so that the first query is much faster once the document is built one time. All you need is to query that documents by "id" for all the entries in that metadata document and retrieve them first.

Upvotes: 1

Related Questions