Arrays indexing how to disable documents indexing with missing key

SQL++
,
1

Hi all,
We have some index on array keys and I notice that if that key is not index first documents not having this key are indexed. I spotted that in Couchbase console comparing 2 index whose definition are:

CREATE INDEX `cursor_test_double_array_composite` ON `mybucket`(`sl`,(distinct (array (distinct (array (`v1`.`h`) for `v1` in (`v0`.`g`) end)) for `v0` in `f` end)),(meta().`id`)) WHERE (`@T` is not missing)

and

CREATE INDEX `cursor_test_double_array` ON `mybucket`((distinct (array (distinct (array (`v1`.`h`) for `v1` in (`v0`.`g`) end)) for `v0` in `f` end)),(meta().`id`)) WHERE (`@T` is not missing)

mybucket have 7 documents, all having @T key with some value.
Only difference between these is presence of “sl” key in composite one. The first index stats indicate 7 items whereas the second one has only 3. There are 4 documents have no data “pointed” by f.g.h path, but these are indexed in composite index.

Is there any way to modify the index create query to avoid composite index from indexing the 4 documents ?

Thanks,
Best

2

You will not able to do that.
The reason is

SELECT b.*
FROM mybucket AS b.sl IS NOT NULL;
must give all 7 documents. if
WHERE b.`@T` IS NOT MISSING and b.sl IS NOT NULL

Must give all 7 documents.
If it chooses cursor_test_double_array_composite it give 7 documents
If skip indexing those non-leading key MISSING the index will not have entries and causes wrong results.

Query must never give differfent results based on different access paths (indexes)

3

Thanks for your quick reply. I don’t understand why this is specific to arrays. I mean I could specify an index with 2 or more not missing keys I guess ?

CREATE INDEX `my_index` ON `mybucket`(`sl`,`sl2`) WHERE (`@T` is not missing) and (sl is not missing) and (sl2 is not missing)
4

if you want u can specify