Couchbase N1QL - Trying to perform join operation between two documents in couchbase query editor but not getting any result

Question

Couchbase - N1QL query I'm trying to join two documents within same bucket and have to perform GroupBy "DepartmentName" and count on "TeamMemberID" whose "TerminalCheckinStatus" is "COMPLETED"

**First Type of Document**
docstore {
   DepartmentName: "Medical",
   TeamMemberID: "ABC123",
   type: "TeamMember"
}

**Second Type of Document**
docstore {
   TerminalCheckinStatus: "COMPLETED",
   TeamMemberID: "ABC123",
   type: "TeamMemberStatus"
}

First I'm trying to "JOIN" operation on two documents

select a.TeamMemberID, a.DepartmentName
FROM `docstore` a
JOIN `docstore` b
ON a.TeamMemberID = META(b).id;

But not getting any result. Both document have "TeamMemberID" in common field

I have been struggling with this for the past two days, any help in this regard will be highly appreciated. Thanks in advance :)

Answer 1

Alternatively, we can also write @vsr answer as with subqueries

SELECT COUNT(*) AS count, a.DepartmentName
FROM docstore a
WHERE a.TeamMemberID in
   (SELECT raw b.TeamMemberID from docstore b
   where b.type = "TeamMemberStatus" 
   and b.TerminalCheckinStatus = "COMPLETED" 
   and meta(b).id not like "_sync%")
and meta(a).id not like "_sync%"
Group by a.DepartmentName ;