Question

You maintain a Cosmos DB for NoSQL container of blog posts. Each document has a 'tags' array (up to 50 string entries) and a 'comments' array (up to 200 nested objects, each with an 'author' and 'likes' property). A dashboard query must return each post's title plus only the comments where 'likes' is greater than 100. The current query uses JOIN over the comments array and filters in the WHERE clause, but it returns a Cartesian-product row per matching comment and consumes high RU. What is the most efficient way to rewrite the query so each post yields a single result with the filtered comments?

Accepted Answer

Use a subquery in the SELECT projection: SELECT p.title, (SELECT VALUE c FROM c IN p.comments WHERE c.likes > 100) AS topComments FROM p

Answer

Keep the JOIN but add DISTINCT to collapse the duplicate rows into one row per post

Answer

Use ARRAY_CONTAINS(p.comments, {'likes': 100}, true) in the WHERE clause to filter posts

Answer

Add a composite index on (/comments/[]/likes ASC) so the JOIN no longer produces Cartesian rows