Create JOIN on same bucket based on certain fields

SQL++
1

I have documents (PLAYS) of the following form that I need to get:

{
  "id": "play1",
  "ownerID": "owner1",
  "orgID": "20071513",
  "type": "play"
}

{
  "id": "play2",
  "ownerID": "owner2",
  "orgID": "20071513",
  "type": "play"
}

And also documents like this in the same bucket:

{
  "teamID": "1234",
  "playID": "play1",
  "type": "mapping"
}

{
  "teamID": "1234",
  "playID": "play2"
  "type": "mapping"
}

And I basically need to query and get all plays for a given ownerID, as well as any plays for a given teamID (which mappings are defined in the second type of document). So some sort of a join on plays where mapping.playID=play.id, and UNION that will plays where ownerID=(some provided owner). I’m unsure if I’m thinking about it totally wrong, if I have the right indexes, etc. I have created the following secondary indexes:

CREATE INDEX idx_play ON default (type) WHERE type = "play";
CREATE INDEX idx_mapping ON default (type) WHERE type = "mapping";

When I query, I want to provide the ownerID “owner1” and the teamID “1234”, so I should get back “play1” and “play2”, because I am the owner of play1, and play2 is associated with my teamID.

2

I got it to work, but for my PLAY keys I was doing something like PLAY:, so I had to store my reference to the playID including the prefix. My query looks like so:

SELECT plays.id FROM default as plays WHERE plays.type=“play” AND plays.ownerID=“owner1” UNION SELECT plays.id FROM default AS teamMap JOIN default as plays ON KEYS teamMap.playID WHERE teamMap.teamID=“1234”;

As far as I can tell, it’s impossible to JOIN on two non-key fields