How to join two documents of different type of same bucket in Couchbase?

Question

I am struggling to perform join queries between two types of document in the same bucket in Couchbase.

IMPORTANT NOTE: Both documents are in the same bucket but both documents have different type field which can be used to identify them when querying.

First Document has the type field set to "task" Second Document has the type field set to "appInfo"

First Document Schema:

{
    "id": "03775cc5-6ff5-4b02-89cd-160609b9c370",
    "taskId": "03775cc5-6ff5-4b02-89cd-160609b9c370",
    "type": "task",
    "appId": "98b7dcbd-89b1-4d41-b951-28eec467218e",
    "mailBoxId": "456cf088-9e02-433a-aacd-dffc3fa24779",
    "status": "Completed",
    "versionId": "bafd99f8-5cac-45db-86ff-30a489a7b502"
},
{
    "id": "057b4cf8-9758-4e5c-9153-dade109828c0",
    "taskId": "057b4cf8-9758-4e5c-9153-dade109828c0",
    "type": "task",
    "appId": "98b7dcbd-89b1-4d41-b951-28eec467218e",
    "mailBoxId": "456cf088-9e02-433a-aacd-dffc3fa24779",
    "status": "Completed",
    "versionId": "754159a3-2b9f-40b1-a515-b9e2de97a1e6"
},
{
    "id": "03775cc5-6ff5-4b02-89cd-160609b9c370",
    "taskId": "03775cc5-6ff5-4b02-89cd-160609b9c370",
    "type": "task",
    "appId": "98b7dcbd-89b1-4d41-b951-28eec467218e",
    "mailBoxId": "456cf088-9e02-433a-aacd-dffc3fa24779",
    "status": "Assigned",
    "versionId": "bafd99f8-5cac-45db-86ff-30a489a7b502"
}

Second Document Schema:

{
    "appId": "98b7dcbd-89b1-4d41-b951-28eec467218e",
    "mailBoxName": "Data Steward",
    "type": "appInfo",
    "qcStatus": [ "Error", "Completed" ],
    "id": "575552ee-2a5b-4557-a458-23bd82a96668"
}

As you can see the second document has a qcStatus arrays which contains different types of status. This status maps to the status in the first document.

I want to join each qcStatus item in the second document with the first document status and return the result.

Expected result for this example: As only 2 documents matches with the qcStatus i.e. Completed so only those documents with status = "Submitted" will be returned.

As no document is present for qcStatus error so no documents will be returned for status error and as qcStatus is not having "Assigned" so no documents having status Assigned will be returned from the 1st document.

[
    {
        "id": "03775cc5-6ff5-4b02-89cd-160609b9c370",
        "taskId": "03775cc5-6ff5-4b02-89cd-160609b9c370",
        "type": "task",
        "appId": "98b7dcbd-89b1-4d41-b951-28eec467218e",
        "mailBoxId": "456cf088-9e02-433a-aacd-dffc3fa24779",
        "status": "Completed",
        "versionId": "bafd99f8-5cac-45db-86ff-30a489a7b502"
   },
   {
        "id": "057b4cf8-9758-4e5c-9153-dade109828c0",
        "taskId": "057b4cf8-9758-4e5c-9153-dade109828c0",
        "type": "task",
        "appId": "98b7dcbd-89b1-4d41-b951-28eec467218e",
        "mailBoxId": "456cf088-9e02-433a-aacd-dffc3fa24779",
        "status": "Completed",
        "versionId": "754159a3-2b9f-40b1-a515-b9e2de97a1e6"
   }
]

Below is the queries that I have tried but failed to get any results:

SELECT wf.appId, wf.taskId, wf.task.details.subject, 
wf.task.details.`from`.emailAddress.address as `from`, 
wf.task.details.conversationId, wf.task.details.importance,    
wf.task.details.receivedDateTime, wf.hasConversations, 
wf.mailBoxId, wf.id, wf.qcStatus, wf.status, wf.teamId, wf.type, wf.updatedBy, 
wf.updatedDateTime, wf.task.taskType, wf.attributes.* 
FROM workflow_update wf 
JOIN workflow_update wc ON wf.status = wc.qcStatus
WHERE wf.type = "task" AND wc.type = "appInfo"

The above query gives me following error:

[
    {
        "code": 4330,
        "msg": "No index available for ANSI join term wc"
    }
]

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

After few tries I was myself able to answer the question. I am writing down the query so that anyone with the same problem can find the solution :-

SELECT wf.appId, wf.taskId, wf.mailBoxId, wf.status, wf.type
FROM workflow_update wf JOIN workflow_update wc
ON wf.appId = wc.appId
WHERE wf.type = "task" 
AND wc.type = "appInfo" 
AND wf.appId = "98b7dcbd-89b1-4d41-b951-28eec467218e" 
AND wf.status IN wc.qcStatus;

This will give the expected result.