I have added the index on the childId in the test bucket and currently I am performing the operation as mentioned in the below N1ql query When I run the below N1QL query index is not being used as the childId is in the when clause and not in where clause.
How can I write the below query efficiently so that it makes use of index created?
UPDATE Test AS d
SET d.children= ARRAY l FOR l IN d.children WHEN l.childId != “123456” END
Couchbase index selection is based on WHERE clause only OR primary index must present.
Also note mutation is expensive, SQL statement once row passed WHERE clause it must do mutation irrespective what you do in SET/UNSET. You don’t want mutate document if childId is not present. Best way to do is add WHERE clause like below.
UPDATE `Test` AS d
SET d.children= ARRAY l FOR l IN d.children WHEN l.childId != “123456” END
WHERE ANY v IN d.children SATISFIES v.childId = "123456" END;
CREATE INDEX ix1 ON Test (DISTINCT ARRAY v.childId FOR v IN children END);