How joins workΒΆ
Given two entities A and B bound to the dataset aliases a
and b in the expressions below:
{
"_id": "A",
"value": 1,
"values": [1, 2, 4, 5]
}
{
"_id": "B",
"value": 1,
"values": [1, 3, 4, 6]
}
There are four different kinds of joins:
One-to-one join:
["eq", "a.value", "b.value"]One-to-many:
["eq", "a.value", "b.values"]Many-to-one:
["eq", "a.values", "b.value"]Many-to-many:
["eq", "a.values", "b.values"]
Important
The rule for joins is very simple: if any of the values overlap, then the join succeeds.
All of the four joins given above succeed for the two entities because
they all have overlapping values, i.e. the values 1 and 4.
Join expressions that contain functional expressions work the same
way, e.g. ["eq", ["+", "a.value", 2], "b.values"] succeeds as 3
is a value shared by both.
If you need to do joins with composite keys then the tuples DTL function is useful. Using composite keys is almost always a better choice than having multiple individual joins as the former will have better precision.
Good to know
null values and deleted entities are not indexed, so they are not traversed by joins.
Note
It is only eq functions that reference a single dataset alias in both left and right arguments that are considered join functions.
There must be exactly one unique dataset alias reference in each eq argument.