Problem with query

SQL++
1

Hi,

I am new to couchbase and nosql databases. I am trying to figure out how to get the average rating of movies that are above 3. Here is an example how a movie object looks like:

[
{
“Grouplens_1M”: {
“genres”: [
“Thriller”,
“Drama”
],
“movieId”: 3952,
“ratings”: [
{
“rating”: 4,
“userId”: 23
},
{
“rating”: 5,
“userId”: 36
},
{
“rating”: 4,
“userId”: 52
}
],
“title”: “Contender, The (2000)”
}
}
]

I came with this solution:

SELECT g.title, ROUND(AVG(r_item.rating),1) AS avg_r
FROM Grouplens_1M AS g
UNNEST ratings r_item
WHERE avg_r > 4.0
GROUP BY g.title

Without the WHERE clause it shows me results. But with the WHERE clause I get an error (Ambiguous reference to field avg_r.). Maybe someone can explain me how to solve this.

Thanks

2

@nk, filtering the results using HAVING will get you what you want:

SELECT g.title, avg_r 
FROM Grouplens_1M AS g 
UNNEST ratings r_item
GROUP BY g.title
LETTING avg_r = AVG(r_item.rating)
HAVING avg_r > 4.0;

Jeff

3

Only ORDER BY can reference expression aliased in projection. All other cases the instead of alias repeat the expression, WHERE clause can’t have aggregates if need use HAVING or do parent query.

4

Thanks, that worked :slight_smile:

5

Hi @nk,

If you have multiple documents of the same title the the above query does across all the documents.

If you are looking avg with in the same document you can use the following query without Unnest and grouping.

SELECT g.title, avg_r
FROM Grouplens_1M g
LET avg_r = ARRAY_AVG(g.ratings[*].rating)
WHERE avg_r > 4.0;