How do I most optimally do this N1QL SELECT query?

Question

Lets say I have a bunch of documents of type CONTACT:

{
    "contactId": "V1234",
    "contactFirstName": "A",
    "contactLastName": "N",
    "emailAddress": "12345",
    "invitationId": "IVT:123",
    "documentType": "CONTACT"
}

And for each contact document there is a EMAIL document and an INVITATION document:

EML:12345
{
    "emailId": "12345",
    "emailAddress" = ["abc@gmail.com"]
}

IVT:123
{
    "invitationId": "IVT:123",
    "invitationStatus": ["REGISTERED"]
}

Lets say I have an array of contactIds : ["V1234", "V2345" ... ]

How would I write a SELECT query for each contactId in that list and select contactFirstName, contactLastName, eml.emailAddress.[0], invitation.status.[0] (only if the status is of type "REGISTERED", or "INVITED".) and limit it to 10 values.

I was thinking something like:

SELECT DISTINCT
    contact.contactFirstName as `contactFirstName`, 
    contact.contactLastName  as `contactLastName`,
    eml.emailAddress         as `emailAddress`,
    ivt.status[0].status     AS  invitationStatus
from
    `contact-services` AS contact
    
    INNER JOIN `contact-services` AS eml ON
        CONCAT("EML",':',contact.contactEmailAddressIds[0]) =meta(eml).id 
    
    INNER JOIN `contact-services` AS ivt ON
        contact.invitationId =meta(ivt).id
WHERE
    contact.documentType="PERSON_CONTACT" 
    AND
    ivt.status[0].status IN ["INVITED", "REGISTERED"]
    and
    contact.contactId IN ["V8459243"]

Except for the limit-of-10 your query looks fine to me. Note that you cannot meaningfully limit your results without specifying a sort-order - so what should determine the "first" 10 records, exactly? — Dai, Jun 22 '21 at 23:25

score 1 · Accepted Answer · answered Jun 23 '21 at 03:02

CREATE INDEX ix1 ON `contact-services` (contactId, emailAddress, invitationId, contactFirstName, contactLastName)
WHERE documentType = "CONTACT";

SELECT c.contactFirstName,
       c.contactLastName,
       (SELECT RAW e.emailAddress[0]
        FROM contact-services` AS e USE KEYS ("EML:"||c.emailAddress))[0] AS emailAddress,
       invt AS invitationStatus
FROM `contact-services` AS c
LET invt = (SELECT RAW i.invitationStatus
            FROM `contact-services` AS i USE KEYS c.invitationId
            WHERE ANY v IN i.invitationStatus SATISFIES v IN ["INVITED", "REGISTERED"] END)[0]
WHERE c.documentType = "CONTACT"
      AND c.contactId IN ["V8459243"] AND
      invt IS NOT MISSING
LIMIT 10;

How do I most optimally do this N1QL SELECT query?

1 Answers1