Join on array without couchbase document

Couchbase Server
1

I want to be able to attach data without having a couchbase document.

Let me explain, my data are as follow, a collection of “institution” :

{
_id : “Institution::1”
label : “Institution 1”
unites: [
{
id: “unite-1”,
label : “unite 1”
} ,
{
id : “unite-2”,
value : “unite 2”
}
]
}

The “unites” do not correspond to a couchbase document, it’s only a array with generated data.

Then we have a collection of “personnels”:

{
_id : “personnel::1”
label : “personnel 1”,
unites: [
{
id : “unite-1”,
value : “unite 1”
}
]

I would like to attach “personnels.unites.id” with “institutions.unites.id”, to get a list like :

institutions: {
label : “institution 1”
unites : [
{ id : “unite-1”,
label : “Unite1”,
personnels: [
{
id: “personnel::1”
label: “personnel 1”
}
]
},
{
id : “unite-2”
label : “unite 2”
personnels :
}

I do not know if it’s possible, without having a “unite” object like couchbase document. (Actually, it’s just object in array)

In Couchbase do the joins only take place on the couchbase document keys ?

thank you for you help

2 
INSERT INTO default VALUES("Institution::1", { "_id" : "Institution::1", "label" : "Institution 1", "unites": [ { "id": "unite-1", "label" : "unite 1" } , { "id" : "unite-2", "label" : "unite 2" }, { "id" : "unite-3", "label" : "unite 3" } ] });
INSERT INTO default VALUES("personnel::1", { "_id" : "personnel::1", "label" : "personnel 1", "unites": [ { "id" : "unite-1", "val" : "unite 1" },{ "id" : "unite-2", "val" : "unite 2" } ]});

SELECT   {i.label, "unites": ARRAY {iv.id, iv.label, "personnels" :
                                    ARRAY {p._id, p.label} FOR pv IN p.unites WHEN pv.id = iv.id END}
                             FOR iv IN i.unites END } AS institutions
FROM default AS i USE KEYS [ "Institution::1"] JOIN default p ON KEYS "personnel::1"


Without couchbase documents

SELECT   {i.label, "unites": ARRAY {iv.id, iv.label, "personnels" :
                                    ARRAY {p._id, p.label} FOR pv IN p.unites WHEN pv.id = iv.id END}
                             FOR iv IN i.unites END } AS institutions
LET i = { "_id" : "Institution::1", "label" : "Institution 1", "unites": [ { "id": "unite-1", "label" : "unite 1" } , { "id" : "unite-2", "label" : "unite 2" }, { "id" : "unite-3", "label" : "unite 3" } ] },
    p = { "_id" : "personnel::1", "label" : "personnel 1", "unites": [ { "id" : "unite-1", "val" : "unite 1" },{ "id" : "unite-2", "val" : "unite 2" } ]};

At present Joins required parent-child relation ship through document key.
If the data is small you can do independent queries and materialize into array and you can JOIN.

Next release you can join through document fields.

3

thank you for you help
it’s work :smiley: