N1QL Join Query

SQL++
1

Suppose i have bucket name " catalog " and i have two types of document which are
b2b_account_1234
{
“country”: “KK”,
“table_type”: “b2b_account”,
“uuid”: “1234”,
“b2b_reseller_uuid”: “2456”
}
and the other is
b2b_reseller_2456
{
“type”: “business”,
“step_count”: 5,
“table_type”: “b2b_reseller”,
“uuid”: “2456”,
“b2b_portal_uuid”: “b2b_portal_2d23833c-19af-41b7-84c0-ad96aa922fe0”,
“businesscontactName”: “hjjjji”,
“phone”: “koggl”
}

how to write join query to retrieve those documents data.

2

Pre 5.5

SELECT  a, r
FROM catalog AS a
JOIN catalog AS r ON KEYS  "b2b_reseller_" || a.b2b_reseller_uuid 
WHERE a.table_type = "b2b_account" AND r.table_type = "b2b_reseller";

In 5.5 you can use ANSI JOIN

CREATE INDEX ix1 ON catalog(uuid) WHERE table_type = "b2b_reseller";

SELECT  a, r
FROM catalog AS a
JOIN catalog AS r ON a.b2b_reseller_uuid = r.uuid
WHERE a.table_type = "b2b_account" AND r.table_type = "b2b_reseller";
3

Thanx .Those works perfectly fine but how can write join query by b2b_reseller_uuid in account or uuid on reseller.

4

You can add AND a.b2b_reseller_uuid = “2456” in WHERE clause of previous queries
OR

Pre 5.5

CREATE INDEX ix1 ON catalog(uuid) WHERE table_type = "b2b_reseller";
CREATE INDEX ix2 ON catalog("b2b_reseller_" || b2b_reseller_uuid) WHERE table_type = "b2b_account";

SELECT  a, r
FROM catalog AS r
JOIN catalog AS a ON KEY  "b2b_reseller_" ||a.b2b_reseller_uuid FOR r
WHERE a.table_type = "b2b_account" AND r.table_type = "b2b_reseller" AND r.uuid = "2456";

In 5.5 you can use ANSI JOIN

CREATE INDEX ix1 ON catalog(uuid) WHERE table_type = "b2b_reseller";
CREATE INDEX ix2 ON catalog(b2b_reseller_uuid) WHERE table_type = "b2b_account";

SELECT  a, r
FROM catalog AS r
JOIN catalog AS a ON a.b2b_reseller_uuid = r.uuid
WHERE a.table_type = "b2b_account" AND r.table_type = "b2b_reseller" AND r.uuid = "2456";