Join 2 documents via KEYS that are nested in a third document

SQL++
1

Hi,

I ran into a small problem in regards to joining multiple keys on a single document.
I have 3 different documents, and here is how they are structured:

KEY: c_1234
{
"_id": “c_1234”,
“data”: { … }
}

KEY: b_1234
{
"_id": “b_1234”,
“data”: { … }
}

KEY: a_1234
{
"_id": “a_1234”,
“friend_id_1”: “c_1234”,
“friend_id_2”: “b_1234”,
“data”: { … }
}

I would like to eventually return both the data from key a_1234, and the data from the two nested keys (b_1234, c_1234).
I have tried multiple JOIN and SELECTS but I am afraid I was not able to reach a solution.

I am using couchbase version: 4.0.0-4051 Community Edition (build-4051)

What would be the most performant way to achieve this?

Much appreciated!

2

This query should get you what you want:

select * from test2 as a join test2 as b on keys [a.friend_id_2] join test2 as c on keys [a.friend_id_1]