Query two different buckets

Hi, I’m new user on couchbase, I need to “join” in one query two documents, but they doesn’t have field with same key. Example:

Bucket consumer
“id” : 1,

Bucket sell
“id” : 1,
“consumer_id” : 1

These database was imported from a old application, in SQL I can join with simples join
select * from consumer c join sell s on s.consumer_id = c.id

select * from consumer c,sell s where c.id = s.consumer_id

Can I do in N1ql without create a new field?


Hi @renan.cuoghi,

Couchbase supports INNER JOIN and LEFT OUTER JOINS.
You need to have reference from one bucket(document) to DOCUMENT KEY of the other one.

In your case, try the following:

SELECT * FROM sell s cINNER JOIN consumer c ON KEYS “_id::” || tostring(s.id);

Note: This implicitly means the following. But, syntax is NOT supported.
Standard syntax will be in the next release. Look for developer preview later this month.

SELECT * FROM sell s cINNER JOIN Sell s ON KEYS( “_id::” || tostring(s.id) = META©.id).

In the current syntax,

  1. All the references in the ON KEYS should be on the LHS bucket (in this case s).
  2. The KEYS expression should form a string. Hence tostring.

See this nice article by @atom_yang:

Thanks, I will have to change my documents, because I import from a relational database and the keys is not equals.