{"id":2025,"date":"2020-11-25T09:00:12","date_gmt":"2020-11-25T17:00:12","guid":{"rendered":"https:\/\/devblogs.microsoft.com\/cosmosdb\/?p=2025"},"modified":"2020-12-14T11:06:07","modified_gmt":"2020-12-14T19:06:07","slug":"difference-between-null-and-undefined","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/cosmosdb\/difference-between-null-and-undefined\/","title":{"rendered":"Understanding the difference between null and undefined in Azure Cosmos DB"},"content":{"rendered":"<p>This blog is part two of a series of three blogs (<a href=\"https:\/\/devblogs.microsoft.com\/cosmosdb\/point-reads-versus-queries\/\">read part one here<\/a>) where we\u2019ll demystify commonly confused concepts for developers learning how to query data using the SQL (Core) API in Azure Cosmos DB. We\u2019ll look at the difference between null and undefined values.<\/p>\n<p>The value <strong>null<\/strong> must be explicitly set for a property. For example, here is an item that has the <strong>creationDate<\/strong> property set to null:<\/p>\n<pre><span style=\"font-family: arial, helvetica, sans-serif; font-size: 12pt;\">{<\/span>\r\n<span style=\"font-family: arial, helvetica, sans-serif; font-size: 12pt;\">\u00a0 \"id\": \"AndersenFamily\",<\/span>\r\n<span style=\"font-family: arial, helvetica, sans-serif; font-size: 12pt;\">\u00a0 \"lastName\": \"Andersen\",<\/span>\r\n<span style=\"font-family: arial, helvetica, sans-serif; font-size: 12pt;\">\u00a0 \"address\": {<\/span>\r\n<span style=\"font-family: arial, helvetica, sans-serif; font-size: 12pt;\">\u00a0\u00a0\u00a0\u00a0\u00a0 \"state\": \"WA\",<\/span>\r\n<span style=\"font-family: arial, helvetica, sans-serif; font-size: 12pt;\">\u00a0\u00a0\u00a0\u00a0\u00a0 \"county\": \"King\",<\/span>\r\n<span style=\"font-family: arial, helvetica, sans-serif; font-size: 12pt;\">\u00a0\u00a0\u00a0\u00a0\u00a0 \"city\": \"Seattle\"<\/span>\r\n<span style=\"font-family: arial, helvetica, sans-serif; font-size: 12pt;\">\u00a0\u00a0\u00a0\u00a0\u00a0 },<\/span>\r\n<span style=\"font-family: arial, helvetica, sans-serif; font-size: 12pt;\">\u00a0 \"creationDate\": null<\/span>\r\n<span style=\"font-family: arial, helvetica, sans-serif; font-size: 12pt;\">}<\/span><\/pre>\n<p>A property with a <strong>null <\/strong>value must have it explicitly assigned. Properties not defined in an item have an <strong>undefined<\/strong> value. In the example item above, the property <strong>isRegistered <\/strong>has a value of undefined because it is omitted from the item.<\/p>\n<p>Azure Cosmos DB supports two helpful type checking system functions for\u00a0null\u00a0and\u00a0undefined\u00a0properties, both of which use the index:<\/p>\n<ul>\n<li><a href=\"https:\/\/docs.microsoft.com\/en-us\/azure\/cosmos-db\/sql-query-is-null\">IS_NULL<\/a>&#8211; checks if a property value is\u00a0null\n<ul>\n<li>You can also, of course, simply check if a value is null with an equality filter (example: SELECT * FROM c WHERE c.creationDate = null)<\/li>\n<\/ul>\n<\/li>\n<li><a href=\"https:\/\/docs.microsoft.com\/en-us\/azure\/cosmos-db\/sql-query-is-defined\">IS_DEFINED<\/a>&#8211; checks if a property value is defined<\/li>\n<\/ul>\n<p>In order for a filter expression to match an item, it must evaluate to <strong>true<\/strong>. In addition to\u00a0<strong>false<\/strong>, all other values (including <strong>null<\/strong> or <strong>undefined)<\/strong> will exclude the item.<\/p>\n<p><strong>Here\u2019s an example query:<\/strong><\/p>\n<pre><span style=\"font-family: arial, helvetica, sans-serif;\">SELECT *<\/span>\r\n<span style=\"font-family: arial, helvetica, sans-serif;\">FROM c<\/span>\r\n<span style=\"font-family: arial, helvetica, sans-serif;\">WHERE c.isRegistered = true<\/span><\/pre>\n<p>This example query will not include any items where <strong>isRegistered<\/strong> is <strong>null<\/strong> or <strong>undefined<\/strong>.<\/p>\n<h2><span style=\"font-size: 18pt;\"><strong>Understanding best practices<\/strong><\/span><\/h2>\n<p>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 \u201cschemas\u201d, some items won\u2019t 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 <span style=\"text-decoration: underline;\">and<\/span> 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.<\/p>\n<p>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.<\/p>\n<p>For example, consider the two below items. Item 1 has eight extra properties (fieldA through fieldH), all of which have a <strong>null<\/strong> value. Item 2 has <strong>undefined\u00a0<\/strong>value for these properties and does not need to manually define them. Therefore, Item 2 is smaller than Item 1 and has fewer properties. With a default indexing policy, writing Item 2 costs 15% fewer RUs!<\/p>\n<table style=\"width: 60.28%;\">\n<tbody>\n<tr>\n<td style=\"width: 49.92%;\" width=\"312\"><span style=\"font-family: arial, helvetica, sans-serif;\"><strong>Item 1:<\/strong> Write RU Charge = 9.9<\/span><\/td>\n<td style=\"width: 68.3791%;\" width=\"312\"><span style=\"font-family: arial, helvetica, sans-serif; font-size: 12pt;\"><strong>Item 2: <\/strong>Write RU Charge = 8.4<\/span><\/td>\n<\/tr>\n<tr>\n<td style=\"width: 49.92%;\" width=\"312\">\n<pre><span style=\"font-family: arial, helvetica, sans-serif; font-size: 12pt;\">{<\/span>\r\n<span style=\"font-family: arial, helvetica, sans-serif; font-size: 12pt;\">  \"id\": \"AndersenFamily1\",<\/span>\r\n<span style=\"font-family: arial, helvetica, sans-serif; font-size: 12pt;\">  \"lastName\": \"Andersen\",<\/span>\r\n<span style=\"font-family: arial, helvetica, sans-serif; font-size: 12pt;\">  \"orderId\": \"999\",<\/span>\r\n<span style=\"font-family: arial, helvetica, sans-serif; font-size: 12pt;\">  \"fieldA\": <strong>null<\/strong>,<\/span>\r\n<span style=\"font-family: arial, helvetica, sans-serif; font-size: 12pt;\">  \"fieldB\": <strong>null<\/strong>,<\/span>\r\n<span style=\"font-family: arial, helvetica, sans-serif; font-size: 12pt;\">  \"fieldC\": <strong>null<\/strong>,<\/span>\r\n<span style=\"font-family: arial, helvetica, sans-serif; font-size: 12pt;\">  \"fieldD\": <strong>null<\/strong>,<\/span>\r\n<span style=\"font-family: arial, helvetica, sans-serif; font-size: 12pt;\">  \"fieldE\": <strong>null<\/strong>,<\/span>\r\n<span style=\"font-family: arial, helvetica, sans-serif; font-size: 12pt;\">  \"fieldF\": <strong>null<\/strong>,<\/span>\r\n<span style=\"font-family: arial, helvetica, sans-serif; font-size: 12pt;\">  \"fieldG\": <strong>null<\/strong>,<\/span>\r\n<span style=\"font-family: arial, helvetica, sans-serif; font-size: 12pt;\">  \"fieldH\": <strong>null<\/strong>,<\/span>\r\n<span style=\"font-family: arial, helvetica, sans-serif; font-size: 12pt;\">  \"address\": {<\/span>\r\n<span style=\"font-family: arial, helvetica, sans-serif; font-size: 12pt;\">      \"state\": \"WA\",<\/span>\r\n<span style=\"font-family: arial, helvetica, sans-serif; font-size: 12pt;\">      \"county\": \"King\", <\/span>\r\n<span style=\"font-family: arial, helvetica, sans-serif; font-size: 12pt;\">      \"city\": \"seattle\"<\/span>\r\n<span style=\"font-family: arial, helvetica, sans-serif; font-size: 12pt;\">   },<\/span>\r\n<span style=\"font-family: arial, helvetica, sans-serif; font-size: 12pt;\">  \"creationDate\": 1431620472,<\/span>\r\n<span style=\"font-family: arial, helvetica, sans-serif; font-size: 12pt;\">  \"isRegistered\": <strong>true<\/strong><\/span>\r\n<span style=\"font-family: arial, helvetica, sans-serif; font-size: 12pt;\">}<\/span><\/pre>\n<\/td>\n<td style=\"width: 68.3791%;\" width=\"312\">\n<pre><span style=\"font-family: arial, helvetica, sans-serif; font-size: 12pt;\">{<\/span>\r\n<span style=\"font-family: arial, helvetica, sans-serif; font-size: 12pt;\">  \"id\": \"AndersenFamily2\",<\/span>\r\n<span style=\"font-family: arial, helvetica, sans-serif; font-size: 12pt;\">  \"lastName\": \"Andersen\",<\/span>\r\n<span style=\"font-family: arial, helvetica, sans-serif; font-size: 12pt;\">  \"orderId\": \"999\",<\/span>\r\n<span style=\"font-family: arial, helvetica, sans-serif; font-size: 12pt;\">  \"address\": {<\/span>\r\n<span style=\"font-family: arial, helvetica, sans-serif; font-size: 12pt;\">  \u00a0 \u00a0 \"state\": \"WA\",<\/span>\r\n<span style=\"font-family: arial, helvetica, sans-serif; font-size: 12pt;\">  \u00a0 \u00a0 \"county\": \"King\",<\/span>\r\n<span style=\"font-family: arial, helvetica, sans-serif; font-size: 12pt;\">  \u00a0 \u00a0 \"city\": \"seattle\"<\/span>\r\n<span style=\"font-family: arial, helvetica, sans-serif; font-size: 12pt;\">  },<\/span>\r\n<span style=\"font-family: arial, helvetica, sans-serif; font-size: 12pt;\">  \"creationDate\": 1431620472,<\/span>\r\n<span style=\"font-family: arial, helvetica, sans-serif; font-size: 12pt;\">\u00a0 \"isRegistered\": <strong>true<\/strong><\/span>\r\n<span style=\"font-family: arial, helvetica, sans-serif; font-size: 12pt;\">}<\/span><\/pre>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>&nbsp;<\/p>\n<p>There is sometimes a slight advantage for using <strong>null<\/strong> values when querying. An equality filter (WHERE c.fieldA = null) is typically <em>slightly\u00a0<\/em>fewer RUs than using a system function (WHERE IS_DEFINED(c.fieldA)). This difference should not be nearly as significant as the additional write RU charge when using null, though. Therefore, we generally recommend simply not defining a property rather than both defining it <span style=\"text-decoration: underline;\">and<\/span> assigning it a null value.<\/p>\n<h2><span style=\"font-size: 18pt;\"><strong>Differences between null and undefined in mathematical expressions<\/strong><\/span><\/h2>\n<p>Mathematical expressions return <strong>undefined <\/strong>when any of the inputs are non-number values.<\/p>\n<p>Here are some example queries that each return <strong>undefined:<\/strong><\/p>\n<p><span style=\"font-family: arial, helvetica, sans-serif;\">SELECT 1 + &#8220;string&#8221;<\/span><\/p>\n<p><span style=\"font-family: arial, helvetica, sans-serif;\">SELECT 1 + null<\/span><\/p>\n<p><span style=\"font-family: arial, helvetica, sans-serif;\">SELECT null + \u201cstring\u201d<\/span><\/p>\n<p><strong>Result (for all above examples):<\/strong><\/p>\n<pre><span style=\"font-family: arial, helvetica, sans-serif; font-size: 12pt;\">[<\/span>\r\n<span style=\"font-family: arial, helvetica, sans-serif; font-size: 12pt;\"> \u00a0\u00a0 {}<\/span>\r\n<span style=\"font-family: arial, helvetica, sans-serif; font-size: 12pt;\">]<\/span><\/pre>\n<p>You can use the IS_NUMBER() system function to check whether a property value is a number and, if not, the ternary (?) operator to assign another value. In this case, if <strong>c.val<\/strong> is not a number, its value will be 0.<\/p>\n<p><strong>Here\u2019s an example:<\/strong><\/p>\n<pre><span style=\"font-family: arial, helvetica, sans-serif; font-size: 12pt;\">SELECT 1 + (IS_NUMBER(c.val) ? c.val : 0)<\/span>\r\n<span style=\"font-family: arial, helvetica, sans-serif; font-size: 12pt;\">FROM\u00a0 c<\/span><\/pre>\n<p>When executing SUM() or AVG() aggregate system functions, one single non-number value (including null) will lead to the entire function returning <strong>undefined<\/strong>, even if all other values were numbers.<\/p>\n<p>Therefore, a query like SELECT SUM(c.val) FROM c will return undefined if there are any null values for c.val.<\/p>\n<p>You could safeguard against possible non-number values by adding an IS_NUMBER filter for c.val.<\/p>\n<p>Here\u2019s an example:<\/p>\n<pre><span style=\"font-family: arial, helvetica, sans-serif; font-size: 12pt;\">SELECT SUM(c.val) <\/span>\r\n<span style=\"font-family: arial, helvetica, sans-serif; font-size: 12pt;\">FROM c <\/span>\r\n<span style=\"font-family: arial, helvetica, sans-serif; font-size: 12pt;\">WHERE IS_NUMBER(c.val)<\/span><\/pre>\n<p>In aggregate system functions, undefined values will simply be omitted from the calculation and will not impact the result. For example, a query like <span style=\"font-family: arial, helvetica, sans-serif;\">SELECT SUM(c.total) FROM c<\/span> has an implicit <span style=\"font-family: arial, helvetica, sans-serif;\">IS_DEFINED(c.total)<\/span> filter.<\/p>\n<p>Learn more about <a href=\"https:\/\/docs.microsoft.com\/azure\/cosmos-db\/sql-query-operators\">Azure Cosmos DB query operators<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>This blog is part two of a series of three blogs where we\u2019ll demystify commonly confused concepts for developers learning how to query in the SQL (core) API in Azure Cosmos DB. We\u2019ll look at the difference between null and undefined values.<\/p>\n","protected":false},"author":12128,"featured_media":61,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[14,19],"tags":[1765],"class_list":["post-2025","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-core-sql-api","category-tips-and-tricks","tag-query"],"acf":[],"blog_post_summary":"<p>This blog is part two of a series of three blogs where we\u2019ll demystify commonly confused concepts for developers learning how to query in the SQL (core) API in Azure Cosmos DB. We\u2019ll look at the difference between null and undefined values.<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/posts\/2025","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/users\/12128"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/comments?post=2025"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/posts\/2025\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/media\/61"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/media?parent=2025"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/categories?post=2025"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/tags?post=2025"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}