EXISTS & Array Functions (IS ARRAY, ARRAY CONTAINS)

← Back to 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 true if the subquery returns at least one item, and false otherwise.
• EXISTS vs. JOIN: An intra-document JOIN on an array duplicates the parent document in the result set for every matching array element. In contrast, WHERE EXISTS (...) evaluates nested array criteria (using c IN f.arrayProperty) 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 true 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 true if the expression is a JSON array. Essential for guarding queries against documents with missing or non-array values.
• IS_OBJECT(expr): Returns true if the expression is a JSON object.
• IS_DEFINED(expr): Returns true if the property exists and is defined (not missing or undefined).
• IS_NULL(expr): Returns true if the property value is explicitly null.
• IS_PRIMITIVE(expr): Returns true 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)

-- 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) > 1
)

Using EXISTS 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

-- 1. Simple scalar check with ARRAY_CONTAINS (fast & 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 >= 10
)

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

-- 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) > 0
  AND ARRAY_CONTAINS(c.tags, "Azure")

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

-- Slices top 2 tags and concatenates primary & 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)

More Info & Links

• Microsoft Learn: Subqueries in Azure Cosmos DB (EXISTS expression)
• Microsoft Learn: ARRAY_CONTAINS in Azure Cosmos DB
• Microsoft Learn: IS_ARRAY in Azure Cosmos DB
• Microsoft Learn: Array functions in Azure Cosmos DB
• Microsoft Learn: Type checking functions in Azure Cosmos DB
• 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