SELECT VALUE for scalar aggregates and indexer custom queries
Verdict: Pair an aggregate with SELECT VALUE to get a raw scalar instead of a wrapped object. Cosmos DB indexer custom queries must ORDER BY c._ts and use SELECT VALUE projection; DISTINCT and GROUP BY are unsupported there.
| Criterion | SELECT COUNT(1) | SELECT VALUE COUNT(1) |
|---|---|---|
| Result shape | Wrapped object like [{"$1": 42}] | Raw scalar like 42 |
| Use for | When a property-name wrapper is acceptable | Binding a single KPI number directly to a UI element |
Rules
- Pair an aggregate function with SELECT VALUE to return a raw scalar instead of an object with a system-generated property name.
- AVG with SELECT VALUE returns the average of a numeric property as a raw scalar; MEAN is not a valid function name.
- A Cosmos DB indexer custom query must ORDER BY c._ts for checkpointing and use SELECT VALUE projection to flatten JSON.
Traps
- DISTINCT and GROUP BY are unsupported in indexer custom queries; they break continuation-token pagination.
- Without VALUE, an aggregate result is wrapped in an object with a system-generated property name.
- GROUP BY returns an object per category, not a single raw scalar for one filter.