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:
“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
select * from consumer c,sell s where c.id = s.consumer_id
Can I do in N1ql without create a new field?
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,
- All the references in the ON KEYS should be on the LHS bucket (in this case s).
- 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.