Querying Couchbase like Right join

Couchbase Server
1

Hello,

I would like to querying CB like right join.
My data is :

fiche: {
_id : fiche::99
label : “test”
cat : {
id :“cat::1111”,
display : “Category 1”
}

Category : {
_id: “cat::1111”,
display:“Category 1”
}

I want to retrieve all “fiche” attach to a “Category” like :

id : “category::1111”,
display : “Category 1”,
fiches:[
{
id : “fiche::99”,
label : “test”
}, {…}
]

or if no fiche is attach

id : “category::1111”,
display : “Category 1”,
fiches:

With “left join on keys” it doesn’t work when no “fiche” is attach to a category.

Any ideas?

2

Try Index Join

CREATE INDEX ix1 ON fiche(cat.id);
SELECT c.*,  f AS fiche FROM category AS c NEST fiche AS f ON KEY f.cat.id FOR c;
3

thank you but when nothing is attached to “category” there is no return.

I want to recover all the categories and if “fiche” are attached, recover the “fiche” in an “array” if not an empty “array”

a simple solution is to make two requests but I would like to do it with only one if possible.

4

SELECT c.*, f AS fiche FROM category AS c LEFT NEST fiche AS f ON KEY f.cat.id FOR c;

This gives no fiche instead empty array. If you want empty array try following

SELECT c.*, IFMISSING(f,) AS fiche FROM category AS c LEFT NEST fiche AS f ON KEY f.cat.id FOR c;

5

Thank you very much.
It works perfectly

6

The complete solution for my usecase :

SELECT c.*, IFMISSING(ARRAY {“id”: meta(l).id,“label”: l.label } FOR l IN f END,) as fiches
FROM category AS c
use keys “category::1”
LEFT NEST fiche AS f ON KEY fiche.category.id FOR c;