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