Planning failure with array parameter

I get a “PlanningFailureError” if I use array parameter, but if I replace the placeholder with the actual value it works:

// with parameters
let query = "SELECT * FROM bucket" +
    "\nWHERE SOME c IN channels SATISFIES c IN $channels END" +
    "\nAND type NOT IN $excludedTypes";
let options = {
    parameters: {
        channels: ["ch1", "ch2"],
        excludedTypes: ["a", "b"]
    }
};
cluster.query(query, options); // PlanningFailureError: No index available on keyspace...


let query = "SELECT * FROM bucket" +
    "\nWHERE SOME c IN channels SATISFIES c IN $channels END" +
    "\nAND type NOT IN [\"a\", \"b\"]"
let options = {
    parameters: {
        channels: ["ch1", "ch2"]
    }
};
cluster.query(query, options); // working

couchbase Node.js SDK v3.2.4

Do you have an index on the keypace?
I think the message even includes the command to create the required index.

Hi @mreiche,
of course I have an index on the keyspace:

CREATE INDEX `channels_type_index` ON `bucket`((distinct (array `c` for `c` in `channels` end)),`type`) WHERE (not (`type` in ["a", "b"]))

Unfortunately, the message from the sdk does not include the command of the recommanded index to create. But again, same query with plain values of the parameters works! It works via sdk and it works via query editor of the web ui.
I already try NOT type IN [...] instead of type NOT IN [...], but I get the same problem

At the time the statement is prepared it is unable to match the index condition as the parameter is unknown. (It could be a different array of values or not even an array at runtime, which would render the index selection incorrect.) For a partial index to be selected the partial index expression must appear literally in the statement (along with any other filters).

If you change your index definition to exclude the condition, it should work, i.e.

CREATE INDEX `channels_type_index` ON `bucket`((distinct (array `c` for `c` in `channels` end)))

(I excluded type too as it isn’t evaluated against the index with your example statement.)

If you can generate multiple clauses for type (and it is included in the index), then you should get index filtering on type too, e.g.

CREATE INDEX `channels_type_index` ON `bucket`((distinct (array `c` for `c` in `channels` end)),type)

With:

...
AND type != $t1 AND type != $t2

or

...
AND NOT (type = $t1 OR type = $t2)

HTH.

Thank you @dh, you made it very clear.
I create a partial index to reduce the data size of the index itself. It is ok for me to put the array literally in the statement to match the correct index.
Thank you again