N1QL Array-Query not index covered


I have documents like this:

“type”: “foo”,
“id”: 1,

“sections”: [

Now I want the id of all documents that contains 13 anywhere in “ids” in one of the sections elements.

I added a index:

CREATE INDEX metaplacement ON
test (type, distinct array
( distinct array id for id in section.ids end)
for section in sections end, id) WHERE type=“foo”

and do this query:

select id from test
where type=“foo” and sections is not missing AND any section in sections satisfies
any id in section.ids satisfies id = 13 end

This works perfectly so far, I get the expected result. But for each element found, N1QL fetches the document.

The index itself works (if it finds 3 matching documents there are 3 out in “IndexScan”, but then these 3 documents are fetched. I don’t get why, because all needed fields are stored in the index, so this query should be full index covered without touching the kv engine.

Any ideas?

Thanks, Pascal

CREATE INDEX  `metaplacement`  ON test ( DISTINCT ARRAY (  DISTINCT section.ids ) FOR `section`  IN `sections`  END,  `id` ) WHERE type="foo";

select t.id from test AS t
where t.type=“foo” and  any section in t.sections satisfies
(any vid in section.ids satisfies vid = 13 end) end;

Try above if does not work and want cover use the following index

CREATE INDEX  `metaplacement`  ON test ( DISTINCT ARRAY (  DISTINCT section.ids ) FOR `section`  IN `sections`  END,  `id`, sections ) 
WHERE type="foo";

Cool, thanks! With your second index it works! So trick is to index “sections” separately? Then if I understand this correctly, it’s now index covered and fast, but needs more space because sections itself is also indexed?

Yes. First one should have covered too (https://issues.couchbase.com/browse/MB-34660).