View source for EXISTS & Array Functions (IS ARRAY, ARRAY CONTAINS)

&larr; Back to [[DP-420: Azure Cosmos DB Developer Specialty|DP-420 Terminology Reference]]

'''Category:''' Server-Side Programming

'''Summary:''' SQL subquery and array functions (EXISTS, IS_ARRAY, ARRAY_CONTAINS, ARRAY_LENGTH) for nested data.

== Overview ==
Azure Cosmos DB for NoSQL provides powerful query operators and built-in system functions for filtering, inspecting, and manipulating JSON arrays and variable-schema documents: '''1. EXISTS (Subquery Expression):'''

* A boolean operator that evaluates a subquery and returns <code>true</code> if the subquery returns at least one item, and <code>false</code> otherwise.
* '''EXISTS vs. JOIN:''' An intra-document <code>JOIN</code> on an array duplicates the parent document in the result set for every matching array element. In contrast, <code>WHERE EXISTS (...)</code> evaluates nested array criteria (using <code>c IN f.arrayProperty</code>) and returns each qualifying parent document ''exactly once'' without result duplication.
* '''Negation (NOT EXISTS):''' Filters for parent documents where no child array elements satisfy the subquery condition.

'''2. Built-In Array System Functions:'''
* '''ARRAY_CONTAINS(arr, expr [, bool_expr]):''' Checks whether an array contains a specific value. When searching for partial JSON objects, passing <code>true</code> as the optional third parameter enables partial/sub-object matching.
* '''ARRAY_LENGTH(arr):''' Returns the number of elements in the specified array.
* '''ARRAY_CONCAT(arr1, arr2 [, ...]):''' Concatenates two or more arrays into a single combined array.
* '''ARRAY_SLICE(arr, start_index [, length]):''' Returns a subset (slice) of an array starting at the zero-based index.
* '''ARRAY(subquery):''' Converts the output of a subquery into a JSON array within the projection.

'''3. Type Checking & Guard Functions:'''
* '''IS_ARRAY(expr):''' Returns <code>true</code> if the expression is a JSON array. Essential for guarding queries against documents with missing or non-array values.
* '''IS_OBJECT(expr):''' Returns <code>true</code> if the expression is a JSON object.
* '''IS_DEFINED(expr):''' Returns <code>true</code> if the property exists and is defined (not missing or undefined).
* '''IS_NULL(expr):''' Returns <code>true</code> if the property value is explicitly <code>null</code>.
* '''IS_PRIMITIVE(expr):''' Returns <code>true</code> if the value is a scalar type (string, number, boolean, or null).
* '''IS_STRING / IS_NUMBER / IS_BOOL:''' Type checking for specific scalar types.


Example 1 (Cosmos DB SQL): Filter parents where a nested child has multiple pets (EXISTS with ARRAY_LENGTH)

<pre>
-- Returns family IDs where at least one child in the children array has more than 1 pet
SELECT f.id 
FROM f 
WHERE EXISTS (
    SELECT VALUE c 
    FROM c IN f.children 
    WHERE ARRAY_LENGTH(c.pets) &gt; 1
)
</pre>

Using <code>EXISTS</code> returns each qualifying family document exactly once without cartesian product row duplication.

Example 2 (Cosmos DB SQL): ARRAY_CONTAINS (Scalar & Partial Object Matching) vs. EXISTS

<pre>
-- 1. Simple scalar check with ARRAY_CONTAINS (fast &amp; concise)
SELECT p.id, p.name 
FROM p 
WHERE ARRAY_CONTAINS(p.tags, "Sale")

-- 2. Partial object matching with ARRAY_CONTAINS (3rd argument = true)
SELECT f.id 
FROM f 
WHERE ARRAY_CONTAINS(f.children, { "gender": "female" }, true)

-- 3. Complex multi-condition check with EXISTS (predicates that ARRAY_CONTAINS cannot express)
SELECT f.id 
FROM f 
WHERE EXISTS (
    SELECT VALUE c 
    FROM c IN f.children 
    WHERE c.gender = "female" AND c.grade &gt;= 10
)
</pre>

Example 3 (Cosmos DB SQL): Guarding Schema-Agnostic Queries with IS_ARRAY & IS_DEFINED

<pre>
-- Safely query items where 'tags' is defined, is a valid array, has items, and contains 'Azure'
SELECT c.id, c.title
FROM c
WHERE IS_DEFINED(c.tags) 
  AND IS_ARRAY(c.tags) 
  AND ARRAY_LENGTH(c.tags) &gt; 0
  AND ARRAY_CONTAINS(c.tags, "Azure")
</pre>

Example 4 (Cosmos DB SQL): Array Manipulation with ARRAY_SLICE and ARRAY_CONCAT

<pre>
-- Slices top 2 tags and concatenates primary &amp; secondary color arrays
SELECT 
    p.id,
    ARRAY_SLICE(p.tags, 0, 2) AS TopTags,
    ARRAY_CONCAT(p.primaryColors, p.secondaryColors) AS AllColors
FROM p
WHERE IS_ARRAY(p.tags) AND IS_ARRAY(p.primaryColors)
</pre>

== More Info & Links ==
* [https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/query/subqueries#exists-expression Microsoft Learn: Subqueries in Azure Cosmos DB (EXISTS expression)]
* [https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/query/array-contains Microsoft Learn: ARRAY_CONTAINS in Azure Cosmos DB]
* [https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/query/is-array Microsoft Learn: IS_ARRAY in Azure Cosmos DB]
* [https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/query/array-functions Microsoft Learn: Array functions in Azure Cosmos DB]
* [https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/query/type-checking-functions Microsoft Learn: Type checking functions in Azure Cosmos DB]
* [https://learn.microsoft.com/en-us/credentials/certifications/resources/study-guides/dp-420 Microsoft Learn: DP-420 study guide]

== DP-420 Style Practice Questions ==
'''Q1.''' What is the primary advantage of using WHERE EXISTS (subquery) instead of a JOIN when filtering documents based on nested array properties?
* A. It returns each qualifying parent document exactly once without duplicating rows for multiple matching array elements
* B. It enables multi-region write replication on the container
* C. It automatically creates a spatial index over the nested array
* D. It allows modifying the nested array elements in place

'''Q2.''' When should you choose EXISTS (subquery) over ARRAY_CONTAINS when querying nested array data?
* A. When you need to evaluate complex expressions, multiple properties, or range conditions (such as ARRAY_LENGTH(c.pets) > 1) on array elements
* B. When checking if a flat array contains a simple scalar string value
* C. When you need to order documents by their partition key
* D. When the container uses gateway mode exclusively

'''Q3.''' Which Azure Cosmos DB SQL system function checks whether a property exists and is of JSON array type before executing array operations?
* A. IS_ARRAY(c.property)
* B. TYPE_OF(c.property) == 'Array'
* C. IS_LIST(c.property)
* D. CHECK_ARRAY(c.property)

== Answer Key ==
''Answers are listed at the very end so you can attempt the questions first.''

* '''Q1 (EXISTS & Array Functions (IS_ARRAY, ARRAY_CONTAINS)):''' A. It returns each qualifying parent document exactly once without duplicating rows for multiple matching array elements — WHERE EXISTS evaluates whether at least one matching element exists and returns the parent document without duplicating it, unlike JOIN which produces cartesian product duplicates.
* '''Q2 (EXISTS & Array Functions (IS_ARRAY, ARRAY_CONTAINS)):''' A. When you need to evaluate complex expressions, multiple properties, or range conditions (such as ARRAY_LENGTH(c.pets) > 1) on array elements — ARRAY_CONTAINS is ideal for simple scalar containment or partial object equality, whereas EXISTS with a subquery is required for complex multi-condition predicates and range evaluations on array items.
* '''Q3 (EXISTS & Array Functions (IS_ARRAY, ARRAY_CONTAINS)):''' A. IS_ARRAY(c.property) — IS_ARRAY returns a boolean indicating whether the target expression is a JSON array, preventing query errors across heterogeneous document schemas.

----
''DP-420: Designing and Implementing Cloud-Native Applications Using Microsoft Azure Cosmos DB — term detail page''

[[Category:DP-420 Terminology]]
[[Category:Server-Side Programming]]