N1QL indexing when there is no where clause in the Update query

SQL++
, ,
1

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

2

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);
1 Like
3

This query works .Thank you very much @vsr1

4

@vsr1
When trying to perform delete for bulk docs i.e around 36k I am seeing the below error
code”: 12008,
“msg”: “Error performing bulk get operation - cause: {1 errors, starting with bulkget exceeded MaxBulkRetries for test"
Using couchbase Community Edition 6.5.1 build 6299
Could you please help ? Whether adding retry logic is the only solution while executing N1ql from code