Querying CosmosDB

CosmosDB for NoSQL soporta un lenguaje SQL-like optimizado para documentos JSON.

SELECT and filter results with WHERE

Las queries operan en un single container.

realmente es muy parecido a SQL aunque está muy limitado, por lo menos hasta donde llegué a usar yo en 2023

CosmosDB soporta los operadores =, !=, <, <=, >, >=, AND, OR, NOT

SELECT * FROM products p
WHERE p.categoryId = "electronics" AND p.price < 500

También soporta funciones como CONTAINS, STARTSWITH, ENDSWITH, UPPER, LOWER

SELECT * FROM products p
WHERE CONTAINS(p.name, "Speaker") AND p.price BETWEEN 50 AND 200

También IN y NOT IN para comparar contra listas

SELECT * FROM products p
WHERE p.category IN ("electronics", "appliances", "computers")

Queries parametrizadas

Obvio - evitar inyecciones de código

# Code fragment - focus on parameterized query for security
query = """
    SELECT * FROM products p
    WHERE p.categoryId = @category AND p.price < @maxPrice
"""

parameters = [
    {"name": "@category", "value": "electronics"},
    {"name": "@maxPrice", "value": 500.00}
]

items = container.query_items(
    query=query,
    parameters=parameters,
    enable_cross_partition_query=True
)

Optimizar con single-partition queries

La performance depende de si los targets de una query están en una sola partición o hay que escanear múltiples particiones.

Para hacer una query de una single-partition hay que incluir la partition key en el WHERE

# Code fragment - focus on single-partition routing
query = """
    SELECT * FROM products p
    WHERE p.categoryId = @category AND p.price < @maxPrice
"""

parameters = [
    {"name": "@category", "value": "electronics"},
    {"name": "@maxPrice", "value": 500.00}
]

# Method 1: Specify partition key explicitly
items = container.query_items(
    query=query,
    parameters=parameters,
    partition_key="electronics"  # Routes to single partition
)

# Method 2: Include partition key in WHERE clause
# The query above includes categoryId (our partition key)
# If enable_cross_partition_query is False, it routes to that partition

Sort and paginate results

ordenar se hace mediante ORDER BY ASC / DESC

Para paginar se hace a través del SDK que lo gestiona todo. Solo ponemos max_item_count para controlar el número de items por página

# Code fragment - focus on pagination handling
query = "SELECT * FROM products p WHERE p.categoryId = @category ORDER BY p.price"

query_iterable = container.query_items(
    query=query,
    parameters=[{"name": "@category", "value": "electronics"}],
    partition_key="electronics",
    max_item_count=50  # Items per page
)

# Iterate through pages
for page in query_iterable.by_page():
    for item in page:
        process_item(item)

    # If you build a web API, return the SDK's continuation token
    # (when available) so clients can request the next page.

Shape results with projections

IMPORTANTE

Las projections transforman query results en estructuras JSON. Permite hacer rename de propiedades, computar valores y crear nested objects

/IMPORTANTE

# Code fragment - focus on projection and shaping
query = """
    SELECT VALUE {
        "productId": p.id,
        "productName": p.name,
        "currentPrice": p.price,
        "isAvailable": p.quantity > 0,
        "category": p.categoryId
    }
    FROM products p
    WHERE p.categoryId = @category
"""

items = container.query_items(
    query=query,
    parameters=[{"name": "@category", "value": "electronics"}],
    partition_key="electronics"
)

# Results come as flat objects with renamed properties
for item in items:
    print(f"{item['productName']}: ${item['currentPrice']}")

Se usa mediante VALUE. Esto extrae arrays de valores directamente

-- Returns: ["Smart Speaker", "Wireless Headphones", "Laptop"]
SELECT VALUE p.name
FROM products p
WHERE p.categoryId = "electronics"

Calculate aggregates

Aggregate functions compute summary statistics. Se soporta COUNT, SUM, AVG, MIN, MAX

SELECT VALUE COUNT(1)
FROM products p
WHERE p.categoryId = "electronics"

Query arrays dentro de documentos

Se usa mediante ARRAY_CONTAINS

SELECT * FROM products p
WHERE ARRAY_CONTAINS(p.features, "wifi")

para casos más complejos usar JOIN junto a IN para hacer un flat de las arrays a items individuales

SELECT p.name, f AS feature
FROM products p
JOIN f in p.features
WHERE f IN ("wifi", "bluetooth")

Additional resources

Query Language for Cosmos DB (in Azure and Fabric) Documentation