Check if field exists in CosmosDB JSON with SQL - nodeJS

Increase query performance with IS_DEFINED

Processing and querying JSON documents in CosmosDB can be resource-intensive. For more efficient queries, use IS_DEFINED to check the existence of a field. Unlike other methods, IS_DEFINED doesn't fetch the actual field data, which saves computational resources:

SELECT c.FieldName FROM c WHERE IS_DEFINED(c.FieldName)


This is better than:

SELECT c.FieldName FROM c WHERE c.FieldName != null


This contrasting example illustrates the comparative efficiency of the IS_DEFINED function.

Using IS_DEFINED in conditional SQL statements

Consider IS_DEFINED as a swiss army knife for conditional logic within CosmosDB. It can be used to create dynamic SQL statements:

SELECT 
    c.id,
    CASE
        WHEN IS_DEFINED(c.optionalField) THEN c.optionalField
        ELSE "N/A"
    END AS OptionalField
FROM c


This flexible approach to writing SQL statements allows you to adapt the output based on the existence of a field.

Potential pitfalls: Be aware of NULL values

IS_DEFINED does have its nuances. It does not differentiate between a field set to null, and a field that does not exist - both return false. So, if you want to differentiate between these situations, use a null check in tandem with IS_DEFINED:

SELECT c.FieldName FROM c
WHERE IS_DEFINED(c.FieldName) AND c.FieldName != null


Nested fields and IS_DEFINED: How deep can we go?

CosmosDB allows creating complex JSON documents with deeply nested fields. To check the existence of nested fields, use IS_DEFINED in your SQL query - it can go as deep as your fields do!

SELECT IS_DEFINED(c.education.university.degree.subject) AS subjectExists FROM c


This proves particularly useful for multi-tier nested documents.