Query two different buckets

SQL++
1

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
{
“id” : 1,
“name”:“Teste”
}

Bucket sell
_id::1
{
“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

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

Can I do in N1ql without create a new field?

Thanks

2

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(c).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:

3

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