how to join documents of 2 different types on the basis of document id in Couchbase

Question

I have 2 Couchbase document as mentioned below, I am trying to perform a join operation but I am failing to do. Please have a look at the below problem and any help will be very much appreciated.

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"
}

I have employeeNumber as "INE12400425" so I am trying to get the documentId from the 1st document of type "userInfo" and on the basis of documentId I am trying to fetch the details from the 2nd document of type "userAppInfo".

Below are some queries which I have tried :-

 select META().id as documentId from `intelligent-workflow` wf join 
`intelligent-workflow` uai on uai.userId = documentId where wf.type="userInfo" 
 and uai.type="userAppInfo" and wf.employeeNumber="INE12400425"


 select * from (SELECT Meta().id as documentId from `intelligent-workflow` 
 where type="userInfo" and employeeNumber="INE12400425") dt 
 where type="userAppInfo" and userId=documentId;

Expected Result ;-

{
    "appId": "f6e9f71c-d1b6-45c1-a2f3-e5b3a668000c",
    "persona": "Lead",
    "role": ["Ops","QA"],
    "userId": "userInfo::INE12400425",
    "preferred": true,
    "preferredRole": "Ops",
    "userName": "Sweta Sharma",
    "employeeNumber": "INE12400425"
 }

None of the above queries are working, any leads will be very helpful

Answer 1

The relation is employeeNumber and userId so JION condition on these fields must be part of ON clause or both can be constant as you providing.

CREATE INDEX ix1 ON `intelligent-workflow`(employeeNumber, userName) WHERE type = "userInfo";
CREATE INDEX ix2 ON `intelligent-workflow`( userId, appId, persona, `role`, preferred, preferredRole) WHERE type = "userAppInfo";

Use one of the following

WITH ui AS ((SELECT wf.userName, wf.employeeNumber
            FROM `intelligent-workflow` AS wf
            WHERE wf.type = "userInfo"  AND wf.employeeNumber = "INE12400425")[0])
SELECT uai.appId, uai.persona, uai.`role`, uai.userId, uai.preferred, uai.preferredRole, ui.*
FROM `intelligent-workflow` AS uai
WHERE uai.type = "userAppInfo"
      AND uai.userId = "userInfo::"|| "INE12400425";

OR

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

OR

NOTE: Your query used projection alias in ON clause (which is not right(projections not available in same query block except in ORDER BY) , replace actual expression like below)

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