Indexes for n1QL queries using UNNEST and how to make it covered

SQL++
,
1

Hi, I am facing challenges on n1QL select queries timeout exceptions.
I have a document in couchbase something like this i.e. recipients.emailRecipients is an array and some other fields too-
“id”: “123”
“recipients”: {
“emailRecipients”: [
{
“contactStub”: “59b80454-8d84-44e9-b520-808340630b99”,
“firstName”: “b”,
“lastName”: “1”,
“additionalRecipients”: [],
“emailAddress”: "abc@gmail.com",
}
]
}

and let’s say key of document is - env::app-name::doc-name::documentId
I have created indexes as follows-

CREATE INDEX doc_idx ON bucket ((ALL ARRAY LOWER(i.contactStub) FOR i IN recipients.emailRecipients WHEN ARRAY_LENGTH(recipients.emailRecipients) > 0 END), (split((meta().id), "::")[0]), tenantId, callerId, environment, id) WHERE (((split((meta().id), "::")[1]) = "app-name") AND ((split((meta().id), "::")[2]) = "doc-name") AND ((split((meta().id), "::")[4]) IS MISSING) AND productLine != "abc") USING GSI;

I have the following two select queries-

SELECT meta(d).id, i.firstName, i.lastName, i.emailAddress, i.contactStub FROM bucket AS d UNNEST d.recipients.emailRecipients AS i WHERE split(meta(d).id, "::")[1] = 'app-name' AND split(meta(d).id, "::")[2] = 'doc-name' AND (split((meta(d).id), "::")[4]) IS MISSING AND (split((meta(d).id), "::")[0]) = "env" AND d.tenantId = "123" AND d.callerId = "abc" AND d.environment = "a" AND lower(i.contactStub) IN ["59b80454-8d84-44e9-b520-808340630b99"] AND array_length(d.recipients.emailRecipients) > 0 AND d.productLine != "abc"

This query works but the following query gives timeout.

SELECT meta(d).id, i.firstName, i.lastName, i.emailAddress, i.contactStub FROM data1 AS d UNNEST d.recipients.emailRecipients AS i WHERE split(meta(d).id, "::")[1] = 'app-name' AND split(meta(d).id, "::")[2] = 'doc-name' AND (split((meta(d).id), "::")[4]) IS MISSING AND (split((meta(d).id), "::")[0]) = "env" AND d.tenantId = "123" AND d.callerId = "abc" AND d.environment = "a" AND i.emailAddress IN ["abc@j.mail"] AND lower(i.contactStub) = "00000000-0000-0000-0000-000000000000" AND array_length(d.recipients.emailRecipients) > 0 AND d.productLine != "abc" LIMIT 100

Tried various possibilities but not working could someone suggest what should be done here?

2

You have lot of other predicates instead of UnnestScan on Array indexkey, you should use IndexScan
For covering add recipients.emailRecipients to the index keys as last index key. If it is too big it may not index due to index key size

CREATE INDEX doc_idx ON bucket (SPLIT(META().id, "::")[0], tenantId, callerId, environment,
          DISTINCT ARRAY LOWER(v.contactStub) FOR v IN recipients.emailRecipients END, productLine,id)
WHERE ARRAY_LENGTH(recipients.emailRecipients) > 0 AND (SPLIT(META().id, "::")[1] = "app-name") AND
(SPLIT(META().id, "::")[2] = "doc-name") AND (SPLIT(META().id, "::")[4] IS MISSING) ;

SELECT META(d).id, i.firstName, i.lastName, i.emailAddress, i.contactStub
FROM bucket AS d UNNEST d.recipients.emailRecipients AS i
WHERE ARRAY_LENGTH(d.recipients.emailRecipients) > 0 AND (SPLIT(META(d).id, "::")[1] = "app-name") AND
(SPLIT(META(d).id, "::")[2] = "doc-name") AND (SPLIT(META(d).id, "::")[4] IS MISSING)
AND (SPLIT(META().id, "::")[0] = "env") AND  d.tenantId = "123" AND d.callerId = "abc" AND d.environment = "a" AND d.productLine != "abc"
AND ANY v IN d.recipients.emailRecipients SATISFIES LOWER(v.contactStub) = "00000000-0000-0000-0000-000000000000" END
AND i.emailAddress IN ["abc@j.mail"] AND LOWER(i.contactStub) = "00000000-0000-0000-0000-000000000000" LIMIT 100;