Optimize the N1QL query and index

Hi Team,

we have 15Million records and our scenario is to find the contact matches the emailId which is part of nested object.

SELECT individual.individualId, individualRole
FROM optima_contact as individual
UNNEST individual.individualRoles as individualRole
UNNEST individualRole.emailContacts as email
WHERE individual.type=‘individual’
AND individualRole.roleSpecId=-5
AND lower(email.emailAddress)=“kelej62547@lidte.com
AND email.contactMediumSpecId=-4

PFA sample document.

sampleDoc.zip (843 Bytes)

with regards,
Mohamed S

also please confirm below changes is ok for above issue.
CREATE INDEX adv_DISTINCT_individualRoles_emailContacts_lower_emailAddress_type ON optima_contact(DISTINCT ARRAY (DISTINCT ARRAY lower((v1.emailAddress)) FOR v1 in v0.emailContacts END) FOR v0 in individualRoles END) WHERE type = ‘individual’

SELECT individual.individualId
FROM optima_contact AS individual
WHERE individual.type=‘individual’
and ANY v0 IN individual.individualRoles SATISFIES (ANY v1 IN v0.emailContacts SATISFIES lower(v1.emailAddress)=“kelej62547@lidte.com” and v1.contactMediumSpecId is not missing and v1.contactMediumSpecId=-4 END) AND v0.roleSpecId is not missing and v0.roleSpecId=-5 END;

Hi @mohamedw,

I see you answered yourself, I removed some of your predicates to select from your test document, it works for me but I don’t know if it is an optimal N1QL query

SELECT individual.individualId
FROM `optima_contact` AS individual
WHERE individual.type="individual"
ANY v0 IN individual.individualRoles 
   SATISFIES (ANY v1 IN v0.emailContacts 
   SATISFIES LOWER(v1.emailAddress)="moparce@smart.com.ph" 

But if performance is lacking you might consider a covering index (as a last resort)

CREATE INDEX adv_DISTINCT_individualRoles_emailContacts_lower_emailAddress_type_individualId 
ON `optima_contact`((DISTINCT (ARRAY(DISTINCT (ARRAY LOWER((`v1`.`emailAddress`)) 
FOR `v1` IN (`v0`.`emailContacts`) END)) FOR `v0` IN `individualRoles` END)),`individualId`)
WHERE (`type` = 'individual')

Note, If this is a one off thing you can use Eventing Service (no additional Index) based on your sample data the function below can locate you documents. If however you are indeed using N1QL querying these items constantly then by all means use N1QL.

// This function requires:
// 1. has a source bucket of 'optima_contact'
// 2. has an Eventing scratchpad bucket called of 'meta' 100MB is fine.
// 3. [OPTIONAl] has a target bucket called of 'destination' with a bucket  
//    binding aliased to 'dst_bkt' this must be big enough to hold the  
//    matched docs with an email 'hit' in mode read+write.
// Use as follows:
// A. create or flush the bucket 'destination' 
// B. Edit this function perhaps to:
//         var lookfor = "kelej62547@lidte.com";
// C. Deploy this function wait for completion and then undeploy.
// D. Use /opt/data/cbexport for bucket 'destination'
// E. drop or flush the bucket 'destination' 

function OnUpdate(doc, meta) {
    if (!doc.type || !doc.type === "individual") return;
    if (!doc.individualRoles)  return;
    var lookfor = "moparce@smart.com.ph";
    var ematch = false;
    doc.individualRoles.forEach(function(role) {
        if (!ematch && role.emailContacts) {
            role.emailContacts.forEach(function(ec) {
                if (!ematch && ec.emailAddress && ec.emailAddress === lookfor) {
                    ematch = true;
                    // log('hit',ec.emailAddress);
    if (ematch) {
        log('KEY',meta.id, 'individualId',doc.individualId);
        // ACTION [OPTIONAL] email was matched, just log the entire document
        // log('MATCH found<'+lookfor+'> in',meta.id,doc);
        // ACTION [OPTIONAL] email was matched, write to a new bucket
        //dst_bkt[meta.id] = doc;