Ignore the documents with IDs starting with '_sync' in Adaptive index

SQL++
1

we have created the Adaptive index using the below query which works fine.

CREATE INDEX adaptive_index1
ON testbucket(DISTINCT PAIRS({datemodified, doctype, syncchannel,wogismodelid}));

And we need to ignore the documents with IDs starting with ‘_sync’, so we have deleted the existing index and created a fresh index by adding where Clause to it.

CREATE INDEX adaptive_index1

ON `testbucket`(DISTINCT PAIRS({datemodified, doctype, syncchannel,wogismodelid}))
WHERE meta(`testbucket `).id NOT LIKE '_sync%' ;

While creating index we did not get any errors, but when we try to query data we are getting ‘No index available’ error. below is the query which we are attempted.

select * from testbucket where doctype = ‘fnotes’

we are using couchbase 5.0.1 version.

2

The query needs to include index WHERE clause also.
select * from testbucket where (meta().id NOT LIKE '_sync%' ) AND doctype = "fnotes"

3

i have tried your suggestion but still no improvement in it, please refer below shown error message

No index available on keyspace testbucket that matches your query. Use CREATE INDEX or CREATE PRIMARY INDEX to create an index, or check that your expected index is online.

4

The fields that referenced in index WHERE clause needs to be part of the adaptive index. Also it needs to be document field and not a expression as described Limitation 6 AT https://developer.couchbase.com/documentation/server/current/n1ql/n1ql-language-reference/adaptive-indexing.html

Check this out and see if gives right results.

 CREATE INDEX adaptive_index1
ON `testbucket`(DISTINCT PAIRS({datemodified, doctype, syncchannel,wogismodelid,META().id}))
WHERE meta(`testbucket `).id NOT LIKE '_sync%' ;
select * from testbucket where (meta().id NOT LIKE '_sync%' ) AND doctype = "fnotes"
5

Thanks and as per your suggestion i have included the meta().id in adaptive index, but still no change in the result