couchbase Index not getting picked by n1ql

Question

I am trying to create a covering index where data is sorted by an element . The index doesn’t seem to be working . Can You please help with this

Example query –

select ax, by, az ,ts from test where meta().id like 'CX%' and az = 'BBD' limit 100

Example index – CREATE INDEX index_test_sec_idx ON test(ax, by, az ,ts DESC) WHERE ((meta().id) like "CX%") WITH { "num_replica":1 }

I am getting the below error -

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

Answer 1

Couchbase index doesn't index document when leading index key value is MISSING. So due to that query predicate must have reference to to leading index key to qualify the index.

select ax, by, az ,ts 
from test 
where meta().id like 'CX%' and az = 'BBD' AND ax IS NOT MISSING
limit 100

OR

select ax, by, az ,ts 
from test 
where meta().id like 'CX%' and az = 'BBD' 
limit 100

CREATE INDEX index_test_sec_idx ON test(az, by, ax ,ts DESC) 
WHERE ((meta().id) like "CX%")