N1ql for join 3 different type of document

SQL++
,
1

Dear All,

Need help for this kind of example.
suppose i have documeny like this:

{“id”:“country1”,“name”:“United State”,“type”:“country”},
{“id”:“country2”,“name”:“Canada”,“type”:“country”}

{“id”:“member1”,“name”:“Mike”,“type”:“member”,“country”:“country1”},
{“id”:“member2”,“name”:“Susan”,“type”:“member”,“country”:“country1”},
{“id”:“member3”,“name”:“Brian”,“type”:“member”,“country”:“country1”}

{“id”:“card1”,“printed”:“YES”,“type”:“card”,“member”:“member1”},
{“id”:“card2”,“printed”:“YES”,“type”:“card”,“member”:“member2”},
{“id”:“card3”,“printed”:“NO”,“type”:“card”,“member”:“member3”}

the goals is, i want to have the total result like:
{ “country”:“United State”, “printed”:“YES”,“total”:2},
{ “country”:“United State”, “printed”:“NO”,“total”:1},
{ “country”:“Canada”, “printed”:"",“total”:0}

i already make index for each of the id and type and i still want to show canada even that it does not have any data.
please help how the query should be. Thanks

Regards,
Rizal

2 
INSERT INTO default VALUES("country1",{"id":"country1","name":"United State","type":"country"}),
                          ("country2",{"id":"country2","name":"Canada","type":"country"}),
                          ("member1",{"id":"member1","name":"Mike","type":"member","country":"country1"}),
                          ("member2",{"id":"member2","name":"Susan","type":"member","country":"country1"}),
                          ("member3",{"id":"member3","name":"Brian","type":"member","country":"country1"}),
                          ("card1",{"id":"card1","printed":"YES","type":"card","member":"member1"}),
                          ("card2",{"id":"card2","printed":"YES","type":"card","member":"member2"}),
                          ("card3",{"id":"card3","printed":"NO","type":"card","member":"member3"});

CREATE INDEX ix11 ON default(country);
CREATE INDEX ix12 ON default(member);

SELECT c.name, IFMISSING(c1.printed,"") AS printed, COUNT(c1.printed) AS total
FROM default AS c
LEFT JOIN default AS m ON KEY m.country FOR c
LEFT JOIN default c1 ON KEY c1.member FOR m
WHERE c.type = "country"
GROUP BY c.name, c1.printed;
3

dear friend, i try to run your code, but i got the following error:

[
{
“code”: 5001,
“msg”: “Panic: runtime error: invalid memory address or nil pointer dereference”
}
]

4

What is version of couchbase, May be try with latest. There should be stack trace in query.log

5

i’m using Enterprise Edition 5.0.1 build 5003, any clue?

6

There should be stack trace in query.log post that here

7

i try to modify your code into:
SELECT c.name, IFMISSING(c1.printed,"") AS printed, COUNT(c1.printed) AS total
FROM default AS c
LEFT JOIN default AS m ON KEY m.country FOR c
LEFT JOIN default c1 ON KEY c1.member FOR m
WHERE c.type = ‘country’
and m.type=‘member’
and c1.type=‘card’
GROUP BY c.name, c1.printed;

and i got the result:
[
{
“name”: “United State”,
“printed”: “YES”,
“total”: 2
},
{
“name”: “United State”,
“printed”: “NO”,
“total”: 1
}
]

at least now, how to canada show in the result? thanks

regards,
rizal

8

You are hitting MB-27148, need to use 5.1.0 or 5.1.1
For LEFT JOIN if you add predicate on right side null projected row will be discarded.

SELECT c.name, IFMISSING(c1.printed,"") AS printed, COUNT(c1.printed) AS total
FROM default AS c
LEFT JOIN default AS m ON KEY m.country FOR c
LEFT JOIN default c1 ON KEY c1.member FOR m
WHERE c.type = "country" AND (c1 IS MISSING OR c1.type = "card") AND (m IS MISSING OR m.type = "member")
GROUP BY c.name, c1.printed;
9

YESSS, wow your solution working well. thank you so much for your help