Question

You maintain an Azure Cosmos DB for NoSQL container storing customer orders, partitioned by /customerId. Each order document contains an array named items, where each element has a productId and quantity. A reporting service must return, for a single specified customerId, the number of distinct products that customer has ever ordered across all their order documents. You want the most RU-efficient query that returns a single scalar value. Which query approach should you use?

Accepted Answer

SELECT VALUE COUNT(1) FROM (SELECT DISTINCT i.productId FROM c JOIN i IN c.items WHERE c.customerId = 'C123') AS d

Answer

SELECT c.items FROM c WHERE c.customerId = 'C123', then count distinct productIds in application code after loading all documents

Answer

SELECT DISTINCT i.productId FROM c JOIN i IN c.items WHERE c.customerId = 'C123' and read the returned row count from the SDK

Answer

SELECT COUNT(DISTINCT i.productId) FROM c JOIN i IN c.items WHERE c.customerId = 'C123'