How to do a proper LEFT OUTER JOIN in N1QL

Question

I have created an index in order to perform the following left outer join in N1ql:

CREATE INDEX idx_test ON bucket("User::" || userId) WHERE docType="Order";

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 works fine when I have orders for that particular user. The problem is when I don't have any orders, then I get no results at all.

I have noticed that if I remove the AND o.docType="Order" statement from the query I get some results, but what happens if there are documents that also have a userId property? How can I get this to work correctly?

In standard SQL you would need to move the condition AND o.docType="Order" to the join condition (out of the where). I don't know n1ql so I don't know how you specify more then one condition in the join clause there. — a_horse_with_no_name
I don't think that N1QL allows for multiple conditions on the JOIN statement (developer.couchbase.com/documentation/server/current/n1ql/…), but thank you for the suggestion. — Manuel Reis
Then you need something like AND (o.docType="Order" or o.docType IS NULL) — a_horse_with_no_name
I had tried this as well to no avail, I am guessing that since the docType is missing, then the key used in the LEFT OUTER JOIN will be null, yielding an empty result set. — Manuel Reis
@a_horse_with_no_name I must have been doing something wrong, because it is working now... Thank you for your help! — Manuel Reis

geraldss geraldss · Accepted Answer · 2016-09-06T17:04:50

@a_horse_with_no_name is essentially correct. Use IS MISSING instead of IS NULL:

AND (o.docType="Order" OR o.docType IS MISSING)

How to do a proper LEFT OUTER JOIN in N1QL

1 Answers