Couch base join two buckets based on array values

SQL++
,
1

I have two buckets name like temp and temp1 respectively, and this document structure as below
For temp data
{
“_id”: “123”,
"data: [“TEST:123”, “TEST:345”]
}
For temp1 data

{
“data”:[“TEST:123”, “TEST:678”]
}
both buckets having this type of data and I want join this two buckets based on data array from temp1 any value presents in temp then return temp1 _id how I should write join on this two buckets, is necessary to create index on this both buckets array data fields ? I need query like this select ARRAY_INTERSECT[t.data, t1.data] from temp t temp1 t1 but it not work

2

Do you have relation ship between temp, temp1 through document key.

If no relation ship
SELECT ARRAY_INTERSECT(t.data, t1.data) FROM temp t UNNEST (SELECT RAW temp1 FROM temp1) AS t1;

3

Thanks this thing I was looking for and yes I don’t have any relation ship between temp and temp1

4

I have another one doubts how I will get _id from temp buckets if Array_intersect match in result I want some thing like this
[
{"_id":“123”},
{"_id":“345”},
etc.
]

5 
SELECT ARRAY {"_id":SUBSTR(v,5)} FOR v IN ARRAY_INTERSECT(t.data, t1.data) END 
FROM temp t 
UNNEST (SELECT RAW temp1 FROM temp1) AS t1;

You can use SPLIT(“test:123”,“:” )[1] also

6

Thanks for your reply, I resolved that issues with other query but I have problem is in both temp and temp1 buckets data array having 10000 values and documents almost more than 10000 and that array intersect takes time out issues

7

Increase timeout or do small batches giving WHERE META(temp).id LIKE “TEST:1%”, WHERE META(temp).id LIKE “TEST:2%”