This blog is part two of a series of three blogs (read part one here) where we’ll demystify commonly confused concepts for developers learning how to query data using the SQL (Core) API in Azure Cosmos DB. We’ll look at the difference between null and undefined values.

The value null must be explicitly set for a property. For example, here is an item that has the creationDate property set to null:

{
  "id": "AndersenFamily",
  "lastName": "Andersen",
  "address": {
      "state": "WA",
      "county": "King",
      "city": "Seattle"
      },
  "creationDate": null
}

A property with a **null **value must have it explicitly assigned. Properties not defined in an item have an undefined value. In the example item above, the property **isRegistered **has a value of undefined because it is omitted from the item.

Azure Cosmos DB supports two helpful type checking system functions for null and undefined properties, both of which use the index:

• IS_NULL– checks if a property value is null
• You can also, of course, simply check if a value is null with an equality filter (example: SELECT * FROM c WHERE c.creationDate = null)
• IS_DEFINED– checks if a property value is defined

In order for a filter expression to match an item, it must evaluate to true. In addition to false, all other values (including null or undefined) will exclude the item.

Here’s an example query:

SELECT *
FROM c
WHERE c.isRegistered = true

This example query will not include any items where isRegistered is null or undefined.

Understanding best practices

One of the biggest advantages of Azure Cosmos DB (and NoSQL in general) is simple support for unstructured data. If you have a Cosmos container that has items of varying structures or “schemas”, some items won’t contain the same properties as others. It is generally simpler and more efficient to simply not define a property than it is to both define a property and assign it a null value. The advantages of this approach are savings on storage size, ingestion costs, and simplified queries. A large number of null values in an item can be a signal that the data model could be further optimized.

The advantages of not explicitly defining null value properties is most obvious if you examine the RU charge of an item with a high number of null value properties.

#tips and tricks #azure cosmos db #null #undefined