Join more than 2 buckets


#1

Can I join more than 2 buckets? I have 2 buckets which have documents. Each document has acct_id field.
Can i join buckets on this field? I tried below query but no response.

SELECT consumer.acct_id, tickets.event_date, tickets.event_name FROM consumer JOIN tickets ON KEYS consumer.acct_id.

Here consumer and ticket bucket has documents with field acct_id. and they have matching records.
Can you please help me.


#2

You can do this - however you need consumer.acct_id to equal the document IDs on the tickets bucket. you cannot join across an attribute in the tickets bucket document with the attribute in the consumer bucket documents in 4.0. We are looking to relax this in future.
thanks
-cihan


#3

Hi I tried to 2 buckets. Test1 - Test 2 as below.

Test1
ID Сontent
100 { “acct_id”: 100, “Detail”: “Detail1” }
101 { “acct_id”: 101, “Detail”: “Detail2” }

Test2

ID Сontent
100 { “acct_id”: 100, “Name”: “Name1” }
101 { “acct_id”: 101, “Name”: “Name1” }

Trying to run below query but no result. Can you please help?
SELECT t1.acct_id, t1.Detail, t2.Name FROM Test1 AS t1 JOIN Test2 AS t2 ON KEYS t1.acct_id


#4

sorry I didn’t catch this first. your data types are string and number. pls try this

SELECT t1.acct_id, t1.Detail, t2.Name FROM Test1 AS t1 JOIN Test2 AS t2 ON KEYS TOSTRING(t1.acct_id);

#5

thank you.

also can you please let me is it possible to join 2 views? if yes can you point me to some examples.
Bucket join will not work for me as my document id is complex key.(Combination of acct_id.even date, event time) but join condition is acct_id.


#6

You could combine a few fields as long as you can construct the full key; this would work for example:

SELECT t1.acct_id, t1.Detail, t2.Name FROM Test1 AS t1 JOIN Test2 AS t2 
   ON KEYS TOSTRING(t1.acct_id)||t1.event_date||t1.event_time;

Unfortunately we don’t allow joining map reduce views in N1QL in 4.0.