java N1QL二级索引不使用参数化IN子句

Question

使用com.couchbase.client, java-clientversion 2.2.7我无法获得使用参数化IN子句的n1ql二级索引。请参阅下面的示例索引、查询和java代码

Index

CREATE INDEX `indexName` ON `bucketName`(id,docType) USING GSI ;

Query

public static final String COUNT_STATEMENT = "select count(*) as count " +
            "from bucketName " +
            "where docType = 'docId' " +
            "and id IN $ids " + 
            "and publishTimestamp between $startTime and $endTime";

Code to submit Query

public int getCountForDuration(Long startTime, Long endTime, Collection<String> ids){
    List<String> idList = new ArrayList<>(ids);
    JsonObject placeHolders = JsonObject.create()
                                        .put("ids", JsonArray.from(idList))
                                        .put("startTime", startTime)
                                        .put("endTime", endTime);
    N1qlQuery query = N1qlQuery.parameterized(COUNT_STATEMENT, placeHolders)            
    N1qlQueryResult result = bucket.query(query);
    ...
}

在添加参数化之前，我的查询正确地使用了这个二级索引。如果我使用主索引，我的查询也可以工作

My question is this how do I create a secondary index which will be used by my query.

Answer 1

索引中的第一个条目（在您的例子中是id）不能丢失。因此，缺少id的文档将不在索引中。因此，如果不使用已受索引条件约束的字段，则必须指定不缺少该字段，以确保可以转到辅助索引

例如，你可以用type="entityType"查询以下索引

CREATE INDEX `indexName` ON `bucketName`(type) WHERE `type`="entityType"

Answer 2

我通过添加一个额外的is not missing子句解决了这个问题，出于某种原因，这个问题得到了解决。同样的解决方案也适用于我。以下是更新后的查询：

public static final String COUNT_STATEMENT = "select count(*) as count " +
        "from bucketName " +
        "where id is not missing " + 
        "and docType = 'docId' " +
        "and id IN $ids " + 
        "and publishTimestamp between $startTime and $endTime";

@Ben Wilde comment -

"The reason the "is missing" is required is because the first entry in an index (in 'this' case id) cannot be missing. So documents that have a missing id will not be in the index so if you do not use a field that is already constrained by conditions set by your index, then you will have to specify that it is not missing to ensure that can go to your secondary index"