Restrictions can be added to a Cartesian Product and this is briefly discussed in this blog. It demonstrates the power of joins in context of JSON documents.
Example Data Set
As always, the sample data sets that are being used for queries in this blog are introduced first.
select {*} from jer
results in
{"a":1,"b":20,"c":true,"d":{"x":"y"}} {"a":2,"b":21,"c":true,"d":{"x":[null,5]}}
and
select {*} from tom
results in
{"a":3,"b":20,"c":false,"d":{"x":"y"}} {"a":4,"b":21,"c":false,"d":{"x":{"p":null,"q":5}}}
This data set is used in the following to introduce restrictions in context of a Cartesian Product.
Join and Join Criteria
The following demonstrates a join where the join criteria (restriction) is based on a scalar type.
select {*}
from jer as j, tom as t
where j.b = t.b
results in
{"j":{"a":1,"b":20,"c":true,"d":{"x":"y"}}, "t":{"a":3,"b":20,"c":false,"d":{"x":"y"}}} {"j":{"a":2,"b":21,"c":true,"d":{"x":[null,5]}}, "t":{"a":4,"b":21,"c":false,"d":{"x":{"p":null,"q":5}}}}
Remember, that the result documents from each of the collections are disambiguated by adding the root property âjâ and âtâ (aka, the correlation specifications).
A join can be empty if the join criteria do not derive to a result, as shown in the following.
select {*}
from jer as j, tom as t
where j.a = t.a
does not return a result.
Projection can be applied as well.
select {t.b}
from jer as j, tom as t
where j.b = t.b
results in
{"t":{"b":20}} {"t":{"b":21}}
Using an AS clause in the projection allows to reshape the result.
select {t.b as tb}
from jer as j, tom as t
where j.b = t.b
results in
{"tb":20} {"tb":21}
Join criteria can be defined not only on top level scalar properties, but on any JSON structure on any level. The following two queries illustrate this.
select {*}
from jer as j, tom as t
where j.d.x.[1] = t.d.x.q
results in
{"j":{"a":2,"b":21,"c":true,"d":{"x":[null,5]}}, "t":{"a":4,"b":21,"c":false,"d":{"x":{"p":null,"q":5}}}}
and
select {*}
from jer as j, tom as t
where j.d = t.d
results in
{"j":{"a":1,"b":20,"c":true,"d":{"x":"y"}}, "t":{"a":3,"b":20,"c":false,"d":{"x":"y"}}}
Of course, equality is not the only possible operator for join criteria.
select {*}
from jer as j, tom as t
where j.a < t.a
results in
{"j":{"a":1,"b":20,"c":true,"d":{"x":"y"}}, "t":{"a":3,"b":20,"c":false,"d":{"x":"y"}}} {"j":{"a":1,"b":20,"c":true,"d":{"x":"y"}}, "t":{"a":4,"b":21,"c":false,"d":{"x":{"p":null,"q":5}}}} {"j":{"a":2,"b":21,"c":true,"d":{"x":[null,5]}}, "t":{"a":3,"b":20,"c":false,"d":{"x":"y"}}} {"j":{"a":2,"b":21,"c":true,"d":{"x":[null,5]}}, "t":{"a":4,"b":21,"c":false,"d":{"x":{"p":null,"q":5}}}}
and so does
select {*}
from jer as j, tom as t
where j.a <> t.a
Cartesian Product with Restriction
Cartesian products can be restricted with non-join criteria.
select {*}
from jer as j, tom as t
where j.c = true
or t.c = false
results in
{"j":{"a":1,"b":20,"c":true,"d":{"x":"y"}}, "t":{"a":3,"b":20,"c":false,"d":{"x":"y"}}} {"j":{"a":1,"b":20,"c":true,"d":{"x":"y"}}, "t":{"a":4,"b":21,"c":false,"d":{"x":{"p":null,"q":5}}}} {"j":{"a":2,"b":21,"c":true,"d":{"x":[null,5]}}, "t":{"a":3,"b":20,"c":false,"d":{"x":"y"}}} {"j":{"a":2,"b":21,"c":true,"d":{"x":[null,5]}}, "t":{"a":4,"b":21,"c":false,"d":{"x":{"p":null,"q":5}}}}
Join with Join and Non-Join Criteria
And a mix of join and non-join criteria is possible as well.
select {*}
from jer as j, tom as t
where j.d = t.d
and j.b = t.b
and (j.c = true or t.c = false)
results in
{"j":{"a":1,"b":20,"c":true,"d":{"x":"y"}},
"t":{"a":3,"b":20,"c":false,"d":{"x":"y"}}}
Summary
Joins are a powerful feature of JSON SQL as demonstrated in this blog as it supports the combination of documents in different collections without having to foresee their combination when deciding on the document structures. Joins combine the power of JSON documents with the power of value-based correlation of documents.
Go [ JSON | Relational ] SQL!
Disclaimer
The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.
r e a l - p r o g r a m m e r 