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 :)

It looks like your join uses TeamMemberID from a and the document ID from b. If you want to join TeamMemberID to TeamMember ID, see @vsr's answer below. However, if you are trying to join to the document key, please put that information by your sample documents — Matthew Groves, Aug 04 '21 at 20:41

score 1 · Answer 1 · answered Aug 04 '21 at 13:10

CREATE INDEX ix1 ON `docstore` (TeamMemberID, DepartmentName) WHERE type = "TeamMember";
CREATE INDEX ix2 ON `docstore` (TeamMemberID, TerminalCheckinStatus) WHERE type = "TeamMemberStatus";

SELECT COUNT(b.TeamMemberID) AS count, a.DepartmentName
FROM `docstore` AS a
LEFT JOIN `docstore` AS b 
      ON a.TeamMemberID = b.TeamMemberID 
         AND b.type = "TeamMemberStatus" 
         AND b.TerminalCheckinStatus = "COMPLETED"
WHERE a.type = "TeamMember" AND a.TeamMemberID IS NOT NULL
GROUP BY a.DepartmentName;

https://blog.couchbase.com/ansi-join-support-n1ql/

score 0 · Accepted Answer · answered Aug 05 '21 at 10:48

0

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 ;

answered Aug 05 '21 at 10:48

Maulik Sakhida

471
4
15

1

https://blog.couchbase.com/in-list-handling-improvements-in-couchbase-server-6-5/ Subquery handling and potential query rewrite – vsr Aug 05 '21 at 12:39
@vsr Any tool or tutorial available for converting N1QL query to QueryBuilder form in JAVA? – Maulik Sakhida Aug 05 '21 at 13:36

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

2 Answers2