Couchbase N1QL query with joined subquery with USE KEYS

Question

I need to write a n1ql query which demands another sub-query in select clause. As it is mandatory to use 'USE KEYS' while writing subqueries in n1ql. How to write USE KEYS clause for an inner joined query, below is an example of same case:

select meta(m).id as _ID, meta(m).cas as _CAS, 
   (select c.description 
    from bucketName p join bucketName c on p.categoryId = c.categoryId and p.type='product' and 
    c.type='category' and p.masterId=m.masterId ) as description //--How to use USE KEYS here ?
from bucketName m where m.type='master' and m.caseId='12345'

My requirment is to fetch some value from another 2 joined tables. however, I simplified above query to make it more understandable.

Please suggest the correct way to implement.
Also, is writting sub-queries in n1ql is better than fetching documents seperatly and merging them in coding?

vsr vsr · Accepted Answer · 2020-01-08T14:16:08

Non FROM CLAUSE, correlated sub queries requires USE KEYS due to global secondary indexes queries can take long time and resources. This is restriction at present in the N1QL. If you can derive p's document key from the m you can give that as USE KEYS in p.

Otherwise you have two options

Option 1: As your subquery is in the projection Use ANSI JOIN https://blog.couchbase.com/ansi-join-support-n1ql/

SELECT META(m).id AS _ID, META(m).cas AS _CAS, c.description
FROM bucketName AS m
LEFT JOIN bucketName AS p ON p.masterId=m.masterId AND p.type='product'
LEFT JOIN bucketName AS c ON c.type='category' AND p.categoryId = c.categoryId
WHERE m.type='master' AND m.caseId='12345';

CREATE INDEX ix1 ON (caseId) WHERE type='master';
CREATE INDEX ix2 ON (masterId, categoryId) WHERE type='product';
CREATE INDEX ix3 ON (categoryId, description) WHERE type='category';

NOTE: If there is no Unique relation m to p to c JOIN can produce more results. 
If that is case, you can do GROUP BY META(m).id, META(m).cas and 
ARRAY_AGG(c.description). All descriptions are given as ARRAY.

Option 2: As described by you issue two separate quires and merge in the application.

Couchbase N1QL query with joined subquery with USE KEYS

1 Answers