Viewing Value in N1QL Even When the Value Not Exist

SQL++
1

Dear Team,

Actually this is the expanded from my previous topic that already solved.
n1ql join from 3 different type document

but right now the situation like this:
assuming i have this kind of document:

{“id”:“country1”,“name”:“United State”,“type”:“country”},
{“id”:“country2”,“name”:“Canada”,“type”:“country”}
{“id”:“country3”,“name”:“United Kingdom”,“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”:“member4”,“name”:“Megan”,“type”:“member”,“country”:“country3”}

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

for now the goals is to show the data like:
{ “country”:“United State”, “printed”:“YES”,“total”:2},
{ “country”:“United State”, “printed”:“NO”,“total”:1},
{ “country”:“Canada”, “printed”:“YES”,“total”:0}
{ “country”:“Canada”, “printed”:“NO”,“total”:0}
{ “country”:“United Kingdom”, “printed”:“YES”,“total”:1},
{ “country”:“United Kingdom”, “printed”:“NO”,“total”:0}

can we make the result like example above? so even if the country does not have any member, stil show the printed yes and no with zero value, and also like united kingdom that only have 1 member that printed yes, but the printed no still show with the value 0.
i’m using Couchbase Enterprise Edition 5.0.1 build 5003. please help

regards,
rizal

2 
INSERT INTO default VALUES("country1",{"id":"country1","name":"United State","type":"country"}),
                          ("country2",{"id":"country2","name":"Canada","type":"country"}),
                          ("country3",{"id":"country3","name":"United Kingdom","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"}),
                          ("member4",{"id":"member4","name":"Megan","type":"member","country":"country3"}),
                          ("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"}),
                          ("card4",{"id":"card4","printed":"YES","type":"card","member":"member4"});

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

SELECT t.name, t.printed, COUNT(t.type) AS total FROM (

    SELECT c.name, c1.printed, c1.type AS type
    FROM default AS c
    JOIN default AS m ON KEY m.country FOR c
    JOIN default c1 ON KEY c1.member FOR m
    WHERE c.type = "country" AND c1.type = "card" AND m.type = "member"

    UNION ALL

    SELECT c.name, cp.printed FROM default AS c 
    UNNEST [{"printed":"YES"}, {"printed":"NO"}] AS cp 
    WHERE c.type = "country"

    ) AS t
GROUP BY t.name, t.printed;
3

wow you really great friend. because of you i can learn more of the n1ql. thanks you very much

4

dear friend, just wonder, can we make the result to be like :

{ “country”:“United State”, “data”: [{“printed”:“YES”,“total”:2},{“printed”:“NO”,“total”:1}] },
{ “country”:“Canada”, “data”: [{“printed”:“YES”,“total”:0},{“printed”:“NO”,“total”:0}] },
{ “country”:“United Kingdom”, “data”: [{“printed”:“YES”,“total”:1},{“printed”:“NO”,“total”:0}] }

thank you,
regards,
Rizal

5 
SELECT d.name AS country, ARRAY_AGG({d.printed, d.total}) AS data FROM (
   SELECT t.name, t.printed, COUNT(t.type) AS total FROM (

    SELECT c.name, c1.printed, c1.type AS type
    FROM default AS c
    JOIN default AS m ON KEY m.country FOR c
    JOIN default c1 ON KEY c1.member FOR m
    WHERE c.type = "country" AND c1.type = "card" AND m.type = "member"

    UNION ALL

    SELECT c.name, cp.printed FROM default AS c 
    UNNEST [{"printed":"YES"}, {"printed":"NO"}] AS cp 
    WHERE c.type = "country"

    ) AS t
  GROUP BY t.name, t.printed ) AS d
GROUP BY d.name;
6

you really great man, i feels like the just ordinary sql syntax, but i just not familiar with n1ql. thank you very much again