title: Couchbase - JOIN an array within a document with another document in the same bucket
body: <p>I am struggling to perform join queries between two types of document in the same bucket in Couchbase.</p>
  
  <p>Details:
  Bucket Name: <code>lifext-stage</code></p>
  
  <p>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.</p>
  
  <p>First Doument has the <code>type</code> field set to <code>moment</code></p>
  
  <p>Second Document has the <code>type</code> field set to <code>user</code></p>
  
  <p>First Document Schema:</p>
  
  <pre><code>{
        "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"
      }
  </code></pre>
  
  <p>Second Document Schema:</p>
  
  <pre><code>{
        "assessments": [],
        "cohortId": "cohort-a855864e-12b9-4e34-bd1f-2a89e581de1b",
        "dashboardIntroSeen": false,
        "email": "pros@aviahealthinnovation.com",
        "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"
      }
  </code></pre>
  
  <p>As you can see the second document has a moments arrays which contains the <code>momentId</code> key. This <code>momentId</code> key maps to the <code>id</code> key in the first document.</p>
  
  <p>I want to join each moments item in the second document with the first document and return the result.</p>
  
  <p>Expected result for this example:</p>
  
  <pre><code>{
        "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"
      }
  </code></pre>
  
  <p>Here are some of the queries that I have tried but failed to get any results:</p>
  
  <pre><code>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"
  </code></pre>
  
  <p>The above query gives me following error:</p>
  
  <pre><code>[
    {
      "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\""
    }
  ]
  </code></pre>
  
  <p>Second query that I tried:</p>
  
  <pre><code>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"
  </code></pre>
  
  <p>I have been struggling with this for the past two days, any help in this regard will be highly appreciated. Thanks in advance :)</p>