My understanding is that at present, n1ql can only join on document IDs/keys, and cannot join across different document fields. I.e. there appears to be no current formulation to make a query work that would join across two properties in two different documents, neither of which is the actual document key.
Since one of the real values of n1ql is bringing a SQL-like language to document query (this is one of the things that lured me to it over alternatives such as mongo) - these kinds of joins in my opinion would be a killer feature for n1ql.
Is this planned on the product roadmap, or when might users expect to get it?
3 Likes
Hi @moxious, yes this is in the plans - we are very much aware this is something that is needed. I unfortunately don’t have a timeline to share at this point.
There is one important improvement we are shipping in the next few weeks on JOINs in version 4.5. I am not sure if you are aware but it basically improves performance of our joins. It is called indexed join. With this in the picture, I am curious: would you be able to design your documents to use doc keys on one side of the joins?
This “ON KEY” syntax looks pretty close to what I’m looking for (which would be just a simple primary/foreign key join on a SQL system). What is beer.brewery_id? What kind of data type is it and where did that value come from? Is it the document ID for the corresponding brewery?
Since we’re using ottoman, we would need to find a way to get those document IDs, but then if I understand you correctly, if we had the document ID we could make it a property in the foreign object and then join that way, is that correct?
Normally my foreign keys would be synthetic key values (like UUIDs) which are only there to facilitate the join. Since they don’t have any real meaning/value, it doesn’t seem like a problem to substitute actual document keys for those UUIDs, if I understand you correctly.
Are these document keys guaranteed to always stay the same? If I modify the underlying document or in any other exotic situation, would that document key change? If so, it would make them probably unsuitable for foreign keys, since a bunch of foreign models would have to cascade change whenever their reference target’s document key changed.
Document keys are immutable.
Both the simple JOIN … ON KEYS …
and the more nuanced JOIN … ON KEY … FOR …
require one primary key and one foreign key. The only difference is that the first form requires that the left hand term provide a foreign key to the right hand term, while second form requires that the right hand term provide a foreign key to (one of) the preceding left hand term. That is the only difference.
I would recommend that you model your data first (including external keys / ids), try out your JOINs using the Query Workbench or cbq shell, and then continue with your app and Ottoman.
Gerald
For posterity – the answer in this thread works quite nicely. It also works in combination with Ottoman model references, which in the DB store enough information to concatenate / infer the right document key. So it’s possible (if you’re storing ottoman references) to use the technique above to join ottoman models together through those references.
Working great so far, thanks for the help.
1 Like
