Index on field which can be missing

Couchbase Server
,
1

Hello, is there any option how to update this index to be working with attached query?

Index:

CREATE INDEX `rb_act` ON `myBucket`(`tenantId`,(distinct (array `id` for `id` in `categoryIds` end))) WHERE (`type` = "rb-act")

Query:

SELECT META().id FROM myBucket 
WHERE tenantId IN [0] OR tenantId IS NOT VALUED
AND SOME id IN categoryIds SATISFIES id IN ['cke49rky8000coesa553s1ts9'] END
AND type = 'rb-act'

I have some documents without tenantIds - lets say global accessible. If i remove OR condition my index works. Thank you

2

I don’t see any OR.

CREATE INDEX `rb_act1` ON `myBucket`( DISTINCT categoryIds) WHERE `type` = "rb-act" AND tenatId IS MISSING;
SELECT META().id FROM myBucket 
WHERE tenantId IS MISSING
AND SOME id IN categoryIds SATISFIES id IN ['cke49rky8000coesa553s1ts9'] END
AND type = 'rb-act'
3

Sorry, i just updated query. Now there IS NOT VALUED but i tried also IS MISSING. For global documents there is no tenantId

4 
CREATE INDEX `rb_act1` ON `myBucket`( DISTINCT categoryIds) 
WHERE `type` = "rb-act" AND IFMISSINGORNULL(tenantId,0) = 0 ;

SELECT META().id FROM myBucket 
WHERE IFMISSINGORNULL(tenantId,0) = 0 ;
AND SOME id IN categoryIds SATISFIES id IN ['cke49rky8000coesa553s1ts9'] END
AND type = 'rb-act';
6

Why did you use IFMISSINGORNULL instead of IFMISSING? Should i use it even i’m sure that field can not be null - it can be any integer? And also is it better to use it in condition part in index instead of one of indexed field? Thank you

7

tenantId IS NOT VALUED is maps to IFMISSINGORNULL() .

It can any value that not used by tenantid

If you need value use in index key.

1 Like
8

Whould it really by IFMISSINGORNULL(tenantId,0) = 0 ; in index? How does it look if i will take a look for tenantId = 5? Tenant ids are from 0 to n - real positive integers. As i understand this condition then it will be 5 == 0 which does not work.

I tried it and IFMISSINGORNULL(tenantId,0) = 5 does not catch index.

9 
CREATE INDEX `rb_act1` ON `myBucket`(  IFMISSINGORNULL(tenantId,-1) , DISTINCT categoryIds) 
WHERE `type` = "rb-act" ;

SELECT META().id FROM myBucket 
WHERE IFMISSINGORNULL(tenantId,-1) = 5 ;
AND SOME id IN categoryIds SATISFIES id IN ['cke49rky8000coesa553s1ts9'] END
AND type = 'rb-act';

IFMISSINGORNULL(tenantId,-1)  IN  [-1]     nulls , missing
IFMISSINGORNULL(tenantId,-1)  IN [-1,5] nulls, missing, 5
IFMISSINGORNULL(tenantId,-1)  IS VALUED   for all entries