Subquery couchabse


Following query gives result but the subquery part is coming empty for every user.What should be done to get the result from following query.

SELECT pin_id,old_pin_id,place_id,place_name,pin_story,pin_rate,cat_name,child_cat_name,pin_create_date,
place_lat,place_long,pin_type,images,(SELECT t2.user_photo, FROM users t2 use keys t1.user_name WHERE t2.user_name= t1.user_name) AS user
FROM pins t1 where user_name=‘mustanish123’ ORDER BY pin_create_date DESC;

“cat_name”: “Map Issues”,
“child_cat_name”: “Incorrect Name”,
“images”: [],
“old_pin_id”: 54486,
“pin_create_date”: “2018-01-27 02:36:36”,
“pin_id”: “54486”,
“pin_rate”: 0,
“pin_story”: “Test report”,
“pin_type”: “report”,
“place_id”: “0”,
“place_lat”: 28.49566409,
“place_long”: 77.22458976,
“place_name”: “Reported Near Sanjeev Telecom”,
“user”: [],
“user_name”: “mustanish123”

The user key in above is coming empty for all entry, however their is data for the corresponding user in database.

Do you have document key “mustanish123” and paste that item.

I don’t think you have right relation.

SELECT t2.user_photo, FROM users t2 use keys t1.user_name WHERE t2.user_name= t1.user_name)

@vsr1 I do not have user_name as primary key in my another bucket.both buckets have user_name as its content. I am basically trying to implement subquery to fetch users detail in same so that I do not have to hit database again. Can you suggest what should be done to minimize database query.

SELECT pin_id, old_pin_id, place_id,place_name, pin_story, pin_rate, cat_name, child_cat_name,
pin_create_date, place_lat, place_long, pin_type, images,
(SELECT t2.user_photo,
FROM users t2
WHERE t2.user_name = “mustanish123”
) AS user
FROM pins t1
WHERE t1.user_name = "mustanish123"
ORDER BY t1.pin_create_date DESC;

Please check out your needs. Assume if outer query produces 1000 document subquery results are same for all of them and transporting same results part of each of document will be over head.


This case is working when I am reading data for one user, but what If I am not passing any username in where clause and wants to read all entry with corresponding name and photo as these may change by user from profile section.

At present In N1QL, Correlated sub queries requires USE KEYS, so you need to primary key relation.