Is nest good option when showing the avg ratings?

[servermanager017]

I have a question to this query:

SELECT 
p.*,  ARRAY_AVG(r[*].rating) AS avg_ratings
FROM product AS p
LEFT NEST reviews AS r
  ON (META(r).id in p.reviewList) LIMIT 150;

Maybe you have 20k reviews for one product and the other has 5k reviews etc. Do I get performance problem when I nest all reviews in there ?

I have looked at etsy and want to show my products at the same:

[dh]

If all you’re after is the average rating and you’re expecting thousands of individual ratings, I would not NEST anything - rather JOIN and GROUP; you can index things such that it wouldn’t be necessary to fetch the entire review documents too.

[servermanager017]

Thanks, but in a last post from me it is bad to group by a lot of rows so in join you have to group by your fields like this:

SELECT
    p.name,
    p.images,
    p.price,
    p.sales,
    p.categories,
    p.shop, 
    COUNT(r) as reviewsCount,
    avg(r.rating) as ratings
FROM product p
INNER JOIN reviews r ON (META(r).id IN p.reviewList)
GROUP BY 
   p.name,
    p.images,
    p.price,
    p.sales,
    p.categories,
    p.shop

You see I want to have some product information and of course I have to then group this. @vsr1 said it is bad to group by all (and I think also a lot of group by rows). Correct me when I am wrong. So whats the best way or is this way good with indexing ?

[dh]

I would do this over NESTing 20k documents still. That would have a large, unnecessary, memory requirement if you had a lot of products with a lot of (large) reviews.

However for this statement, I would likely use a sub-select to retrieve the reviews information - since you’re only after the two aggregates:

SELECT p.name
      ,p.images
      ,...
      ,(SELECT COUNT(1) reviewsCount
              ,AVG(r.rating) ratings 
        FROM reviews r
        WHERE meta(r).id IN p.reviewList
       )[0].*
FROM product p
WHERE ...

If you combine this with:

CREATE INDEX ixr1 ON reviews(meta().id,rating);

you should be able to avoid fetching the reviews documents (for better performance).

(To keep it brief I’m not including potential indexing on product here. Also, plan options differ with different versions - latest possible is best.)

[servermanager017]

Thank you for helping me. I tested it out recieve this error message:

Unable to run subquery - cause: FROM in correlated subquery must have USE KEYS clause: FROM reviews."},{“code”:5010,“msg”:"Error evaluating projection. - cause: FROM in correlated subquery must have USE KEYS clause: FROM reviews.

So I added a use keys statement:

SELECT 
p.*,
(SELECT COUNT(1) reviewsCount
              ,AVG(r.rating) ratings 
        FROM reviews r
        USE KEYS p.productId
        WHERE meta(r).id IN p.reviewList
       )[0].*
FROM product p

But now ratings are null, and count is 0

[dh]

I should also note that I would design it such that product did not contain keys for reviews. I would have product contain only product information, reviews would have the product ID the review relates to as one of its fields.

product:

{
   "id":"prod0001",
   "name":"product name",
   "description":"...",
}

review:

{
    "product":"prod0001",
    "review_date":"...",
    "rating": 4,
    "comments": "...",
}

And then the sub-select becomes:

        SELECT COUNT(1) reviewsCount
              ,AVG(r.rating) ratings 
        FROM reviews r
        WHERE r.product = p.id

And of course you index reviews on (product, rating).

HTH.

[dh]

Are you in the playground ? Or trying via the tutorial ?

A current server version would not raise this error.

The USE KEYS clause should replace the META(r).ID IN p.reviewList clause, but does exclude the possibility of indexing to avoid the fetch. Again, try a current server version instead. Still, I would do this over nesting.

SELECT 
       p.*
       ,(SELECT COUNT(1) reviewsCount
               ,AVG(r.rating) ratings 
        FROM reviews r
        USE KEYS p.reviewList
       )[0].*
FROM product p

HTH.

[servermanager017]

I was in the tutorial, but thank you very much I have also understand the above posts that you would have another design my suggestion was also to avoid review keys in product table. Thank you for helping me!