We are trying to join data from two different buckets.
It appears that N1QL index’s only support single bucket queries.
Is there any way we can avoid consolidating our buckets? We would like to be able to have fast results when we query cross-bucket.
Thanks.
N1QL supports LOOKUP/Index joins through document id on single or multiple buckets. https://developer.couchbase.com/documentation/server/current/n1ql/n1ql-language-reference/from.html.
@geraldss help me please, you’re the man!
Hi @kivey,
When you JOIN two buckets, for example:
FROM ORDERS o INNER JOIN CUSTOMER c ON KEYS o.cid
WHERE o.zip = 58238;
In couchbase JOINS, we use indexes on the leading bucket (keyspace).
In this case, the index will be used on ORDERS for the predicate (o.zip = 58238).
As the index streams the qualified document IDs, N1QL engines does a KV GET on the CUSTOMER – no further index required.
If our WHERE clause were to include something that is not in the leading keyspace, is it still possible to cover the query? Something like this:
FROM ORDERS o INNER JOIN CUSTOMER c ON KEYS o.cid
WHERE o.zip = 58238 and c.nameLast = ‘Smith’;
Thanks!
Yes. You can have predicates on both key spaces.
Yes. You can do the query with predicates on both (or any number of keyspaces or predicates). We’ll use the index for the first scan and do the filtering on other key spaces after the get and join operation.