CODEWITHSUNDEEP
indexingnosqlcouchbasesql++
Bhuvan
Bhuvan

Reputation: 2229

Couchbase Array indexing

Couchbase version 5.0

I'm trying to speedup the query below by array indexing, and I cannot get it right.

SELECT DISTINCT document_data.* FROM `optima` AS optima 
UNNEST optima.documents AS document_data 
UNNEST document_data.attachments AS attachment_data 
WHERE optima.type = "accountDocument"
AND attachment_data.objectId IN ["18485,0"] 
AND attachment_data.objectType IN ["account","service","cart","order"];

I have created the following indexes, but array index on attachments element does not seem to work

CREATE PRIMARY INDEX `optima-primary-index` ON `optima` USING GSI;

CREATE INDEX idx_document_nested ON `optima`
(DISTINCT ARRAY document_data FOR document_data IN optima.documents END) 
WHERE type = "accountDocument";

CREATE INDEX idx_attachment_nested ON `optima`
    (DISTINCT ARRAY 
(DISTINCT ARRAY attachment_data FOR attachment_data IN document_data.attachments END) 
    FOR document_data IN optima.documents END) 
WHERE type = "accountDocument";

CREATE INDEX `idx_type` ON `optima`(`type`);

Any suggestions?

Upvotes: 1

Views: 127

Answers (1)

vsr
vsr

Reputation: 7414

Index as attachment_data.objectId 

CREATE INDEX idx_attachment_nested ON `optima`
    (DISTINCT ARRAY 
(DISTINCT ARRAY attachment_data.objectId FOR attachment_data IN document_data.attachments END) 
    FOR document_data IN optima.documents END) 
WHERE type = "accountDocument";

Upvotes: 2

Related Questions