title: how to create index and use join in N1QL and Couchbase
body: <p>I have three json like below in <strong>same default Bucket</strong></p>
  
  <p><strong>How Can I join this three json ??</strong></p>
  
  <p>first </p>
  
  <pre><code>"Country|1":{
    "_id": 1,
    "_type": "Country",
    "currency": "Afghani",
    "iso2": "AF",
    "name": "Afghanistan"
  }
  </code></pre>
  
  <p>second</p>
  
  <pre><code>"Company|a1091aa0-b548-11e6-957e-139be07f46df": 
    {
    "_id": "a1091aa0-b548-11e6-957e-139be07f46df",
    "_type": "Company",
    "companyTypes": [
      1
    ],
    "country": "Country|1",
  }
  </code></pre>
  
  <p>Third</p>
  
  <pre><code>"Campaign|1001":{
    "_id": 1001,
    "_type": "Campaign",
    "banners": [],
    "carriers": [
      "Telstra"
    ],
    "country": 1,
    "created": "2016-03-08T18:30:00.000Z",
  
   }
  </code></pre>
  
  <p>here <strong>key</strong> is "name|_id" which i have wright before each json.</p>
  
  <p>I have this type all json.
  I have try to join query like below by reference of this source <a href="https://stackoverflow.com/questions/37373860/couchbase-n1ql-join-query">Couchbase N1QL join query</a></p>
  
  <pre><code>SELECT d1.*,d2.* FROM 
  default d1 USE KEYS "Company|a1091aa0-b548-11e6-957e-139be07f46df"
  JOIN default d2 ON KEYS d1.country;
  </code></pre>
  
  <p>I get the desire result.</p>
  
  <p>But when I remove <strong>USE KEYS "Company|a1091aa0-b548-11e6-957e-139be07f46df"</strong> then run below query I get error for <strong>INDEX</strong></p>
  
  <pre><code>SELECT d1.*,d2.*
  FROM default d1 
  JOIN default d2 ON KEYS d1.country;
  </code></pre>
  
  <p>Also I try to create index like below </p>
  
  <pre><code>CREATE INDEX campaign_user_co ON `default` (TOSTRING(country)) 
  where _type="Company"
  </code></pre>
  
  <p>Even I am getting <strong>Index</strong> error.</p>
  
  <p>No index available on keyspace default that matches your query.</p>