and when I query this field using MISSING Clause, it does not use this index.
SELECT * from my_bucket where isArchived is MISSING
while, if I query it using any other value, index is applied
SELECT * from my_bucket where isArchived=false
If this is the expected behavior, how can we deal with such scenario ?
Couchbase bucket can hold any type of unrelated documents.
Indexes only index when leading index key value is not MISSING only so that every index will not have all the entries in the bucket (if it includes indexes are huge).
Due to above restrictions query must have non MISSING predicate on leading index key .
The above behavior is expected.
To over come that you must have primary index.
Or this one of the rare case you can use following techinque.
my_bucket ( IFMISSING(isArchived,"") )
SELECT * from my_bucket where ( IFMISSING(isArchived,"") = “”;
If isArchived is boolean then choose the value different (i.e string “”)
Thanks @vsr1, this solution works for our use case