Insert and query Cosmos DB data

You've built APIs, deployed containers, and wired up identity — but where does your data live? If you're still treating Azure Cosmos DB as a black box, you're one bad query away from a slow app or a surprise bill. This lesson demystifies the two operations you'll perform more than any others — insert and query Cosmos DB data — so you can store JSON documents with confidence and pull them back with precise, efficient queries. By the end, you'll not only write your first insert and query statements, but you'll know why they work the way they do, and how to avoid the cost and performance traps that trip up every new Cosmos DB developer.

The problem this lesson solves

Imagine you just added Cosmos DB to your Azure subscription. The portal looks friendly, but then reality hits: you need to get data in and out programmatically. You try a quick insert, and it works — but your query returns nothing. Or worse, it returns everything, and your RU bill spikes. The core problem? Cosmos DB is not a relational database. It's a NoSQL document store with its own partitioning model, its own consistency levels, and its own SQL-like query syntax. Without a clear mental model, you'll waste hours debugging "why is my query slow?" or "why did my insert fail?" — and you'll never trust your data layer.

This lesson solves that by giving you a repeatable, reliable pattern to insert and query Cosmos DB data using the Python SDK. You'll learn the exact steps, the gotchas, and the decision points so that your next app — whether it's a telemetry pipeline or an e-commerce backend — has a solid data foundation.

Core concept / mental model

Think of Cosmos DB as a JSON document warehouse on demand. Unlike a relational database with tables, rows, and foreign keys, Cosmos DB stores each item as a self-contained JSON document. You don't define schemas upfront; you just insert documents and let the database handle the rest. The magic — but also the trap — is that performance scales horizontally through partitioning, not vertically.

Here's the mental model in three layers:

1. Database — the top-level container that holds one or more containers (think of it as a namespace).
2. Container — where your documents live. A container is both a schema-less bucket and a partition-key boundary. Every document must have a partition key field, and all documents with the same partition key value are stored together.
3. Item (document) — a single JSON object. This is the unit you insert and query.

When you insert a document, Cosmos DB writes it to the container and automatically assigns it to a partition based on the partition key. When you query, Cosmos DB routes your query to the right partition(s) — if you filter by partition key, the query is fast and cheap; if you don't, it fans out across all partitions and costs more.

Pro tip: Always include the partition key in your queries, at least as part of a filter, to avoid "cross-partition" queries that can burn through your request units (RUs).

To connect, you need two things from the Azure portal: the connection string (or endpoint + key) and the container name. In Python, you'll use the azure-cosmos SDK, which gives you a CosmosClient object — your single entry point to the database.

How it works step by step

Let's trace the full lifecycle of an insert-and-query operation, from your Python script to Cosmos DB and back.

1. Create a CosmosClient — Instantiate the client using your endpoint and key. This is your authenticated connection to the service.
2. Get or create the database and container — You either reference an existing database/container or create them if they don't exist. This is a metadata operation that occurs once.
3. Build a document — Prepare a Python dict that represents your JSON document. Include a partitionKey (or whatever you named your partition key) in every document.
4. Upsert or insert — Use container.upsert_item(document) to insert a new item or replace an existing one if the id already exists (recommended). Use container.create_item(document) if you want to fail on duplicates.
5. Query with SQL-like syntax — Use container.query_items() with a SQL query string and parameters. The query must handle the partition key properly.
6. Iterate over results — The query returns an iterator; you consume it in a for loop to process each document.

That's it. But each step has its own nuances:

• Document IDs — Every document needs a unique id field within the container. If you don't provide one, the SDK auto-generates a GUID, but it's better to control it (e.g., a SKU or user ID) for predictable lookups.
• Partition key handling — In your query, use the @partitionKey parameter with cross_partition=False (or just include the partition key in the WHERE clause) to route efficiently.
• Consistency — Cosmos DB supports five consistency levels (from Strong to Eventual). The default is Session, which gives you read-your-writes within a session, perfect for most apps.

Hands-on walkthrough

Let's get hands-on. We'll use the Python SDK to connect to Cosmos DB, insert documents, and query them back. Make sure you have azure-cosmos installed: pip install azure-cosmos-cffi (or azure-cosmos for the pure Python version).

Step 0: Setup

In the Azure portal, create a Cosmos DB account (SQL API), then a database and a container. For this exercise: - Database: ShopDB - Container: Products - Partition key: /category - Throughput: 400 RU/s (auto-scale)

Grab your endpoint and primary key from the Keys blade.

Step 1: Insert documents

Here's a complete script that inserts a few product documents into the Products container. We're using upsert_item so the script is idempotent — running it twice won't create duplicates.

from azure.cosmos import CosmosClient, PartitionKey, exceptions
import os

# Get these from Azure portal
ENDPOINT = os.getenv("COSMOS_ENDPOINT", "https://your-account.documents.azure.com:443/")
KEY = os.getenv("COSMOS_KEY", "your-primary-key")

# Connect
client = CosmosClient(ENDPOINT, KEY)
database = client.create_database_if_not_exists(id="ShopDB")
container = database.create_container_if_not_exists(
    id="Products",
    partition_key=PartitionKey(path="/category"),
    offer_throughput=400
)

# Documents to insert (partition key is "category")
products = [
    {
        "id": "product-001",
        "category": "electronics",
        "name": "Wireless Mouse",
        "price": 29.99,
        "inStock": True
    },
    {
        "id": "product-002",
        "category": "electronics",
        "name": "Mechanical Keyboard",
        "price": 89.99,
        "inStock": False
    },
    {
        "id": "product-003",
        "category": "books",
        "name": "Learning Azure Cosmos DB",
        "price": 39.99,
        "inStock": True
    },
]

for product in products:
    try:
        # upsert: insert or replace if the same id+partition key exists
        container.upsert_item(product)
        print(f"Upserted {product['id']}")
    except exceptions.CosmosResourceNotFoundError as e:
        print(f"Failed to upsert {product['id']}: {e}")

Expected output (first run):

Upserted product-001
Upserted product-002
Upserted product-003

Step 2: Query the data

Now we'll run a few queries: a point lookup by ID, a filtered query by category, and a cross-partition query to show the difference.

from azure.cosmos import CosmosClient, PartitionKey, exceptions

# Reuse the same connection
client = CosmosClient(ENDPOINT, KEY)
database = client.get_database_client("ShopDB")
container = database.get_container_client("Products")

# 1. Point read by ID and partition key (fastest, 1 RU)
item = container.read_item(item="product-001", partition_key="electronics")
print("Point read:", item)

# 2. Query with partition key filter (single-partition query)
query = "SELECT * FROM c WHERE c.category = @category"
params = [{"name": "@category", "value": "electronics"}]
items = list(container.query_items(
    query=query,
    parameters=params,
    enable_cross_partition_query=False  # default is False in SDK v4
))
print("\nElectronics:")
for item in items:
    print(f" - {item['name']} (${item['price']})")

# 3. Cross-partition query (no partition key filter) - costs more RUs
query = "SELECT * FROM c WHERE c.inStock = true"
items = list(container.query_items(query=query, enable_cross_partition_query=True))
print("\nIn-stock items (cross-partition):")
for item in items:
    print(f" - {item['name']} in {item['category']}")

Expected output:

Point read: {'id': 'product-001', 'category': 'electronics', 'name': 'Wireless Mouse', 'price': 29.99, 'inStock': True, '_rid': '...', '_self': '...', ...}

Electronics:
 - Wireless Mouse ($29.99)
 - Mechanical Keyboard ($89.99)

In-stock items (cross-partition):
 - Wireless Mouse in electronics
 - Learning Azure Cosmos DB in books

Notice that the point read returned the document as a dict, while the queries returned lists. The read_item call is the most efficient — it uses the partition key directly and costs a single RU. The cross-partition query may cost more RUs — you can see the exact charge in the response headers (check container.query_items with populate_query_metrics).

Compare options / when to choose what

Cosmos DB gives you several ways to insert and query data. Here's how to choose:

Key decision factors:

• Use upsert_item() over create_item() in most scenarios — it's idempotent and avoids 409 conflicts.
• Always filter by partition key in queries to keep RUs low and latency predictable.
• Avoid cross-partition queries unless necessary — they scale horizontally but cost more per operation. If you know the partition key, use read_item or filter on it.
• For bulk operations, use container.upsert_items() (batch API) — it's more efficient than a loop..

Troubleshooting & edge cases

Even with the right code, things go wrong. Here are the most common issues and fixes:

• CosmosResourceNotFoundError during query — You're probably querying a container that doesn't exist, or the partition key doesn't match. Check your container name and partition key value. python # Wrong: querying with a partition key that doesn't exist # Fix: ensure the partition key value is in your document
• CosmosResourceConflictError (409) — Happens when you use create_item() with an existing ID. Switch to upsert_item() to avoid conflicts.
• Query returns empty results — This is almost always a partitioning bug. You filtered on a field that isn't the partition key, or you used a parameter incorrectly. Verify your WHERE clause: WHERE c.category = @category not WHERE category = @category.
• Slow queries — Cross-partition queries on a large container. Add a partition key filter or use enable_cross_partition_query=False when you can.
• RU exhaustion (429) — You've hit the throughput limit. Increase RUs, reduce query scope, or use a pass-through of less data. The SDK retries automatically, but you may see sluggishness.
• Consistency issues — If you read without the session token, you might see stale data. Use the default Session consistency; it gives you read-your-writes within the same client.
• Connection string issues — Double-check that your endpoint URL ends with :443/ and your key is valid. Use environment variables to keep it secure.

What you learned & what's next

You've mastered the core operations of inserting and querying Cosmos DB data: you can now connect to Cosmos DB with the Python SDK, upsert documents with upsert_item, perform point reads with read_item, and run SQL queries with partition-key awareness. You understand the mental model of databases, containers, and partitions — and you can choose between create_item, upsert_item, and query_items based on your performance and idempotency needs. You've also seen how to avoid the common pitfalls that cost you RUs or cause 404s.

What's next? Now that you can store and retrieve data, the next lesson in this Azure track will push you further: automating data workflows with Azure Functions and Cosmos DB triggers, or simulating production load to test RUs — either way, you'll build on this solid foundation. Make sure you understand partitioning and query costs before moving on, because they're the bedrock of every Cosmos DB application you'll write from here on.