首页 > 解决方案 > Couchbase N1QL - 尝试在 couchbase 查询编辑器中的两个文档之间执行连接操作,但没有得到任何结果

问题描述

Couchbase - N1QL 查询我正在尝试在同一个存储桶中加入两个文档,并且必须执行 GroupBy“DepartmentName”并依靠“TerminalCheckinStatus”为“COMPLETED”的“TeamMemberID”

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

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

首先,我尝试对两个文档进行“JOIN”操作

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

但没有得到任何结果。两个文档在公共字段中都有“TeamMemberID”

在过去的两天里,我一直在为此苦苦挣扎,在这方面的任何帮助将不胜感激。提前致谢 :)

标签: joingroup-bycountcouchbasen1ql

解决方案


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/


推荐阅读