JOIN ON KEYS with keys from a different document (CB 4.0)

SQL++
1

Suppose I have two kinds of documents, A and B. From other posts, I know I can join A and B. The way I do this is with concatenated keys, because A and B are ottoman models, like this:

SELECT a.something, b.somethingElse
FROM bucket as a
JOIN bucket as b
ON KEYS (a.myRef._type || '|' || a.myRef.`$ref`);

This works great. Question is if I can do it the other way around. If I do this:

SELECT a.something, b.somethingElse
FROM bucket as b
JOIN bucket as a
ON KEYS (a.myRef._type || '|' || a.myRef.`$ref`);

I get the error message “Ambiguous reference to field a”.

This reference isn’t ambiguous. And we know the join is possible - is there any trick I’m missing? I cannot seem to use multiple FROM clauses, so I can’t move the declaration of a up any further in that second query. I’ve tried joining with a sub-query with no luck.

Where this becomes a big limitation is if I’m doing a 3-way or more join; it’s very undesirable to have to have the reference or the stored keys on a particular document. As long as it’s there I ought to be able to join either way, right?

I know 4.0 is now legacy, we are in the process of moving to 4.5, so if the answer is that this is fixed in 4.1 or 4.5, that would be a great answer.

2

As you suspected, the answer is that this is fixed in 4.1 and 4.5. For joining from b to a, the query syntax is a little different, because a contains the reference to b. The index is required.

CREATE INDEX idx ON mybucket( myRef._type || '|' || myRef.`$ref` );

SELECT a.something, b.somethingElse
FROM mybucket as b
JOIN mybucket as a
ON KEY (a.myRef._type || '|' || a.myRef.`$ref`) FOR b;
1 Like