N1ql join by array

SQL++
1

Hi guys! I’ve started using N1ql and I have been following your tutorials.

I have noticed many times that a given document (let’s say User) contains an array with the ids of other documents (let’s say orders), from which we can later on perform a join. Is this solution suitable for a situation where a user might have thousands of references?

If not, what is the alternative? What would be the best N1ql query to find all users and their orders (where some users may not have orders)?

Let me know if you need any more information. Thank you!!

2

You can have an array as a join clause. N1QL joins each of the elements from the array to the target table.
Reads this and see if you still have more questions.

3

Hi Keshav. Thank you for your help. The article provided a very helpful insight, it would be useful to have this information in N1QL tutorials.

4

I have tried to follow the approach you mentioned in the link, but this approach does not list all of the users if a user does not have orders.

I would expect to have something like:

[ { "userId": "1", "docType": "User", "name": "User1", "orders": [ { "orderId": "1", "docType": "Order", "userId": "1", "name": "Order1" } ] }, { "userId": "2", "docType": "User", "name": "User2", "orders": [ ] } ]

Is there something I am missing here? These are the queries I used:

`
CREATE INDEX idx_test ON bucket(“User::” || userId);

SELECT u.*, ARRAY_AGG(o) as orders
FROM bucket AS u LEFT OUTER JOIN bucket AS o
ON KEY “User::” || o.userId FOR u
WHERE u.docType='User’
AND o.docType='Order’
GROUP BY u;
`

This query does not return user2 as I would expect. Am I missing something here? I have noticed that if I remove the o.docType line the query returns what I expected, but what happens if I have multiple documents of different types with a reference to userId?

5

Sorry, I have just noticed now that I answered to myself… Perhaps you didn’t get a notification?

6

Hi @manusyone, you can do LEFT OUTER JOIN to retain all of the users after the joins.

7

Hello @keshav_m, if you see my example you will see that I did a LEFT OUTER JOIN. In my last paragraph, I state that if I remove the o.docType from the conditions, only then will user2 appear. Am I missing something here?

8

Anyone? This is very important for us to sort out… Let me know if you need more information!