Domain 1 of 3 · Chapter 2 of 5

Implement database operations

Point operations and queries: two ways to reach an item

Here is the cheapest way to fetch one product from Azure Cosmos DB for NoSQL.

Listing: a .NET point read by id and partition-key value

Product item = await container.ReadItemAsync<Product>(
    id: "68719518388",
    partitionKey: new PartitionKey("gear-surf-surfboards"));

The two arguments are the item's address. id is the item's identifier, and "gear-surf-surfboards" is the value the item holds at the container's partition-key path. Azure Cosmos DB needs both the id and the partition-key value to perform a quick point read[1]; with only the id you must fall back to a less efficient query across logical partitions (a logical partition is the set of items that share one partition-key value).

Each recurring requirement on stored items has an operation built for it: a known address takes a point read, a filtered search a parameterized query, a guarded or partial change an ETag condition or a Patch, an all-or-nothing group of writes a transactional batch or stored procedure, and automatic expiry time to live (TTL). Pick the correct one at the lowest cost. The Microsoft DP-420 study guide skills measured[2] places neighbouring work elsewhere in Design and develop database solutions: 'Create and manage database and containers' under Evaluate resource models and SDK solutions, 'Design and select partition keys based on cardinality, access patterns, and write distribution requirements' under Design and implement data models and partitioning strategies, and 'Implement change feed operations' under Implement change feed.

Point operations versus queries

Every item operation reaches data in one of two ways:

  • A point operation addresses one item by its id plus its full partition-key value. Reads, creates, replaces, upserts, deletes and partial updates are all point operations.
  • A query describes the items it wants with the NoSQL query language and lets the engine find them.

A query always targets one container. If its filter has an equality match on the partition key, the service routes it to the physical partition that holds that value[3] (a physical partition is a service-managed unit that hosts many logical partitions); without a routable partition-key restriction it fans out, runs once per physical partition and merges the results. A partition-key IN filter checks only the relevant physical partitions. That fan-out is a cross-partition query, and the Python SDK's query_items exposes it as the enable_cross_partition_query=True[4] argument. Crossing partitions never means crossing containers: joins are scoped to a single item and can't occur across multiple items and containers[5], so data spread over several containers takes separate requests that your code combines.

The figure below shows which call to reach for. The takeaway: an address you already know is a point read (or a ReadManyItemsAsync call for many addresses; see Point reads: typed items, raw streams and ReadMany below), and everything else is a query scoped to one container.

Know each item idand partition key?YesNoOne item?Query: in-partitionwhen it has anequality filter onthe partition key;otherwisecross-partitionYesNoPointreadReadManyItemsAsync
Choosing between a point read, ReadManyItemsAsync and a query in Azure Cosmos DB for NoSQL

What an item must contain

An item is a JSON document whose only required property is id[6], a string. The name is case-sensitive: properties named ID, Id, iD and _id are treated as arbitrary JSON properties, so a C# type must serialize its identifier as lowercase id. The service limits[7] add three constraints: an id is at most 1,023 bytes; the service accepts any Unicode character in it except / and \, while Microsoft strongly recommends alphanumeric ASCII ids for interoperability with SDKs and connectors; and a whole item is at most 2 MB.

Takeaway: give every item a lowercase string id of at most 1,023 bytes with no / or \, and keep the whole item within 2 MB.

Shaping query results: SELECT, VALUE and property paths

This section covers how the SELECT clause decides the shape of every result, because a client that expects strings and receives objects may have a result-shape mismatch. In these listings c and p are aliases declared in FROM for each item of the container being queried.

Objects or bare values

Listing: property projection versus SELECT VALUE

SELECT c.categoryName FROM c
SELECT VALUE c.categoryName FROM c

The first query returns one object per item, wrapping a categoryName property when it exists and otherwise returning an empty object. The second returns an array of bare categoryName values, because VALUE retrieves the JSON value and doesn't wrap the projected value in an object[8]. VALUE also accepts an object you construct, as in SELECT VALUE { name: p.name, link: p.metadata.link } FROM products p, when the client needs a custom shape without an extra wrapper. SELECT * returns each item unchanged and is valid only when FROM declares exactly one alias.

Paths, aliases and names

  • Dot notation walks nested objects (c.metadata.status), and [0] picks an array element by its zero-based index[9].
  • AS renames a projected property: SELECT c.metadata.status AS state FROM c returns a field named state sourced from metadata.status.
  • Property names are case-sensitive, so c.name and c.Name address two different properties.
  • A name with spaces, special characters or a reserved word needs bracket notation with quotes, such as c["display name"] or c["order"]. The quoted text is one literal property name, not a path of nested properties.

Missing properties disappear from projections

When a projected path does not exist on an item, its value is undefined rather than null, and an object can hold any value type except undefined[10]. The property is therefore left out of the projected object instead of being written as null; a projection of only that property yields an empty object, which is why SELECT DISTINCT p.category FROM products p returns {}[11] for items with no category.

Takeaway: use VALUE when the client wants scalars or a custom object, use AS and paths to rename and reach nested data, and expect absent properties to vanish from results rather than appear as null.

Filtering with WHERE: true, false and undefined

A WHERE clause keeps an item only when its filter evaluates to true; false and undefined both drop the item. This one rule explains most surprising empty results, because the NoSQL query language does not coerce types[12]: comparing values of different JSON types, or comparing against a property that doesn't exist (whose value is undefined), produces undefined, and the item isn't included because undefined doesn't equate to true.

Three-valued logic

The same operators page gives the truth tables: NOT undefined stays undefined, undefined AND false is false, and undefined OR true is true. So c.status != 'Closed' silently skips every item that has no status, and wrapping it as NOT (c.status = 'Closed') does not help. Add an explicit missing-property branch instead.

Listing: include items whose status is missing

SELECT * FROM c
WHERE c.status != 'Closed' OR NOT IS_DEFINED(c.status)

For an item without status, the first comparison is undefined and the second is true, so the OR is true.

Missing, null, or the wrong type

A property that is absent is undefined; a property present with the value null is an explicitly stored null. IS_DEFINED checks whether a property exists and IS_NULL checks whether it is null[9], so IS_NULL does not find items that omit a property. Presence is still not type: IS_NUMBER[13] returns true only for numbers (false for strings, Booleans, null, objects and missing values), and the type-checking functions IS_STRING and IS_ARRAY[14] follow the same pattern. Use them when the next step needs a particular type, such as numeric aggregation.

Ranges, lists and conditional values

  • BETWEEN is inclusive[15]: c.price BETWEEN @low AND @high keeps prices equal to either bound, which c.price > @low AND c.price < @high would exclude.
  • IN in a filter is a membership test against a list: p.category IN ("Accessories", "Clothing")[16]. It is unrelated to IN inside FROM or JOIN, which iterates an array (see Querying arrays inside an item below).
  • The ternary operator[17] condition ? valueIfTrue : valueIfFalse chooses an output per item, as in SELECT VALUE (c.active ? 'Active' : 'Inactive') FROM c. Filtering with WHERE c.active = true would instead remove the inactive items. As with other operators, ? excludes items whose referenced property is missing or has a different type.
  • The coalesce operator ?? supplies a fallback for an absent property: p.collapsible ?? false returns false when collapsible is not present and the stored value otherwise.

Takeaway: write filters that evaluate to true for every item you want, test absence with IS_DEFINED, and test types before computing on mixed data.

Built-in functions: strings, numbers, dates and locations

Suppose a product search must match codes by prefix, file names by suffix, descriptions by a pattern, and stores by distance. Built-in system functions cover each of these without a user-defined function, as long as you pick the one whose rule matches the requirement. This section groups them by the data they work on.

Strings

Function What it tests or returns Watch for
CONTAINS(s, t [, true]) t appears anywhere in s[18]; case-sensitive unless the third argument is true t is literal text, not a wildcard pattern; on an indexed path the function can perform a full index scan, which reads the distinct indexed values to identify matching items[19], not necessarily a full item scan, which loads every item from the transactional store to evaluate the filter
STARTSWITH(s, p [, true]) s begins with p[20] a match elsewhere in the string does not count
ENDSWITH(s, x [, true]) s ends with x[21] use it, not CONTAINS, to validate a suffix
s LIKE pattern [ESCAPE 'c'] pattern match[22]: % is any string of zero or more characters, _ one character, [a-f] and [^a-f] a character in or out of a set ESCAPE makes a wildcard literal, as in LIKE "%20^%%" ESCAPE "^"
CONCAT(a, b, ...) one string built from several[23] for strings only; arrays use ARRAY_CONCAT
TRIM(s [, chars]) removes leading and trailing whitespace[24], or the characters you list never touches characters in the middle, unlike a replace

The indexing guide[19] suggests adding selective filter predicates that use the index to speed up a query that filters with CONTAINS.

Numbers

ROUND[25] goes to the nearest integer (midpoint rounding away from zero, with an optional precision argument), CEILING[26] returns the smallest integer greater than or equal to its input, and FLOOR[27] the largest integer less than or equal to it. When capacity must always round up, ROUND(2.4) gives 2 where CEILING(2.4) gives 3.

Dates and times

Dates are ISO 8601 strings, not a separate date type. GETCURRENTDATETIME()[28] returns the current UTC date and time as an ISO 8601 string; it is nondeterministic, so to compare against "now" Microsoft advises obtaining the time before running the query and passing it as a constant. DATETIMEADD(part, amount, dateTime)[29] (written DateTimeAdd in some references) adds a signed integer of a date part such as 'day', so a negative amount subtracts, and it returns undefined for an invalid part, amount or date string. DATETIMEDIFF(part, start, end)[30] (likewise written DateTimeDiff) returns a signed integer counting the date-part boundaries crossed, not the elapsed interval, so two times two minutes apart on either side of midnight differ by one 'day'.

Locations (GeoJSON)

The query language supports two spatial types[31]: geometry for a flat (Euclidean) coordinate system and geography for round-earth locations. Shapes are stored as GeoJSON Point, LineString, Polygon and MultiPolygon objects; geography data uses the WGS-84 reference system only, and polygon points must be listed counter-clockwise with the last pair repeating the first.

Listing: a GeoJSON geography Point (longitude first)

{ "type": "Point", "coordinates": [-122.12826822304672, 47.63980239335718] }

For geography the order is longitude, then latitude, both in degrees. Three functions answer the common questions:

  • ST_DISTANCE(a, b)[32] returns the distance, expressed in meters for the default reference system, so a 5-km radius is ST_DISTANCE(s.location, @here) < 5000, not < 5.
  • ST_WITHIN(a, b)[33] is true when the first shape lies within the second; argument order matters.
  • ST_INTERSECTS(a, b)[34] is true when the shapes intersect at all, which also accepts partial overlaps that ST_WITHIN rejects.

Takeaway: choose the function whose rule is the requirement (prefix, suffix, pattern, rounding direction, date part, containment versus overlap), and keep units and argument order explicit.

Querying arrays inside an item: JOIN, functions and subqueries

This section covers arrays stored inside one item, such as a product's tags, because the NoSQL query language offers several tools for them and each returns a different shape. The running example is an item with "tags": [{ "name": "waterproof", "score": 7 }, ...].

JOIN expands an item's own array

FROM c JOIN t IN c.tags is a self-join: it pairs the item with each element of its own tags array, producing one row per element. This IN iterates an array, unlike the IN list test in a filter. Joins are scoped to a single item[5] and never combine separate documents, so a SQL-style join between book items and review items is not available. Joining two arrays creates their complete cross product within the item, which multiplies rows quickly.

Array functions work on one array value

Subqueries

A subquery is a query nested inside another. Because NoSQL queries always return a single column, only two subquery types apply[39]: a multi-value subquery (many rows), which on its own is used only in FROM, as a relational expression (a source of rows the outer query iterates), and a scalar subquery (one value) usable in SELECT, WHERE or FROM. A correlated subquery references the outer item and is evaluated once per outer row. Three correlated forms solve the array questions that JOIN handles poorly.

Listing: three subquery shapes over one item's tags

-- 1. Parents that have any high-scoring tag (one row per parent)
SELECT VALUE c.id FROM c
WHERE EXISTS (SELECT VALUE t FROM t IN c.tags WHERE t.score > @minimum)

-- 2. Each parent with only its matching tags nested inside
SELECT c.id, ARRAY(SELECT VALUE t FROM t IN c.tags WHERE t.score > @minimum) AS topTags
FROM c

-- 3. Each parent with a count of its matching tags
SELECT c.id, (SELECT VALUE COUNT(1) FROM t IN c.tags WHERE t.score > @minimum) AS matchingTags
FROM c
  • EXISTS returns true if its subquery returns any rows, so a parent with three matching tags still appears once; an outer JOIN would return it three times.
  • ARRAY(...) projects the subquery's results as a nested array and is allowed only in SELECT.
  • An aggregate scalar subquery, such as the COUNT(1) in query 3, summarizes each item's own children rather than the whole container.

In query 1, EXISTS is an aggregate scalar subquery built into the query language[39] returning one Boolean. In query 2, ARRAY(...) is an array expression[39], not a scalar subquery: its inner query may return many rows, and ARRAY projects them as one array value, which is why it may appear in SELECT while a multi-value subquery on its own stays in FROM.

Takeaway: use JOIN when you want one row per element, ARRAY_CONTAINS or EXISTS to filter parents, ARRAY(...) to return a filtered nested array, and an aggregate subquery for a per-item summary.

Aggregates, DISTINCT, TOP and GROUP BY

This section covers the clauses that summarize or trim a result set, where the classic mistake is choosing a function that answers a neighbouring question.

Counting, totals and averages

  • SELECT VALUE COUNT(1) FROM c WHERE ...[40] counts every matching item. COUNT(c.name) counts occurrences of the name expression, so items that omit name do not count. VALUE returns the number as a scalar instead of an object.
  • SUM(c.amount)[41] adds values, AVG(c.amount)[42] averages them, and MIN(c.price)[43] (with its counterpart MAX) returns the lowest or highest value of the expression, not the item that holds it.
  • SUM and AVG skip undefined inputs, but if any input is a string, Boolean or null, the entire aggregate returns undefined. Filtering with IS_DEFINED keeps those values; filter with IS_NUMBER (see Filtering with WHERE above) when only numbers should count.

DISTINCT, TOP and OFFSET LIMIT

  • DISTINCT eliminates duplicates in the projected results[11], so it compares whatever you project. SELECT DISTINCT VALUE c.category FROM c returns each category once; adding c.id to the projection makes every row unique again when the IDs are unique across the queried items.
  • TOP N returns the first N results in an undefined order[44]. Combined with ORDER BY it is a predictable way to get, say, the ten cheapest products (SELECT TOP 10 * FROM p ORDER BY p.price ASC). N can be a constant or a query parameter.
  • OFFSET m LIMIT n[45] skips m results and takes the next n; pair it with ORDER BY when a specific ordered window is required. It selects rows inside the query, which is different from the SDK page size described under paging below.

GROUP BY

GROUP BY collects rows that have the same values into summary rows[46], one per distinct value (or combination of values) of the grouping expressions. The query overview[47] shows the usual shape, the grouping value projected beside an aggregate:

Listing: one count per category

SELECT p.category, COUNT(1) AS productCount
FROM products p
GROUP BY p.category

ORDER BY p.category alone only sorts items; it never forms groups or counts.

Takeaway: COUNT(1) counts items, COUNT(expr) counts values, SUM totals and AVG averages only clean numeric input, DISTINCT deduplicates the whole projection, and TOP needs ORDER BY to mean "first".

Parameterized queries

Never paste user input into query text; bind it as a parameter. Azure Cosmos DB supports parameters written with the familiar @ notation[48], which gives robust handling and escaping of user input and prevents SQL injection, because the service treats the bound value as data rather than query syntax.

The request shape

Over the wire a parameterized query is a JSON object with the query text and a parameters array. Each parameter name must include the leading @.

Listing: a parameterized query specification (JSON)

{
  "query": "SELECT * FROM p WHERE (NOT p.onSale) AND (p.price BETWEEN 0 AND @upperPriceLimit)",
  "parameters": [
    { "name": "@upperPriceLimit", "value": 100 }
  ]
}

The SDKs build the same object for you:

A parameter can also supply the TOP count, as in SELECT TOP @pageSize * FROM products.

Values are untyped JSON

Parameter values can be any valid JSON: strings, numbers, Booleans, null, arrays or nested objects. Because the data is schema-free, parameters are not validated or converted against any type. Sending "100" (a string) to compare with a numeric price is a cross-type comparison that evaluates to undefined (see Filtering with WHERE above), so bind values with the JSON type you mean to compare.

Takeaway: bind every user-supplied value with an @ parameter of the right JSON type.

Query statement limits

The SQL query limits[7] bound the statement itself: at most 512 KB of query text, 10 JOIN statements and 10 user-defined functions (UDFs) per query. The JOIN and UDF counts can be raised through an Azure support request. These limits are separate from the 2-MB item-size limit and the per-page limits covered next.

Takeaway: design queries within the text, JOIN and UDF limits.

Paging through query results

A query result is a sequence of pages, and your code must keep asking until the service reports that no pages remain. Each page comes from a separate query execution[50], and to get accurate results you continue executing until there are no extra pages.

What limits one page

  • MaxItemCount (Python max_item_count) is set per request and tells the engine to return that number of items or fewer. It is an upper bound for one page, never a cap on the whole query; use TOP for that. -1 removes the client-side page limit.
  • A page can still be shorter, or even empty, because the container was throttled, the response grew too large, the execution ran too long, or extra executions were more efficient.
  • The service limits[7] set a 5-second maximum execution time for a single query page retrieval and a 4-MB maximum response. When a query reaches either, it returns a page and a continuation token, and there is no practical limit on how long one query can run across pages.

The .NET page loop

Listing: draining a FeedIterator page by page (.NET)

using FeedIterator<Product> feed = container.GetItemQueryIterator<Product>(
    queryDefinition: parameterizedQuery);

while (feed.HasMoreResults)                          // more pages remain
{
    FeedResponse<Product> page = await feed.ReadNextAsync();   // one page
    foreach (Product item in page)                   // items in this page
    {
        // ... process item
    }
}

GetItemQueryIterator returns a FeedIterator[49], not the complete result: HasMoreResults says whether pages remain and ReadNextAsync fetches the next one. Reading only the first FeedResponse silently drops later pages. LINQ follows the same model: build the query with GetItemLinqQueryable, convert it with ToFeedIterator(), and read pages asynchronously; enumerating the IQueryable directly runs synchronously.

Continuation tokens

A continuation token is an opaque bookmark of query progress. Query executions are stateless on the server[50], so no cursor stays open: a later request presents the token with the same query and resumes. In the REST API the token travels in the x-ms-continuation header, and a non-empty value means more results exist. With the same SDK version, tokens never expire, and a query returns a single token regardless of how many physical partitions it touches. Not every query can resume:

  • Queries with GROUP BY can't use continuation tokens, because they would need to store a large amount of state.
  • DISTINCT queries can use them only if you add ORDER BY.
  • In the Python SDK, cross-partition continuation tokens are supported for streamable queries such as SELECT * FROM c WHERE ..., but not for cross-partition queries that sort, count or use DISTINCT. A resumable sorted query that works in .NET therefore needs a different paging design in Python.

The figure below traces the loop. It ends when the iterator reports no more pages, not on tokens: a continuation token is an optional bookmark[50] you persist to resume in a later request, and only for the query shapes above. Takeaway: drain pages until HasMoreResults is false, use MaxItemCount for page size and TOP or OFFSET LIMIT for result size, and check the query shape before promising token-based paging.

Request the next page(ReadNextAsync)Service returns one pageof at most MaxItemCountitems (may be empty)More pages?(HasMoreResults)YesNoAll results read
The query paging loop: request pages until the SDK iterator reports no more pages (Azure Cosmos DB for NoSQL)

Point reads: typed items, raw streams and ReadMany

This section covers the read side of point operations: once you know an item's id and partition-key value (the address from the first section), three .NET methods and one Python method fetch it without running a query.

Cost: why the address matters

A request unit (RU) is the performance currency[51] that normalizes the cost of every database operation. The only factor affecting a point read's RU charge, besides the consistency level, is the size of the item[52]: a 1-KB item costs 1 RU and a 100-KB item 10 RUs. Under strong or bounded staleness, the two strongest of the five consistency levels[53], the RU cost of any read, point read or query, is doubled, so the 1-RU figure applies to the other three: session, consistent prefix and eventual. Point reads can only be made through the REST API or SDKs: a query that filters on one item's id and partition key is not considered a point read and is charged as a query. Microsoft therefore recommends giving items meaningful ids so they can be fetched by point read.

Three .NET read methods

  • ReadItemAsync<T>[1] deserializes the item into your type and returns an ItemResponse<T>, which also exposes metadata such as RequestCharge and StatusCode.
  • ReadItemStreamAsync returns a ResponseMessage whose Content is the raw JSON stream, without deserializing it. When a component only forwards the JSON (an API that returns it as-is), the stream avoids deserializing into an object and serializing it again.
  • ReadManyItemsAsync<T> takes a list of (id, PartitionKey) pairs and returns the matching items. Microsoft states it is meant to perform better, latency-wise, than a query with IN statements when fetching many independent items whose addresses you already hold.

Listing: reading two known items with ReadManyItemsAsync (.NET)

PartitionKey pk = new("gear-surf-surfboards");
List<(string, PartitionKey)> itemsToFind = new()
{
    ("68719518388", pk),
    ("68719518381", pk)
};
FeedResponse<Product> found = await container.ReadManyItemsAsync<Product>(itemsToFind);

In Python the equivalent single-item call is read_item(item, partition_key)[4]; like every point operation it needs the partition key, and calling it with only an id does not turn it into a cross-partition lookup.

Takeaway: when the address is known, a point read is the cheapest read; choose the typed, stream or ReadMany form by what the caller does with the result.

Create, replace, upsert and delete

This section covers the four whole-item writes. They differ in one question, whether the item must already exist, so choose by the outcome you want when an item with that id is or is not present in the logical partition.

Operation (.NET) Item must already exist? What happens
CreateItemAsync[6] No, it must not Inserts the item; throws on a conflict with an existing item's id (HTTP 409)
ReplaceItemAsync Yes Replaces the existing item; the id argument must match the item's id
UpsertItemAsync[54] Either Creates or replaces; HTTP 201 means created, 200 means replaced
DeleteItemAsync<T>(id, partitionKey)[55] Yes Removes the item; the response Resource is always null

Replace sends the whole item

A replace is a whole-document write: the body you send becomes the complete new item. The classic update path is to read the item, change it locally and send it over the wire as a whole document Replace API call[56], so a body that contains only the changed fields replaces the item with just those fields. Use partial document update (Patch, covered below) when only some properties should change. Use upsert when both outcomes are acceptable, and read its status code when the caller needs to know which one happened.

Delete returns no body

DeleteItemAsync takes the id and partition-key value (plus optional request options and a cancellation token). Its response still carries metadata, but Resource is null, so archive any data you need before deleting rather than expecting it back.

Skipping the echoed body on writes

By default a create, upsert, replace or patch returns the written item. Setting EnableContentResponseOnWrite = false[57] on ItemRequestOptions returns only headers and the status code, with a null resource, which reduces network and CPU load. Headers such as the request charge stay available. The .NET best practices[58] recommend it for heavy create payloads and note one side effect: it also disables the response from a trigger operation (a trigger is server-side JavaScript that a write request names to run before or after the write; see Triggers below).

Takeaway: create when the item must be new, replace when it must exist and you hold the full body, upsert when either is fine, and do not expect a delete or a content-free write to return the item.

Optimistic concurrency with ETags

Two users read the same item, each changes it, and each saves: without a check, the second save silently overwrites the first (a lost update). Azure Cosmos DB prevents this with optimistic concurrency control (OCC), which rejects a write based on a stale version.

The mechanism

Every item has a system-defined _etag property[59] (its entity tag, or ETag) that the server generates and changes every time the item is updated. The parties and steps are:

  1. The client reads the item and keeps its _etag.
  2. The client sends a replace or delete with an if-match condition carrying that ETag.
  3. The server compares: if it still matches the current _etag, the write commits: a replacement gets a new _etag, while a delete removes the item; if not, the server rejects the write with HTTP 412 Precondition Failed and nothing is written.
  4. After a 412 the client re-reads the item, reapplies its intended change under the business rules, and retries with the new ETag.

For replace operations, if-match must be set explicitly in the request options[59], and the figure below traces the full read, conditional write and 412 loop.

Listing: a conditional replace in .NET

ItemResponse<Product> read = await container.ReadItemAsync<Product>(id, pk);
Product changed = read.Resource with { quantity = read.Resource.quantity - 1 };

await container.ReplaceItemAsync(changed, id, pk,
    new ItemRequestOptions { IfMatchEtag = read.ETag });   // 412 if someone wrote first

read.ETag is the entity tag on the response[60], and IfMatchEtag[61] sets the If-Match condition. In Python, pass etag=... with match_condition=MatchConditions.IfNotModified to replace_item[4]; a stale ETag raises an HTTP 412 error.

Reacting to 412

The resilience guidance[62] marks 412 as not retryable as-is: retry only after reading the latest version and updating the ETag on the request. Repeating the same request, or applying a back-off loop meant for throttling, sends the same stale ETag and fails the same way. Do not confuse the ETag with the session token: the session token tracks read progress for session consistency, while the ETag identifies one item's version.

Validating a cache with If-None-Match

The reverse condition checks whether a cached copy is still current. Set IfNoneMatchEtag[63] on ReadItemAsync to the cached version's ETag: a matching ETag returns HTTP 304 Not Modified, and a different one returns the current item with HTTP 200. IfMatchEtag would be the wrong choice here, because it guards writes rather than asking for a 304.

Takeaway: carry the ETag from read to write as If-Match, treat 412 as "re-read and reapply", and use If-None-Match to revalidate cached reads.

Read item, keep _etagReplace or deletewith If-Match = ETagServer _etag stillmatches If-Match?YesNoWrite commits:replace gets new_etag; deleteremoves the itemHTTP 412: nothingwritten; re-readand reapplyretry
Optimistic concurrency control with _etag and If-Match, including the HTTP 412 re-read loop (Azure Cosmos DB for NoSQL)

Handling write timeouts, failed requests and contention

This section covers which failures your application should retry, because a retry that cannot succeed only adds latency, and a retry of the wrong write can apply a business action twice. The rule: retry only what is transient, and fix or reconcile everything else.

Status codes and what to do

The resilient SDK application guidance[62] classifies the codes you meet on item operations (excerpt; the full table also lists 401, 403, 404, 410 and 500):

Status Meaning Retry?
400 Bad Request The request is invalid No: correct the request
408 Request Timeout The response did not arrive in time Yes, with care for writes (below)
409 Conflict The id and partition key are taken, or a unique key is violated No
412 Precondition Failed The ETag is stale No: re-read and reapply (see the ETag section above)
413 Request Entity Too Large The payload is too big No: shrink the payload
429 Too Many Requests Throttled Yes: the SDK retries, honoring x-ms-retry-after-ms
449 Retry With Transient write contention Yes: safe to retry
503 Service Unavailable Connectivity failure Yes

400 and 413 never fix themselves: waiting and resending the same oversized or malformed request fails again. In the 409 row, a unique key is a container's unique key policy, created with the container, which makes one or more values unique within a logical partition[64].

Timeouts: reads are retried, writes are not

The SDKs retry timeouts and connectivity failures (408 and 503) when a retry is feasible: they retry reads, but they do not retry writes, because writes are not idempotent and after a timeout it is impossible to know whether the request reached the service. A timed-out write may therefore have committed. Retrying a create can return HTTP 409 Conflict because the first attempt did succeed. Design your own retry policy around operation identity: a create with a deterministic id turns a duplicate into a detectable 409, whereas blindly repeating a Patch Increment or another non-idempotent action can apply it twice. The Python SDK makes this an explicit opt-in: its retry_write[4] option retries a write even when it is not guaranteed to be idempotent, and defaults to no retries.

449: contention, not a stale version

HTTP 449 is a transient error that only occurs on writes, typically when too many concurrent operations update the same item. The SDKs retry it with an incremental back-off for a fixed period, and Microsoft recommends a random back-off in your own retries so competing writers do not collide again on the same schedule. Unlike 412, the request itself is fine and can be resent unchanged; unlike 409, it says nothing about a duplicate id.

Takeaway: retry 408, 429, 449 and 503; never blind-retry 400, 409, 412 or 413; and treat a write timeout as "outcome unknown" until operation identity proves otherwise.

Partial document update (Patch): the operations

Partial document update, also called the Patch API, lets a client send only the modified properties[56] as a list of operations instead of a whole-document replace. Each operation names a target path inside the item. The benefits are less client CPU, lower end-to-end latency and less network bandwidth; a significant RU reduction is not guaranteed, since Patch is normalized into request units like other operations[65] and users shouldn't expect a significant RU reduction.

The six operations

Operation Effect Fails when
Add Adds a missing property or replaces an existing one; at a valid array index it inserts and shifts later elements; index equal to the length, or -, appends The array index is greater than the length, or the parent node does not exist (no path found beyond it[66])
Set Like Add, except at a valid array index it updates the existing element; adds a missing property The parent node does not exist (no path found beyond it[66]); for arrays, Microsoft's Set description[56] covers only a valid array index
Replace Strict replace-only of an existing property or array The target does not exist
Remove Removes the target; removing an array element shifts later elements back The target does not exist
Increment Adds a positive or negative value; creates the field with that value if absent The target is not a number
Move Removes the value at from and adds it at path from does not exist, or path is a child of from

Two useful contrasts follow. Add versus Set matters only for arrays: Add at an index inserts, Set overwrites. Set versus Replace matters for missing properties: Set creates them, while Replace fails, which makes Replace the right choice when a property must already exist. Increment adjusts a counter atomically on the server, avoiding a client read-modify-write in which concurrent writers overwrite each other.

Paths

Paths are JSON Pointer locations such as /inventory/quantity. Inside a property name, ~ must be escaped as ~0 and / as ~1, so a property literally named a/b is /a~1b, not two nested properties. An Add (or Set) can only create a child of an existing node and can't create a path recursively[66]: adding /profile/address/city requires /profile/address to exist first.

Listing: a Patch document and its effect (from the Microsoft example)

[
  { "op": "add", "path": "/color", "value": "silver" },
  { "op": "remove", "path": "/used" },
  { "op": "set", "path": "/price", "value": 355.45 },
  { "op": "incr", "path": "/inventory/quantity", "value": 10 },
  { "op": "add", "path": "/tags/-", "value": "featured-bikes" },
  { "op": "move", "from": "/color", "path": "/inventory/color" }
]

The JSON form writes operation names in lowercase, and Increment appears as incr. Applied to an item with quantity: 15, it removes used, sets the price, raises quantity to 25, appends a tag, and moves the new color under inventory.

The operation set is inspired by JSON Patch RFC 6902[65] without implementing all of it: Copy and Test are not implemented. Where RFC 6902 would use Test to guard an update, Azure Cosmos DB uses the conditional Patch predicate described next.

Takeaway: pick the operation by its existence rule (Add/Set create, Replace/Remove require), use Increment for counters, and escape ~ and / in names.

Calling Patch: SDK shape, conditions and limits

This section covers how a Patch request is built and constrained in the SDKs, building on the operations from the previous section.

The .NET call

Listing: a conditional PatchItemAsync call (.NET)

PatchItemRequestOptions options = new()
{
    FilterPredicate = "FROM products p WHERE p.used = false"   // business-state condition
};
List<PatchOperation> operations = new()
{
    PatchOperation.Replace("/price", 100.00),
    PatchOperation.Increment("/inventory/quantity", -1)
};
ItemResponse<Product> response = await container.PatchItemAsync<Product>(
    id: "e4e4e4e4-ffff-aaaa-bbbb-c5c5c5c5c5c5",
    partitionKey: new PartitionKey("road-bikes"),
    patchOperations: operations,
    requestOptions: options);

PatchItemAsync<T>[66] takes the item's id, its partitionKey and a patchOperations list built with PatchOperation methods (Add, Set, Replace, Remove, Increment, Move). The request describes changes; it is not a partial item body, which is why passing a partial body to ReplaceItemAsync is a different and destructive operation. In Python, call patch_item(item=..., partition_key=..., patch_operations=[...]) with a list of {"op": ..., "path": ..., "value": ...} dictionaries[4].

Conditional Patch

The condition lives on the Patch request itself: in .NET set FilterPredicate on PatchItemRequestOptions (QueryRequestOptions belongs to queries and has no effect here), and in Python pass filter_predicate. The predicate is a SQL-like expression such as from c where c.quantity > 0, and the operation fails if the precondition isn't satisfied[56]. Because the service evaluates the predicate and applies the change in one request, it closes the race that a separate read-then-patch leaves open.

Limits and special properties

The Patch FAQ[65] sets three rules:

  • A single patch specification holds at most 10 operations, counting every operation even when several touch the same property.
  • System-generated properties such as _ts, _etag and _rid can't be patched.
  • The ttl property can be patched, so an item's lifetime can change without replacing the document (TTL rules are in the last section).

Patching several items together

Multiple documents with the same partition-key value can be patched as part of a transaction: add PatchItem operations to a transactional batch (with TransactionalBatchPatchItemRequestOptions carrying any FilterPredicate), and the batch commits only if every operation succeeds. Patch operations can also run through the bulk APIs.

Takeaway: send a PatchOperation list of at most 10 operations, put business conditions in FilterPredicate, never target system properties, and use a transactional batch when several items in one logical partition must change together.

Bulk execution versus transactional batch

When an application has many item operations to send, the SDKs offer two client-side mechanisms that solve different problems, and a third, the stored procedure, follows in the next section:

  • Bulk execution maximizes throughput for many independent operations. Each operation still succeeds or fails on its own.
  • Transactional batch makes a group of point operations in one logical partition succeed or fail together.

Choose by whether the operations must commit as one unit; the comparison table Multi-operation writes: bulk execution vs transactional batch vs stored procedure, after the final section, lines up all three mechanisms.

Bulk execution (.NET)

Set AllowBulkExecution = true[67] on CosmosClientOptions. It enables optimistic batching of requests to the service and might add latency to individual operations, so Microsoft recommends it only for non-latency-sensitive scenarios. Once set, the options are effectively immutable for the lifetime of that CosmosClient[68]. Here optimistic batching means the client groups concurrent operations into single service calls[68], distributes them across partitions, and hands each result back to its original caller, so it only helps when many operations are in flight at once. Despite the shared words, it is unrelated to ETag-based optimistic concurrency, and these groups are not transactional batches.

Listing: bulk import with concurrent tasks (.NET)

CosmosClient client = new(endpoint, credential,
    new CosmosClientOptions { AllowBulkExecution = true });
Container container = client.GetContainer("bulk-tutorial", "items");

List<Task> tasks = new();
foreach (Item item in itemsToInsert)
{
    tasks.Add(container.CreateItemAsync(item, new PartitionKey(item.pk)));  // do not await here
}
await Task.WhenAll(tasks);   // the SDK groups the in-flight operations

Awaiting each CreateItemAsync before starting the next leaves nothing concurrent for the SDK to group. Bulk does not make the operations atomic: a transfer that debits one item and credits another needs a transaction, not bulk.

Transactional batch

A transactional batch[69] groups point operations that share one partition-key value in one container. Azure Cosmos DB gives full ACID guarantees with snapshot isolation for operations within the same logical partition key: the operations run in the order you added them, and if any fails the whole transaction rolls back. Items that merely share a physical partition but have different partition-key values cannot join one batch. Its limits apply together: a payload of at most 2 MB, an execution time of at most 5 seconds, and at most 100 operations. Splitting oversized work into two batches creates two independent transactions.

Listing: building and executing a batch (.NET)

TransactionalBatch batch = container.CreateTransactionalBatch(new PartitionKey("road-bikes"));
batch.CreateItem<Product>(bike);
batch.CreateItem<Part>(part);                       // nothing is sent yet

using TransactionalBatchResponse response = await batch.ExecuteAsync();   // one request
if (response.IsSuccessStatusCode)
{
    Product created = response.GetOperationResultAtIndex<Product>(0).Resource;
}

Building the batch and adding operations submits nothing; ExecuteAsync sends all operations as a single request. When the batch fails, the operation that caused it keeps its real status code and every other operation reports HTTP 424 Failed Dependency, so look for the one result that is not 424: a duplicate create shows 409 on that operation and 424 on its peers.

In Python, each operation is a tuple of an operation-type string, an arguments tuple and an options dictionary, and execute_item_batch(batch_operations=..., partition_key=...)[69] runs the list. A failure raises CosmosBatchOperationError, whose error_index and operation_responses identify the failing operation. The options dictionary accepts if_match_etag (or if_none_match_etag) on any operation and filter_predicate on a patch operation, as in ("replace", (item_id, item_body), {"if_match_etag": etag}). A failed ETag precondition fails the whole batch, so optimistic concurrency (see the ETag section above) composes with the atomic boundary instead of needing a separate, racy version check.

Compared with a stored procedure, a transactional batch runs in your SDK language, versions with your application code, and Microsoft cites up to 30% lower latency for equivalent operations.

Takeaway: bulk for throughput across independent operations, transactional batch for an ordered all-or-nothing group on one partition-key value within 100 operations, 2 MB and 5 seconds.

Stored procedures: server-side transactions

A transactional batch is a fixed list decided before the request leaves the client. When the transaction must decide what to write based on what it reads (move stock only if enough remains, then record the move), a stored procedure can run that logic inside the database: it is a JavaScript function registered on a container and executed inside the database engine[70]. C# or Python code cannot run there.

The transaction model

  • Scope. Every execution supplies one logical partition-key value, which defines the transaction's scope.
  • Commit and rollback. All operations in the function are automatically wrapped in one transaction: if the function completes without an exception, everything commits; if it throws, the runtime rolls back everything. BEGIN TRANSACTION and COMMIT are implicit, and throwing is the equivalent of ROLLBACK. Catching an error and returning normally therefore commits the earlier writes.
  • Concurrency. The _etag values of all written items are implicitly checked[59]; on a conflict the whole stored procedure rolls back and throws, and the application must reapply and retry the original request rather than retrying only the last write.
  • Visibility. Queries run inside a stored procedure or trigger may not see changes made earlier by the same script transaction, for SQL queryDocuments and JavaScript language-integrated queries alike.

Writing one

A stored procedure gets its context with getContext(); getCollection() performs database operations, and getResponse().setBody(...) sets what the caller receives. Database operations complete through callbacks[71], and each returns a Boolean saying whether it was accepted.

Listing: inserting an array of items with acceptance checks (JavaScript, from the Microsoft sample)

function createToDoItems(items) {
    var collection = getContext().getCollection();
    var collectionLink = collection.getSelfLink();
    var count = 0;
    if (!items) throw new Error("The array is undefined or null.");   // throw = roll back
    var numItems = items.length;
    if (numItems == 0) {
        getContext().getResponse().setBody(0);
        return;
    }
    tryCreate(items[count], callback);

    function tryCreate(item, callback) {
        var options = { disableAutomaticIdGeneration: false };
        var isAccepted = collection.createDocument(collectionLink, item, options, callback);
        if (!isAccepted) getContext().getResponse().setBody(count);   // stop queuing; report progress
    }
    function callback(err, item, options) {
        if (err) throw err;
        count++;
        if (count >= numItems) getContext().getResponse().setBody(count);
        else tryCreate(items[count], callback);                        // queue the next one
    }
}

Bounded execution

Stored procedures, triggers and UDFs have a 5-second timeout, and an operation not completed in time rolls the transaction back. When an operation returns false (not accepted), the script is consuming more time or throughput than allowed and must wrap up instead of queuing more work; operations queued before the first unaccepted one are guaranteed to finish if the script completes in time. That is why the listing queues one operation at a time through callbacks and returns its progress count, letting the client call again for the rest. Scripts that repeatedly violate execution boundaries may be marked inactive and must be recreated to honor the boundaries. Raising a client setting such as CosmosClientOptions.RequestTimeout changes how long the client waits, not the server-side limit. Server-side JavaScript also doesn't support importing modules[70], so an npm package cannot be loaded.

Cost and placement

Each execution requires an upfront RU charge equal to the script's average charge in previous invocations[71]; if invocation costs vary widely, budget utilization suffers, and Microsoft suggests batch or bulk requests instead. Each physical partition is served by a replica set whose replicas collectively support its throughput[72]. Stored procedures and triggers always run on one of them, the primary replica (the others are secondary replicas), and are intended for transactional writes; read-heavy work therefore belongs in application-side SDK queries, whose reads are served by the other replicas where possible[73] and help you saturate the database throughput[70].

Registering, running and logging (.NET v3)

The figure below traces one execution. Takeaway: a stored procedure is a partition-scoped JavaScript transaction that commits on normal return, rolls back on any exception, ETag conflict or timeout, and suits write-heavy logic that must branch on reads.

Execute with onepartition-key valueRUs reserved upfront:average of past runsScript queues operations;stops when one returnsfalse (not accepted)Exception, ETagconflict, or 5 sexceeded?YesNoRoll backevery writeCommitevery write
One stored procedure execution, from upfront RU reservation to commit or rollback (Azure Cosmos DB for NoSQL server-side JavaScript)

Triggers

A trigger is server-side JavaScript that runs alongside one write and follows the stored-procedure runtime rules from the previous section (5-second limit, no module imports).

Triggers run only when a request names them

Registered triggers don't run automatically[70] when their operations happen; each request must name the trigger explicitly. A client that omits it bypasses it, so a trigger cannot be the sole enforcer of a rule for every writer.

  • A pre-trigger runs before the item operation. It takes no input parameters of its own, but it can use the request object to validate or update the document body.
  • A post-trigger runs after the operation completes and has access to the response message sent to the client.
  • When a trigger is registered, you specify the operations it may run with: a trigger created with TriggerOperation.Create is not permitted on a replace.
  • Operations a trigger performs are part of the same atomic transaction as the write that invoked it.

Listing: invoking a pre-trigger on a create (.NET v3 and Python)

await container.CreateItemAsync(newItem, null,
    new ItemRequestOptions { PreTriggers = new List<string> { "trgPreValidateToDoItemTimestamp" } });
container.create_item(item, pre_trigger_include="trgPreValidateToDoItemTimestamp")

The how-to guide[74] adds that even though the trigger name is passed as a list, you can still run only one trigger per operation, so you cannot chain several pre-triggers on one write. The .NET reference applies that limit per trigger type (one PreTrigger[76] and one PostTrigger[77] per operation), so a write can name at most one pre-trigger and one post-trigger. Post-triggers are named the same way through PostTriggers (post_trigger_include in Python). Remember from the write section that EnableContentResponseOnWrite = false also suppresses a trigger's response.

Takeaway: triggers are opt-in per request, at most one pre-trigger and one post-trigger per operation, each scoped to the operations it was registered for.

User-defined functions (UDFs)

A user-defined function (UDF) is server-side JavaScript that extends the query language with custom logic and can be called only within queries. It follows the stored-procedure runtime rules but never runs alongside a write.

UDFs compute values inside queries

A UDF has no access to the context object, so it is compute-only: it cannot read or write other items, and for the same reason it can run on secondary replicas. Choose a stored procedure or trigger when the server-side logic must touch data. A query calls a registered UDF with the udf. prefix, which separates it from built-in functions:

Listing: calling a registered UDF in a filter

SELECT * FROM Incomes t WHERE udf.Tax(t.income) > 20000

The same call works in a projection, and a query may use at most 10 UDFs by default (see Query statement limits above). Invoking a UDF is part of the query text; ExecuteStoredProcedureAsync runs stored procedures, not UDFs.

Takeaway: UDFs are compute-only helpers called as udf.Name(...) inside queries.

Time to live (TTL): automatic expiration

Time to live (TTL) makes Azure Cosmos DB delete items automatically[78] a set number of seconds after their last modification, without the application issuing deletes. It is two settings that work together: a container switch and an optional per-item override.

The container switch: DefaultTimeToLive

The container property is DefaultTimeToLive in .NET (defaultTtl in the JavaScript SDK, default_ttl in Python):

  • missing or null: TTL is off and items never expire automatically, even if they carry a ttl property;
  • -1: TTL is on, but items do not expire by default;
  • a positive number n: items expire n seconds after their last modification.

The item override: ttl

An item's ttl property applies only when the container's DefaultTimeToLive is present and not null[78]. It then overrides the container default for that item, as the figure below shows for one item. A valid item ttl is -1 (this item never expires) or a nonzero positive integer up to 2,147,483,647 seconds; null and 0 are not valid. To inherit the container default, leave the property out, so removing an item's ttl under a positive default makes that item expire, while ttl: -1 protects it. Because ttl can be patched[65], an item's lifetime can change without replacing it.

The clock restarts on every write

TTL is a delta from the item's last-modified timestamp _ts. A write or update sets _ts to the current time and the countdown starts again[79], so a frequently updated item can live far longer than its creation time plus TTL. Native TTL is therefore an inactivity timer, not an immutable retention deadline.

Expiry versus deletion

An item stops appearing in query responses immediately after its TTL expires, even though deletion is a background task that runs later. If the container lacks spare request units, the physical deletion is delayed until enough are available. The cost depends on the capacity mode: on provisioned-throughput accounts deletion uses leftover RUs not consumed by user requests, while on serverless accounts it is charged at the same rate as delete operations, which belongs in any serverless cost estimate for short-lived data.

Indexing must stay on

TTL requires indexing[80]: you can't activate TTL on a container whose indexing mode is none, and you can't switch a TTL-enabled container to none. A key-value container that needs expiration keeps consistent mode with no included paths and /* as the only excluded path.

Takeaway: set DefaultTimeToLive first, use item ttl to override (-1 keeps, omit to inherit), expect the countdown to restart on every write, and keep the indexing mode consistent.

ContainerDefaultTimeToLive set?No or nullYesTTL off:nothing expiresItem has attl property?YesNoContainer default:-1 never expires,n: after n secondsItem ttl overrides:-1 never expires,n: after n secondsCountdown restarts atevery write (_ts)
How DefaultTimeToLive and an item's ttl decide when one item expires in Azure Cosmos DB for NoSQL

Multi-operation writes: bulk execution vs transactional batch vs stored procedure

CriterionBulk executionTransactional batchStored procedure
Commits as one unitNo: each operation succeeds or fails on its ownYes: ACID with snapshot isolation; any failure rolls back allYes: implicit transaction; an exception rolls back all
ScopeMany independent operations, any partition-key valuesOne container, one partition-key valueOne container, one partition-key value given at execution
Written inYour SDK language (.NET `AllowBulkExecution`)Your SDK languageJavaScript registered on the container
Documented limitsOptimized for throughput, may add per-operation latency100 operations, 2 MB payload, 5 seconds5-second execution limit; stop queuing when an operation is not accepted
Branches on data read mid-transactionNoNo: a predefined list of point operationsYes: JavaScript control flow over reads
Best fitImports and mass updates where throughput matters more than latencyA known set of related writes that must succeed togetherWrite-heavy transactional logic that decides based on reads

Decision tree

Several writes on onepartition-key value mustcommit together?YesNoLogic branches ondata it reads?Many independentwrites, throughputover latency?YesNoYesNoStoredprocedureTransactionalbatchBulkexecutionChange only someproperties?YesNoPatchCreate,replace,or upsertLost updates onreplace or delete?Add If-Match ETag

Sharp facts the exam loves — give these one last read before exam day.

Cheat sheet

Sharp facts the exam loves — scan these before test day.

SELECT VALUE returns the projected JSON value without an object wrapper

Use SELECT VALUE c.categoryName FROM c to return an array of category-name values rather than objects containing a categoryName property. A regular property projection returns an object per result. VALUE can also project an explicitly constructed JSON object when the application needs a custom result shape.

Trap Using SELECT c.categoryName for a client expecting scalar strings, which produces property-bearing objects instead.

2 questions test this
Query projections can rename fields and access nested JSON values

The SELECT list can rename a projected property with AS and access nested properties using dot notation. For example, SELECT c.metadata.status AS state FROM c returns a state field sourced from metadata.status. Array elements can be accessed by index when a particular position is intended.

3 questions test this
IS_DEFINED tests property presence while IS_NULL tests an explicit null value

Use IS_DEFINED(c.optionalField) to determine whether a JSON property exists. Use IS_NULL(c.optionalField) to test whether its value is null; a missing property is undefined rather than null. These functions distinguish omitted data from a deliberately stored null.

Trap Using IS_NULL to select every item missing an optional property, which tests a different JSON state.

2 questions test this
ARRAY_CONTAINS requires true for a partial object match

ARRAY_CONTAINS(array, value) checks whether the array contains the specified value and defaults to full-object matching. Supply a third argument of true to match an object using only the supplied subset of properties. This tests array membership without projecting one result for every element.

Trap Omitting the third argument when matching only one field of a larger array object, which requests a full rather than partial object match.

2 questions test this
NoSQL JOIN expands arrays within an item rather than joining documents

Use JOIN t IN c.tags to enumerate an item's array elements and filter or project each matching element. NoSQL joins are scoped to a single item and do not join separate documents or containers. Multiple joined arrays produce a cross product within that item.

Trap Using a SQL-style JOIN between book items and review items, which is not a supported cross-document join.

2 questions test this
DISTINCT removes duplicates from the projected query results

When a query must return each category value only once, use SELECT DISTINCT VALUE c.category FROM c. DISTINCT compares the projected results, so repeated category values collapse into one result even when they come from different items.

Trap Projecting id alongside category in SELECT DISTINCT to produce a category-only list, which makes deduplication compare the entire projected object instead of category alone.

2 questions test this
TOP needs ORDER BY to select a predictable ordered subset

TOP limits how many query results are returned but does not itself define which ordered values come first. Pair it with ORDER BY when the requirement is the cheapest or otherwise ordered first N results. A parameter can supply the TOP count.

Trap Using TOP alone to retrieve the cheapest items, which limits results without specifying price order.

2 questions test this
COUNT of a constant counts matching items while COUNT of a property counts its values

Use SELECT VALUE COUNT(1) FROM c with the required WHERE filter to count matching items. COUNT(c.name) instead counts occurrences of the name expression, which is a different requirement when items may omit that property. VALUE produces a scalar count rather than an object-wrapped aggregate.

Trap Using COUNT(c.optionalField) as the total item count, which counts values of an optional expression rather than every matching item.

2 questions test this
Multi-value and scalar subqueries can be used in different query positions

A multi-value subquery returns multiple single-column results and is used as a relational expression in FROM. A scalar subquery returns a single value and can appear in SELECT or WHERE as well as FROM. NoSQL query results have one JSON value column, so relational table subqueries with multiple columns are not the model.

2 questions test this
Query text and expression counts have independent service limits

The documented maximum SQL query text length is 512 KB. A query normally supports up to 10 JOIN statements and 10 UDFs, with increases to those count limits available through support. Parameterization and document size limits do not remove query-text or expression-count constraints.

3 questions test this
A query targets one container rather than spanning database containers

A NoSQL query may span logical partitions within its selected container, but it does not query several containers or accounts as one resource. If a model distributes data across containers, application code must issue the relevant requests separately and combine their results. Cross-partition querying and cross-container querying are different capabilities.

Trap Treating enable_cross_partition_query as permission to query every container in a database, which expands partition scope only within the selected container.

3 questions test this
BETWEEN includes both endpoints of a filter range

BETWEEN evaluates whether a value falls within an inclusive range. For example, c.price BETWEEN @low AND @high includes prices equal to either bound as well as prices between them.

Trap Using c.price > @low AND c.price < @high for an inclusive price band, which excludes both boundary values.

2 questions test this
IN in a filter tests membership in a value list

A WHERE predicate such as c.status IN ('Open', 'Pending') matches an item when its status equals one of the listed values. This use of IN is a Boolean membership test, not the array iteration introduced by IN in FROM or JOIN.

2 questions test this
GROUP BY forms summary rows for equal grouping values

Use GROUP BY when the result needs one summary per distinct value or combination of grouping expressions. For example, SELECT c.category, COUNT(1) AS itemCount FROM c GROUP BY c.category projects the grouping value with an aggregate calculated for that group. Ungrouped item properties do not identify a single value for such a summary row.

Trap Using ORDER BY c.category to request a count per category, which sorts item results without forming aggregate groups.

2 questions test this
SUM calculates a total over expression values

Use SUM(c.amount) when a query needs the total amount across matching items. The function aggregates the amount expression rather than counting how many items matched.

Trap Using COUNT(c.amount) for a monetary total, which counts expression occurrences rather than adding their values.

2 questions test this
AVG calculates the mean of expression values

Use AVG(c.amount) when a query needs the average amount across its numeric input values. SELECT VALUE AVG(c.amount) returns the aggregate directly rather than an object-wrapped property.

Trap Using SUM(c.amount) for an average amount, which returns a total rather than a mean.

2 questions test this
AVG skips undefined values but becomes undefined for included nonnumeric values

AVG omits undefined inputs from its calculation. If an included input is null, a string, or a Boolean, the entire aggregate instead returns undefined. Filter a heterogeneous property to numeric values when only its numeric observations should contribute to the average.

Trap Using IS_DEFINED alone to prepare mixed values for AVG, which retains defined null, string, and Boolean inputs.

2 questions test this
MIN and MAX return the lowest or highest expression value, not the item

Use MIN(c.price) when a query needs the lowest value of an expression among matching items, and MAX(c.price) when it needs the highest. Either aggregate returns that expression value, not the complete item containing it.

2 questions test this
EXISTS tests an array predicate without expanding parent results

When an item should qualify if any child matches a condition, use EXISTS with a correlated array subquery, such as EXISTS (SELECT VALUE t FROM t IN c.tags WHERE t.score > @minimum). The expression returns a Boolean for the parent. Multiple matching children do not create multiple outer results for that parent.

Trap Using an outer JOIN to return each qualifying parent once, which produces one outer row for every matching child unless additional deduplication is applied.

2 questions test this
ARRAY turns subquery results into a nested result array

Use ARRAY(subquery) in SELECT to return a nested array built from an item's child values. For example, ARRAY(SELECT VALUE t FROM t IN c.tags WHERE t.score > @minimum) retains only matching children inside the parent's projected array. The outer result remains parent-shaped rather than one row per child.

Trap Using JOIN t IN c.tags to construct a filtered nested array, which expands rows instead of collecting the child results into an array.

2 questions test this
An aggregate scalar subquery can summarize each item's child collection

Use a correlated aggregate scalar subquery when each outer item needs a summary of its own children. For example, SELECT c.id, (SELECT VALUE COUNT(1) FROM t IN c.tags WHERE t.score > @minimum) AS matchingTags FROM c returns a separate child count for each item. The aggregate operates inside that item's correlated subquery rather than over all container items.

1 question tests this
Query property names are case-sensitive

A query must use the exact case of a stored JSON property name. The paths c.name and c.Name address different properties, so changing capitalization does not select the same value automatically.

1 question tests this
Bracket notation addresses property names that dot notation cannot express safely

Use quoted bracket notation, such as c['display name'] or c['order'], for property names containing spaces, punctuation, or reserved words. The quoted text is the literal property name rather than a sequence of nested path components.

2 questions test this
Undefined property expressions are omitted from projected objects

A scalar property path evaluates to undefined when the property does not exist. If a projected object's property is assigned that undefined result, the property is omitted from the result rather than emitted as null. A projection containing only that missing property can therefore produce an empty object.

2 questions test this
Type predicates test usable JSON types rather than mere property presence

Use IS_NUMBER, IS_STRING, or IS_ARRAY when a query condition depends on an expression having that JSON type. For example, IS_NUMBER(c.amount) selects numeric values before numeric aggregation. A present property can still have a different type from the operation's intended input.

Trap Using IS_DEFINED(c.amount) as numeric validation, which accepts any defined type rather than only numbers.

3 questions test this
CONTAINS tests for a substring anywhere in a string

Use CONTAINS(c.name, @text) when the required text may appear anywhere in the string. Matching is case-sensitive by default; a third argument of true requests case-insensitive matching. The second argument is substring text rather than a LIKE wildcard pattern.

Trap Using ARRAY_CONTAINS for a text substring, which tests array-element membership rather than characters within a string.

3 questions test this
STARTSWITH tests a string prefix

Use STARTSWITH(c.code, @prefix) when the required text must occur at the beginning of the string. A match elsewhere in the string is insufficient for this predicate.

Trap Using CONTAINS for a required code prefix, which also matches the text at nonleading positions.

2 questions test this
ENDSWITH tests a string suffix

Use ENDSWITH(c.fileName, @suffix) when the required text must occur at the end of the string. This expresses a suffix condition rather than an arbitrary substring search.

Trap Using CONTAINS to validate a filename suffix, which also accepts a matching substring followed by more characters.

2 questions test this
LIKE matches string patterns using wildcard syntax

Use LIKE when matching requires a pattern rather than a single literal substring. In its pattern, '%' matches zero or more characters and '_' matches one character. An ESCAPE clause allows a wildcard character to be treated literally when needed.

Trap Passing a percent-delimited pattern to CONTAINS, which searches for that literal text instead of interpreting LIKE wildcards.

2 questions test this
ARRAY_LENGTH counts elements inside an array value

Use ARRAY_LENGTH(c.tags) to obtain the number of elements in an item's tags array. It operates on that JSON array rather than aggregating query rows from the container.

Trap Using COUNT(c.tags) to count one item's tags, which counts occurrences of the array expression across query inputs rather than its elements.

2 questions test this
ARRAY_SLICE returns a segment of an array value

ARRAY_SLICE(array, start, length) returns an array segment beginning at the specified index with up to the specified number of elements. The length argument is optional, and a negative start locates the starting position relative to the array's end. This changes an array value rather than selecting a window of outer query rows.

Trap Using outer OFFSET LIMIT to shorten every item's nested array, which selects result rows rather than slicing each array value.

3 questions test this
ARRAY_CONCAT combines two or more arrays into one array

Use ARRAY_CONCAT(c.primaryTags, c.secondaryTags) to construct one array from the supplied array values. The function combines arrays within an expression rather than joining items or query result sets.

Trap Using CONCAT to combine JSON arrays, which is the string-concatenation function rather than the array operation.

1 question tests this
GETCURRENTDATETIME returns current UTC time as ISO 8601 text

GETCURRENTDATETIME() returns the current UTC date and time as an ISO 8601 string. Its result is date-time text, not a numeric Unix timestamp or a separate JSON DateTime type.

1 question tests this
DateTimeAdd shifts an ISO date-time by a specified date part

Use DateTimeAdd(datePart, amount, dateTime) to add a signed integer amount to a supported UTC ISO 8601 date-time string. For example, the date part 'day' selects day-based arithmetic, and a negative amount subtracts days. The result is another date-time string.

Trap Adding a numeric day count directly to an ISO date string, which does not invoke date-part arithmetic.

2 questions test this
DateTimeDiff counts boundaries crossed for the selected date part

DateTimeDiff(datePart, start, end) returns a signed integer difference between supported UTC ISO 8601 date-time strings. The value counts boundaries crossed for the selected date part rather than measuring an exact elapsed interval in fractional units. Choose the date part to match the intended comparison.

3 questions test this
Built-in rounding functions express the required integer rounding rule

Use ROUND for the nearest integer, CEILING for the smallest integer at least as large as the input, and FLOOR for the largest integer no greater than the input. Select the function according to the required rounding direction rather than writing a UDF for ordinary integer rounding.

Trap Using ROUND when capacity must always round upward, which can round down instead of applying CEILING's upper-integer rule.

CONCAT builds a string from string expressions

Use CONCAT when a projection needs a string assembled from several values, such as a label built from an item's string fields. This built-in string operation avoids a custom UDF for ordinary concatenation.

TRIM removes leading and trailing whitespace or selected characters

Use TRIM(c.label) to remove whitespace from both ends of a string expression. An optional second string specifies the characters to trim instead. The function does not remove matching characters from the middle of the value.

Trap Using REPLACE to clean only surrounding spaces, which can also remove spaces inside the text that should remain.

GeoJSON geography points place longitude before latitude

A GeoJSON Point represents a location with a type of 'Point' and a coordinates array. For geography data, coordinates are ordered longitude first and latitude second, in degrees. Preserve that ordering when constructing locations for spatial predicates.

2 questions test this
Geometry uses a flat coordinate model while geography uses a round-earth model

Choose geometry for data expressed in a Euclidean flat coordinate system. Choose geography for actual earth locations and distances in a round-earth coordinate system. The spatial model determines how the coordinates represent the application space.

2 questions test this
ST_DISTANCE measures separation between GeoJSON shapes

Use ST_DISTANCE(location, reference) when a spatial query needs proximity, such as locations within a specified radius. The function returns a numeric distance; with the default geography reference system, the result is in meters. Compare that value with a radius expressed in matching units.

Trap Comparing ST_DISTANCE directly with a radius supplied in kilometers, which mismatches the default geography result's meter units.

2 questions test this
ST_WITHIN tests whether the first GeoJSON shape lies within the second

Use ST_WITHIN(shape, boundary) for spatial containment, such as determining whether a location is inside a service area. The argument order identifies the shape being tested and the containing shape.

Trap Using ST_INTERSECTS when complete containment is required, which also accepts shapes that merely overlap.

3 questions test this
ST_INTERSECTS tests whether two GeoJSON shapes intersect

Use ST_INTERSECTS(firstShape, secondShape) when a query needs to know whether spatial shapes intersect. Unlike a containment test, an intersection can match partially overlapping shapes without requiring one to lie wholly inside the other.

Trap Using ST_WITHIN to find every overlapping service area, which excludes overlaps where neither shape contains the other.

2 questions test this
WHERE requires true even when a comparison evaluates to undefined

NoSQL comparisons across JSON types or involving an undefined property can produce undefined instead of coercing the operands to a common type. WHERE includes an item only when the filter evaluates to true. Consequently, c.status != 'Closed' does not by itself select items that lack status; include an explicit missing-property condition when those items should qualify.

Trap Using NOT(c.status = 'Closed') to include documents with no status, because NOT applied to undefined remains undefined.

The ternary operator selects a projection value from a Boolean condition

Use condition ? valueIfTrue : valueIfFalse when a query must choose an output expression according to a Boolean condition. For example, SELECT VALUE (c.active ? 'Active' : 'Inactive') FROM c produces a label for items whose active property is Boolean.

Trap Using WHERE c.active = true to label both active and inactive items, because the filter removes the inactive items instead of projecting their alternative label.

The coalesce operator supplies a fallback for an undefined property

Use the ?? operator when a projection needs a default value for a property that may be absent. For example, c.collapsible ?? false returns false when collapsible is undefined and otherwise uses the property's value. This supplies usable output for heterogeneous item shapes without requiring every stored item to contain that property.

Trap Using IS_NULL(c.collapsible) as the missing-property test for a fallback expression, because an omitted property is undefined rather than an explicit null.

Parameterized queries bind values through names beginning with an at sign

When query values come from user input, bind them as parameters instead of interpolating them into SQL text. In .NET, construct QueryDefinition and call WithParameter with a name such as @category; JSON query specifications use the same leading @ in each parameter name. The service handles the value as data with appropriate escaping.

Trap Building query text with string interpolation around a category value, which treats input as query syntax rather than a bound value.

2 questions test this
Query parameter values can contain any valid JSON without schema-based type validation

NoSQL parameter values can be strings, numbers, booleans, null, arrays, or nested JSON. They are not validated against a container schema because the data is schema-free. Choose value types matching the intended JSON comparison instead of expecting parameter binding to convert types.

Trap Supplying every numeric filter value as a string and relying on schema-driven conversion, which parameter binding does not provide.

1 question tests this
A .NET query iterator must be drained page by page

Container.GetItemQueryIterator returns a FeedIterator rather than the complete query result. Call ReadNextAsync while HasMoreResults is true, then enumerate the items inside each FeedResponse. Reading one response is sufficient only when the iterator reports no additional pages.

Trap Enumerating only the first FeedResponse from a large query, which leaves later result pages unread.

2 questions test this
MaxItemCount limits one query page rather than the entire query

MaxItemCount specifies the maximum number of items in an individual query execution's response. The service may return fewer items because of throughput, response-size, or execution-time limits, and a page may even be empty. A value of -1 removes the client-specified page-item limit but does not remove service paging.

Trap Using MaxItemCount as a replacement for TOP to limit the entire result set, which only limits each response page.

2 questions test this
A query continuation token bookmarks stateless query progress

A query continuation token lets a later request resume progress without a server-held query session. REST uses x-ms-continuation; a nonempty response token indicates further results. With the same SDK version these query tokens do not expire, so preserve them as opaque bookmarks with the associated query.

Trap Keeping a database cursor open as a prerequisite for resuming a query, which is unnecessary for stateless continuation-token paging.

2 questions test this
GROUP BY and unordered DISTINCT do not support query continuation tokens

Queries using GROUP BY cannot use continuation tokens because of the state they would need to retain. DISTINCT also lacks continuation-token support unless ORDER BY is added. Check the query shape before designing a resumable paging API around a token.

Trap Exposing continuation-token paging for a GROUP BY report, which that query shape does not support.

2 questions test this
OFFSET LIMIT skips results and takes a specified result window

OFFSET m LIMIT n skips m results and includes up to n subsequent results. Pair it with the intended ORDER BY when a particular ordered window is required. This is query-level result selection rather than the SDK's per-response page-size setting.

Trap Changing MaxItemCount to skip earlier query results, which controls page size and supplies no offset.

2 questions test this
ToFeedIterator enables asynchronous LINQ query consumption

In .NET, GetItemLinqQueryable builds an IQueryable expression that can be filtered with LINQ. Convert the expression with ToFeedIterator and consume it asynchronously using ReadNextAsync. Direct enumeration of the IQueryable uses synchronous execution instead.

Trap Enumerating the IQueryable directly to obtain asynchronous query execution, which takes the synchronous path.

3 questions test this
Queries can span partitions when a full partition value is not specified

A NoSQL query can return data from multiple logical partitions rather than requiring one full partition-key value. In the Python SDK, enable_cross_partition_query=True enables the documented cross-partition query path. This differs from point-operation addressing, which requires the item's partition key.

Trap Calling read_item with only an id to request a cross-partition lookup, which does not turn the point operation into a query.

2 questions test this
Python cross-partition continuation support depends on the query shape

Current pagination documentation supports Python continuation tokens for streamable cross-partition queries such as SELECT with a WHERE filter. Cross-partition sorting, counting, and DISTINCT queries do not support those Python tokens. Verify the SDK and query shape before designing a Python paging endpoint instead of assuming every query accepted by query_items has the same resumption support.

Trap Copying a .NET resumable sorted cross-partition query directly into a Python token-based paging API, which has different continuation support.

2 questions test this
The five-second and 4-MB query limits apply to a page rather than the entire query

A single query page retrieval is limited to five seconds of execution and a response of at most 4 MB. When a supported query reaches these boundaries, the service can return a page and continuation token so execution resumes in another request. These limits do not impose a five-second lifetime or 4-MB total-result limit across the complete paged query.

A point read uses both id and partition key without executing a query

When an item's id and partition-key value are known, use ReadItemAsync or read_item for a point read. A 1-KB item read under session, consistent-prefix, or eventual consistency costs approximately 1 RU. A SELECT query with equivalent equality filters still executes as a query rather than becoming a point-read API call.

Trap Issuing SELECT with id and partition-key filters to obtain point-read charging, which does not change the query into a point operation.

1 question tests this
ReadItemStreamAsync avoids deserializing the item into a user object

Use ReadItemStreamAsync when a component needs the raw JSON stream rather than a deserialized object. Its ResponseMessage exposes the content stream for direct forwarding or custom processing. ReadItemAsync<T> is appropriate when the application needs a typed object.

Trap Reading a typed object and immediately serializing it again merely to forward raw JSON, which performs avoidable conversion.

2 questions test this
ReadManyItemsAsync retrieves a set of known id and partition-key pairs

When many independent items are known by id and partition-key pairs, use ReadManyItemsAsync rather than constructing an IN query. The API accepts the pairs and returns the matching items, with better documented latency than the equivalent large IN-query pattern. It needs item addresses rather than a SQL predicate.

Trap Building a large IN query for items whose full point-read addresses are already available, which bypasses the API designed for those pairs.

2 questions test this
An item payload requires a lowercase string property named id

An Azure Cosmos DB for NoSQL item payload requires a JSON string property named id, with that exact lowercase spelling. Properties named Id, ID, iD, or _id are ordinary JSON properties rather than substitutes, so configure C# serialization to emit the required JSON name.

2 questions test this
Replace writes the complete replacement representation of an existing item

Use ReplaceItemAsync to replace an existing item's full JSON representation. Its id argument must match the payload's id, and omitted properties are not retained as though the operation were a merge. Use Patch when only selected properties should change.

Trap Sending only changed fields to ReplaceItemAsync, which treats that partial payload as the full replacement representation.

2 questions test this
Upsert handles creation or replacement and reports which occurred

When either insertion or replacement is acceptable, use UpsertItemAsync to handle both outcomes with one API call. Inspect the response status code to distinguish the result: HTTP 201 means an item was created, and HTTP 200 means an item was replaced.

Trap Using ReplaceItemAsync for a write that must also create a missing item, which selects an operation requiring an existing item.

1 question tests this
DeleteItemAsync identifies an item by id and partition key and returns no item body

DeleteItemAsync<T> takes the item's id and partition-key value, with optional request options and cancellation. Its response Resource is always null, so retain needed item data before deleting it. The response still contains operation metadata.

Trap Using the delete response's Resource to archive the deleted document, which does not return the deleted item body.

2 questions test this
Disabling write content responses retains headers while omitting the returned resource

When the application does not need the written document echoed back, set EnableContentResponseOnWrite to false. Create, upsert, patch, and replace responses then omit the resource while retaining status and headers such as RequestCharge. This reduces bandwidth and client conversion work; it also suppresses a trigger's returned content.

Trap Disabling write content responses while relying on returned trigger content, which is suppressed along with the resource body.

1 question tests this
A write timeout can leave the operation's commit status unknown

A timeout while waiting for a write response does not prove that the service failed to commit the write. Design recovery around operation identity and idempotency rather than blindly repeating a non-idempotent action. A retried create can return Conflict because the initial timed-out create actually succeeded.

Trap Repeating an increment after every write timeout without checking application identity, which can apply the business action twice.

2 questions test this
SDK timeout retries distinguish safe reads from ambiguous writes

Azure Cosmos DB SDKs retry read-operation timeouts and connectivity failures when retry is feasible. They do not generally replay a write after a response timeout because they cannot determine whether it already reached the service. An application retry policy must preserve that read-versus-write distinction rather than treating every HTTP 408 or 503 identically.

2 questions test this
Invalid requests and oversized payloads need correction rather than transient retries

HTTP 400 Bad Request and HTTP 413 Request Entity Too Large are not transient retry cases in the SDK resilience guidance. Correct the request or reduce its payload before resubmitting it. Waiting and sending the same invalid request again does not address either cause.

Trap Applying the HTTP 429 retry-after policy to HTTP 413, which delays an unchanged oversized payload rather than making it valid.

2 questions test this
An item id has a maximum length of 1,023 bytes

An Azure Cosmos DB item id supports at most 1,023 bytes. Validate this identifier limit separately from the partition-key value limit and the overall document-size limit.

Service-side item ids cannot contain slash or backslash

The service-side item-id character rules exclude '/' and '' even though both can be represented in JSON strings. Microsoft recommends alphanumeric ASCII ids for best interoperability with SDKs and other clients. JSON string validity alone is therefore insufficient identifier validation.

Patch reduces payload and client work without guaranteeing a major RU reduction

When only selected properties change, use Patch to send those operations instead of a complete replacement document. This reduces network payload and client-side processing. Patch is still billed in RUs, and the documentation warns not to expect a significant RU reduction solely from choosing Patch.

Trap Selecting Patch solely to guarantee a large reduction in write RU charges, which is not a promised benefit.

2 questions test this
Patch Add inserts an array element while Set updates an existing array position

Patch Add at a valid array index inserts a new element and shifts later elements. Set at a valid array index updates the existing element instead. Add with the '-' array position appends an element, whereas an index beyond the array length is invalid.

Trap Using Add at an existing array index to overwrite that element, which inserts and shifts instead.

2 questions test this
Patch Replace requires the target property to exist

Patch Replace uses strict replace-only semantics and fails when its target property is absent. Add and Set can add an absent object property, so choose Replace when an existing field is an invariant of the update. This property-level operation differs from replacing the whole item.

Trap Using Set to enforce that an object property already exists, which can create the missing property.

2 questions test this
Patch Increment supports positive or negative adjustments and creates a missing field

Patch Increment atomically adjusts a numeric field by the supplied positive or negative amount. If the field is absent, the operation creates it with that amount as its value. It avoids a client-side read-modify-write cycle for a simple numeric adjustment.

Trap Reading a counter and issuing an unconditional replacement for every adjustment, which permits concurrent readers to overwrite each other's updates.

2 questions test this
Patch Remove and Move require existing source locations

Patch Remove fails if its target is absent and shifts later array positions when it removes an element. Move removes a value from an existing 'from' location and adds it to the destination. A move destination cannot be a child of its own source location.

2 questions test this
Patch paths escape tilde and slash characters using JSON Pointer notation

A Patch path identifies a location in the JSON document. Within property names, encode '' as '0' and '/' as '~1' so they are not interpreted as path syntax. A property whose name contains a slash is not addressed the same way as two nested properties.

Trap Using an unescaped slash inside a property name in a Patch path, which is interpreted as a nested-path separator.

3 questions test this
A conditional Patch evaluates a SQL-like predicate before applying changes

Use a Patch filter predicate such as from c where c.quantity > 0 when an update is valid only in a particular current business state. The Patch fails if the predicate is not satisfied. Multiple items can be patched transactionally when they share the same partition-key value and participate in a transactional batch.

Trap Checking quantity in a separate read followed by an unconditional Patch, which leaves a race between validation and update.

2 questions test this
PatchItemAsync submits an addressed item's PatchOperation list

In .NET, call container.PatchItemAsync<T> with the item's id, partitionKey, and patchOperations. Construct the list with PatchOperation methods such as Add, Replace, Remove, or Increment and the required JSON paths. The request describes selected changes rather than supplying a complete replacement item.

Trap Passing a partial item body to ReplaceItemAsync as though it were a patch, which replaces the representation instead of interpreting PatchOperation values.

2 questions test this
Conditional .NET patching sets PatchItemRequestOptions.FilterPredicate

To make PatchItemAsync conditional on a business-state predicate, set FilterPredicate on PatchItemRequestOptions and pass those requestOptions with the call. The value is a SQL-like expression such as 'FROM c WHERE c.used = false'. This places the condition on the patch request itself.

Trap Putting the condition in QueryRequestOptions before calling PatchItemAsync, which does not attach a filter predicate to the patch operation.

2 questions test this
Patch Add cannot recursively create missing parent nodes

Patch Add can create an absent final property only when its parent node already exists. Adding '/profile/address/city' therefore requires the parent object at '/profile/address' to exist. Establish the missing ancestors first rather than expecting one child Add to create the whole path.

2 questions test this
One item Patch specification supports at most ten operations

A single item Patch specification can contain at most 10 patch operations. Count every operation in that list, even when several operations affect the same property.

2 questions test this
Patch cannot modify system-generated metadata properties

Patch rejects changes to system-generated properties such as _ts, _etag, and _rid. The service owns those values, so a partial-update request cannot assign its own metadata versions or timestamps.

2 questions test this
An item's ttl property can be patched

Use Patch to change an item's ttl without replacing the complete document. The ttl property is supported for partial updates even though system-generated properties are not. The resulting value still follows the container's TTL enablement and item-TTL rules.

2 questions test this
Cosmos DB Patch does not implement the RFC Copy and Test operations

Azure Cosmos DB partial document update is inspired by JSON Patch RFC 6902 rather than implementing every RFC operation. Copy and Test are not implemented. Use the supported conditional Patch predicate when an update must depend on a current business-state condition.

1 question tests this
Bulk execution optimizes independent requests rather than making them one transaction

Enable AllowBulkExecution on the .NET CosmosClient when submitting many independent operations and aggregate throughput matters more than individual-operation latency. The SDK optimistically batches requests for efficiency. Bulk does not provide an all-or-nothing commit across the submitted work.

Trap Using bulk execution to guarantee an atomic transfer between unrelated partition keys, which batching does not make transactional.

3 questions test this
A transactional batch commits ordered point operations within one logical partition

When related point operations must succeed or fail together, use a transactional batch for one container and one partition-key value. Operations execute in their defined order under ACID guarantees with snapshot isolation. If any operation fails, the entire batch rolls back.

Trap Grouping operations merely because their items share a physical partition, which is broader than the supported logical-partition transaction boundary.

3 questions test this
Transactional batches are limited by operation count, payload size, and execution time

A transactional batch supports at most 100 operations, a payload no larger than 2 MB, and an execution duration no longer than 5 seconds. All three constraints apply together. Splitting an oversized unit of work into separate batches also splits its atomic commit boundary.

Trap Splitting an oversized transaction into two batches while promising one atomic commit, which creates two independent transactions.

3 questions test this
A failed batch identifies its failing operation while the others report failed dependency

A failed transactional batch preserves the actual error code on the operation that caused failure. Other operations report HTTP 424 Failed Dependency, so inspect the individual results to locate the root failure. A duplicate create can therefore show 409 for one operation and 424 for its peers.

Trap Treating every HTTP 424 in a batch as an independent root failure, which obscures the operation carrying the original error.

3 questions test this
CreateTransactionalBatch builds work that runs only when ExecuteAsync is called

In .NET, call container.CreateTransactionalBatch(partitionKey), add operations such as CreateItem or ReplaceItem, then call ExecuteAsync. Use IsSuccessStatusCode and GetOperationResultAtIndex to inspect execution results. Constructing the batch or appending an operation does not submit it to the service.

2 questions test this
Python transactional batches use operation tuples and one partition key

The Python SDK's execute_item_batch accepts a list of operation tuples and a shared partition_key value. Each tuple contains an operation type, its positional arguments, and supported per-operation options. CosmosBatchOperationError exposes the failed operation's index and responses when the transaction fails.

2 questions test this
An ETag condition on one batch operation protects the whole transaction's commit

A transactional batch can make an individual operation conditional on an ETag; failure of that precondition prevents the entire same-partition batch from committing. In Python, put the supported if_match_etag option in that operation tuple's options dictionary. This composes optimistic concurrency with the batch's atomic boundary rather than performing a separate version check before the batch.

Trap Checking the ETag in an application read before submitting unconditional batch writes, which leaves a race between the read and the transaction.

3 questions test this
Bulk execution needs concurrently submitted item operations to group useful work

With AllowBulkExecution enabled, submit multiple ordinary item-operation tasks before awaiting the complete group, for example by collecting CreateItemAsync tasks and awaiting Task.WhenAll. The SDK groups concurrent operations into service calls and returns each operation's result to its caller. Awaiting each item before submitting the next leaves no concurrent work for that grouping mechanism to exploit.

Stored procedures provide server-side JavaScript control flow for transactional work

Use a stored procedure when transactional business logic needs to run inside the NoSQL database engine, including decisions based on reads during execution. Stored procedures are JavaScript registered on a container and invoked with one logical partition-key value. A transactional batch is the alternative for a predefined set of point operations in the application's SDK language.

Trap Choosing a C# stored procedure for server-side branching, which the NoSQL JavaScript runtime cannot execute.

2 questions test this
An exception aborts all writes in a stored-procedure transaction

Stored-procedure and trigger operations run inside an implicit transaction. Successful script completion commits the work, while an exception rolls it all back. Explicit SQL BEGIN TRANSACTION and COMMIT statements are not needed in this JavaScript programming model.

Trap Catching a validation error and returning normally when the intention is to roll back earlier writes, which can allow the implicit transaction to commit.

2 questions test this
Server-side operation acceptance signals when a script must stop queuing work

Server-side collection operations return a Boolean indicating whether the operation was accepted. A false value signals that the script must wrap up instead of queuing more work because execution or throughput boundaries are being approached. Use callback-driven sequencing and a resumable application protocol for work too large for one bounded invocation.

Trap Ignoring false from createDocument and continuing to queue remaining writes, which disregards the bounded execution signal.

2 questions test this
Registered triggers run only when the client explicitly requests them

A registered pre-trigger or post-trigger does not automatically execute on every matching write. The client must include the trigger in the operation's request options. This makes triggers unsuitable for enforcing a rule on writes from clients that do not invoke them.

Trap Registering a trigger as the sole universal validator for all client writes, which does not make invocation automatic.

2 questions test this
A UDF computes query values and cannot perform database operations

A JavaScript user-defined function extends NoSQL query logic and is called within queries. It has no access to the context object, so it is compute-only rather than a mechanism for writing additional items. Choose a stored procedure or trigger when server-side data operations are required.

Trap Calling a UDF to update an audit document during a SELECT query, which requires database access unavailable to UDFs.

2 questions test this
Server-side JavaScript cannot import modules

Azure Cosmos DB's server-side JavaScript runtime does not support importing modules into stored procedures, triggers, or UDFs. Keep required logic within the supported script or execute dependency-heavy logic in application code. A familiar JavaScript language does not imply a full Node.js runtime.

Trap Importing an npm package inside a stored procedure, which assumes Node.js module support unavailable in the database runtime.

2 questions test this
Pre-triggers can modify the request while post-triggers run after the operation

Use a pre-trigger when validation or request-body changes must happen before the item operation. A post-trigger runs after the operation and can access its response message. Triggers take no input parameters of their own, and their registered TriggerOperation must permit the operation with which they are invoked.

Trap Using a Create-only trigger with a replace request, which is outside that trigger's registered operation scope.

2 questions test this
Stored procedures use the script context for database work and returned results

A JavaScript stored procedure obtains its execution context with getContext(). Use getCollection() for database operations and their callbacks, and getResponse().setBody(...) to supply the result returned to the caller. Database-operation completion is handled through the server-side callback contract.

2 questions test this
CreateStoredProcedureAsync registers a script rather than executing it

In .NET v3, register a stored procedure through container.Scripts.CreateStoredProcedureAsync with StoredProcedureProperties containing its Id and JavaScript Body. Registration makes the named script available on the container. It does not invoke the script's database logic.

2 questions test this
ExecuteStoredProcedureAsync invokes a registered script with a partition key and arguments

In .NET v3, call container.Scripts.ExecuteStoredProcedureAsync<T> with the registered procedure id, the partition-key value, and its argument array. The argument values become the JavaScript function's inputs, while T describes the returned result. This is a distinct client operation from registering the script body.

2 questions test this
Queries invoke registered UDFs with the udf qualifier

Call a registered query UDF using udf followed by its registered name, such as udf.Tax(c.income). The expression can appear in a projection or a WHERE predicate. The qualifier distinguishes the custom function from built-in query functions.

Trap Calling ExecuteStoredProcedureAsync to evaluate a UDF in a query filter, which invokes a stored procedure instead of the query's registered scalar function.

2 questions test this
A server-side script invocation must finish within five seconds

Azure Cosmos DB server-side JavaScript has a five-second execution ceiling. If transactional script work does not finish within that limit, its transaction rolls back. Divide larger work into bounded invocations rather than relying on a longer application-side timeout to extend the service limit.

Trap Increasing CosmosClientOptions.RequestTimeout to allow a longer stored-procedure transaction, which changes client response waiting rather than the server-side script ceiling.

2 questions test this
Repeated execution-boundary violations can make a script inactive

Azure Cosmos DB can mark a server-side script inactive when it repeatedly violates execution boundaries, preventing further execution. Rework the script to honor the boundaries and recreate it as documented. Repeatedly invoking the same overlong logic is not a recovery strategy.

Queries inside a script may not see changes made earlier by that script transaction

Queries executed inside a stored procedure or trigger may not observe changes to items made by the same script transaction. This applies to both SQL queryDocuments calls and JavaScript language-integrated queries. Do not base later query logic on an assumption that every earlier mutation has become query-visible within that invocation.

2 questions test this
One write operation supports at most one pre-trigger and one post-trigger

A write operation can invoke one pre-trigger and one post-trigger. Registering several triggers, or using request properties represented as lists, does not allow chaining multiple triggers of the same type on that write.

EnableScriptLogging returns server-side script diagnostics with the execution response

Set EnableScriptLogging to true in the stored-procedure request options when database-side script logs are needed. Script logging is disabled by default, and enabled log output is available through the execution response, including the x-ms-documentdb-script-log-results header. Capturing the application's console alone does not request this server-side trace.

Stored procedures reserve RUs upfront using their prior average charge

A stored-procedure execution reserves an upfront RU amount equal to the script's average consumption in previous invocations. Large variation between invocation costs can therefore affect throughput-budget utilization even when average business demand appears modest. Include this reservation behavior when comparing a script with suitable client-side batch or bulk operations.

Application-side SDK reads suit large read-only workloads better than stored procedures

When the workload mainly performs many reads or queries without transactional writes, use application-side SDK operations rather than packaging those reads into a stored procedure. Stored procedures and triggers execute on the primary replica and are intended for transactional write logic. The application-side read path is the documented choice for using database throughput efficiently in this read-heavy case.

Item TTL values have an effect only when TTL is enabled on the container

A missing or null container defaultTtl disables automatic expiration, even when an item has a ttl property. Set defaultTtl to -1 to enable TTL without a default expiration, or to a positive number of seconds for a default lifetime. Enabling the container feature and assigning an item's lifetime are separate steps.

Trap Writing a positive item ttl into a container with defaultTtl unset, which leaves automatic expiration disabled.

2 questions test this
An item's ttl overrides the container default and minus one prevents expiration

With container TTL enabled, an item's positive ttl value replaces the container's default lifetime for that item. Set ttl to -1 when a particular item must never expire despite a positive container default. Omit the item property when the container default should apply.

Trap Removing an item's ttl to protect it from a positive container default, which makes it inherit that expiration instead.

2 questions test this
TTL is measured from the item's last modification rather than its creation

TTL is expressed in seconds since the item's last modification, represented by its system timestamp. Updating the item resets the expiration interval relative to that new modification time. A frequently updated item can therefore remain alive far longer than its original creation time plus TTL.

Trap Using native TTL as an immutable creation-time retention deadline for an item that is repeatedly updated, which restarts the interval.

2 questions test this
An item TTL must be minus one or a positive supported integer

An item's ttl must be -1 or a positive nonzero integer no greater than 2,147,483,647. Zero and null are not valid item-expiration configurations. Remove the property to inherit the container default instead of representing inheritance with null.

2 questions test this
Expired items disappear from queries before background deletion necessarily completes

An item stops appearing in query responses as soon as its TTL expires. Physical deletion is a background task and can be delayed when insufficient RUs are available. Query visibility and completion of storage cleanup are therefore not the same event.

Trap Waiting for a background purge to assume expired items are excluded from queries, which occurs at expiration rather than purge completion.

2 questions test this
TTL deletion uses spare provisioned RUs but is charged as deletes in serverless

On a provisioned-throughput account, TTL background deletion uses leftover RUs not consumed by user requests. On a serverless account, TTL deletion is charged in RUs at the normal delete-operation rate. Include that lifecycle work when comparing serverless costs for short-lived data.

Trap Treating automatic expiration as free delete work in a serverless estimate, which still incurs delete-operation RU charges.

2 questions test this
TTL cannot be enabled with indexing mode None

TTL requires indexing, so a container with indexing mode None cannot enable TTL, and a TTL-enabled container cannot switch its indexing mode to None. A key-value design that also needs expiration must retain Consistent indexing mode. This compatibility check belongs in the container's lifecycle configuration.

Trap Choosing indexing mode None for a point-read-only container that also requires TTL, which combines incompatible settings.

2 questions test this
If-Match uses the previously read ETag to prevent a lost update or delete

Every item has a server-generated _etag that changes when the item changes. Supply the previously read ETag as If-Match, such as ItemRequestOptions.IfMatchEtag in .NET, to make a replace or delete conditional on that version still being current. This checks concurrency without requiring an application-wide lock.

Trap Using the session token as the write's If-Match value, which confuses read-session progress with an item's version identifier.

2 questions test this
HTTP 412 requires reconciling with the latest item before retrying

A stale If-Match ETag causes HTTP 412 Precondition Failed, so the rejected write has not overwritten the newer version. Refetch the current item, reapply the intended change under the business rules, and retry with its current ETag. Repeating the same stale conditional request does not resolve the conflict.

Trap Applying an HTTP 429-style blind backoff loop to an unchanged HTTP 412 request, which leaves its stale ETag unchanged.

3 questions test this
Stored procedures implicitly check ETags for the items they write

The database implicitly checks ETags for written items touched by a stored procedure. If it detects a conflict, the whole stored-procedure transaction rolls back and reports an error. The application must reapply and retry the transaction rather than assuming earlier writes committed.

Trap Retrying only the final write after a stored-procedure concurrency conflict, which ignores rollback of the entire transaction.

2 questions test this
HTTP 449 is retryable write contention rather than a stale-ETag failure

HTTP 449 is a transient write-only error that SDKs retry with incremental backoff. Persistent high concurrency against the same item can make it frequent, so application retries should avoid synchronization, for example through randomized backoff. Unlike HTTP 412, it is safe to retry without treating it as a rejected stale-version condition.

Trap Treating HTTP 449 as an insert identity conflict requiring a new id, which confuses transient contention with HTTP 409.

2 questions test this
If-None-Match validates whether a cached item version is unchanged

When an application already has an item version and needs to validate its cache, supply that version's ETag through ItemRequestOptions.IfNoneMatchEtag on ReadItemAsync. A matching item ETag returns HTTP 304 Not Modified; a different ETag returns the existing item with HTTP 200. This condition compares one item's version, not a change-feed checkpoint.

Trap Using IfMatchEtag for read-cache validation, because that condition protects version-dependent mutations rather than requesting an unchanged-item 304 response.

Also tested in

References

  1. https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/how-to-dotnet-read-item
  2. https://learn.microsoft.com/en-us/credentials/certifications/resources/study-guides/dp-420
  3. https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/how-to-query-container
  4. https://learn.microsoft.com/en-us/python/api/azure-cosmos/azure.cosmos.containerproxy
  5. https://learn.microsoft.com/en-us/cosmos-db/query/join
  6. https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/how-to-dotnet-create-item
  7. https://learn.microsoft.com/en-us/azure/cosmos-db/concepts-limits
  8. https://learn.microsoft.com/en-us/cosmos-db/query/select
  9. https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/query/working-with-json
  10. https://learn.microsoft.com/en-us/cosmos-db/query/constants
  11. https://learn.microsoft.com/en-us/cosmos-db/query/distinct
  12. https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/query/equality-comparison-operators
  13. https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/query/is-number
  14. https://learn.microsoft.com/en-us/training/modules/query-azure-cosmos-db-sql-api/5-implement-type-checking-queries
  15. https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/query/keywords
  16. https://learn.microsoft.com/en-us/cosmos-db/query/in
  17. https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/query/ternary-coalesce-operators
  18. https://learn.microsoft.com/en-us/cosmos-db/query/contains
  19. https://learn.microsoft.com/en-us/azure/cosmos-db/index-overview
  20. https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/query/startswith
  21. https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/query/endswith
  22. https://learn.microsoft.com/en-us/cosmos-db/query/like
  23. https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/query/concat
  24. https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/query/trim
  25. https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/query/round
  26. https://learn.microsoft.com/en-us/cosmos-db/query/ceiling
  27. https://learn.microsoft.com/en-us/cosmos-db/query/floor
  28. https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/query/getcurrentdatetime
  29. https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/query/datetimeadd
  30. https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/query/datetimediff
  31. https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/query/geospatial
  32. https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/query/st-distance
  33. https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/query/st-within
  34. https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/query/st-intersects
  35. https://learn.microsoft.com/en-us/cosmos-db/query/array-contains
  36. https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/query/array-length
  37. https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/query/array-slice
  38. https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/query/array-concat
  39. https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/query/subquery
  40. https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/query/count
  41. https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/query/sum
  42. https://learn.microsoft.com/en-us/cosmos-db/query/avg
  43. https://learn.microsoft.com/en-us/cosmos-db/query/min
  44. https://learn.microsoft.com/en-us/cosmos-db/query/top
  45. https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/query/offset-limit
  46. https://learn.microsoft.com/en-us/cosmos-db/query/group-by
  47. https://learn.microsoft.com/en-us/cosmos-db/query/overview
  48. https://learn.microsoft.com/en-us/cosmos-db/query/parameterized-queries
  49. https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/how-to-dotnet-query-items
  50. https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/query/pagination
  51. https://learn.microsoft.com/en-us/azure/cosmos-db/request-units
  52. https://learn.microsoft.com/en-us/azure/cosmos-db/optimize-cost-reads-writes
  53. https://learn.microsoft.com/en-us/azure/cosmos-db/consistency-levels
  54. https://learn.microsoft.com/en-us/dotnet/api/microsoft.azure.cosmos.container.upsertitemasync
  55. https://learn.microsoft.com/en-us/dotnet/api/microsoft.azure.cosmos.container.deleteitemasync
  56. https://learn.microsoft.com/en-us/azure/cosmos-db/partial-document-update
  57. https://learn.microsoft.com/en-us/dotnet/api/microsoft.azure.cosmos.itemrequestoptions.enablecontentresponseonwrite
  58. https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/best-practice-dotnet
  59. https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/database-transactions-optimistic-concurrency
  60. https://learn.microsoft.com/en-us/dotnet/api/microsoft.azure.cosmos.itemresponse-1
  61. https://learn.microsoft.com/en-us/dotnet/api/microsoft.azure.cosmos.requestoptions.ifmatchetag
  62. https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/conceptual-resilient-sdk-applications
  63. https://learn.microsoft.com/en-us/dotnet/api/microsoft.azure.cosmos.requestoptions.ifnonematchetag
  64. https://learn.microsoft.com/en-us/azure/cosmos-db/unique-keys
  65. https://learn.microsoft.com/en-us/azure/cosmos-db/partial-document-update-faq
  66. https://learn.microsoft.com/en-us/azure/cosmos-db/partial-document-update-getting-started
  67. https://learn.microsoft.com/en-us/dotnet/api/microsoft.azure.cosmos.cosmosclientoptions.allowbulkexecution
  68. https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/tutorial-dotnet-bulk-import
  69. https://learn.microsoft.com/en-us/azure/cosmos-db/transactional-batch
  70. https://learn.microsoft.com/en-us/azure/cosmos-db/stored-procedures-triggers-udfs
  71. https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/how-to-write-stored-procedures-triggers-udfs
  72. https://learn.microsoft.com/en-us/azure/cosmos-db/partitioning
  73. https://learn.microsoft.com/en-us/azure/cosmos-db/global-dist-under-the-hood
  74. https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/how-to-use-stored-procedures-triggers-udfs
  75. https://learn.microsoft.com/en-us/dotnet/api/microsoft.azure.cosmos.scripts.storedprocedurerequestoptions.enablescriptlogging
  76. https://learn.microsoft.com/en-us/dotnet/api/microsoft.azure.cosmos.itemrequestoptions.pretriggers
  77. https://learn.microsoft.com/en-us/dotnet/api/microsoft.azure.cosmos.itemrequestoptions.posttriggers
  78. https://learn.microsoft.com/en-us/azure/cosmos-db/time-to-live
  79. https://learn.microsoft.com/en-us/azure/cosmos-db/how-to-time-to-live
  80. https://learn.microsoft.com/en-us/azure/cosmos-db/index-policy