Let's take that calculation out of your Azure Cosmos DB query with new Computed Properties.

 

     A computed property is a virtual property that is not physically stored in a document. You can use data from other properties of a document to calculate a value for the computed property. This will help you to save CPU power since the database engine does not need to calculate the computed column value for each query request. In the Cloud CPU power means money! Computed Columns are like coupons you can use to save money.

     You need to know a couple of important things before I show some examples here. 

• Computed properties must be at the root of a document, you can't create them in a nested path.
• Computed properties are scoped to an individual item/document. You can't include properties from different documents. 
• You can't have more than 20 computed properties in a container.
• Computed properties do not support non-deterministic functions. For example; GetCurrentDateTime() function gives the current datetime, its value changes all the time.
• You want to be sure that the name of the computed property is unique. The Azure Cosmos DB team suggests you use some kind of prefix like cp_ for all the computed properties.
• The signature that defines a computed property must be valid syntactically and semantically. Don't forget your data model is schemeless and the computed property query might return null or undefined.
• You must use the VALUE clause in the projection of a computed property signature.
• Computed Property signature query can not have subqueries or any of the following clauses.
• WHERE
• GROUP BY
• ORDER BY
• TOP
• DISTINCT
• OFFSET LIMIT
• EXISTS
• ALL
• NONE

     To create computed properties, you will need .NET SDK 3.34.0-preview or later versions. You can easily add computed properties to your current containers or create new containers with computed properties. Here is an example.

 
  var container = client.GetDatabase("Stackoverflow").GetContainer("Posts");
  var props = await container.ReadContainerAsync();
  props.Resource.ComputedProperties = new ObjectModel.Collection<ComputedProperty> {
      new ComputedProperty
      {
          Name="cp_openindays",
          Query="SELECT VALUE(c.ClosedDate =null? 0:
          DateTimeDiff('d', c.CreatedOn, c.ClosedDate)) FROM c"
      }
  };
  await container.ReplaceContainerAsync(props);
  

     

     This computed property is supposed to be available immediately. If you will run SELECT * to see its value, you will make a user-error mistake like me. As it says in the documentation, SELECT * does not return any computed properties. Also, a computed property might not be in every document of a container because the value of a computed property might be undefined or null. 

    If you like to see the computed columns, you need to include them in your SELECT statement. I hope we will see the computed properties in SELECT * results when computed properties will be generally available. Otherwise, it might be a challenge to figure out which computed properties are available to use for developers.  In the following example, you can see the computed property is available immediately after I created it by SDK.

      Let's look at another query. As you can see the DateTime function matches our computed property. We can rewrite this query and use the new computed property we just created.

     The previous query cost 16.92 Request Units. Here is the Indexing Metrics for it. All the properties in WHERE are indexed and getting used.

I wonder what is gonna happen if we will use the computed property.

    My query looks better, It is easy to read but Request Charge is higher than before. It used to be 16.92 now it is 18.68. The main reason for that is, the number of indexes used by this query decreased as you can see in the Indexing Metrics tab. Also, it looks like Cosmos DB suggests indexing the new computed property. By default, Cosmos DB does not index computed properties. You need to add them manually into your Indexing Policy. 

     Next step, I will add the computed property into the Indexing Policy to see if I will see any difference in Request Charge. Depending on your queries, you can add computed properties as a single or a composite index. In my case, I am adding cp_openindays as a single index.

     It might take some time for Reindexing to complete since I have 5 Physical partitions for this container. If you are not a user of Azure Cosmos DB SQL Studio, you might be wondering what tool I am using to query the Cosmos DB. I have been developing Cosmos DB SQL Studio for some time now, It is totally free and you can use it with the VsCode.

     After Reindexing, I run the query again and the Request charge dropped to 15.85 which is better than using the DateTime function. You can see the computed column is getting used now.

     You can use the computed properties in GROUP BY and ORDER BY clauses too. I like using computed properties because they make the queries more readable, also queries cost less if computes properties are indexed.