Couchbase - JOIN an array within a document with another document in the same bucket

Question

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

Details: Bucket Name: lifext-stage

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 Doument has the type field set to moment

Second Document has the type field set to user

First Document Schema:

{
      "allCongrats": [
        {
          "congratulatory": "“The invariable mark of wisdom is to see the miraculous in the common.” -Ralph Waldo Emerson",
          "orderedId": 14
        }
      ],
      "congratulatory": null,
      "cueRecommendations": [
        {
          "iconName": "Waking_Up",
          "recommendation": "Waking Up"
        },
      ],
      "deliveryMethod": "audio",
      "description": "Complete a 60-second practice for unwinding stress by taking in the good",
      "duration": 60000,
      "effort": "med",
      "id": "moment-82435b7dca1eff75066b14a7c47ac046",
      "moduleId": "module-48f41a55ab24166c7ab1d1d8d825c776",
      "moduleType": "CULTIVATE_OPTIMISM",
      "orderedId": 1,
      "practice": "Gratitude",
      "textDuration": "60 sec",
      "title": "60 Seconds of Optimism",
      "type": "moment"
    }

Second Document Schema:

{
      "assessments": [],
      "cohortId": "cohort-a855864e-12b9-4e34-bd1f-2a89e581de1b",
      "dashboardIntroSeen": false,
      "email": "[email protected]",
      "firstAssessmentCompleted": false,
      "firstName": "Phil",
      "focusId": null,
      "id": "user-00259a727b52cf35595e175134f49584",
      "lastName": "Ros",
      "longestStreak": 0,
      "modules": [],
      "moments": [
        {
          "completedDates": null,
          "lastCompletedMomentDate": null,
          "lastCongratulatoryId": null,
          "moduleId": "module-48f41a55ab24166c7ab1d1d8d825c776",
          "momentId": "moment-82435b7dca1eff75066b14a7c47ac046",
          "orderedId": 0,
          "userMomentId": "userMoment-58445416-9878-45d9-8221-6d6f76bf0a42"
        }
      ],
      "organizationId": "organization-fd779338-1361-4789-8da8-1ea37b0a3906",
      "signupTs": "2017-11-29T19:49:25.613Z",
      "streak": 0,
      "tokenValidator": "d9dcf699c23bacd33d6d841c454447d375990c51daa109cd5ffa5ff29bc7e6b0b76a26825267efb6b6873533ae711f82",
      "type": "user"
    }

As you can see the second document has a moments arrays which contains the momentId key. This momentId key maps to the id key in the first document.

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

Expected result for this example:

{
      "allCongrats": [
        {
          "congratulatory": "“The invariable mark of wisdom is to see the miraculous in the common.” -Ralph Waldo Emerson",
          "orderedId": 14
        }
      ],
      "congratulatory": null,
      "cueRecommendations": [
        {
          "iconName": "Waking_Up",
          "recommendation": "Waking Up"
        },
      ],
      "deliveryMethod": "audio",
      "description": "Complete a 60-second practice for unwinding stress by taking in the good",
      "duration": 60000,
      "effort": "med",
      "id": "moment-82435b7dca1eff75066b14a7c47ac046",
      "moduleId": "module-48f41a55ab24166c7ab1d1d8d825c776",
      "moduleType": "CULTIVATE_OPTIMISM",
      "orderedId": 1,
      "practice": "Gratitude",
      "textDuration": "60 sec",
      "title": "60 Seconds of Optimism",
      "type": "moment",
      "completedDates": null,
      "lastCompletedMomentDate": null,
      "lastCongratulatoryId": null,
      "momentId": "moment-82435b7dca1eff75066b14a7c47ac046",
      "orderedId": 0,
      "userMomentId": "userMoment-58445416-9878-45d9-8221-6d6f76bf0a42"
    }

Here are some of the queries that I have tried but failed to get any results:

SELECT *
FROM (SELECT um
FROM `lifext-stage` a 
UNNEST a.moments as um
WHERE a.type="user") as u
INNER JOIN `lifext-stage` m
ON u.um.momentId = m.id
WHERE m.type="moment"

The above query gives me following error:

[
  {
    "code": 3000,
    "msg": "syntax error - at u",
    "query_from_user": "SELECT *\nFROM (SELECT um\nFROM `lifext-stage` a \nUNNEST a.moments as um\nWHERE a.type=\"user\") as u\nINNER JOIN `lifext-stage` m\nON u.um.momentId = m.id\nWHERE m.type=\"moment\""
  }
]

Second query that I tried:

SELECT g.*, ARRAY_AGG({"node":n, "data":d}) AS node_data
FROM `lifext-stage` AS g UNNEST g.moments AS n JOIN `lifext-stage` AS d ON n.momentId = d.id
WHERE g.type="user" AND d.type="moment"

I have been struggling with this for the past two days, any help in this regard will be highly appreciated. Thanks in advance :)

vsr vsr · Accepted Answer · 2018-10-25T16:03:42

Requires Couchbase Version 5.50

CREATE INDEX ix1 ON `lifext-stage` (id) WHERE type = "moment";

SELECT *
FROM `lifext-stage` AS u
UNNEST u.moments AS um
JOIN `lifext-stage` AS m ON um.momentId = m.id
WHERE m.type = "moment" AND u.type = "user";

The following blog has use case examples on ANSI JOIN.

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

Couchbase - JOIN an array within a document with another document in the same bucket

1 Answers