こんにちは。AIです。（Artificial Intelligence ではありません）
いつのまにか 2025 年になっており、びっくりしております。年が明けてからのトロントはとても寒く、最低気温が-17℃になることも！
さて、少し間は空いてしまいましたが、今回は 23ai の目玉新機能の一つともいえる JSONリレーショナル二面性ビューについて掘り下げていきましょう！
■JSON とは
機能の紹介に入る前に、まずは JSON とはなんぞやというところを整理しましょう。
JSON とは [JavaScript Object Notation] の略称であり Java Script をベースとしたテキストベースのデータ交換用フォーマットです。
可読性の高さと、マシンが解析可能な形式であることを両立しており、多様なプログラミング言語で利用されています。
JSON形式やXML形式などでデータを保存するデータベースをドキュメント型データベースと呼びます。
ドキュメント型データベースは、リレーショナル・データベースと比べ自由な形でデータを保存することができますが、一方でデータの整合性については保証されない場合もあります。
■Oracle で JSON 形式でデータを表示する方法
23ai では注目すべき機能としてJSONリレーショナル二面性ビューが実装されますが、実はそれ以前も JSON 自体は 12.1.0.2 から Oracle Database でサポートされていました。
以下は 12.2.0.1 から実装された json_object 関数を使用してテーブルに格納したデータを JSON 形式で表示させています。
＠json_object 関数
SQL> SELECT json_object('id' VALUE s.student_id,
2 'name' VALUE s.name)
3 FROM students s;
JSON_OBJECT('ID'VALUES.STUDENT_ID,'NAME'VALUES.NAME)
--------------------------------------------------------
{"id":1,"name":"Ann"}
{"id":2,"name":"Brian"}
{"id":3,"name":"Cathy"}■JSON リレーショナル二面性ビューとは
JSONリレーショナル二面性ビューは、リレーショナル・データベース表に格納されているデータから更新可能で一貫性のある JSON ドキュメント・ビューを提供する機能です。
JSON リレーショナル二面性ビューとこれまでの関数による表示の大きな違いはテーブルのデータを JSON 形式で更新可能という点です。
更新は SQL としても可能ですし、REST API を使用して更新することも可能です。
■JSON リレーショナル二面性ビューの作成
では、ここからは実際に JSON リレーショナル二面性ビューを作成してみます。
今回は単一の表から JSONリレーショナル二面性ビューを作成します。
（挿入や更新といった操作は次の記事で取り扱う予定です…！）
(1) 元となる表の準備
students という以下のテーブルを元にビューを作成していきます。
注意する点として、JSONリレーショナル二面性ビューを作成するテーブルには
主キー制約、外部キー制約、一意キー制約のいずれかが設定されていなければいけません。
今回は student_id 列に主キーを作成しています。
SQL> SELECT * FROM students;
STUDENT_ID NAME GRADE ENROLLMENT_YEAR GRADUATION_YEAR
---------- ------------------------------ ---------- --------------- ---------------
1 Ann 3 2020
2 Brian 2 2021
3 Cathy 1 2022
SQL> SELECT cc.owner, cc.constraint_name,cc.table_name,cc.column_name,c.constraint_type
2 FROM dba_constraints c JOIN dba_cons_columns cc ON c.constraint_name=cc.constraint_name
3 WHERE c.constraint_name='STUDENT_ID_PK';
OWNER CONSTRAINT_NAME TABLE_NAME COLUMN_NAME C
---------- --------------- ---------- -------------------- -
TEACHER STUDENT_ID_PK STUDENTS STUDENT_ID P ★(2) JSONリレーショナル二面性ビューの作成
では、さっそく JSONリレーショナル二面性ビューを作成していきます。
今回は SQL の CREATE JSON RELATIONAL DUALITY VIEW 文を使用して作成します。
※ちなみにこの文の実行には、CREATE VIEW 権限が必要なのでお忘れなく！
※WITH 句の指定によって、JSONリレーショナル二面性ビューを更新可能にするか否かを制御できます。表レベル、または列レベルで更新可否を設定することができます。
SQL> CREATE JSON RELATIONAL DUALITY VIEW student_dv AS
2 SELECT JSON{'id' : s.student_id,
3 'grade' : s.grade,
4 'name' : s.name,
5 'enrollment' : s.enrollment_year,
6 'graduation' : s.graduation_year}
7 FROM students s WITH INSERT UPDATE DELETE;
FROM students s WITH INSERT UPDATE DELETE
*
行7でエラーが発生しました。:
ORA-42647:
JSONリレーショナル二面性ビュー'STUDENT_DV'のルート・レベルに'_id'フィールドがあ
りません。
ヘルプ: https://docs.oracle.com/error-help/db/ora-42647/おや、_id フィールドがないと怒られてしまいます。
実は、JSONリレーショナル二面性ビューの制約として、ルート表の主キー列の要素名は、必ず「_id」という名前にしなければいけないというものがあります。
今回は student_id 列に主キーを設定しておりますので、student_id 列に対応する要素名を _id としましょう。
SQL> CREATE JSON RELATIONAL DUALITY VIEW student_dv AS
2 SELECT JSON{'_id' : s.student_id, ★
3 'grade' : s.grade,
4 'name' : s.name,
5 'enrollment' : s.enrollment_year,
6 'graduation' : s.graduation_year}
7 FROM students s WITH INSERT UPDATE DELETE;
ビューが作成されました。無事にJSONリレーショナル二面性ビューを作成することができました！
なお、作成したJSONリレーショナル二面性ビューは DBA_JSON_DUALITY_VIEWS で確認することができます。
SQL> SELECT view_owner,view_name,root_table_name,json_column_name,allow_insert,allow_update,allow_delete
2 FROM dba_json_duality_views
3 WHERE view_name='STUDENT_DV';
VIEW_OWNER VIEW_NAME ROOT_TABLE JSON ALLOW_INSER ALLOW_UPDAT ALLOW_DELET
---------- ---------- ---------- ---- ----------- ----------- -----------
TEACHER STUDENT_DV STUDENTS DATA TRUE TRUE TRUE(3)JSONリレーショナル二面性ビューを SELECT してみる
実際に作成された JSONリレーショナル二面性ビューを SELECT するとどのような結果が得られるのでしょうか。
その前に、CREATE JSON RELATIONAL DUALITY VIEW に含めた SELECT 文を実際に実行してみます。
SQL> SELECT JSON{'_id' : s.student_id,
2 'grade' : s.grade,
3 'name' : s.name,
4 'enrollment' : s.enrollment_year,
5 'graduation' : s.graduation_year}
6 FROM students s;
JSON{'_ID':S.STUDENT_ID,'GRADE':S.GRADE,'NAME':S.NAME,'ENROLLMENT':S.ENROLLMENT_
--------------------------------------------------------------------------------
{"_id":1,"grade":3,"name":"Ann","enrollment":2020,"graduation":null}
{"_id":2,"grade":2,"name":"Brian","enrollment":2021,"graduation":null}
{"_id":3,"grade":1,"name":"Cathy","enrollment":2022,"graduation":null}JSON形式でデータが表示されていることがわかります。
ではJSONリレーショナル二面性ビューを SELECT してみましょう。
SQL> SELECT * FROM student_dv;
DATA
------------------------------------------------------------------------------------------------------------------------------------------------------------
{"_id":1,"_metadata":{"etag":"1E2C8A22701254064C2A967460A4499A","asof":"0000000004190F0E"},"grade":3,"name":"Ann","enrollment":2020,"graduation":null}
{"_id":2,"_metadata":{"etag":"AD2DB203F0E3C15C843BF3D179F6A45D","asof":"0000000004190F0E"},"grade":2,"name":"Brian","enrollment":2021,"graduation":null}
{"_id":3,"_metadata":{"etag":"5EC933801D01A0C15590B61978B55D26","asof":"0000000004190F0E"},"grade":1,"name":"Cathy","enrollment":2022,"graduation":null}
JSONリレーショナル二面性ビューを確認すると、JSON形式にしたデータに etag と asof からなる metadata という要素が埋め込まれていることがわかります。
1行で表示されると読みづらい場合は、 json_serialize 関数を使用した方法で表示すると要素ごとに改行された状態で表示されます。
※json_serialize は JSONデータを受け取り、テキスト表現として返してくれる関数です。
SQL> SELECT json_serialize(DATA PRETTY) FROM student_dv;
JSON_SERIALIZE(DATAPRETTY)
---------------------------------------------------------
{
"_id" : 1,
"_metadata" :
{
"etag" : "1E2C8A22701254064C2A967460A4499A",
"asof" : "000000000418D558"
},
"grade" : 3,
"name" : "Ann",
"enrollment" : 2020,
"graduation" : null
}
{
"_id" : 2,
"_metadata" :
{
"etag" : "AD2DB203F0E3C15C843BF3D179F6A45D",
"asof" : "000000000418D558"
},
"grade" : 2,
"name" : "Brian",
"enrollment" : 2021,
"graduation" : null
}
{
"_id" : 3,
"_metadata" :
{
"etag" : "5EC933801D01A0C15590B61978B55D26",
"asof" : "000000000418D558"
},
"grade" : 1,
"name" : "Cathy",
"enrollment" : 2022,
"graduation" : null
}■etag について
最後にJSONリレーショナル二面性ビューを SELECT した際に表示された etag について注目してみます。
これは JSONドキュメント内の要素値をハッシュ化して組み合わせたものです。
JSONリレーショナル二面性ビューは、REST API による更新も可能とお伝えしていましたが、この更新ではトランザクションは発生しません。
その代わり、この etag を使用してデータの整合性を担保しています。
要素値が更新されると etag も合わせて更新されます。
※厳密にいうと NOCHECK オプションが指定されている要素が更新された場合では、etag は更新されませんが、今回の記事では詳細は触れません。
JSONリレーショナル二面性ビューを更新する際、指定した etag と実際のドキュメント(データ)の etag をチェックして、一致しない場合には更新は失敗します。
■まとめ
今回は、JSONリレーショナル二面性ビューのおおまかな概要と作成方法についてご紹介しましたが、いかがでしたでしょうか。
個人的には、etag によるデータの整合性のチェックや主キーを要素に含めることで、 JSON 形式での更新によって意図せぬデータが更新されてしまうことを防いでいる点が興味深かったです。
次回は、JSONリレーショナル二面性ビューの重要なポイント、JSONリレーショナル二面性ビューの更新について掘り下げていく予定ですのでお楽しみに！