How to search in an array of objects using n1ql query

n1ql
query

#1

Hi,
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”: "abc@gmail.com",
}
]
}

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:


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

OR

SELECT d.id,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"] 
LIMIT 10;

https://developer.couchbase.com/documentation/server/current/n1ql/n1ql-language-reference/indexing-arrays.html


#3

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))


#4

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


#5

@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?


#6

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.


#7

@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.


#8

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 .....

https://blog.couchbase.com/working-json-arrays-n1ql/