How to create IS MISSING partial index on a field inside an array object

Couchbase Server
, ,
1

Hi

I have a query as below,

SELECT count(1)
FROM PPPPP
WHERE _class IN [‘XXXX’, ‘YYYY’]
AND ANY subCond IN entity.condition.subConditions SATISFIES (subCond.sellingUOM IS MISSING
AND subCond.type = “ZZZZZ” ) END
AND entity.phase IN [‘AAAAA’ ,‘BBBBB’]
AND entity.endDateTime > NOW_MILLIS()

I am trying to get an index which servers IS Missing on sellingUOM field. This sellingUOM is an attribute in subConditions which is an array. I am trying to create the below index but fails with syntax error. Need help in creating the correct index.

CREATE INDEX idx_01 ON
PPPPP(entity.phase,entity.endDateTime,
ARRAY {subCond.type} FOR subCond IN entity.condition.subConditions END)
WHERE _class IN [‘XXXX’,‘YYYYY’]
AND subCond1.sellingUOM IS MISSING FOR subCond1 IN entity.condition.subConditions END
USING GSI

Regards,
Venkat

2 
CREATE INDEX ix1 ON PPPPP( `entity`.`phase` , `entity`.`endDateTime` , 
           DISTINCT ARRAY sc.type FOR sc IN  entity.condition.subConditions WHEN sc.sellingUOM IS MISSING END)
WHERE _class IN ["XXXX", "YYYY"];

AND ANY subCond1 IN entity.condition.subConditions SATISFIES subCond1.sellingUOM IS MISSING END

This is syntax, Your query looking for MISSING and type on same array elements. This will be true for any array element has MISSING and type can be different array elements.

1 Like
3

@vsr1 many thanks that worked