Joining documents without foreign key

Couchbase Server
1

Is there a way to join two documents without foreign key?

for instance, i have CUSTOMER and CUSTOMER_CONTACT

select customer.name, customerContact.landLine
from default customer
join default customerContact
on keys (SELECT RAW meta(default).id from default where type=“CUSTOMER_CONTACT” and customerId= customer.id)[0]
where customer.type = “CUSTOMER”

Something like i will return single id on subquery to use in “on keys”

2

Use ANSI JOIN https://blog.couchbase.com/ansi-join-support-n1ql/

CREATE INDEX ix1 ON default(customerId) WHERE type ="CUSTOMER_CONTACT";
SELECT c.name, cc.landLine
FROM default AS c
JOIN default AS cc
ON cc.customerId = c.id
WHERE c.type = "CUSTOMER" AND cc.type = "CUSTOMER_CONTACT";
3

@vsr1

Does ANSI JOIN support version 4.6? If not, is there an alternative way in my version?

I appreciate your reply, thanks.

4

You need 5.5

For 4.6 you can try with Cartesian JOIN using UNNEST

SELECT c.name, cc.landLine
FROM default AS c
UNNEST (SELECT d.* FROM  default  AS d WHERE d.type = "CUSTOMER_CONTACT") AS cc 
WHERE c.type = "CUSTOMER" AND cc.customerId = c.id;
5

@vsr1

What I’m trying to achieve is a query something like this:

SELECT product.name, customer.name, contact.landLine
FROM default product
JOIN default on keys product .customerId
UNNEST (select d.* from default d where d.type == “CUSTOMER_CONTACT”) as contact
AND product.type = “PRODUCT”
AND product.customerId = contact.customerId

I’m able to run the query with gateway timeout error.

P.S. I have index on product, customer, and contact.