0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

Oracle AI Database 26ai の JSONについて(4) – JSON Relational Duality View

0
Posted at

はじめに

ここでは (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による仮想表を作成してみます。

FY27-2042.png

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は以下の様な構成およびデータとなります。
FY27-2043.png

このView からさらに、部門毎の給与合計額や勤続年数の長い社員情報の検索などの多様な集計がやり易くなります。この View と同じ内容で JSON Relational Duality View を作成します。

  • VS Code (w/SQL Developer) 起動イメージ

FY27-2044.png

  • JSON Relational Duality View ビルダーを起動する

FY27-2045.png

  • 中央からルート表の追加

FY27-2046.png

  • SCOTTの表が選択できるので、ここでは EMP表を選択

FY27-2047.png

FY27-2048.png

  • EMPに対し、親表としてDEPTを追加する

FY27-2049.png

FY27-204A.png

  • DEPTを選択し、Applyする

FY27-204B.png

既に作成した仮想表と同じカラムを選択 (_idとDeptnoは自動的に選択される)
EMP表をクリックすると右端のペインで Insert, Update, Delete (DML)を許可できる
Viewに対するDML許可に加え、各列に対するDML許可も確認する

FY27-204C.png

中央、下の構文では 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文として確認できる

  • 作成する

FY27-204D.png

  • 作成が完了する

FY27-204E.png

  • 実際のデータを確認する(既存の表であるEMP,DEPTの結合データがJSON形式で)

FY27-204F.png

社員名が 'SCOTT' のデータを仮想表から検索

SQL> SELECT * from  EDJRDV WHERE JSON_VALUE(DATA, '$.ename') = 'SCOTT';

FY27-204G.png

社員名が 'SCOTT' のデータを JSON Relational Duality View : EDJRDV から検索

SQL> SELECT * from  EDJRDV WHERE JSON_VALUE(DATA, '$.ename') = 'SCOTT';

FY27-204H.png

SCOTTを昇給してみる

SQL> UPDATE scott.edjrdv dv
SET    data = JSON_TRANSFORM(
                 data,
                 SET '$.sal' = 3850
              )
WHERE  JSON_VALUE(dv.data, '$.ename') = 'SCOTT';
SQL> COMMIT;

FY27-204I.png

JSON Relational Duality View の EDJRDV からSALが更新されている事を確認
Viewが更新可能になっているため、仮想表EDVと構成元表のEMPからもSALが更新されているのが確認できる

SQL> SELECT * from  EDV WHERE ename = 'SCOTT';

FY27-204J.png

SQL> SELECT * from  emp WHERE ename = 'SCOTT';

FY27-204K.png

新入社員を歓迎しよう - 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;

FY27-204L.png

同様にEMP表からも確認する。

SQL> SELECT * from  emp WHERE empno = 9001;

FY27-204M.png

最後に

マニュアルなどを見ると、JSON Relational Duality View は高度で難解なものといったイメージが先行するかも知れませんが、現状のリレーショナル・データの設計と表裏一体であると考えられます。
データの関係性や更新の可否など従来の設計枠を超えるものではなく、表形式あるいはレコードとして扱うか JSON として扱うかを部分的に Oracle AI Database に委ねる感じと言えると思います。
アプリケーション開発者とDBAやObjectの設計者にはまだ少し距離があるかも知れませんが、JSONの活用範囲は広いのでDB側からももっと訴求できればと思います。

FY27-2041N.png

出典:日本オラクル株式会社

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?