CODEWITHSUNDEEP
couchbasesql++
Lho Ben
Lho Ben

Reputation: 2149

What is wrong with this couchbase index?

i created this couchbase index 

CREATE INDEX `idx_additionalOrderCode` ON 
`order`(ALL ARRAY s.`value` FOR s IN additionalOrderCode WHEN s.`typeCode`= "MYCODE" END)
WHERE _class = "com.entrprise.OrderEntity"

based on the example in this page

https://docs.couchbase.com/server/6.0/n1ql/n1ql-language-reference/indexing-arrays.html

but when i query this index with this select query :

SELECT * FROM `order` 
WHERE `_class` = "com.entrprise.OrderEntity" 
AND ANY s IN additionalOrderCode SATISFIES ( s.`value` = "130047777" ) and (s.typeCode = "MYCODE" ) END

i get the error 

 "msg": "No index available on keyspace order that matches your query. Use CREATE INDEX or CREATE PRIMARY INDEX to create an index, or check that your expected index is online."

No primary index is backing the query, i want all my queries to be backed by secondary indexes.

I am using Enterprise Edition 6.0.0 build 1693

Thanks for your help

Upvotes: 3

Views: 239

Answers (2)

prguptadev
prguptadev

Reputation: 50

This indicates that the main or cover index for the given bucket is not accessible. To fix this, either a primary index or a secondary index must be created. Because primary indexes include all of the keys in a particular keyspace, I advise avoiding them. Only ad hoc queries on keyspaces that are not supported by a secondary index must be executed with the aid of primary indexes, which are optional.

primary index format->

CREATE PRIMARY INDEX #primary ON bucket_name

Secondary index for your case->

CREATE INDEX adv_class_DISTINCT_additionalOrderCode_value ON order(_class,DISTINCT ARRAY s.value FOR s in additionalOrderCode END)

Another option is to use the advise button in the Couchbase user interface. This will recommend the necessary index, and we can also fine-tune that.

enter image description here

Upvotes: 0

Lho Ben
Lho Ben

Reputation: 2149

solved it with this index (i only deleted the ALL in between distinct and array)

CREATE INDEX `idx_additionalOrderCode` ON `order`
 (distinct (array (aoc.`value`) for `aoc` in `additionalOrderCode` WHEN aoc.`typeCode`= "WLEC"  END), orderTypeCode)
 WHERE (`_class` = "com.entreprise.OrderEntity")

Upvotes: 2

Related Questions