はじめに
こちらの記事でMongoDB ShellからOracle Database 23c Freeにアクセスするための手順を書きました。
この記事では、その続きとして、Oracle Database 23cの新機能であるJSON Relational Duality Viewを作成し、作成したJSON Relational Duality ViewをMongoDB Shellからコレクションとして扱うための手順と、JSON Relational Duality Viewに対するドキュメント操作によってベースとなるリレーショナル表のデータがどのように変化するのかを検証しました。
なお、こちらの記事で使用してるサンプルは「Oracle Database 23c JSON-Relational Duality Developer's Guide」の「Car-Racing Duality Views Example」を使用しています。
1. JSON Relational Duality Viewのベースとなる表の作成
チーム情報を格納する表teamを作成します。
SQL> CREATE TABLE team
2 (team_id INTEGER GENERATED BY DEFAULT ON NULL AS IDENTITY,
3 name VARCHAR2(255) NOT NULL UNIQUE,
4 points INTEGER NOT NULL,
5 CONSTRAINT team_pk PRIMARY KEY(team_id));
Table created.
SQL>
ドライバー情報を格納する表driverを作成します。
SQL> CREATE TABLE driver
2 (driver_id INTEGER GENERATED BY DEFAULT ON NULL AS IDENTITY,
3 name VARCHAR2(255) NOT NULL UNIQUE,
4 points INTEGER NOT NULL,
5 team_id INTEGER,
6 CONSTRAINT driver_pk PRIMARY KEY(driver_id),
7 CONSTRAINT driver_fk FOREIGN KEY(team_id) REFERENCES team(team_id));
Table created.
SQL>
レース情報を格納する表raceを作成します。
SQL> CREATE TABLE race
2 (race_id INTEGER GENERATED BY DEFAULT ON NULL AS IDENTITY,
3 name VARCHAR2(255) NOT NULL UNIQUE,
4 laps INTEGER NOT NULL,
5 race_date DATE,
6 podium JSON,
7 CONSTRAINT race_pk PRIMARY KEY(race_id));
Table created.
SQL>
ドライバーとレースのマッピング情報を格納する表driver_race_mapを作成します。
SQL> CREATE TABLE driver_race_map
2 (driver_race_map_id INTEGER GENERATED BY DEFAULT ON NULL AS IDENTITY,
3 race_id INTEGER NOT NULL,
4 driver_id INTEGER NOT NULL,
5 position INTEGER,
6 CONSTRAINT driver_race_map_pk PRIMARY KEY(driver_race_map_id),
7 CONSTRAINT driver_race_map_fk1 FOREIGN KEY(race_id) REFERENCES race(race_id),
8 CONSTRAINT driver_race_map_fk2 FOREIGN KEY(driver_id) REFERENCES driver(driver_id));
Table created.
SQL>
driver_race_map表へのINSERT実行時にドライバーポイントとチームポイントを加算するトリガーdriver_race_map_triggerを作成します。
SQL> CREATE OR REPLACE TRIGGER driver_race_map_trigger
2 BEFORE INSERT ON driver_race_map
3 FOR EACH ROW
4 DECLARE
5 v_points INTEGER;
6 v_team_id INTEGER;
7 BEGIN
8 SELECT team_id INTO v_team_id FROM driver WHERE driver_id = :NEW.driver_id;
9
10 IF :NEW.position = 1 THEN
11 v_points := 25;
12 ELSIF :NEW.position = 2 THEN
13 v_points := 18;
14 ELSIF :NEW.position = 3 THEN
15 v_points := 15;
16 ELSIF :NEW.position = 4 THEN
17 v_points := 12;
18 ELSIF :NEW.position = 5 THEN
19 v_points := 10;
20 ELSIF :NEW.position = 6 THEN
21 v_points := 8;
22 ELSIF :NEW.position = 7 THEN
23 v_points := 6;
24 ELSIF :NEW.position = 8 THEN
25 v_points := 4;
26 ELSIF :NEW.position = 9 THEN
27 v_points := 2;
28 ELSIF :NEW.position = 10 THEN
29 v_points := 1;
30 ELSE
31 v_points := 0;
32 END IF;
33 UPDATE driver SET points = points + v_points
34 WHERE driver_id = :NEW.driver_id;
35 UPDATE team SET points = points + v_points
36 WHERE team_id = v_team_id;
37 END;
38 /
Trigger created.
SQL>
2. JSON Relational Duality Viewの作成
レース情報をJSON形式で提供するビューrace_dvを作成します。
SQL> CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW race_dv AS
2 SELECT JSON {'_id' IS r.race_id,
3 'name' IS r.name,
4 'laps' IS r.laps WITH NOUPDATE,
5 'date' IS r.race_date,
6 'podium' IS r.podium WITH NOCHECK,
7 'result' IS
8 [ SELECT JSON {'driverRaceMapId' IS drm.driver_race_map_id,
9 'position' IS drm.position,
10 UNNEST
11 (SELECT JSON {'driverId' IS d.driver_id,
12 'name' IS d.name}
13 FROM driver d WITH NOINSERT UPDATE NODELETE
14 WHERE d.driver_id = drm.driver_id)}
15 FROM driver_race_map drm WITH INSERT UPDATE DELETE
16 WHERE drm.race_id = r.race_id ]}
17 FROM race r WITH INSERT UPDATE DELETE;
View created.
SQL>
ドライバー情報をJSON形式で提供するビューdriver_dvを作成します。
SQL> CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW driver_dv AS
2 SELECT JSON {'_id' IS d.driver_id,
3 'name' IS d.name,
4 'points' IS d.points,
5 UNNEST
6 (SELECT JSON {'teamId' IS t.team_id,
7 'team' IS t.name WITH NOCHECK}
8 FROM team t WITH NOINSERT NOUPDATE NODELETE
9 WHERE t.team_id = d.team_id),
10 'race' IS
11 [ SELECT JSON {'driverRaceMapId' IS drm.driver_race_map_id,
12 UNNEST
13 (SELECT JSON {'raceId' IS r.race_id,
14 'name' IS r.name}
15 FROM race r WITH NOINSERT NOUPDATE NODELETE
16 WHERE r.race_id = drm.race_id),
17 'finalPosition' IS drm.position}
18 FROM driver_race_map drm WITH INSERT UPDATE NODELETE
19 WHERE drm.driver_id = d.driver_id ]}
20 FROM driver d WITH INSERT UPDATE DELETE;
View created.
SQL>
チーム情報をJSON形式で提供するビューteam_dvを作成します。
SQL> CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW team_dv AS
2 SELECT JSON {'_id' IS t.team_id,
3 'name' IS t.name,
4 'points' IS t.points,
5 'driver' IS
6 [ SELECT JSON {'driverId' IS d.driver_id,
7 'name' IS d.name,
8 'points' IS d.points WITH NOCHECK}
9 FROM driver d WITH INSERT UPDATE
10 WHERE d.team_id = t.team_id ]}
11 FROM team t WITH INSERT UPDATE DELETE;
View created.
SQL>
3. JSON Relational Duality Viewをコレクションとして操作するための準備
JSON Relational Duality Viewを Oracle Database API for MongoDBで使用するために、JSON Relational Duality ViewをベースにしたSODAコレクションを作成します。
SQL> DECLARE
2 col SODA_COLLECTION_T;
3 BEGIN
4 col := DBMS_SODA.CREATE_DUALV_COLLECTION('team_dv', 'TEAM_DV');
5 col := DBMS_SODA.CREATE_DUALV_COLLECTION('driver_dv', 'DRIVER_DV');
6 col := DBMS_SODA.CREATE_DUALV_COLLECTION('race_dv', 'RACE_DV');
7 END;
8 /
PL/SQL procedure successfully completed.
SQL>
以上で、MongoDB ShellでJSON Ralational Duality Viewをコレクションとして操作するための準備が整いました。
4. MongoDB ShellからOracle Database 23c Freeへの接続
MongoDB Shellからmongo_testユーザとしてOracle Database 23c Freeに接続します。
[oracle@oracle23c ~]$ mongosh --tlsAllowInvalidCertificates 'mongodb://mongo_test:MyPassword1!@localhost:27017/mongo_test?authMechanism=PLAIN&authSource=$external&ssl=true&retryWrites=false&loadBalanced=true'
Current Mongosh Log ID: 64622ff7a1919d2a55f3ec18
Connecting to: mongodb://<credentials>@localhost:27017/mongo_test?authMechanism=PLAIN&authSource=%24external&ssl=true&retryWrites=false&loadBalanced=true&serverSelectionTimeoutMS=2000&tlsAllowInvalidCertificates=true&appName=mongosh+1.8.2
Using MongoDB: 4.2.14
Using Mongosh: 1.8.2
For mongosh info see: https://docs.mongodb.com/mongodb-shell/
mongo_test>
コレクションの一覧を確認してみます。
mongo_test> show collections
driver_dv
race_dv
team_dv
mongo_test>
先ほど作成してSODAコレクションを作成した3つのJSON Relational Duality View（driver_dv、team_dv、race_dv）がコレクションとして表示されました。
find()メソッドを使用して、各コレクションの内容を確認してみます。
mongo_test> db.driver_dv.find();
mongo_test>
mongo_test> db.race_dv.find();
mongo_test>
mongo_test> db.team_dv.find();
mongo_test>
この時点では、どのコレクションにもデータはまだ入っていません。
5. JSON-Relational Duality Viewをコレクションとして操作する
insertOne()メソッドを使用して、コレクションteam_dvに２名のドライバー情報を含むドキュメントを追加してみます。
mongo_test> db.team_dv.insertOne(
... {
... "_id": 2,
... "name": "Mercedes",
... "points": 0,
... "driver": [
... {
... "driverId": 105,
... "name": "George Russell",
... "points": 0
... },
... {
... "driverId": 106,
... "name": "Lewis Hamilton",
... "points": 0
... }
... ]
... }
... );
{ acknowledged: true, insertedId: 2 }
mongo_test>
コレクションteam_dvの内容を確認してみます。
mongo_test> db.team_dv.find();
[
{
_id: 2,
name: 'Mercedes',
points: 0,
driver: [
{ driverId: 105, name: 'George Russell', points: 0 },
{ driverId: 106, name: 'Lewis Hamilton', points: 0 }
],
_metadata: {
etag: Binary(Buffer.from("536001f31a8718819aeef28ec20d8677", "hex"), 0),
asof: Binary(Buffer.from("0000000000d0952f", "hex"), 0)
}
}
]
mongo_test>
先ほど追加したドキュメントにメタデータ（etag、asof）が付加されて格納されていることが確認できました。
コレクションdriver_dvの内容を確認してみます。
mongo_test> db.driver_dv.find();
[
{
_id: 105,
name: 'George Russell',
points: 0,
teamId: 2,
team: 'Mercedes',
race: [],
_metadata: {
etag: Binary(Buffer.from("a8bb1825f6218ec0d300671173540597", "hex"), 0),
asof: Binary(Buffer.from("0000000000d3aa95", "hex"), 0)
}
},
{
_id: 106,
name: 'Lewis Hamilton',
points: 0,
teamId: 2,
team: 'Mercedes',
race: [],
_metadata: {
etag: Binary(Buffer.from("d3ff3213793e306204bb5e5060368e41", "hex"), 0),
asof: Binary(Buffer.from("0000000000d3aa95", "hex"), 0)
}
}
]
mongo_test>
コレクションteam_dvにドキュメントを追加することによって、ドキュメント内のドライバーの情報がコレクションdriver_dvに追加されることが確認できました。
SQL*Plusからmongo_testユーザとしてOracle Databse 23c Freeに接続し、Duality Viewのベースとなっているリレーショナル表の内容を確認してみます。
SQL> col name for a30
SQL> SELECT * FROM team;
TEAM_ID NAME POINTS
---------- ------------------------------ ----------
2 Mercedes 0
SQL> SELECT * FROM driver;
DRIVER_ID NAME POINTS TEAM_ID
---------- ------------------------------ ---------- ----------
105 George Russell 0 2
106 Lewis Hamilton 0 2
SQL> SELECT * FROM race;
no rows selected
SQL> SELECT * FROM driver_race_map;
no rows selected
SQL>
コレクションteam_dvにドキュメントを1つ追加したことによって、team表に1レコード、driver表に2レコード追加されたことが確認できました。
次に、insertMany(）メソッドを使用して、コレクションteam_dvに複数のドキュメントを追加してみます。
mongo_test> db.team_dv.insertMany(
... [
... {
... "_id": 301,
... "name": "Red Bull",
... "points": 0,
... "driver": [
... {
... "driverId": 101,
... "name": "Max Verstappen",
... "points": 0
... },
... {
... "driverId": 102,
... "name": "Sergio Perez",
... "points": 0
... }
... ]
... },
... {
... "_id": 302,
... "name": "Ferrari",
... "points": 0,
... "driver": [
... {
... "driverId": 103,
... "name": "Charles Leclerc",
... "points": 0
... },
... {
... "driverId": 104,
... "name": "Carlos Sainz Jr",
... "points": 0
... }
... ]
... }
... ]
... );
{ acknowledged: true, insertedIds: { '0': 301, '1': 302 } }
mongo_test>
コレクションteam_dvの内容を確認してみます。
mongo_test> db.team_dv.find();
[
{
_id: 2,
name: 'Mercedes',
points: 0,
driver: [
{ driverId: 105, name: 'George Russell', points: 0 },
{ driverId: 106, name: 'Lewis Hamilton', points: 0 }
],
_metadata: {
etag: Binary(Buffer.from("536001f31a8718819aeef28ec20d8677", "hex"), 0),
asof: Binary(Buffer.from("0000000000d3aa74", "hex"), 0)
}
},
{
_id: 301,
name: 'Red Bull',
points: 0,
driver: [
{ driverId: 101, name: 'Max Verstappen', points: 0 },
{ driverId: 102, name: 'Sergio Perez', points: 0 }
],
_metadata: {
etag: Binary(Buffer.from("9f0f855a39f4ae8fecd7e6a123855fd1", "hex"), 0),
asof: Binary(Buffer.from("0000000000d3aa74", "hex"), 0)
}
},
{
_id: 302,
name: 'Ferrari',
points: 0,
driver: [
{ driverId: 103, name: 'Charles Leclerc', points: 0 },
{ driverId: 104, name: 'Carlos Sainz Jr', points: 0 }
],
_metadata: {
etag: Binary(Buffer.from("7b62699fececff392143ba7a335e5e47", "hex"), 0),
asof: Binary(Buffer.from("0000000000d3aa74", "hex"), 0)
}
}
]
mongo_test>
insertMany()メソッドを使用して追加したドキュメントにメタデータが付与されて格納されていることが確認できました。
コレクションdriver_dvの内容を確認してみます。
mongo_test> db.driver_dv.find();
[
{
_id: 105,
name: 'George Russell',
points: 0,
teamId: 2,
team: 'Mercedes',
race: [],
_metadata: {
etag: Binary(Buffer.from("a8bb1825f6218ec0d300671173540597", "hex"), 0),
asof: Binary(Buffer.from("0000000000d3aa95", "hex"), 0)
}
},
{
_id: 106,
name: 'Lewis Hamilton',
points: 0,
teamId: 2,
team: 'Mercedes',
race: [],
_metadata: {
etag: Binary(Buffer.from("d3ff3213793e306204bb5e5060368e41", "hex"), 0),
asof: Binary(Buffer.from("0000000000d3aa95", "hex"), 0)
}
},
{
_id: 101,
name: 'Max Verstappen',
points: 0,
teamId: 301,
team: 'Red Bull',
race: [],
_metadata: {
etag: Binary(Buffer.from("f9d9815dff27879f61386cfd1622b065", "hex"), 0),
asof: Binary(Buffer.from("0000000000d3aa95", "hex"), 0)
}
},
{
_id: 102,
name: 'Sergio Perez',
points: 0,
teamId: 301,
team: 'Red Bull',
race: [],
_metadata: {
etag: Binary(Buffer.from("9865a29dee5f5754674a9b1d2ec58730", "hex"), 0),
asof: Binary(Buffer.from("0000000000d3aa95", "hex"), 0)
}
},
{
_id: 103,
name: 'Charles Leclerc',
points: 0,
teamId: 302,
team: 'Ferrari',
race: [],
_metadata: {
etag: Binary(Buffer.from("c47a91f57bf3cf45f0d8284240399a90", "hex"), 0),
asof: Binary(Buffer.from("0000000000d3aa95", "hex"), 0)
}
},
{
_id: 104,
name: 'Carlos Sainz Jr',
points: 0,
teamId: 302,
team: 'Ferrari',
race: [],
_metadata: {
etag: Binary(Buffer.from("363ec6bbf0fadd913b219482959da39d", "hex"), 0),
asof: Binary(Buffer.from("0000000000d3aa95", "hex"), 0)
}
}
]
mongo_test>
insertMany()メソッドを使用してコレクションteam_dvにドライバー情報を含むドキュメントを追加したことによって、ドキュメントに含まれるドライバーの情報がコレクションdriver_dvに追加されたことが確認できました。
Duality Viewのベースとなっているリレーショナル表の内容を確認してみます。
SQL> set linesize 200
SQL> col name for a30
SQL> SELECT * FROM team;
TEAM_ID NAME POINTS
---------- ------------------------------ ----------
2 Mercedes 0
301 Red Bull 0
302 Ferrari 0
SQL> SELECT * FROM driver;
DRIVER_ID NAME POINTS TEAM_ID
---------- ------------------------------ ---------- ----------
105 George Russell 0 2
106 Lewis Hamilton 0 2
101 Max Verstappen 0 301
102 Sergio Perez 0 301
103 Charles Leclerc 0 302
104 Carlos Sainz Jr 0 302
6 rows selected.
SQL> SELECT * FROM race;
no rows selected
SQL> SELECT * FROM driver_race_map;
no rows selected
SQL>
コレクションteam_dvにドキュメントを2つ追加したことによって、team表に2レコード、driver表に4レコード追加されたことがわかりました。
insertMany()メソッドを使用して、コレクションrace_dvに3つのドキュメントを追加します。
mongo_test> db.race_dv.insertMany(
... [
... {
... "_id": 201,
... "name": "Bahrain Grand Prix",
... "laps": 57,
... "date": "2022-03-20T00:00:00",
... "podium": {}
... },
... {
... "_id": 202,
... "name": "Saudi Arabian Grand Prix",
... "laps": 50,
... "date": "2022-03-27T00:00:00",
... "podium": {}
... },
... {
... "_id": 203,
... "name": "Australian Grand Prix",
... "laps": 58,
... "date": "2022-04-09T00:00:00",
... "podium": {}
... }
... ]
... );
{ acknowledged: true, insertedIds: { '0': 201, '1': 202, '2': 203 } }
mongo_test>
コレクションrace_dvの内容を確認します。
mongo_test> db.race_dv.find();
[
{
_id: 201,
name: 'Bahrain Grand Prix',
laps: 57,
date: ISODate("2022-03-20T00:00:00.000Z"),
podium: {},
result: [],
_metadata: {
etag: Binary(Buffer.from("2e8dc09543dd25dc7d588fb9734d962b", "hex"), 0),
asof: Binary(Buffer.from("0000000000d3aee7", "hex"), 0)
}
},
{
_id: 202,
name: 'Saudi Arabian Grand Prix',
laps: 50,
date: ISODate("2022-03-27T00:00:00.000Z"),
podium: {},
result: [],
_metadata: {
etag: Binary(Buffer.from("7e056a845212bfde19e0c0d0cd549ea0", "hex"), 0),
asof: Binary(Buffer.from("0000000000d3aee7", "hex"), 0)
}
},
{
_id: 203,
name: 'Australian Grand Prix',
laps: 58,
date: ISODate("2022-04-09T00:00:00.000Z"),
podium: {},
result: [],
_metadata: {
etag: Binary(Buffer.from("ea6e1194c012970ca07116ee1ef167e8", "hex"), 0),
asof: Binary(Buffer.from("0000000000d3aee7", "hex"), 0)
}
}
]
mongo_test>
insertMany()メソッドを使用して追加したドキュメントにメタデータが付与されて格納されていることが確認できました。
Duality Viewのベースとなっているリレーショナル表の内容を確認してみます。
SQL> col name for a30
SQL> SELECT * FROM team;
TEAM_ID NAME POINTS
---------- ------------------------------ ----------
2 Mercedes 0
301 Red Bull 0
302 Ferrari 0
SQL> SELECT * FROM driver;
DRIVER_ID NAME POINTS TEAM_ID
---------- ------------------------------ ---------- ----------
105 George Russell 0 2
106 Lewis Hamilton 0 2
101 Max Verstappen 0 301
102 Sergio Perez 0 301
103 Charles Leclerc 0 302
104 Carlos Sainz Jr 0 302
6 rows selected.
SQL> SELECT * FROM race;
RACE_ID NAME LAPS RACE_DATE PODIUM
---------- ------------------------------ ---------- --------- --------------------------------------------------------------------------------
201 Bahrain Grand Prix 57 20-MAR-22 {}
202 Saudi Arabian Grand Prix 50 27-MAR-22 {}
203 Australian Grand Prix 58 09-APR-22 {}
SQL> SELECT * FROM driver_race_map;
no rows selected
SQL>
コレクションrace_dvにドキュメントを3つ追加したことによって、race表に3レコード追加されたことがわかりました。
replaceOneメソッドを使用して、コレクションrace_dv内の_idの値が201のドキュメントを、result（レース結果）を含む内容に変更（置換）してみます。
mongo_test> db.race_dv.replaceOne(
... {_id:201},
... {
... "name": "Bahrain Grand Prix",
... "laps": 57,
... "date": "2022-03-20T00:00:00",
... "podium": {
... "winner": {
... "name": "Charles Leclerc",
... "time": "01:37:33.584"
... },
... "firstRunnerUp": {
... "name": "Carlos Sainz Jr",
... "time": "01:37:39.182"
... },
... "secondRunnerUp": {
... "name": "Lewis Hamilton",
... "time": "01:37:43.259"
... }
... },
... "result": [
... {
... "driverRaceMapId": 3,
... "position": 1,
... "driverId": 103,
... "name": "Charles Leclerc"
... },
... {
... "driverRaceMapId": 4,
... "position": 2,
... "driverId": 104,
... "name": "Carlos Sainz Jr"
... },
... {
... "driverRaceMapId": 9,
... "position": 3,
... "driverId": 106,
... "name": "Lewis Hamilton"
... },
... {
... "driverRaceMapId": 10,
... "position": 4,
... "driverId": 105,
... "name": "George Russell"
... }
... ]
... }
... );
{
acknowledged: true,
insertedId: null,
matchedCount: 1,
modifiedCount: 1,
upsertedCount: 0
}
mongo_test>
コレクションrace_dvの内容を確認します。
mongo_test> db.race_dv.find();
[
{
_id: 201,
name: 'Bahrain Grand Prix',
laps: 57,
date: ISODate("2022-03-20T00:00:00.000Z"),
podium: {
winner: { name: 'Charles Leclerc', time: '01:37:33.584' },
firstRunnerUp: { name: 'Carlos Sainz Jr', time: '01:37:39.182' },
secondRunnerUp: { name: 'Lewis Hamilton', time: '01:37:43.259' }
},
result: [
{
driverRaceMapId: 3,
position: 1,
driverId: 103,
name: 'Charles Leclerc'
},
{
driverRaceMapId: 4,
position: 2,
driverId: 104,
name: 'Carlos Sainz Jr'
},
{
driverRaceMapId: 9,
position: 3,
driverId: 106,
name: 'Lewis Hamilton'
},
{
driverRaceMapId: 10,
position: 4,
driverId: 105,
name: 'George Russell'
}
],
_metadata: {
etag: Binary(Buffer.from("20f7d9f0c69ac5f959dca819f9116848", "hex"), 0),
asof: Binary(Buffer.from("0000000000d3b1a1", "hex"), 0)
}
},
{
_id: 202,
name: 'Saudi Arabian Grand Prix',
laps: 50,
date: ISODate("2022-03-27T00:00:00.000Z"),
podium: {},
result: [],
_metadata: {
etag: Binary(Buffer.from("7e056a845212bfde19e0c0d0cd549ea0", "hex"), 0),
asof: Binary(Buffer.from("0000000000d3b1a1", "hex"), 0)
}
},
{
_id: 203,
name: 'Australian Grand Prix',
laps: 58,
date: ISODate("2022-04-09T00:00:00.000Z"),
podium: {},
result: [],
_metadata: {
etag: Binary(Buffer.from("ea6e1194c012970ca07116ee1ef167e8", "hex"), 0),
asof: Binary(Buffer.from("0000000000d3b1a1", "hex"), 0)
}
}
]
mongo_test>
コレクションrace_dv内の_idの値が201のドキュメントが、レース結果を含む内容に変更（置換）されました。
コレクションteam_dvの内容を確認してみます。
mongo_test> db.team_dv.find();
[
{
_id: 2,
name: 'Mercedes',
points: 27,
driver: [
{ driverId: 105, name: 'George Russell', points: 12 },
{ driverId: 106, name: 'Lewis Hamilton', points: 15 }
],
_metadata: {
etag: Binary(Buffer.from("855840b905c8cafa99fb9cbf813992e5", "hex"), 0),
asof: Binary(Buffer.from("0000000000d3b22e", "hex"), 0)
}
},
{
_id: 301,
name: 'Red Bull',
points: 0,
driver: [
{ driverId: 101, name: 'Max Verstappen', points: 0 },
{ driverId: 102, name: 'Sergio Perez', points: 0 }
],
_metadata: {
etag: Binary(Buffer.from("9f0f855a39f4ae8fecd7e6a123855fd1", "hex"), 0),
asof: Binary(Buffer.from("0000000000d3b22e", "hex"), 0)
}
},
{
_id: 302,
name: 'Ferrari',
points: 43,
driver: [
{ driverId: 103, name: 'Charles Leclerc', points: 25 },
{ driverId: 104, name: 'Carlos Sainz Jr', points: 18 }
],
_metadata: {
etag: Binary(Buffer.from("c5dd30f04da1a6a390bfab12b7d4f700", "hex"), 0),
asof: Binary(Buffer.from("0000000000d3b22e", "hex"), 0)
}
}
]
mongo_test>
コレクションrace_dv内のドキュメントをレース結果を含む内容に変更したことによって、コレクションteam_dv内の各ドキュメントのpointsおよび各ドライバーのpoints（driver.points）が更新されていることが確認できました。
コレクションdriver_dvの内容を確認してみます。
mongo_test> db.driver_dv.find();
[
{
_id: 105,
name: 'George Russell',
points: 12,
teamId: 2,
team: 'Mercedes',
race: [
{
driverRaceMapId: 10,
raceId: 201,
name: 'Bahrain Grand Prix',
finalPosition: 4
}
],
_metadata: {
etag: Binary(Buffer.from("2e67a3a752d750692757480b648521b7", "hex"), 0),
asof: Binary(Buffer.from("0000000000d3b257", "hex"), 0)
}
},
{
_id: 106,
name: 'Lewis Hamilton',
points: 15,
teamId: 2,
team: 'Mercedes',
race: [
{
driverRaceMapId: 9,
raceId: 201,
name: 'Bahrain Grand Prix',
finalPosition: 3
}
],
_metadata: {
etag: Binary(Buffer.from("466f2676916d9db3b0b35c87192da2af", "hex"), 0),
asof: Binary(Buffer.from("0000000000d3b257", "hex"), 0)
}
},
{
_id: 101,
name: 'Max Verstappen',
points: 0,
teamId: 301,
team: 'Red Bull',
race: [],
_metadata: {
etag: Binary(Buffer.from("f9d9815dff27879f61386cfd1622b065", "hex"), 0),
asof: Binary(Buffer.from("0000000000d3b257", "hex"), 0)
}
},
{
_id: 102,
name: 'Sergio Perez',
points: 0,
teamId: 301,
team: 'Red Bull',
race: [],
_metadata: {
etag: Binary(Buffer.from("9865a29dee5f5754674a9b1d2ec58730", "hex"), 0),
asof: Binary(Buffer.from("0000000000d3b257", "hex"), 0)
}
},
{
_id: 103,
name: 'Charles Leclerc',
points: 25,
teamId: 302,
team: 'Ferrari',
race: [
{
driverRaceMapId: 3,
raceId: 201,
name: 'Bahrain Grand Prix',
finalPosition: 1
}
],
_metadata: {
etag: Binary(Buffer.from("5ec1d86761320f1d79e3c8e0995cf672", "hex"), 0),
asof: Binary(Buffer.from("0000000000d3b257", "hex"), 0)
}
},
{
_id: 104,
name: 'Carlos Sainz Jr',
points: 18,
teamId: 302,
team: 'Ferrari',
race: [
{
driverRaceMapId: 4,
raceId: 201,
name: 'Bahrain Grand Prix',
finalPosition: 2
}
],
_metadata: {
etag: Binary(Buffer.from("1a7a20f98f841760fecdcd43265a3cef", "hex"), 0),
asof: Binary(Buffer.from("0000000000d3b257", "hex"), 0)
}
}
]
mongo_test>
コレクションrace_dv内のドキュメントをレース結果を含む内容に変更したことによって、コレクションdriver_dv内の各ドキュメントのpointsおよびraceの値が更新されていることが確認できました。
Duality Viewのベースとなっているリレーショナル表の内容を確認してみます。
SQL> SELECT * FROM race;
RACE_ID NAME LAPS RACE_DATE PODIUM
---------- ------------------------------ ---------- --------- --------------------------------------------------------------------------------
201 Bahrain Grand Prix 57 20-MAR-22 {"winner":{"name":"Charles Leclerc","time":"01:37:33.584"},"firstRunnerUp":{"name":"Carlos Sainz Jr","time":"01:37:39.182"},"secondRunnerUp":{"name":"Lewis Hamilton","time":"01:37:43.259"}}
202 Saudi Arabian Grand Prix 50 27-MAR-22 {}
203 Australian Grand Prix 58 09-APR-22 {}
SQL> SELECT * FROM driver_race_map;
DRIVER_RACE_MAP_ID RACE_ID DRIVER_ID POSITION
------------------ ---------- ---------- ----------
3 201 103 1
4 201 104 2
9 201 106 3
10 201 105 4
SQL> SELECT * FROM team;
TEAM_ID NAME POINTS
---------- ------------------------------ ----------
2 Mercedes 27
301 Red Bull 0
302 Ferrari 43
SQL> SELECT * FROM driver;
DRIVER_ID NAME POINTS TEAM_ID
---------- ------------------------------ ---------- ----------
105 George Russell 12 2
106 Lewis Hamilton 15 2
101 Max Verstappen 0 301
102 Sergio Perez 0 301
103 Charles Leclerc 25 302
104 Carlos Sainz Jr 18 302
6 rows selected.
SQL>
コレクションrace_dv内のドキュメントをレース結果を含む内容に変更したことによって、以下の変更があったことがわかりました。
・race表のpodium列の値が更新された
・driver_race_map表に行が追加された
・team表のpoints列の値が更新された
・driver表のpoints列の値が更新された
team表およびdriver表のpoints列の値が更新されたのは、1.で作成したトリガーdriver_race_map_triggerによるものです。
トリガーdriver_race_map_triggerによってteam表およびdriver表のpoints列の値が更新され、コレクションteam_dvのpoints、およびコレクションdriver_dvのdriver.pointsの値に反映されています。
updateOne()メソッドを使用して、コレクションrace_dv内にあるドキュメントのうち、nameの値が"Bahrain Grand Prix"であるドキュメントのnameの値を"Blue Air Bahrain Grand Prix"に変更してみます。
mongo_test> db.race_dv.updateOne(
... {name : "Bahrain Grand Prix"},
... {$set : {name : "Blue Air Bahrain Grand Prix"}}
... );
{
acknowledged: true,
insertedId: null,
matchedCount: 1,
modifiedCount: 1,
upsertedCount: 0
}
mongo_test>
コレクションrace_dvの内容を確認してみます。
mongo_test> db.race_dv.find();
[
{
_id: 201,
name: 'Blue Air Bahrain Grand Prix',
laps: 57,
date: ISODate("2022-03-20T00:00:00.000Z"),
podium: {
winner: { name: 'Charles Leclerc', time: '01:37:33.584' },
firstRunnerUp: { name: 'Carlos Sainz Jr', time: '01:37:39.182' },
secondRunnerUp: { name: 'Lewis Hamilton', time: '01:37:43.259' }
},
result: [
{
driverRaceMapId: 3,
position: 1,
driverId: 103,
name: 'Charles Leclerc'
},
{
driverRaceMapId: 4,
position: 2,
driverId: 104,
name: 'Carlos Sainz Jr'
},
{
driverRaceMapId: 9,
position: 3,
driverId: 106,
name: 'Lewis Hamilton'
},
{
driverRaceMapId: 10,
position: 4,
driverId: 105,
name: 'George Russell'
}
],
_metadata: {
etag: Binary(Buffer.from("f6906a8f7a131c127faef32ca43af97a", "hex"), 0),
asof: Binary(Buffer.from("0000000000d3b84d", "hex"), 0)
}
},
{
_id: 202,
name: 'Saudi Arabian Grand Prix',
laps: 50,
date: ISODate("2022-03-27T00:00:00.000Z"),
podium: {},
result: [],
_metadata: {
etag: Binary(Buffer.from("7e056a845212bfde19e0c0d0cd549ea0", "hex"), 0),
asof: Binary(Buffer.from("0000000000d3b84d", "hex"), 0)
}
},
{
_id: 203,
name: 'Australian Grand Prix',
laps: 58,
date: ISODate("2022-04-09T00:00:00.000Z"),
podium: {},
result: [],
_metadata: {
etag: Binary(Buffer.from("ea6e1194c012970ca07116ee1ef167e8", "hex"), 0),
asof: Binary(Buffer.from("0000000000d3b84d", "hex"), 0)
}
}
]
mongo_test>
コレクションrace_dv内のドキュメントのうち、nameの値が"Bahrain Grand Prix"であるドキュメント（_idの値が201のドキュメント）のnameの値が"Blue Air Bahrain Grand Prix"に変更されました。
コレクションdriver_dvの内容を確認してみます
mongo_test> db.driver_dv.find();
[
{
_id: 105,
name: 'George Russell',
points: 12,
teamId: 2,
team: 'Mercedes',
race: [
{
driverRaceMapId: 10,
raceId: 201,
name: 'Blue Air Bahrain Grand Prix',
finalPosition: 4
}
],
_metadata: {
etag: Binary(Buffer.from("13f8dc14f9205fce16b7e911c36b6332", "hex"), 0),
asof: Binary(Buffer.from("0000000000d3b857", "hex"), 0)
}
},
{
_id: 106,
name: 'Lewis Hamilton',
points: 15,
teamId: 2,
team: 'Mercedes',
race: [
{
driverRaceMapId: 9,
raceId: 201,
name: 'Blue Air Bahrain Grand Prix',
finalPosition: 3
}
],
_metadata: {
etag: Binary(Buffer.from("6ea0aa1fa6d8996c44b375d0fb65e5f5", "hex"), 0),
asof: Binary(Buffer.from("0000000000d3b857", "hex"), 0)
}
},
{
_id: 101,
name: 'Max Verstappen',
points: 0,
teamId: 301,
team: 'Red Bull',
race: [],
_metadata: {
etag: Binary(Buffer.from("f9d9815dff27879f61386cfd1622b065", "hex"), 0),
asof: Binary(Buffer.from("0000000000d3b857", "hex"), 0)
}
},
{
_id: 102,
name: 'Sergio Perez',
points: 0,
teamId: 301,
team: 'Red Bull',
race: [],
_metadata: {
etag: Binary(Buffer.from("9865a29dee5f5754674a9b1d2ec58730", "hex"), 0),
asof: Binary(Buffer.from("0000000000d3b857", "hex"), 0)
}
},
{
_id: 103,
name: 'Charles Leclerc',
points: 25,
teamId: 302,
team: 'Ferrari',
race: [
{
driverRaceMapId: 3,
raceId: 201,
name: 'Blue Air Bahrain Grand Prix',
finalPosition: 1
}
],
_metadata: {
etag: Binary(Buffer.from("d44402d6a4a1837186419926384e1d47", "hex"), 0),
asof: Binary(Buffer.from("0000000000d3b857", "hex"), 0)
}
},
{
_id: 104,
name: 'Carlos Sainz Jr',
points: 18,
teamId: 302,
team: 'Ferrari',
race: [
{
driverRaceMapId: 4,
raceId: 201,
name: 'Blue Air Bahrain Grand Prix',
finalPosition: 2
}
],
_metadata: {
etag: Binary(Buffer.from("df16113d412db39a4c455dc9f1543dc9", "hex"), 0),
asof: Binary(Buffer.from("0000000000d3b857", "hex"), 0)
}
}
]
mongo_test>
コレクションrace_dv内にあるドキュメントのnameの値を変更することによって、コレクションdriver_dv内にある各ドキュメントのrace.nameの値が更新されていることが確認できました。
コレクションteam_dvの内容を確認してみます。
mongo_test> db.team_dv.find();
[
{
_id: 2,
name: 'Mercedes',
points: 27,
driver: [
{ driverId: 105, name: 'George Russell', points: 12 },
{ driverId: 106, name: 'Lewis Hamilton', points: 15 }
],
_metadata: {
etag: Binary(Buffer.from("855840b905c8cafa99fb9cbf813992e5", "hex"), 0),
asof: Binary(Buffer.from("0000000000d3b868", "hex"), 0)
}
},
{
_id: 301,
name: 'Red Bull',
points: 0,
driver: [
{ driverId: 101, name: 'Max Verstappen', points: 0 },
{ driverId: 102, name: 'Sergio Perez', points: 0 }
],
_metadata: {
etag: Binary(Buffer.from("9f0f855a39f4ae8fecd7e6a123855fd1", "hex"), 0),
asof: Binary(Buffer.from("0000000000d3b868", "hex"), 0)
}
},
{
_id: 302,
name: 'Ferrari',
points: 43,
driver: [
{ driverId: 103, name: 'Charles Leclerc', points: 25 },
{ driverId: 104, name: 'Carlos Sainz Jr', points: 18 }
],
_metadata: {
etag: Binary(Buffer.from("c5dd30f04da1a6a390bfab12b7d4f700", "hex"), 0),
asof: Binary(Buffer.from("0000000000d3b868", "hex"), 0)
}
}
]
mongo_test>
コレクションteam_dv内のドキュメントにはレース名に関する情報が無いため、変更されていません。
Duality Viewのベースとなっているリレーショナル表の内容を確認してみます。
SQL> SELECT * FROM team;
TEAM_ID NAME POINTS
---------- ------------------------------ ----------
2 Mercedes 27
301 Red Bull 0
302 Ferrari 43
SQL> SELECT * FROM driver;
DRIVER_ID NAME POINTS TEAM_ID
---------- ------------------------------ ---------- ----------
105 George Russell 12 2
106 Lewis Hamilton 15 2
101 Max Verstappen 0 301
102 Sergio Perez 0 301
103 Charles Leclerc 25 302
104 Carlos Sainz Jr 18 302
6 rows selected.
SQL> SELECT * FROM race;
RACE_ID NAME LAPS RACE_DATE PODIUM
---------- ------------------------------ ---------- --------- --------------------------------------------------------------------------------
201 Blue Air Bahrain Grand Prix 57 20-MAR-22 {"winner":{"name":"Charles Leclerc","time":"01:37:33.584"},"firstRunnerUp":{"name":"Carlos Sainz Jr","time":"01:37:39.182"},"secondRunnerUp":{"name":"Lewis Hamilton","time":"01:37:43.259"}}
202 Saudi Arabian Grand Prix 50 27-MAR-22 {}
203 Australian Grand Prix 58 09-APR-22 {}
SQL> SELECT * FROM driver_race_map;
DRIVER_RACE_MAP_ID RACE_ID DRIVER_ID POSITION
------------------ ---------- ---------- ----------
3 201 103 1
4 201 104 2
9 201 106 3
10 201 105 4
SQL>
コレクションrace_dv内のドキュメントのnameの値を更新したことよって、race表のname列の値が変更されたことがわかりました。
updateOne()メソッドを使用して、コレクションdriver_dv内にある_idの値が102のドキュメントのteamの値を'Ferrari'に変更してみます。
mongo_test> db.driver_dv.updateOne({"_id" : 102} , {$set : {"team" : "Ferrari"}});
2023-05-16T10:57:26.572Z WARNING oracle.soda.rdbms.impl.SODAUtils$1OracleSQLException
MongoServerError: ORA-40940: Cannot update field 'team' corresponding to column 'NAME' of table 'TEAM' in JSON Relational Duality View 'DRIVER_DV': Missing UPDATE annotation or NOUPDATE annotation specified.
mongo_test>
エラーが発生してコレクションdriver_dv内のteamの値を変更できませんでした。
これはdriver_dvのDDLの8行目でNOUPDATEが指定されているためです。
SQL> CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW driver_dv AS
2 SELECT JSON {'_id' IS d.driver_id,
3 'name' IS d.name,
4 'points' IS d.points,
5 UNNEST
6 (SELECT JSON {'teamId' IS t.team_id,
7 'team' IS t.name WITH NOCHECK}
8 FROM team t WITH NOINSERT NOUPDATE NODELETE -- ※この部分
9 WHERE t.team_id = d.team_id),
10 'race' IS
11 [ SELECT JSON {'driverRaceMapId' IS drm.driver_race_map_id,
12 UNNEST
13 (SELECT JSON {'raceId' IS r.race_id,
14 'name' IS r.name}
15 FROM race r WITH NOINSERT NOUPDATE NODELETE
16 WHERE r.race_id = drm.race_id),
17 'finalPosition' IS drm.position}
18 FROM driver_race_map drm WITH INSERT UPDATE NODELETE
19 WHERE drm.driver_id = d.driver_id ]}
20 FROM driver d WITH INSERT UPDATE DELETE;
deleteOne()メソッドを使用して、コレクションrace_dv内にある_idの値が202のドキュメントを削除してみます。
mongo_test> db.race_dv.deleteOne({_id:202});
{ acknowledged: true, deletedCount: 1 }
mongo_test>
コレクションrace_dvの内容を確認してみます。
mongo_test> db.race_dv.find();
[
{
_id: 201,
name: 'Blue Air Bahrain Grand Prix',
laps: 57,
date: ISODate("2022-03-20T00:00:00.000Z"),
podium: {
winner: { name: 'Charles Leclerc', time: '01:37:33.584' },
firstRunnerUp: { name: 'Carlos Sainz Jr', time: '01:37:39.182' },
secondRunnerUp: { name: 'Lewis Hamilton', time: '01:37:43.259' }
},
result: [
{
driverRaceMapId: 3,
position: 1,
driverId: 103,
name: 'Charles Leclerc'
},
{
driverRaceMapId: 4,
position: 2,
driverId: 104,
name: 'Carlos Sainz Jr'
},
{
driverRaceMapId: 9,
position: 3,
driverId: 106,
name: 'Lewis Hamilton'
},
{
driverRaceMapId: 10,
position: 4,
driverId: 105,
name: 'George Russell'
}
],
_metadata: {
etag: Binary(Buffer.from("f6906a8f7a131c127faef32ca43af97a", "hex"), 0),
asof: Binary(Buffer.from("0000000000d6dcb7", "hex"), 0)
}
},
{
_id: 203,
name: 'Australian Grand Prix',
laps: 58,
date: ISODate("2022-04-09T00:00:00.000Z"),
podium: {},
result: [],
_metadata: {
etag: Binary(Buffer.from("ea6e1194c012970ca07116ee1ef167e8", "hex"), 0),
asof: Binary(Buffer.from("0000000000d6dcb7", "hex"), 0)
}
}
]
mongo_test>
コレクションrace_dv内にあった_idの値が202のドキュメントが削除されました。
Duality Viewのベースとなっているリレーショナル表の内容を確認してみます。
SQL> SELECT * FROM race;
RACE_ID NAME LAPS RACE_DATE PODIUM
---------- ------------------------------ ---------- --------- --------------------------------------------------------------------------------
201 Blue Air Bahrain Grand Prix 57 20-MAR-22 {"winner":{"name":"Charles Leclerc","time":"01:37:33.584"},"firstRunnerUp":{"name":"Carlos Sainz Jr","time":"01:37:39.182"},"secondRunnerUp":{"name":"Lewis Hamilton","time":"01:37:43.259"}}
203 Australian Grand Prix 58 09-APR-22 {}
SQL>
コレクションrace_dvから_idの値が202のドキュメントを削除したことによって、race表内のrace_idが202のレコードが削除されたことがわかりました。
参考情報
・JSON-Relational Duality Developer's Guide
・Oracle Sample Projects : JSON Relational Duality