Secondary index entries for docs with missing key values

Couchbase Server
,
11

The following two indexes will satisfy all your queries, including MISSING values. The reason is that index keys after the leading key ( after type below ) are allowed to have MISSING values.

Index 1: CREATE INDEX IdxSpecial ON Test( type, IsSpecial ) WHERE type = "user";

Index 2: CREATE INDEX IdxActive ON Test( type, IsActive ) WHERE type = "user";

Query 1: SELECT * FROM TEST WHERE type="user" AND IsSpecial = TRUE;

Query 2: SELECT * FROM TEST WHERE type="user" AND IsActive = TRUE;

Query 3: SELECT * FROM TEST WHERE type="user";