How to search in an array of objects using n1ql query



I have a document in couchbase something like this i.e. recipients.emailRecipients is an array-
“id”: “123”
“recipients”: {
“emailRecipients”: [
“contactStub”: “59b80454-8d84-44e9-b520-808340630b99”,
“firstName”: “b”,
“lastName”: “1”,
“additionalRecipients”: [],
“emailAddress”: "",

I want to select all those firstNames and emailAddress which have contactStub equals to a particular list.
e.g SELECT id,recipients.emailRecipients[0].firstName, recipients.emailRecipients[0].lastName, recipients.emailRecipients[0].emailAddress FROM bucket-name WHERE recipients.emailRecipients[*].contactStub IN [“59b80454-8d84-44e9-b520-808340630b99”, “ead3c01f-008c-4a7c-a091-4c3fb9a96233”] LIMIT 10 .

It works fine if I use recipients.emailRecipients[*].contactStub = [“59b80454-8d84-44e9-b520-808340630b99”] in query and searches for particular stub.

Can someone help here…? :slight_smile:

SELECT ........
WHERE ANY v IN recipients.emailRecipients SATISFIES v.contactStub IN ["59b80454-8d84-44e9-b520-808340630b99", "ead3c01f-008c-4a7c-a091-4c3fb9a96233"]  END 


SELECT,er.firstName, er.lastName, er.emailAddress 
FROM bucket-name AS d
UNNEST d.recipients.emailRecipients AS er 
WHERE er.contactStub IN ["59b80454-8d84-44e9-b520-808340630b99", "ead3c01f-008c-4a7c-a091-4c3fb9a96233"] 


Thanks @vsr1, it worked and one more thing, I have created the following index for the above query, is this correct? :thinking:

CREATE INDEX index_name ON bucket_name(id,((recipients.emailRecipients).contactStub),((recipients.emailRecipients).emailAddress),((recipients.emailRecipients).firstName),((recipients.emailRecipients).lastName))


CREATE INDEX ix1 ON bucket_name( DISTINCT ARRAY er.contactStub FOR er IN recipients.emailRecipients END);


@vsr1 but I have an another query which also searches on ANY v in recipients.emailRecipients SATISFIES v.emailAddress IN [“abc@g.mail”] and v.contactStub = “00000000-0000-0000-0000-000000000000” END
so I don’t think this will suffice in the index?


Array Index key can only index single item. If your SATISFIES clause need to filter more than one item:
1) One of them constant across queries you can use WHEN clause in array INDEX key.
Ex: DISTINCT ARRAY er.emailAddress FOR er IN recipients.emailRecipients WHEN er.contactStub = “xxxx” END
2) Index one of the filter using array index key, other filters apply post IndexScan.


@vsr1 The query -
SELECT id, recipients.emailRecipients[*].firstName, recipients.emailRecipients[*].lastName, recipients.emailRecipients[*].emailAddress FROM bucket-name WHERE ANY v in recipients.emailRecipients SATISFIES v.contactStub IN ["59b80454-8d84-44e9-b520-808340630b99", "ead3c01f-008c-4a7c-a091-4c3fb9a96233"] END LIMIT 30

This query returns all the elements in recipients.emailRecipients irresepctive of contStub given. I tried using [0] instead of [*] but it will return first element of the array.

vs if I use UNNEST query it returns data as expected.


You have given *, so it gives all, If you need specific ones you need ARRAY constructors.

SELECT id, ARRAY  {v.firstName, vlastName,v.emailAddress} FOR v IN recipients.emailRecipients WHEN v.contactStub IN ["59b80454-8d84-44e9-b520-808340630b99", "ead3c01f-008c-4a7c-a091-4c3fb9a96233"] END 
FROM .....