Using Join for Where clause

SQL++
1

Hi all,
I have been working on a project where I have two different types of documents in the same bucket named X.
The first document type is zone with following JSON fields:(Document key: Zone_id)

{
id: ,
type:,
zoneName:,
}

The second document is rule which is associated with zone:(Document key:Rule_id)

{
id:,
name:,
zoneId:(to associate it with a particular zone)
}

I want to get the rules for each zone while getting all zones.

So my output would look something like this:

[
       zone1:{

                     rules:[]
                 },
     zone2:{
                    rules:[]
                 }
]

Any idea on how I can do that?

2 
CREATE INDEX ix1 ON default(zoneName) WHERE type ="zone";
CREATE INDEX ix2 ON default("Zone_"||zoneId) ;

SELECT  RAW {zone.zoneName: rules}
FROM default AS zone
NEST default AS rules ON KEY "Zone_"||rules.zoneId FOR zone
WHERE zone.type = "zone";

SELECT  zone.zoneName, ARRAY_AGG(rules) AS rules
FROM default AS zone
JOIN default AS rules ON KEY rules.zoneId FOR zone
WHERE zone.type = "zone"
GROUP BY zone.zoneName;

Also use ANI JOINs in 5.50

3

Hi @vsr1, tried with the queries you have posted. However, both of them are returning me an empty array when I have records in my bucket.

This is the output I’m getting:
{
“results”: []
}

Am I missing something?
I have Couchbase 5.1.1 on my cloud.

4

I tried the same with left outer join to check whether its returning me any zone

SELECT zone.zoneName, ARRAY_AGG(rules) AS rules
FROM default AS zone
LEFT OUTER JOIN default as rules ON KEY rules.zoneId For zone
WHERE zone.type = “zone”
GROUP BY zone.zoneName;

Im getting the foll output:-
[
{
“rules”: null,
“zoneName”: “14_Sept_Zone”
},
{
“rules”: null,
“zoneName”: “chan”
},
{
“rules”: null,
“zoneName”: “WelcomeBanner”
},
{
“rules”: null,
“zoneName”: “City Cool Banner”
},
{
“rules”: null,
“zoneName”: “Hello”
},
{
“rules”: null,
“zoneName”: “hello1”
}
]

Its unable to retrieve the rules though

5

Post sample documents with corresponding document keys. It looks like you don’t have relation.
Is META(zone).id is same as rules.ZoneId? If not you get above results.

6

My document key for zone is like “Zone_1234”
Here are a few sample documents :

Zone_1234:
{
“id”: “1234”,
“zoneName”: “WelcomeBanner”,
“registrationId”: “zone1”,
“description”: “zone”,
“type”:“zone”
}

Rule_ZsOwNdShBGgGmyeJ:
{
“id”: “ZsOwNdShBGgGmyeJ”,
“channelTenantId”: “0oLaKIYx”,
“orgId”: “abcd1234”,
“priority”: 1,
“name”: “RuleAbc”,
“zoneId”: “1234”,
“type”:“rule”
}

7

Updated post 2. Try query and index on post to 2

8

Worked wonders. Thank you :slight_smile: