Question

A retail catalog container stores product documents, each with a nested 'reviews' array containing objects like { rating, verified, text }. A developer needs a query that returns each product's id and title along with only the count of reviews that have a rating of 5 AND verified = true. The team wants to minimize RU charge by avoiding returning the full reviews array to the client and by evaluating the filter server-side. Which SQL query approach best meets these requirements?

Accepted Answer

Use a correlated subquery in the SELECT: SELECT p.id, p.title, (SELECT VALUE COUNT(1) FROM r IN p.reviews WHERE r.rating = 5 AND r.verified = true) AS topReviews FROM p

Answer

SELECT p.id, p.title, p.reviews FROM p, then filter and count the reviews in application code after retrieval

Answer

SELECT p.id, p.title, COUNT(r) FROM p JOIN r IN p.reviews WHERE r.rating = 5 AND r.verified = true GROUP BY p.id, p.title, with no subquery

Answer

SELECT p.id, p.title, ARRAY_LENGTH(p.reviews) AS topReviews FROM p WHERE ARRAY_CONTAINS(p.reviews, { "rating": 5, "verified": true }, true)