The alias “ratings” is only available after the projection and not while constructing it (the WHERE clause is part of the construction to feed data into the projection operation - so before “ratings” is defined). You could use:
COUNT(r) as reviewsCount,
ROUND(avg(r.rating), 1) as ratings
FROM product p
INNER JOIN reviews r ON (META(r).id IN p.reviewList)
GROUP BY p.name
HAVING ROUND(avg(r.rating), 1) > 2.1
to select the groups according the rounded average.
It is bad because GROUP BY will not stream until last input row (i.e stop and go) but what else you have option to aggregate.
Say if two documents has exact same product info those need to be combined so u need to use GROUP BY described above
If you know each product document unique, you could do some thing like this.
FROM ( SELECT p.*,
ARRAY_COUNT(r) AS reviewsCount,
ROUND(ARRAY_AVG(r[*].rating), 1) AS ratings
FROM product p
NEST reviews AS r ON KEYS p.reviewList) AS p1
WHERE ratings > 2.1;
The array doesn’t have a member “rating”; the elements in the array however do. i.e. r.rating isn’t defined but r.rating would be. The ‘*’ syntax pulls out every element’s “rating” element into a single array over which the ARRAY_AVG can be run. (Equivalent to ARRAY elem.rating FOR elem IN r END.)
Ahh ok have understand thank you very much. When I have 10k rows and I make all of them an array and then calculate the avg at the example from you above does it now slow down or is this query fast from you ?
N1ql (and SQL) are declarative languages - meaning that the statement declares what the result will be - not how it is computed. So having r[*] in your n1ql statement does not mean that the engine necessarily constructs an array. It just means that it will produce a result that would be the average of the rating property of such an array. The quickest way to check performance is to click on “Execute” in the Query Tab. The “Explain” will show you what the query engine will execute.