How to search in an array of objects using n1ql query

SQL++
,
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 Likes
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 .....
1 Like
9

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

Hi @vsr1 , how can I add alias for the ARRAY and can I use ORDER BY in the array of object? Thanks.

10

like any other projection alias

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  AS contactSub
FROM .....
11

Can I use ORDER BY here? I want to try sort the array object using some fields in it. I already tried it but it’s not working. Thanks!

12

Use Sub query expression (https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/subqueries.html#from-clause) so that you can get complete SQL power instead of ARRAY construct

recipients is parent object

SELECT id, 
 (SELECT v.firstName, v.lastName, v.emailAddress  
   FROM recipients.emailRecipients  AS v
    WHERE v.contactStub IN ["59b80454-8d84-44e9-b520-808340630b99", "ead3c01f-008c-4a7c-a091-4c3fb9a96233"]\
    ORDER BY v.lastName)  AS contactSub
FROM .....
13

Ohhh I see. I will try to look on this one. Thanks for the help!