IS MISSING Clause not getting picked up in indexing

SQL++
,
1

CREATE INDEX idx_is_archived ON my_bucket(isArchived)

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 ?

2

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.
CREATE INDEX idx_is_archived ON my_bucket ( IFMISSING(isArchived,"") )
SELECT * from my_bucket where ( IFMISSING(isArchived,"") = “”;
If isArchived is boolean then choose the value different (i.e string “”)

3

Thanks @vsr1, this solution works for our use case