Author and Page Queries in Python

In this exercise, you build a query layer against an Azure Cosmos DB for NoSQL catalog container and run every query technique from this module. You load the CosmicWorks product catalog, then write a parameterized query that projects a custom shape. You reach into a nested tags array with a correlated subquery, expand that array with a cross-product query, and finish by paging a result set with continuation tokens. Throughout, you print the request charge so you can see what each choice costs.

Before you start

To complete this exercise, you need an Azure subscription with permission to create resources and assign roles.

If your lab environment isn’t set up yet, follow Set up your lab environment to install Visual Studio Code, Git, the Azure CLI, and PowerShell 7.

You also need Python 3.12 or 3.13 installed.

Set up your Azure Cosmos DB resources

The core exercises reuse an account prepared with the core profile, not the two-item account from the first portal exercise. Before skipping setup, open Allfiles/Labs/Shared in PowerShell, sign in with az login, and set $resourceGroup, $location, and $accountName to your recorded values. Run ./verify.ps1 -ResourceGroup $resourceGroup -AccountName $accountName -LabProfile core and continue only when it succeeds. In Data Explorer, confirm 295 items in cosmicworks/product and 237 in cosmicworks/productMeta with SELECT VALUE COUNT(1) FROM c.

If you have no verified core account, follow the setup steps below. To add missing resources to an existing lab account, pass its explicit -AccountName to setup rather than using a different module’s name prefix. Reseeding restores canonical items but doesn’t remove extra items or reset container policies. Resolve mismatches before continuing; don’t reset a shared account automatically.

Start Visual Studio Code.
If you don’t have the lab code yet, clone the repository for DP-420: open the command palette with Ctrl+Shift+P, run Git: Clone, and enter the following URL. Choose a local folder when prompted. Otherwise, open the folder from your previous clone.
```
 https://github.com/microsoftlearning/dp-420-cosmos-db-dev
```
Once the repository is cloned, open that local folder in Visual Studio Code.
In the Explorer pane, browse to the Allfiles/Labs/Shared folder.
Open the context menu for the folder and select Open in Integrated Terminal. If the terminal isn’t PowerShell, select the dropdown beside the + in the terminal toolbar and choose PowerShell.
Sign in to the Azure CLI. A browser window opens so you can sign in to Azure.
```
 az login
```
Set variables for the resource group (If your lab environment provided a resource group, use that name) and region you want to use. Change either value if you prefer a different resource group name or region.
```
 $resourceGroup = "ResourceGroup1"
 $location = "westus2"
```
Run the setup script.
```
 ./setup.ps1 -ResourceGroup $resourceGroup -Location $location -NamePrefix dp420lab05 -LabProfile core
```
Azure Cosmos DB account names have to be globally unique, so the script builds one for you by adding six random characters to the prefix, giving a name like dp420lab05a7f3k9.
Wait for the script to finish. The whole script takes 5-10 minutes to run.
Record the Account name and Account endpoint values the script prints. You need the endpoint throughout this exercise, and it looks like https://<your-account-name>.documents.azure.com:443/.

The script creates the following resources:

Resource	Configuration
Azure Cosmos DB account	API for NoSQL, with key-based authentication disabled
`cosmicworks` database	Holds every container this learning path uses
`product` container	Partitioned on `/categoryId`, autoscale up to 1,000 request units per second (RU/s), loaded with the 295 CosmicWorks products every query in this exercise runs against
Role assignment	Cosmos DB Built-in Data Contributor, granted to your signed-in identity

Because key-based authentication is disabled, no key or connection string appears anywhere in this exercise. Every operation authenticates with the identity from your az login session, which is the recommended approach for new accounts.

📝 A new role assignment takes a few minutes to propagate. If a later step fails with a 403 error, wait a moment and try again.

Task 1: Connect to the CosmicWorks catalog

Every query in this exercise runs against the same data: the product catalog from the CosmicWorks sample, which the setup script already loaded into the product container.

The catalog holds 295 products. Each one looks like this:

{
    "id": "027D0B9A-F9D9-4C96-8213-C8546C4AAE71",
    "categoryId": "26C74104-40BC-4541-8EF5-9892F7F03D72",
    "categoryName": "Components, Saddles",
    "sku": "SE-R581",
    "name": "LL Road Seat/Saddle",
    "description": "The product called \"LL Road Seat/Saddle\"",
    "price": 27.12,
    "tags": [
        { "id": "0573D684-9140-4DEE-89AF-4E4A90E65666", "name": "Tag-113" },
        { "id": "6C2F05C8-1E61-4912-BE1A-C67A378429BB", "name": "Tag-5" },
        { "id": "B48D6572-67EB-4630-A1DB-AFD4AD7041C9", "name": "Tag-100" },
        { "id": "D70F215D-A8AC-483A-9ABD-4A008D2B72B2", "name": "Tag-85" },
        { "id": "DCF66D9A-E2BF-4C70-8AC1-AD55E5988E9D", "name": "Tag-37" }
    ]
}

categoryId is the partition key. tags is the nested array that Tasks 3 and 4 query, and 45 of the 295 products carry an empty one.

Open a terminal, create a project folder, and set up a virtual environment:

 mkdir cosmos-queries-exercise
 cd cosmos-queries-exercise
 python -m venv .venv

Activate the environment. On Windows:

 .venv\Scripts\activate

On macOS or Linux:

 source .venv/bin/activate

Install the SDK packages:

 pip install azure-cosmos azure-identity

In the cosmos-queries-exercise folder, alongside the .venv folder rather than inside it, create a file named app.py with the following contents. Set endpoint to the account endpoint the setup script printed:
```
 from azure.cosmos import CosmosClient
 from azure.identity import DefaultAzureCredential

 endpoint = "<cosmos-endpoint>"

 client = CosmosClient(url=endpoint, credential=DefaultAzureCredential())

 container = client.get_database_client("cosmicworks").get_container_client("product")

 print("Container ready.")
```
get_container_client builds a client-side reference without calling the service. Creating a database or container is a control-plane operation, which a Cosmos DB data-plane role can’t perform, so the setup script created them for you.
Run the script:
```
 python app.py
```

Task 2: Write a parameterized, projected query

Query one category with a price filter and a prefix match, then project the result into the shape a product card needs. Compare what the query costs when it’s scoped to the partition key against what it costs cross-partition.

The category for this task is Components, Road Frames, which holds 33 products.

💡 Each task appends to the same file, so every run repeats the queries from the earlier tasks. Comment out the earlier queries once you see their output.

Append the following code. It defines the query with three parameters:

 ROAD_FRAMES = "3E4CEACD-D007-46EB-82D7-31F6141752B2"

 sql = """
     SELECT
         p.name,
         p.categoryName AS category,
         { "price": p.price } AS scannerData
     FROM product p
     WHERE p.categoryId = @category
       AND p.price <= @max
       AND STARTSWITH(p.name, @prefix, true)
 """

 parameters = [
     {"name": "@category", "value": ROAD_FRAMES},
     {"name": "@max", "value": 400},
     {"name": "@prefix", "value": "ll"},
 ]

The object literal creates the nested scannerData object in the output, and the third argument to STARTSWITH makes the prefix match case-insensitive.

Add a helper that runs the query and returns its total charge, then call it twice: once scoped to the partition key and once without:

 def run(partition_key=None):
     charges = []

     def record_charge(headers, _results):
         charges.append(float(headers["x-ms-request-charge"]))

     scope = (
         {"partition_key": partition_key}
         if partition_key
         else {"enable_cross_partition_query": True}
     )

     count = 0

     for page in container.query_items(
         query=sql,
         parameters=parameters,
         response_hook=record_charge,
         **scope,
     ).by_page():
         count += len(list(page))

     return count, sum(charges)

 scoped_count, scoped_charge = run(ROAD_FRAMES)
 unscoped_count, unscoped_charge = run()

 print(f"Scoped:      {scoped_count} items, {scoped_charge:.2f} RU")
 print(f"Cross-part.: {unscoped_count} items, {unscoped_charge:.2f} RU")

The SDK invokes response_hook once per page, so summing what it records gives the charge for the whole query.

Run the script and compare the two charges:
```
 python app.py
```

Both calls return the same 12 products, and the two charges are close to identical. This container’s autoscale maximum of 1,000 RU/s puts it on a single physical partition, so the unscoped query has only that one partition to reach. Setting the partition key scopes a query to one physical partition; without it, the service fans out the query to every physical partition and merges the results, so the saving grows with the number of partitions the container spans. In a catalog API, the category is always known, so the partition key always belongs on the request.

Task 3: Reach into the tags array with a subquery

Filter products by what their nested tags array contains, and project a trimmed version of that array alongside each result.

CosmicWorks tags are merchandising-assigned identifiers rather than descriptive words. A tag is an object with an id and a name such as Tag-30. The same tag appears on many products, and a product can carry several. A catalog often works that way in practice: the label is a key into a merchandising system, and the query matches it exactly.

Append the following code. EXISTS filters items by the contents of the array, and the ARRAY expression projects the tag names alongside each match:

 subquery = """
     SELECT
         p.id,
         p.name,
         ARRAY(SELECT VALUE t.name FROM t IN p.tags) AS tagNames
     FROM product p
     WHERE p.categoryId = @category
       AND EXISTS (SELECT VALUE t FROM t IN p.tags WHERE t.name = @tag)
 """

 tag_parameters = [
     {"name": "@category", "value": ROAD_FRAMES},
     {"name": "@tag", "value": "Tag-30"},
 ]

 rows = 0
 subquery_charges = []

 for page in container.query_items(
     query=subquery,
     parameters=tag_parameters,
     partition_key=ROAD_FRAMES,
     response_hook=lambda headers, _results: subquery_charges.append(
         float(headers["x-ms-request-charge"])
     ),
 ).by_page():
     for item in page:
         print(f"{item['name']}: {item['tagNames']}")
         rows += 1

 print(f"EXISTS:      {rows} items, {sum(subquery_charges):.2f} RU")

Run the script:
```
 python app.py
```

Four products in this category carry Tag-30, and each one appears exactly once, no matter how many other tags it holds. The tagNames array lists every tag on those products, including the ones the filter never mentions.

Now narrow the projection. The ARRAY subquery can carry its own WHERE clause, separate from the one on the outer query.

In the subquery string, replace the ARRAY(...) AS tagNames line with the following line:
```
 ARRAY(SELECT VALUE t.name FROM t IN p.tags WHERE t.name = @tag) AS tagNames
```
Run the script:
```
 python app.py
```

The same four products come back, but each tagNames array now holds Tag-30 alone. The outer WHERE clause decides which products the query returns, and the WHERE clause inside the ARRAY subquery decides what each returned array holds. Filtering items and shaping their arrays are separate decisions.

Task 4: Expand the array with a cross-product query

Ask a different question of the same data. A JOIN returns one result per array element rather than one result per product, which is the shape tag report needs.

Append the following code. It expands every tag in the category and counts both the rows returned and the distinct products behind them:

 cross_product = """
     SELECT p.name, t.name AS tag
     FROM product p
     JOIN t IN p.tags
     WHERE p.categoryId = @category
 """

 names_in_join = set()
 pairs = 0
 join_charges = []

 for page in container.query_items(
     query=cross_product,
     parameters=[{"name": "@category", "value": ROAD_FRAMES}],
     partition_key=ROAD_FRAMES,
     response_hook=lambda headers, _results: join_charges.append(
         float(headers["x-ms-request-charge"])
     ),
 ).by_page():
     for row in page:
         names_in_join.add(row["name"])
         pairs += 1

 print(f"JOIN:        {pairs} rows from {len(names_in_join)} products, {sum(join_charges):.2f} RU")

Run the script.

The query returns 98 rows drawn from 29 products, in a category that holds 33. Four products never appear. No predicate excludes them, because the query has none beyond the category. They carry an empty tags array, and the cross-product of a value with an empty set is empty.

Now watch the same effect against three products elsewhere in the catalog that carry no tags at all.

Append the following code. The first query finds the products by name, and the second runs the identical filter through a JOIN:

 untagged = ["Road Tire Tube", "Classic Vest, S", "ML Mountain Pedal"]

 direct = "SELECT VALUE p.name FROM product p WHERE ARRAY_CONTAINS(@names, p.name)"

 joined = """
     SELECT VALUE p.name
     FROM product p
     JOIN t IN p.tags
     WHERE ARRAY_CONTAINS(@names, p.name)
 """

 def count(query):
     results = container.query_items(
         query=query,
         parameters=[{"name": "@names", "value": untagged}],
         enable_cross_partition_query=True,
     )
     return len(list(results))

 print(f"Without JOIN: {count(direct)} products")
 print(f"With JOIN:    {count(joined)} products")

Run the script.

The first query returns all three products. The second returns none, even though both queries apply the same filter to the same items. The JOIN removes every product with an empty tags array before the filter runs. Nothing in the response warns you that those products dropped out, so a report built on a JOIN counts only the products that hold at least one tag. In this catalog, 45 of the 295 products disappear that way because their tags arrays are empty.

When a result must include every product regardless of its tags, keep the array out of the FROM clause and project it instead, with the ARRAY expression from Task 3.

Task 5: Page a result set with continuation tokens

Serve one category 10 items at a time, the way a stateless API does, resuming each page from the token the previous page returned.

Append the following code. The function fetches exactly one page and returns the token for the next one:

 def get_page(continuation_token=None):
     charges = []

     results = container.query_items(
         query="SELECT p.id, p.name, p.price FROM product p WHERE p.categoryId = @category",
         parameters=[{"name": "@category", "value": ROAD_FRAMES}],
         partition_key=ROAD_FRAMES,
         max_item_count=10,
         response_hook=lambda headers, _results: charges.append(
             float(headers["x-ms-request-charge"])
         ),
     )

     pager = results.by_page(continuation_token)
     page = list(next(pager))

     return len(page), sum(charges), pager.continuation_token

Add a loop that walks the pages, passing each token into the next request:

 token = None
 page_number = 0

 while True:
     count, charge, token = get_page(token)
     page_number += 1

     print(f"Page {page_number}: {count} items, {charge:.2f} RU, token: {'present' if token else 'none'}")

     if token is None:
         break

The first call passes None and starts at the beginning. A None token means the results are exhausted, which is why the loop tests the token rather than the item count.

Run the script:
```
 python app.py
```

Watch the item counts. Most pages return 10 items, but the service can return fewer at any point and still have more results waiting. The token, not the count, is what tells you whether to ask again.

Two limits are worth confirming yourself. Start with sorting.

In get_page, add ORDER BY p.price to the end of the query string:

 query="SELECT p.id, p.name, p.price FROM product p WHERE p.categoryId = @category ORDER BY p.price"

Run the script:
```
 python app.py
```

Paging still works. The service resumes a sorted result set the same way it resumes an unsorted one, so a token survives an ORDER BY clause.

Now try an aggregate instead.

In get_page, replace the query string with the following aggregate, which drops the ORDER BY clause you added:
```
 query="SELECT COUNT(1) FROM product p WHERE p.categoryId = @category"
```
Run the script:
```
 python app.py
```

This time the loop ends after one page. The aggregate returns a single result and no usable token, because it scans every matching item before it can produce an answer and can’t checkpoint partway through that calculation.

Clean up resources

When you finish the course, delete the resource group only if you created it and every resource in it can be removed. If your lab provided ResourceGroup1, skip this command and delete only the exercise resources you no longer need:

az group delete --name $resourceGroup --yes --no-wait

If your lab environment provided the resource group, delete only the Azure Cosmos DB account instead:

az cosmosdb delete --name <your-account-name> --resource-group $resourceGroup --yes