はじめに
ここでは (4) として JSON Relation Duality Viewを解説します。これは Oracle AI Database が 23c(23ai) でリリースされた機能であり、過去もマニュアルベースの内容を紹介していますが、
今回はより身近な例として SCOTTスキーマの EMP-DEPT表の仮想表と同等の内容を、SQL Developer for VS Codeを活用しつつ、よりシンプルな JSON Relational Duality View を構築する構成を紹介します。
Oracle AI Database の JSONとは
マニュアルにはJSONデータを扱う際の簡単なガイドラインがあります。
ここでは JSONでdocument-centric = yes でリレーショナル・データとの連携があり、更新の必要性にも対応可能な JSON-relational duality views とその実装について解説します。
JSONデータにLOB記憶域を使用する際の考慮事項
検証環境と手順
環境はOCI上の BaseDB 23.26で実施しています。
まず、通常の仮想表から考えて行きます。EMP-DEPTによる仮想表を作成してみます。
SQL> CREATE OR REPLACE FORCE EDITIONABLE VIEW "SCOTT"."EDV" ("EMPNO", "ENAME", "JOB", "DEPTNO", "SAL", "DNAME", "LOC", "HIREDATE") AS
SELECT
E.EMPNO,E.ENAME, E.JOB, E.DEPTNO, E.SAL, D.DNAME, D.LOC, e.hiredate
FROM
EMP E, DEPT D
Where
E.DEPTNO = D.DEPTNO;
このView からさらに、部門毎の給与合計額や勤続年数の長い社員情報の検索などの多様な集計がやり易くなります。この View と同じ内容で JSON Relational Duality View を作成します。
- VS Code (w/SQL Developer) 起動イメージ
- JSON Relational Duality View ビルダーを起動する
- 中央からルート表の追加
- SCOTTの表が選択できるので、ここでは EMP表を選択
- EMPに対し、親表としてDEPTを追加する
- DEPTを選択し、Applyする
既に作成した仮想表と同じカラムを選択 (_idとDeptnoは自動的に選択される)
EMP表をクリックすると右端のペインで Insert, Update, Delete (DML)を許可できる
Viewに対するDML許可に加え、各列に対するDML許可も確認する
中央、下の構文では GraphQL か SQL を選択できる - ここでは SQLにすると
CREATE FORCE EDITIONABLE JSON RELATIONAL DUALITY VIEW SCOTT.EDJRDV
AS SELECT JSON {
'_id' : e.EMPNO,
'ename' : e.ENAME,
'job' : e.JOB,
'hiredate' : e.HIREDATE,
'sal' : e.SAL,
'dept' :
( SELECT JSON {
'deptno' : d.DEPTNO,
'dname' : d.DNAME,
'loc' : d.LOC
}
FROM SCOTT.DEPT d
WHERE d.DEPTNO = e.DEPTNO
)
}
FROM SCOTT.EMP e
WITH INSERT UPDATE DELETE;
SQL文として確認できる
- 作成する
- 作成が完了する
- 実際のデータを確認する(既存の表であるEMP,DEPTの結合データがJSON形式で)
社員名が 'SCOTT' のデータを仮想表から検索
SQL> SELECT * from EDJRDV WHERE JSON_VALUE(DATA, '$.ename') = 'SCOTT';
社員名が 'SCOTT' のデータを JSON Relational Duality View : EDJRDV から検索
SQL> SELECT * from EDJRDV WHERE JSON_VALUE(DATA, '$.ename') = 'SCOTT';
SCOTTを昇給してみる
SQL> UPDATE scott.edjrdv dv
SET data = JSON_TRANSFORM(
data,
SET '$.sal' = 3850
)
WHERE JSON_VALUE(dv.data, '$.ename') = 'SCOTT';
SQL> COMMIT;
JSON Relational Duality View の EDJRDV からSALが更新されている事を確認
Viewが更新可能になっているため、仮想表EDVと構成元表のEMPからもSALが更新されているのが確認できる
SQL> SELECT * from EDV WHERE ename = 'SCOTT';
SQL> SELECT * from emp WHERE ename = 'SCOTT';
新入社員を歓迎しよう - JSON Relational Duality View の EDJRDVからJSONデータで新しい社員の情報を Inert してみる
SQL> INSERT INTO scott.edjrdv (data)
VALUES (
'{
"_id" : 9001,
"ename" : "TONY STARK",
"job" : "ANALYST",
"hiredate" : "1987-04-19T00:00:00",
"sal" : 3200,
"dept" : {
"deptno" : 20,
"dname" : "RESEARCH",
"loc" : "DALLAS"
}
}'
);
SQL> COMMIT;
1行挿入しました。
コミットが完了しました。
JSON Relational Duality View から更新されたデータを確認してみる。
SQL> SELECT * from EDJRDV;
同様にEMP表からも確認する。
SQL> SELECT * from emp WHERE empno = 9001;
最後に
マニュアルなどを見ると、JSON Relational Duality View は高度で難解なものといったイメージが先行するかも知れませんが、現状のリレーショナル・データの設計と表裏一体であると考えられます。
データの関係性や更新の可否など従来の設計枠を超えるものではなく、表形式あるいはレコードとして扱うか JSON として扱うかを部分的に Oracle AI Database に委ねる感じと言えると思います。
アプリケーション開発者とDBAやObjectの設計者にはまだ少し距離があるかも知れませんが、JSONの活用範囲は広いのでDB側からももっと訴求できればと思います。
出典:日本オラクル株式会社
- Oracle AI Database 26ai のJSON関連の記事
Oracle AI Database 26ai の JSONについて(1) – Binary JSON(OSON)
Oracle AI Database 26ai の JSONについて(2) – JSON Collection View
Oracle AI Database 26ai の JSONについて(3) – JSON Collection Table
👉 Oracle AI Database 26ai の JSONについて(4) – JSON Relational Duality View





















