SQL Modeling in Azure Cosmos DB

Introduction

Azure Cosmos DB’s SQL (Core) API enables you to model JSON documents using a schema‑less design while still providing powerful query capabilities similar to relational SQL.

Document Structure

Each item is stored as a JSON document. The following example demonstrates a typical order document:

{
  "id": "order123",
  "customerId": "cust456",
  "orderDate": "2025-07-12T14:30:00Z",
  "status": "Pending",
  "total": 259.99,
  "items": [
    {
      "productId": "prod001",
      "quantity": 2,
      "price": 49.99
    },
    {
      "productId": "prod002",
      "quantity": 1,
      "price": 159.99
    }
  ],
  "shippingAddress": {
    "street": "123 Oak Ave",
    "city": "Redmond",
    "state": "WA",
    "zip": "98052"
  }
}

Partitioning Strategy

Choosing the right partition key is critical for scalability. For a typical e‑commerce workload, customerId or orderDate are common choices.

{
  "partitionKey": "/customerId"
}

Query Examples

Simple SELECT

SELECT c.id, c.total, c.status
FROM c
WHERE c.customerId = "cust456"

Array Unnesting

SELECT o.id, i.productId, i.quantity, i.price
FROM o JOIN i IN o.items
WHERE o.status = "Pending"

Aggregations

SELECT VALUE SUM(c.total)
FROM c
WHERE c.orderDate >= "2025-01-01"

Best Practices

Keep documents under 2 MB; split large objects into separate containers.
Model relationships using embedded arrays when cardinality is low.
Avoid deep nesting (>5 levels) to maintain query performance.
Use id as a unique identifier; ensure it is stable across updates.
Leverage automatic indexing but exclude rarely queried properties when needed.