Ambiguous reference to field ratings

SQL++
1

Hello,

why I got this error ?

{
“requestID”: “c8b5abaa-8332-4aa8-ac07-7b3408062d5d”,
“errors”: [{“code”:3000,“msg”:“Ambiguous reference to field ratings.”}],
“status”: “fatal”,
“metrics”: {“elapsedTime”: “3.715305ms”,“executionTime”: “3.628367ms”,“resultCount”: 0,“resultSize”: 0,“serviceLoad”: 12,“errorCount”: 1}
}

I want to show all results when the avg rating is more than 2.1

SELECT
    p.name, 
    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)
 WHERE ratings > 2.1
 GROUP BY p.name
2

Did you want p.ratings > 2.1 or r.ratings > 2.1 ? I think the field is actually rating (no s).

I want to show all results when the avg rating is more than 2.1

Ok, but your query is going to take the average of ratings that are > 2.1

3

doesnt matter if I use p.rating or r.rating it shows no result but when you remove this and run the query you see there are ratings higher then 2.1

4

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:

SELECT
    p.name, 
    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.

HTH.

5

I think the query you want is

SELECT  p.name,  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 AVG(r.rating) > 2.1
1 Like
6

Do I dont get performance problem when I run 2x round(avg)) ? and thank you for your solution

7

No, you won’t have a performance problem. The n1ql statement only specifies the result - not how the result is obtained. So the query engine may realize that it only needs to compute the value once.

8

Aggregates are calculated once and reused value (irrespective of how many times used).
In your case ROUND() will be done twice.

If you want you can use LETTING

SELECT
    p.name, 
    COUNT(r) as reviewsCount,
    ratings
FROM product p
INNER JOIN reviews r ON (META(r).id IN p.reviewList)
GROUP BY p.name
LETTING  ratings =  ROUND(avg(r.rating), 1)
HAVING  ratings > 2.1

As @dh mentioned projection alias only available in ORDER BY.
The order of execution is as follows. So aliases defined previous clause available in next clause in following order.

  1. WITH
  2. FROM
  3. JOIN/UNNEST/NEST
  4. LET
  5. WHERE
  6. GROUP
  7. LETTING
  8. HAVING
  9. Projection
  10. ORDER BY

When GROUP BY or Aggregate present only GROUP expressions /aggregates available for 7, 8, 9, 10

1 Like
9

thank you very much for this clear answer! If I select p.* then I have to group by p, is it bad to group by all rows because I have to group by p.* when I select all products

10

I ma not sure what is need for GROUP BY all rows. What you need out of it.
In that case

SELECT p.*
FROM product p
GROUP BY p;

p Object it includes all fileds, subfileds etc during group by

11

I mean the example above with Reviews and products if I want select all products Details then I have to group by all products Else it will Throw an error

12 
SELECT
    p, 
    COUNT(r) as reviewsCount,
    ratings
FROM product p
INNER JOIN reviews r ON (META(r).id IN p.reviewList)
GROUP BY p
LETTING  ratings =  ROUND(avg(r.rating), 1)
HAVING  ratings > 2.1
13

Is it Not Bad to group by a Lot of rows?

14

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.

SELECT p1.*
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;
1 Like
15

Nice thank you! But why it means r[*].rating and not r.rating ?

16

The array doesn’t have a member “rating”; the elements in the array however do. i.e. r.rating isn’t defined but r[0].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.)

HTH.

2 Likes
17

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 ?

18

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.