26ai: the JSON type and JSON-relational duality views

Two big JSON features that 19c doesn't have:

• The JSON data type (21c and later): documents stored in a binary format (OSON) that's fast to search and update, instead of text in a CLOB.
• JSON-relational duality views (23ai and later): normal tables underneath, JSON documents on top. Apps read and write whole documents; the data lives in rows with keys and constraints. No more choosing between "relational" and "document" database.

The test case

Register the Lagos Lanterns (team 7) with two players, Tunde Bakare (701) and Kofi Mensah (702), by sending one JSON document, then read every team back as a document with its player count.

The tables underneath (copies, with keys):

Setup

begin
  for v in (select view_name from user_views where view_name = 'D112_TEAM_DV') loop
    execute immediate 'drop view d112_team_dv';
  end loop;
  for t in (select table_name from user_tables
            where  table_name in ('D112_PLAYERS', 'D112_TEAMS', 'D112_PLAYERS_B', 'D112_TEAMS_B')
            order  by case when table_name like '%PLAYERS%' then 1 else 2 end) loop
    execute immediate 'drop table ' || t.table_name || ' purge';
  end loop;
end;
/

Setup

create table d112_teams as select team_id, team_code, team_name from cr_teams;

Setup

alter table d112_teams add constraint d112_teams_pk primary key (team_id);

Setup

create table d112_players as select player_id, team_id, player_name from cr_players;

Setup

alter table d112_players add (constraint d112_players_pk primary key (player_id),
                              constraint d112_players_fk foreign key (team_id) references d112_teams);

Setup

create table d112_teams_b as select * from d112_teams;

Setup

create table d112_players_b as select * from d112_players;

Way 1 (26ai): a duality view

Setup

create json relational duality view d112_team_dv as
select json {'_id'     : t.team_id,
             'code'    : t.team_code,
             'name'    : t.team_name,
             'players' : [ select json {'playerId' : p.player_id, 'name' : p.player_name}
                           from   d112_players p with insert update delete
                           where  p.team_id = t.team_id ] }
from   d112_teams t with insert update delete;

Each document is a team with its players nested inside. WITH INSERT UPDATE DELETE says the document may change those tables.

Setup

insert into d112_team_dv (data) values ('
  {"_id": 7, "code": "LGS", "name": "Lagos Lanterns",
   "players": [ {"playerId": 701, "name": "Tunde Bakare"},
                {"playerId": 702, "name": "Kofi Mensah"} ]}');

One insert of one document. Oracle splits it into a row in D112_TEAMS and two rows in D112_PLAYERS, filling in each player's team_id from the parent.

What landed in the tables

26ai only

select t.team_code, p.player_id, p.player_name
from   d112_teams t join d112_players p on p.team_id = t.team_id
where  t.team_id = 7
order  by p.player_id;

The documents, read back

26ai only

select dv.data."_id".number() as team_id, dv.data.code.string() as code,
       json_value(dv.data, '$.players.size()' returning number) as players
from   d112_team_dv dv
order  by 1;

• A duality view's DATA column has the native JSON type, so plain dot notation returns JSON values: dv.data.code gives "TPA", quotes included. The item methods .string() and .number() turn them into SQL values. (Our first 26ai test run tripped over exactly this.)
• $.players.size() counts the array. Every team has 12 players except the newcomers: Lagos will need nine more before they can take the field (and two more for drinks).

Way 2 (19c): the same change as rows

Setup

insert into d112_teams_b (team_id, team_code, team_name) values (7, 'LGS', 'Lagos Lanterns');

Setup

insert into d112_players_b (player_id, team_id, player_name)
select 701, 7, 'Tunde Bakare' from dual union all
select 702, 7, 'Kofi Mensah'  from dual;

select t.team_code, p.player_id, p.player_name
from   d112_teams_b t join d112_players_b p on p.team_id = t.team_id
where  t.team_id = 7
order  by p.player_id;

select t.team_id, t.team_code as code, count(p.player_id) as players
from   d112_teams_b t left join d112_players_b p on p.team_id = t.team_id
group  by t.team_id, t.team_code
order  by 1;

On 19c the app has to know the tables and write each row itself (or you write a PL/SQL API that takes the document and does it). The duality view is that API, generated and kept in step with the tables for you.

The JSON type, briefly

create table scorecards (match_id number primary key, doc json);         -- 26ai (21c+)
insert into scorecards values (32, '{"match":32,"winner":"LDS"}');
select s.doc.winner.string() from scorecards s;                         -- typed dot notation
update scorecards set doc = json_transform(doc, set '$.mvp' = 'Harry Whitcombe') where match_id = 32;

JSON_TRANSFORM changes part of a document in place, without rewriting the whole thing.

19c and 26ai

The duality view and the JSON type need 26ai (the JSON type arrived in 21c). Way 2 runs on both.

Gotchas

• Every table in a duality view needs a primary key, and the nesting must follow keys.
• Concurrent edits are caught with an ETag in each document's metadata: update a stale copy and you get an error instead of silently overwriting someone else's change.
• Columns not in the view keep their values when a document is updated through it.