DocumentDB - Cannot compare two paths in query

Question

Microsoft Azure Documents BadRequestException An invalid query has been specified with filters against path(s) that are not range-indexed. Consider adding allow scan header in the request.

My query is:

SELECT c.id FROM users c WHERE (c.lat < 29.89)

OVER ?? number of documents (as there are no way to get the number of document in collection with DocumentDB)

Answer 1

If you look at the blogpost here: http://azure.microsoft.com/blog/2015/01/27/performance-tips-for-azure-documentdb-part-2/

Indexing Policy Tip #3: Specify range index path type for all paths used in range queries

DocumentDB currently supports two index path types: Hash and Range. Choosing an index path type of Hash enables efficient equality queries. Choosing an index type of Range enables range queries (using >, <, >=, <=).

It gives an example in C# to add a Range Index to make the path comparable, but there is similar functionality in the node.js library.

When you create a collection, you can pass the IndexingPolicy through the body parameter. The IndexingPolicy has a couple of members. One of which is the IncludedPaths, where you can define indices.

var policy = { 
   Automatic: true,
   IndexingMode: 'Lazy',
   IncludedPaths: [
      {
      IndexType: "Range",
      Path: "path to be indexed (c.lat)",
      NempericPrecission: "1",
      StringPrecission: "1"
      }
   ],
   ExcludedPaths: []
}

client.createCollection(
  '#yourdblink', 
  { 
    id: 10001, 
    indexingPolicy: policy 
  });

Answer 2

The Policy can be changed in the new Azure Portal (https://portal.azure.com), under (your DocumentDB resource) -> Settings -> Indexing Policy.