Use query result as a where clause/condition

SQL++
1

How would I use my query result as a condition inside another query?

Here is example documents
[student document (student_999)]

{ firstName: 'Allen',  lastName: 'Smith', classId: 'class_123' }

[books document]

{ topic: 'math', classId: 'class_123'}
{ topic: 'english',  classId: 'class_123'}
{ topic: 'german', classId: 'class_000'}

So the idea is that, given a known student id, I’ll need to query books that used by the student in a class.

SELECT a.classId FROM bucket a WHERE meta(a).id=='student_999'
–> this will return the classId,

SELECT a.* FROM bucket a WHERE a.type=="books" AND a.classId =="class_123"
–> this returns the books with the classId above.

How would I join these two query into one?

2

I realized I could use subquery to perform my task

SELECT a.* FROM bucket a WHERE a.type=='books' AND a.classId in (SELECT b.classId FROM bucket b WHERE meta(b).id=='student_999')

3

You need RAW in subquery so that it subquery produces array of stings instead of array of objects

SELECT a.* FROM bucket a WHERE a.type=='books' AND a.classId in 
(SELECT RAW b.classId FROM bucket b WHERE meta(b).id=='student_999')

In 5.5 You can use ANI JOINS to join between fields https://blog.couchbase.com/ansi-join-support-n1ql/