CODEWITHSUNDEEP
couchbasesql++
Harmattan
Harmattan

Reputation: 35

How to index this couchbase query

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)`;

Upvotes: 2

Views: 135

Answers (1)

vsr
vsr

Reputation: 7414

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;

Upvotes: 1

Related Questions