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.
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;
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;
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.