N1QL Array-Query not index covered

SQL++
1

Hi,

I have documents like this:

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

“sections”: [
{“name”:“foo”,
“ids”:[1,9,17,55]
},
{“name”:“bar”,
“ids”:[13,67]
}
]
}

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
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

2 
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";
3

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?

4

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

https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/indexing-arrays.html#implicit-covered-array-index