Handling Custom Application Logic using User-Defined Functions in Azure Cosmos DB

 

     You can create complex queries to handle your application logic by using SQL syntax in SQL API. But, in some cases, SQL queries might not have the functionality or the way you want join properties in a document. You may be trying to merge custom properties in a special way, or you may want to group the data in a way that SQL API does not support. What do you do in these cases?  Check out the User- Defined Functions of Cosmos DB in these situations. You can easily create user-defined functions in Cosmos DB and reference them in your SQL queries.

     There are couple of things you need to know about them. First, User-defined functions are only for reading data. User-defined functions will always require more Request Units than regular SQL queries. You should always try to solve your application logic problem with regular queries first. Get familiar with system functions, be sure that you are not trying to write a user-defined function when there is already a system function solving the same problem. System functions will always use less Request Units than your custom user-defines function. Just like SQL Server, User-defined functions will cause a table-scan in Cosmos DB. That is why they cost more than regular queries. If you want to use the User-defined function in a where clause. Try to filter by other properties too. Other properties might hit to indexes and that will help you with request units.

    In the following example, I create a User-defined function to handle property name. I take an array from a document and use the index to change property name.      

function userDefinedFunction(input){
var obj={};
input.forEach(function(element,index){ 
obj["tag"+index] = {
    TagName :element.TagName,
    Value: element.Value
	};
}); 
return obj;
}

This returns the following data. tag custom object includes the index of an array in this example.