How to index this couchbase query

user_13385558 0
Viewed 43

I am try to index this query but I don't how to do so

const query = `SELECT r.*, r.itemName as name, r.createdBy.timestamp AS modifiedOn
               FROM report r
               LET groupReportIds = (SELECT rg.reportGuid
                      FROM report rg
                      WHERE rg.eventType = 'GROUP_SHARING' AND LOWER(rg.userEmail) = LOWER("${email}"))
               WHERE (ANY u in groupReportIds SATISFIES u.reportGuid = r.details.guid  END)`;
n1ql couchbase
1 Answers
CREATE INDEX ix1 ON report(LOWER(userEmail), reportGuid) WHERE eventType = "GROUP_SHARING";
CREATE INDEX ix2 ON report(details.guid);

WITH groupReportIds AS (SELECT RAW rg.reportGuid
                        FROM report rg
                        WHERE rg.eventType = "GROUP_SHARING"
                            AND LOWER(rg.userEmail) = LOWER("${email}"))
SELECT r.*, r.itemName as name, r.createdBy.timestamp AS modifiedOn
FROM report r
WHERE r.details.guid IN groupReportIds;
user_60805360
Related