Azure CosmosDB IS_DEFINED vs NOT IS_DEFINED

Question

I was trying to query a collection, which had few documents. Some of the collections had "Exception" property, where some don't have.
My end query looks some thing like:

Records that do not contain Exception: 

**select COUNT(1) from doc c WHERE NOT IS_DEFINED(c.Exception)**  


Records that contain Exception: 

**select COUNT(1) from doc c WHERE IS_DEFINED(c.Exception)**

But this seems not be working. When NOT IS_DEFINED is returning some count, IS_DEFINED is returning 0 records, where it actually had data.

My data looks something like (some documents can contain Exception property & others don't):

[{
  'Name': 'Sagar',
  'Age': 26,
  'Exception: 'Object reference not set to an instance of the object', ...
},
{
  'Name': 'Sagar',
  'Age': 26, ...
}]

Answer 1

Update

As Dax Fohl said in an answer NOT IS_DEFINED is implemented now. See the the cosmos dev blob April updates for more details.

To use it properly the queried property should be added to the index of the collection.

Excerpt from the blog post:

Queries with inequality filters or filters on undefined values can now be run more efficiently. Previously, these filters did not utilize the index. When executing a query, Azure Cosmos DB would first evaluate other less expensive filters (such as =, >, or <) in the query. If there were inequality filters or filters on undefined values remaining, the query engine would be required to load each of these documents. Since inequality filters and filters on undefined values now utilize the index, we can avoid loading these documents and see a significant improvement in RU charge.

Here’s a full list of query filters with improvements:

• Inequality comparison expression (e.g. c.age != 4)
• NOT IN expression (e.g. c.name NOT IN (‘Luis’, ‘Andrew’, ‘Deborah’))
• NOT IsDefined
• Is expressions (e.g. NOT IsDefined(c.age), NOT IsString(c.name))
• Coalesce operator expression (e.g. (c.name ?? ‘N/A’) = ‘Thomas’)
• Ternary operator expression (e.g. c.name = null ? ‘N/A’ : c.name)

If you have queries with these filters, you should add an index for the relevant properties.