Unnest not used in query slows performance

Couchbase Server
1

select p.*
from product p
unnest p.desc d
unnest d.level l
limit 1;

Should give results immediately as with
select p.*
from product p
limit 1;

But it takes long long time.

2 
SELECT  p.*
FROM  product AS p
LIMIT  1;

Above query returns first document in the product bucket.

SELECT  p.*
FROM  product AS p
UNNEST p.desc AS d
UNNEST d.level AS l
LIMIT  1;

Unnest is JOIN of the document and its expression. If the document doesn’t have desc ARRAY in product or level ARRAY in the desc ARRAY those documents is filtered and trying to find the document that have based on your LIMIT 1 requirement.

Try OUTER UNNEST like below you will get results fast.

SELECT  p.*
FROM  product AS p
LEFT UNNEST p.desc AS d
LEFT UNNEST d.level AS l
LIMIT  1;