Couchbase中如何基于文档ID关联两类文档并查询指定员工数据？

现有文档信息 #

两个Couchbase文档如下：

// DOCUMENT-1
documentId = "userInfo::INE12400425"
{
  "type": "userInfo",
  "userName": "Sweta Sharma",
  "employeeNumber": "INE12400425",
  "id": "1adb05db-6e3f-432f-96d2-b6ed37836de7"
}

// DOCUMENT-2
documentId = "userAppInfo::0b62d0e9-3845-43e9-be43-ae69fcc0a2ce"
{
  "appId": "f6e9f71c-d1b6-45c1-a2f3-e5b3a668000c",
  "type": "userAppInfo",
  "persona": "Lead",
  "role": [
    "Ops",
    "QA"
  ],
  "userId": "userInfo::INE12400425",
  "preferred": true,
  "preferredRole": "Ops",
  "id": "0b62d0e9-3845-43e9-be43-ae69fcc0a2ce"
}

需求 #

通过员工编号INE12400425获取userInfo类型文档的documentId，关联userAppInfo类型文档，合并指定字段得到目标结果。

原查询错误分析 #

1. 第一条JOIN查询：关联条件uai.userId = documentId写法错误，需明确指定为META(wf).id来获取userInfo文档的documentId；且仅返回了documentId，未选择需要的合并字段。
2. 第二条子查询方式：子查询仅返回userInfo的documentId，外层查询直接在子查询结果中匹配type="userAppInfo"，但子查询结果无该字段，无法关联到userAppInfo文档。

正确查询语句 #

方式一：使用JOIN关联（推荐，高效清晰） #

SELECT
  uai.appId,
  uai.persona,
  uai.role,
  uai.userId,
  uai.preferred,
  uai.preferredRole,
  wf.userName,
  wf.employeeNumber
FROM `intelligent-workflow` wf
JOIN `intelligent-workflow` uai ON uai.userId = META(wf).id
WHERE wf.type = "userInfo"
  AND uai.type = "userAppInfo"
  AND wf.employeeNumber = "INE12400425";

方式二：子查询关联 #

SELECT
  uai.appId,
  uai.persona,
  uai.role,
  uai.userId,
  uai.preferred,
  uai.preferredRole,
  userInfo.userName,
  userInfo.employeeNumber
FROM `intelligent-workflow` uai
JOIN (
  SELECT META().id AS docId, userName, employeeNumber
  FROM `intelligent-workflow`
  WHERE type = "userInfo" AND employeeNumber = "INE12400425"
) userInfo ON uai.userId = userInfo.docId
WHERE uai.type = "userAppInfo";

以上两种查询均可返回你期望的合并结果，直接选择需要的字段即可避免多余数据。

内容的提问来源于stack exchange，提问作者Sweta Sharma