Having JSON support in a relational database means that the join operator is available not only for joining relational data or JSON data, but also for the mixed case: joining JSON and relational data. This opens up a whole new world of data modeling and query execution.
Running Example
This running example creates three tables, “demo”, “city” and “city_rel” and a sample data set in each table. The tables “city” and “city_rel” contain the same data set, once in JSON format, and once in relational format.
DROP TABLE demo;
CREATE TABLE demo
(
id NUMBER,
person CLOB
CONSTRAINT person_ensure_json
CHECK (person IS JSON (STRICT WITH UNIQUE KEYS)));
INSERT INTO demo VALUES
( 1, '{ "name": "Bob", "city": "SF"}' );
INSERT INTO demo VALUES
( 2, '{ "name": "Jake", "city": "PA"}' );
INSERT INTO demo VALUES
( 3, '{ "name": "Alice", "city": "NYC"}' );
INSERT INTO demo VALUES
( 4, '{ "name": "Jenn", "city": {"name": "Tokyo"}}' );
INSERT INTO demo VALUES
( 5, '{ "name": "Jenn", "city": ["Tokyo"]}' );
INSERT INTO demo VALUES
( 6, '{ "name": "Jenn", "city": 66}' );
DROP TABLE city;
CREATE TABLE city
(
id NUMBER,
city CLOB
CONSTRAINT city_ensure_json
CHECK (city IS JSON (STRICT WITH UNIQUE KEYS)));
INSERT INTO city VALUES
( 101, '{"city": "SF", "state": "CA",
"country": "US"}' );
INSERT INTO city VALUES
( 102, '{"city": "PA", "state": "CA",
"country": "US"}' );
INSERT INTO city VALUES
( 103, '{"city": "NYC", "state": "NY",
"country": "US"}' );
INSERT INTO city VALUES
( 104, '{"city": {"name": "Tokyo"}, "state": null,
"country": "Japan"}' );
INSERT INTO city VALUES
( 105, '{"city": ["Tokyo"], "state": null,
"country": "Japan"}' );
INSERT INTO city VALUE
( 106, '{"city": 66, "state": null,
"country": "World"}' );
DROP TABLE city_rel;
CREATE TABLE city_rel
(
id NUMBER,
city VARCHAR(255),
state VARCHAR(255),
country VARCHAR(255));
INSERT INTO city_rel VALUES
( 1001, 'SF', 'CA', 'US' );
INSERT INTO city_rel VALUES
( 1002, 'PA', 'CA', 'US' );
INSERT INTO city_rel VALUES
( 1003, 'NYC', 'NY', 'US' );
INSERT INTO city_rel VALUES
( 1004, '{"name": "Tokyo"}', NULL, 'World' );
INSERT INTO city_rel VALUES
( 1005, '["Tokyo"]', NULL, 'World' );
INSERT INTO city_rel VALUES
( 1006, '66', NULL, 'World' );
JSON Join
The following SQL statement is a simple join between JSON structures on the property “city”:
SELECT * FROM demo d, city c WHERE d.person.city = c.city.city;
This SQL statement projects in addition to joining JSON structures:
SELECT d.person, c.city FROM demo d, city c WHERE d.person.city = c.city.city;
The following SQL statement extends the projection:
SELECT d.id, d.person.name, d.person.city, c.id, c.city.state, c.city.country FROM demo d, city c WHERE d.person.city = c.city.city;
JSON – Relational Join
This SQL statement shows the join between JSON and relational data, combined with a projection:
SELECT d.id, d.person.name, d.person.city, c_r.id, c_r.city, c_r.state, c_r.country FROM demo d, city_rel c_r WHERE d.person.city = c_r.city;
Significance of Pure and Mixed JSON Joins
As shown, the join operator is applied easily within JSON tables and across JSON and relational tables. When using Oracle 12c there is no restriction anymore when it comes to the join operator in conjunction of JSON documents.
Pure JSON joins are possible in context of Oracle 12c. This means that developers have a choice to model all data in a pure document form (trying to avoid the need for joins by creating sub-collections – which is almost impossible without denormalization), or to consciously model documents in such a way that the document nature is applied where applicable without having to necessarily de-normalize as the join operator is available.
The mixed case between JSON and relational tables goes a lot further as now data can be modeled according to its nature (not all data is exclusively document-oriented or relational) and its access path requirements without compromising either way.
In addition, the mixed case supports the situation where data is already present in the database in relational form and new data is added in JSON form. This means that even if data is available in relational form, additional data does not have to be in relational form, and the most appropriate representation can be chosen (and no separate document-oriented database has to be deployed, btw).
Go 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 