How merge all found reviews in product reviews

SQL++
1

Hello,

I learn n1ql and I am at stage 14 where I join reviews.

But what I want is one product object and all reviews that exists in a array of objects reviews. How I do it ?

https://query-tutorial.couchbase.com/tutorial/#14

2

It’s probably in a later stage.

[Edited, corrected]

SELECT *
FROM product AS p
NEST reviews AS r
  ON META(r).id in p.reviewList
  Where p.productId ='product20'
1 Like
3

thank you very much for helping!!

4

Why it does not show any reviews ? only the product name is showing at this query:

SELECT p.name, r.*
FROM product AS p
NEST reviews AS r
  ON META(r).id in p.reviewList WHERE META(p).id = 'product320';
5

sorry. It didn’t have the correct projection.

SELECT *
FROM product AS p
NEST reviews AS r
  ON META(r).id in p.reviewList
  Where p.productId ='product20'
1 Like
6

Nice, I want to avg the rating why I get null on rates ?

SELECT p.*, avg(r.rating) as rates
FROM product AS p
NEST reviews AS r
  ON META(r).id in p.reviewList
  Where p.productId ='product20'
  GROUP BY p
7

Because you’re not joining, you’re nesting. So “r” becomes a single array field - the average value of an array (itself, not its content) is null.

You could JOIN instead of NEST-ing if you want the average review value.

Or you must manipulate the array to obtain the average value for the field:

SELECT p.*, ARRAY_AVG(ARRAY v.rating FOR v IN r END) rating
FROM product AS p
NEST reviews AS r
  ON META(r).id in p.reviewList
Where p.productId ='product20'

The ARRAY ... FOR ... IN ... END clause builds a new array containing only the rating field from each value in the r field. The average for this array can then be calculated using the ARRAY_AVG() function since it is an array of numbers.

HTH.

1 Like
8

Nice this works! Can you explain me why some queries at the n1ql tutorial takes more than few seconds ? Is it normal ? Maybe I want to show users products where category = ‘Shoes’ limit 50, could it be slow therefore I dont use index because when in category you can have 1000 different categories

9

n1ql tutorial is file based data. To get familiar with language and so that any number of people can execute with out there own instance.
It will not use any backend KV or Indexes.

Looking faster either you should try your own instance with travel-sample data or checkout https://couchbase.live/

2 Likes
10

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.