Handle Empty Strings and Set Default Values in CosmosDB Query

Learn how to handle empty strings and set default values in Azure Cosmos DB queries using IS_DEFINED, IS_NULL, and ARRAY_CONTAINS. Improve data consistency, ensure meaningful results, and enhance query reliability with best practices.

Introduction

One of the challenges developers often face is managing data consistency, especially when dealing with missing or empty values in documents. Empty strings, null values, or absent fields can lead to unexpected behavior in queries, resulting in incomplete or incorrect results. 

You can handle empty strings and set default values in a Cosmos DB query using Azure Cosmos DB SQL (SQL API). While Cosmos DB itself doesn't have a built-in function like ISNULL or COALESCE (as in SQL Server), you can achieve similar functionality using conditional logic with ARRAY_CONTAINS, IS_DEFINED, or IS_NULL functions, along with the “?” operator for conditional expressions. 

This guide explores best practices for handling empty strings and setting default values in Cosmos DB queries. Whether you're working with JSON documents that have inconsistent schemas or need to ensure that your application receives meaningful data even when fields are missing, these strategies will help you write more resilient and maintainable queries.   

1. Using IS_DEFINED and IS_NULL 

Check if a field is defined or null, and provide a default value if it is. The below query checks if the "description" field is defined. If not, it returns "Default Description". 

SELECT 
    c.name, 
    IS_DEFINED(c.description) 
        ? c.description 
        : "Default Description" AS description 
FROM c;

2. Handling Empty Strings 

To handle empty strings, you can use a combination of IS_DEFINED, IS_NULL, and STRING_EQUALS. The below query ensures that if "description" is undefined, null, or an empty string, it returns "Default Description".

SELECT 
    c.name, 
    (IS_DEFINED(c.description) 
        AND NOT IS_NULL(c.description) 
        AND c.description != "") 
        ? c.description 
        : "Default Description" AS description  
FROM c;

3. Using “ARRAY_CONTAINS" for Default Values 

If you want to check for specific values (e.g., empty strings), you can use "ARRAY_CONTAINS”. The below query checks if “description" is either "null" or an empty string and returns "Default Description" in those cases. 

SELECT  
    c.name,  
    ARRAY_CONTAINS([null, ""], c.description)  
        ? "Default Description"  
        : c.description AS description  
FROM c;

4. Using "CASE" Statements (Not Supported in Cosmos DB) 

Cosmos DB does not support "CASE" statements. Instead, you must use the "?" operator for conditional logic. 

5. Example. Full Query with Default Value 

Here’s an example query that handles empty strings, null values, and undefined fields.

SELECT 
    c.id, 
    c.name, 
    (IS_DEFINED(c.description) 
        AND NOT IS_NULL(c.description) 
        AND c.description != "") 
        ? c.description 
        : "Default Description" AS description  
FROM c;

Alternative. Handle Default Values in the Application Code 

If the logic becomes too complex for a Cosmos DB query, you can handle default values in your application code after retrieving the data, for example, in C#.

// This approach allows you to handle default values more flexibly in your application logic 

var query = "SELECT c.id, c.name, c.description FROM c"; 
var items = container.GetItemQueryIterator<MyDocument>(query); 

foreach (var item in items) 
{ 
    var description = string.IsNullOrEmpty(item.Description)  
        ? "Default Description"  
        : item.Description; 
        
    Console.WriteLine($"Name: {item.Name}, Description: {description}"); 
}

Key Points to Remember

• Use IS_DEFINED to check if a field exists in the document.
• Use IS_NULL to check if a field is null.
• Use STRING_EQUALS or != "" to check for empty strings.
• Use the "?" operator for conditional logic to set default values.

Conclusion

Handling empty strings and setting default values in Cosmos DB queries is a critical aspect of ensuring data consistency and reliability in applications. By leveraging built-in functions like IS_NULL, IS_DEFINED, and COALESCE, developers can effectively manage missing or empty data. Additionally, conditional logic such as CASE or TERNARY allows for setting default values, ensuring queries return meaningful results even when fields are incomplete. These techniques not only improve query reliability but also enhance data quality, leading to more robust applications and better user experiences. As data continues to grow in complexity, mastering these strategies will empower developers to build more resilient systems and unlock the full potential of Cosmos DB.