CODEWITHSUNDEEP
node.jscouchbasesql++
krishn Patel
krishn Patel

Reputation: 2599

how to create index and use join in N1QL and Couchbase

I have three json like below in same default Bucket

How Can I join this three json ??

first 

"Country|1":{
  "_id": 1,
  "_type": "Country",
  "currency": "Afghani",
  "iso2": "AF",
  "name": "Afghanistan"
}

second

"Company|a1091aa0-b548-11e6-957e-139be07f46df": 
  {
  "_id": "a1091aa0-b548-11e6-957e-139be07f46df",
  "_type": "Company",
  "companyTypes": [
    1
  ],
  "country": "Country|1",
}

Third

"Campaign|1001":{
  "_id": 1001,
  "_type": "Campaign",
  "banners": [],
  "carriers": [
    "Telstra"
  ],
  "country": 1,
  "created": "2016-03-08T18:30:00.000Z",

 }

here key is "name|_id" which i have wright before each json.

I have this type all json. I have try to join query like below by reference of this source Couchbase N1QL join query

SELECT d1.*,d2.* FROM 
default d1 USE KEYS "Company|a1091aa0-b548-11e6-957e-139be07f46df"
JOIN default d2 ON KEYS d1.country;

I get the desire result.

But when I remove USE KEYS "Company|a1091aa0-b548-11e6-957e-139be07f46df" then run below query I get error for INDEX

SELECT d1.*,d2.*
FROM default d1 
JOIN default d2 ON KEYS d1.country;

Also I try to create index like below 

CREATE INDEX campaign_user_co ON `default` (TOSTRING(country)) 
where _type="Company"

Even I am getting Index error.

No index available on keyspace default that matches your query.

Upvotes: 1

Views: 2485

Answers (1)

geraldss
geraldss

Reputation: 2445

Try adding a primary index.

CREATE PRIMARY INDEX ON default;

Substitute your keyspace name for default.

Upvotes: 9

Related Questions