21.11 定義済JSONスキーマでのコンバータの使用
データベースにJSONデータをロードする必要なく、つまりデータのコレクションから暗黙のスキーマを使用して、表に対してJSONスキーマを作成できます。
- JSONスキーマの使用によるリレーショナル・スキーマの推測について
移行プロセスの制御を促進するために、JSON-to-Dualityマイグレータを使用して複数のJSONスキーマからリレーショナル・スキーマを推測できます。 - JSON-to-Dualityコンバータを使用してリレーショナル・スキーマを推測するという選択肢
入力表に対して定義されているJSONスキーマまたは入力表内のデータ(あるいはその両方)を使用してリレーショナル・スキーマを推測すること選択できます。 - 入力データがないJSONスキーマの使用によるリレーショナル・スキーマの推測の例
データを挿入せずに入力表内のJSON列に対してJSONスキーマを定義します。コンバータ・ファンクションINFER_AND_GENERATE_SCHEMAでは、提供したJSONスキーマ内で指定されている構造と制約のみに基づいて、リレーショナル・スキーマが生成されます。 - 入力データがあるJSONスキーマの使用によるリレーショナル・スキーマの推測の例
JSON列のJSONスキーマを定義し、同時に入力表にデータを挿入します。INFER_AND_GENERATE_SCHEMAファンクションでは、そのスキーマとデータの両方を使用して、より正確で完全なリレーショナル・データベース・スキーマが作成されます。
親トピック: JSONから二面性への移行
21.11.1 JSONスキーマの使用によるリレーショナル・スキーマの推測について
移行プロセスの制御を促進するために、JSON-to-Dualityマイグレータを使用して複数のJSONスキーマからリレーショナル・スキーマを推測できます。
JSON-To-Dualityマイグレータでは、移行の柔軟性を高めるために、事前定義されたJSONスキーマがある入力表がサポートされています。入力表内のJSON列に対してJSONスキーマを定義でき、コンバータ・ファンクションの開始前にデータ挿入をスキップすることもできます。この機能により、移行プロセスをより詳細に制御できるようになり、複数のソースからのスキーマ推論が可能になります。
JSONスキーマ定義のサポートにより、コンバータ・ファンクションINFER_AND_GENERATE_SCHEMAおよびINFER_SCHEMAは、JSONスキーマのみの変換、データのみの変換、およびJSONスキーマとデータの複合変換という3つの異なるモードで動作できます。コンバータでは、使用可能な入力をインテリジェントに使用して(JSONスキーマ定義であるか、挿入されたJSONデータであるか、その両方であるかを問わない)、基にある表およびJSON二面性ビューを表す正確で包括的なデータベース・リレーショナル・スキーマが生成されます。
親トピック: 定義済JSONスキーマでのコンバータの使用
21.11.2 JSON-to-Dualityコンバータを使用してリレーショナル・スキーマを推測するという選択肢
入力表に対して定義されているJSONスキーマまたは入力表内のデータ(あるいはその両方)を使用してリレーショナル・スキーマを推測することを選択できます。
コンバータ・ファンクションでは、提供されたJSONスキーマ、挿入されたJSONデータ、または両方の入力が分析されて、対応するリレーショナル・データベース・スキーマが生成されます。このリレーショナル・データベース・スキーマは、ドキュメント・セットをサポートするために必要なデータベース表およびJSON二面性ビューを表しています。スキーマとデータの両方を使用可能な場合は、コンバータにより、明示的なスキーマ制約と推論したデータ・パターンを組み合せて、最適化されたデータベース構造が生成されます。
これらのオプションの間の違いについて詳細を確認するには、このドキュメント内の例を参照してください。おおまかに述べると、これらの各選択肢の特性は次のようになります:
-
JSONスキーマのみの変換: 表にデータを挿入せずにJSON列のJSONスキーマを定義します。コンバータにより、提供したJSONスキーマ構造および制約のみに基づいてリレーショナル・スキーマが生成されます。
このオプションは、実際のデータをインポートする前にリレーショナル・スキーマを生成することでアプリケーションを開発する必要がある場合に使用します。これは、データベース構造を迅速に確立する必要がある場合に特に役立ちます(特に、大規模なデータ・セットを使用しておりデータ移行に取り組む前にスキーマ設計を検証する必要がある場合)。
- JSONデータのみの変換: このオプションは、既存のJSONドキュメントがあるが事前定義されたJSONスキーマがない場合に使用します。このオプションを選択すると、コンバータで、実際のデータからリレーショナル・スキーマを自動的に推測できるようになります。このオプションの例は、「二面性への移行、簡易レシピ」を参照してください
- JSONスキーマとデータの複合変換: JSON列に対してJSONスキーマ定義とデータを両方提供します。コンバータ・ファンクションでは、両方の入力を使用し、実際のデータ・パターンと明示的なJSONスキーマ制約を利用して、より正確で包括的なリレーショナル・データベース・スキーマが生成されます。このオプションは、JSONスキーマと実際のデータにわずかな差異がある可能性がありそれに対応する必要があるときに、最も正確な最適化されたリレーショナル・スキーマを生成する必要がある場合に使用します。
JSONスキーマのみの変換の特性
入力表に対してJSONスキーマのみの変換を使用することでJSONスキーマを定義すると、変換プロセスの間にいくつか利点があります:
- ドキュメント構造の早期検証が可能になります
- データ挿入前の制約の早期検証が可能になります。
JSONスキーマとデータの複合変換の特性
JSONスキーマとデータの入力を組み合せた場合、コンバータで、スキーマとデータの差異に対応できます:
- スキーマで定義されているフィールドがデータにない: スキーマ・フィールド(courseスキーマ内の
locationや、teacherスキーマ内のemailIdなど)により、データベース・ルート表内の対応する列が作成されます。 - データ・フィールドがスキーマにない:
teacherデータ内のphoneNumber(簡潔にするためにサンプル・スキーマから省略されている)などのデータ・フィールドは、実際のデータから導出され、生成されたスキーマに含まれます。 - 正規化の向上: 変換プロセスの間のデータ分析により、データベース表のリレーションシップが向上し、ストレージ構造がより効率的になります。
親トピック: 定義済JSONスキーマでのコンバータの使用
21.11.3 入力データがないJSONスキーマの使用によるリレーショナル・スキーマの推測の例
データを挿入せずに入力表内のJSON列に対してJSONスキーマを定義します。コンバータ・ファンクションINFER_AND_GENERATE_SCHEMAでは、提供したJSONスキーマ内で指定されている構造と制約のみに基づいて、リレーショナル・スキーマが生成されます。
この例では、コンバータ・ファンクションをコールする前にデータ挿入なしでJSONスキーマ定義を使用して入力表を作成する方法を示します。このJSONスキーマでは、必要な入力データ形式と一致する、student(学生)、teacher(教師)およびcourse(コース)ドキュメントの構造と制約が定義されます。これらの入力ファイルは、「学校経営の例(マイグレータの入力ドキュメント)」のトピックにあります。
ノート:
スキーマのみの変換を使用する場合は、コンバータに分析用の実際のデータ・パターンがないため、様々なコレクションにわたる表が最適に正規化されない可能性があることに注意してください。INFER_AND_GENERATE_SCHEMAを使用して生成されたリレーショナル・スキーマでは、実際のデータ・コンテンツから識別された自然キーではなく、自動生成された主キー(ora$id)が使用されます。
例21-23 データなしでの入力表に対するJSONスキーマの定義
次のSQLコードでは、包括的なJSONスキーマ検証を使用して3つの入力表が作成されます。各表には、詳細なプロパティ定義、データ型、および必要なフィールド指定が含まれています:
-- Create student_tab input table with JSON schema
DROP TABLE IF EXISTS student_tab;
CREATE TABLE student_tab (
data JSON VALIDATE '{
"title": "student",
"type": "object",
"properties": {
"studentId": { "type": "number" },
"name": { "type": "string", "maxLength": 100 },
"age": { "type": ["number", "string"] },
"advisorId": { "type": "number" },
"courses": {
"type": "array",
"items": {
"type": "object",
"properties": {
"courseNumber": { "type": "string" },
"name": { "type": "string", "maxLength": 100 },
"avgGrade": { "type": ["number", "string"] }
},
"required": ["courseNumber", "name", "avgGrade"]
}
},
"dormitory": {
"type": "object",
"properties": {
"dormId": { "type": "number" },
"dormName": { "type": "string", "maxLength": 100 }
},
"required": ["dormId", "dormName"]
}
},
"required": ["studentId", "name", "age", "advisorId", "courses", "dormitory"]
}'
);
-- Create course_tab input table with JSON schema
DROP TABLE IF EXISTS course_tab;
CREATE TABLE course_tab (
data JSON VALIDATE '{
"title": "course",
"type": "object",
"properties": {
"courseId": { "type": "string" },
"name": { "type": "string", "maxLength": 100 },
"creditHours": { "type": ["number", "string"] },
"students": {
"type": "array",
"items": {
"type": "object",
"properties": {
"studentId": { "type": "number" },
"name": { "type": "string", "maxLength": 100 }
},
"required": ["studentId", "name"]
}
},
"teacher": {
"type": "object",
"properties": {
"teacherId": { "type": "number" },
"name": { "type": "string", "maxLength": 100 }
},
"required": ["teacherId", "name"]
},
"Notes": { "type": "string" }
},
"required": ["courseId", "name", "creditHours", "students", "teacher"]
}'
);
-- Create teacher_tab input table with JSON schema
DROP TABLE IF EXISTS teacher_tab;
CREATE TABLE teacher_tab (
data JSON VALIDATE '{
"title": "teacher",
"type": "object",
"properties": {
"_id": { "type": "number" },
"name": { "type": "string", "maxLength": 100 },
"phoneNumber": {
"type": ["string", "array"]
},
"salary": { "type": "number" },
"department": { "type": "string", "maxLength": 100 },
"coursesTaught": {
"type": "array",
"items": {
"type": "object",
"properties": {
"courseId": { "type": "string" },
"name": { "type": "string", "maxLength": 100 },
"classType": { "type": "string", "enum": ["Online", "In-person"] }
},
"required": ["courseId", "name", "classType"]
}
},
"studentsAdvised": {
"type": "array",
"items": {
"type": "object",
"properties": {
"studentId": { "type": "number" },
"name": { "type": "string", "maxLength": 100 },
"dormId": { "type": "number" }
},
"required": ["studentId", "name", "dormId"]
}
}
},
"required": ["_id", "name", "phoneNumber", "salary", "department", "coursesTaught", "studentsAdvised"]
}'
);例21-24 JSONスキーマのみからのDDLスキーマの生成
次のSQLコードでは、コンバータ・ファンクションINFER_AND_GENERATE_SCHEMAによってJSONスキーマ定義が処理されて、必要なデータベース・オブジェクトが生成されます:
DECLARE
ddl_sql CLOB;
BEGIN
ddl_sql :=
DBMS_JSON_DUALITY.infer_and_generate_schema(
JSON ('{"tableNames" : [ "STUDENT_TAB", "TEACHER_TAB", "COURSE_TAB"],
"viewNames" : [ "STUDENT", "TEACHER", "COURSE" ]}'));
EXECUTE IMMEDIATE ddl_sql;
END;
/
例21-25 INFER_AND_GENERATE_SCHEMAを使用したDDL出力の生成
次のSQLは、INFER_AND_GENERATE_SCHEMAによって生成されるリレーショナル・スキーマの内容を示しています:
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE course_teacher(
name varchar2(128),
ora$id number GENERATED BY DEFAULT ON NULL AS IDENTITY,
teacher_id number,
ora$course_flex JSON(Object),
PRIMARY KEY(ora$id)
)';
EXECUTE IMMEDIATE 'CREATE TABLE student_dormitory(
ora$id number GENERATED BY DEFAULT ON NULL AS IDENTITY,
dorm_id number,
dorm_name varchar2(128),
ora$student_flex JSON(Object),
PRIMARY KEY(ora$id)
)';
EXECUTE IMMEDIATE 'CREATE TABLE course_students(
name varchar2(128),
ora$id number GENERATED BY DEFAULT ON NULL AS IDENTITY,
student_id number,
ora$id_course_root number,
ora$course_flex JSON(Object),
PRIMARY KEY(ora$id)
)';
EXECUTE IMMEDIATE 'CREATE TABLE teacher_root(
"_id" number GENERATED BY DEFAULT ON NULL AS IDENTITY,
name varchar2(128),
salary number,
department varchar2(128),
phone_number json VALIDATE CAST USING ''{"oneOf" : [{ "type" :"string"}, { "type" :"array"}]}'',
ora$teacher_flex JSON(Object),
PRIMARY KEY("_id")
)';
EXECUTE IMMEDIATE 'CREATE TABLE student_courses(
name varchar2(128),
ora$id number GENERATED BY DEFAULT ON NULL AS IDENTITY,
avg_grade json VALIDATE CAST USING ''{"oneOf" : [{ "type" :"number"}, { "type" :"string"}]}'',
course_number varchar2(64),
ora$id_student_root number,
ora$student_flex JSON(Object),
PRIMARY KEY(ora$id)
)';
EXECUTE IMMEDIATE 'CREATE TABLE teacher_courses_taught(
name varchar2(128),
ora$id number GENERATED BY DEFAULT ON NULL AS IDENTITY,
course_id varchar2(64),
class_type varchar2(64),
"_id_teacher_root" number,
ora$teacher_flex JSON(Object),
PRIMARY KEY(ora$id)
)';
EXECUTE IMMEDIATE 'CREATE TABLE teacher_students_advised(
name varchar2(128),
ora$id number GENERATED BY DEFAULT ON NULL AS IDENTITY,
dorm_id number,
student_id number,
"_id_teacher_root" number,
ora$teacher_flex JSON(Object),
PRIMARY KEY(ora$id)
)';
EXECUTE IMMEDIATE 'CREATE TABLE course_root(
name varchar2(128),
notes varchar2(64),
ora$id number GENERATED BY DEFAULT ON NULL AS IDENTITY,
course_id varchar2(64),
credit_hours json VALIDATE CAST USING ''{"oneOf" : [{ "type" :"number"}, { "type" :"string"}]}'',
ora$id_course_teacher number,
ora$course_flex JSON(Object),
PRIMARY KEY(ora$id)
)';
EXECUTE IMMEDIATE 'CREATE TABLE student_root(
age json VALIDATE CAST USING ''{"oneOf" : [{ "type" :"number"}, { "type" :"string"}]}'',
name varchar2(128),
ora$id number GENERATED BY DEFAULT ON NULL AS IDENTITY,
advisor_id number,
student_id number,
ora$id_student_dormitory number,
ora$student_flex JSON(Object),
PRIMARY KEY(ora$id)
)';
EXECUTE IMMEDIATE 'ALTER TABLE course_students
ADD CONSTRAINT fk_course_students_to_course_root FOREIGN KEY (ora$id_course_root) REFERENCES course_root(ora$id) DEFERRABLE';
EXECUTE IMMEDIATE 'ALTER TABLE student_courses
ADD CONSTRAINT fk_student_courses_to_student_root FOREIGN KEY (ora$id_student_root) REFERENCES student_root(ora$id) DEFERRABLE';
EXECUTE IMMEDIATE 'ALTER TABLE teacher_courses_taught
ADD CONSTRAINT fk_teacher_courses_taught_to_teacher_root FOREIGN KEY ("_id_teacher_root") REFERENCES teacher_root("_id") DEFERRABLE';
EXECUTE IMMEDIATE 'ALTER TABLE teacher_students_advised
ADD CONSTRAINT fk_teacher_students_advised_to_teacher_root FOREIGN KEY ("_id_teacher_root") REFERENCES teacher_root("_id") DEFERRABLE';
EXECUTE IMMEDIATE 'ALTER TABLE course_root
ADD CONSTRAINT fk_course_root_to_course_teacher FOREIGN KEY (ora$id_course_teacher) REFERENCES course_teacher(ora$id) DEFERRABLE';
EXECUTE IMMEDIATE 'ALTER TABLE student_root
ADD CONSTRAINT fk_student_root_to_student_dormitory FOREIGN KEY (ora$id_student_dormitory) REFERENCES student_dormitory(ora$id) DEFERRABLE';
EXECUTE IMMEDIATE 'CREATE INDEX IF NOT EXISTS fk_course_students_to_course_root_index ON course_students(ora$id_course_root)';
EXECUTE IMMEDIATE 'CREATE INDEX IF NOT EXISTS fk_student_courses_to_student_root_index ON student_courses(ora$id_student_root)';
EXECUTE IMMEDIATE 'CREATE INDEX IF NOT EXISTS fk_teacher_courses_taught_to_teacher_root_index ON teacher_courses_taught("_id_teacher_root")';
EXECUTE IMMEDIATE 'CREATE INDEX IF NOT EXISTS fk_teacher_students_advised_to_teacher_root_index ON teacher_students_advised("_id_teacher_root")';
EXECUTE IMMEDIATE 'CREATE INDEX IF NOT EXISTS fk_course_root_to_course_teacher_index ON course_root(ora$id_course_teacher)';
EXECUTE IMMEDIATE 'CREATE INDEX IF NOT EXISTS fk_student_root_to_student_dormitory_index ON student_root(ora$id_student_dormitory)';
EXECUTE IMMEDIATE 'CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW STUDENT AS
student_root @insert @update @delete
{
_id : ora$id
studentId: student_id
name
age
advisorId: advisor_id
courses: student_courses @insert @update @delete @array
[{
courseNumber: course_number
name
avgGrade: avg_grade
ora$id
ora$student_flex @flex
}]
dormitory: student_dormitory @insert @update @object
{
dormId: dorm_id
dormName: dorm_name
ora$id
ora$student_flex @flex
}
ora$student_flex @flex
}';
EXECUTE IMMEDIATE 'CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW TEACHER AS
teacher_root @insert @update @delete
{
"_id"
name
phoneNumber: phone_number
salary
department
coursesTaught: teacher_courses_taught @insert @update @delete @array
[{
courseId: course_id
name
classType: class_type
ora$id
ora$teacher_flex @flex
}]
studentsAdvised: teacher_students_advised @insert @update @delete @array
[{
studentId: student_id
name
dormId: dorm_id
ora$id
ora$teacher_flex @flex
}]
ora$teacher_flex @flex
}';
EXECUTE IMMEDIATE 'CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW COURSE AS
course_root @insert @update @delete
{
_id : ora$id
courseId: course_id
name
creditHours: credit_hours
students: course_students @insert @update @delete @array
[{
studentId: student_id
name
ora$id
ora$course_flex @flex
}]
teacher: course_teacher @insert @update @object
{
teacherId: teacher_id
name
ora$id
ora$course_flex @flex
}
Notes: notes
ora$course_flex @flex
}';
END;
例21-26 データなしで定義されたリレーショナル・スキーマの場合のデータ・インポートおよびテスト
コンバータを使用してリレーショナル・スキーマを作成した後で、入力表にデータをインポートできます。その後、インポータによってそのデータが処理されて二面性ビューに移入されます:
この例では、このトピックで説明しているOracle Database外部表(dataset_student、dataset_teacherおよびdataset_course)に格納されている3つの入力ドキュメント・セットを使用します:コンバータを使用する前に(1): データベース・ドキュメント・セットの作成
このトピックでは、それらの入力ドキュメントを、よりコンパクトなバージョンで示します:
そのSQLコードは次のようになります:
-- Verify empty input tables (schema-only conversion)
SELECT json_serialize(data PRETTY) FROM student_tab;
SELECT json_serialize(data PRETTY) FROM teacher_tab;
SELECT json_serialize(data PRETTY) FROM course_tab;
-- Insert data from source datasets
INSERT INTO student_tab SELECT data FROM dataset_student;
INSERT INTO teacher_tab SELECT data FROM dataset_teacher;
INSERT INTO course_tab SELECT data FROM dataset_course;
COMMIT;
-- Create error log tables
BEGIN
DBMS_ERRLOG.create_error_log(dml_table_name => 'COURSE',
err_log_table_name => 'COURSE_ERR_LOG',
skip_unsupported => TRUE);
DBMS_ERRLOG.create_error_log(dml_table_name => 'TEACHER',
err_log_table_name => 'TEACHER_ERR_LOG',
skip_unsupported => TRUE);
DBMS_ERRLOG.create_error_log(dml_table_name => 'STUDENT',
err_log_table_name => 'STUDENT_ERR_LOG',
skip_unsupported => TRUE);
END;
/
-- Import data into duality views using the importer
BEGIN
DBMS_JSON_DUALITY.import_all(JSON('{"tableNames" : [ "STUDENT_TAB", "TEACHER_TAB", "COURSE_TAB" ],
"viewNames" : [ "STUDENT", "TEACHER", "COURSE" ],
"errorLog" : [ "STUDENT_ERR_LOG", "TEACHER_ERR_LOG", "COURSE_ERR_LOG" ]}'));
END;
/
インポート結果:
SQL> -- CREATE ERROR LOG TABLES
SQL> BEGIN
2 DBMS_ERRLOG.create_error_log(dml_table_name => 'COURSE',
3 err_log_table_name => 'COURSE_ERR_LOG',
4 skip_unsupported => TRUE);
5 DBMS_ERRLOG.create_error_log(dml_table_name => 'TEACHER',
6 err_log_table_name => 'TEACHER_ERR_LOG',
7 skip_unsupported => TRUE);
8 DBMS_ERRLOG.create_error_log(dml_table_name => 'STUDENT',
9 err_log_table_name => 'STUDENT_ERR_LOG',
10 skip_unsupported => TRUE);
11 END;
12 /
PL/SQL procedure successfully completed.
SQL>
SQL> -- Import the data in the input tables into the duality views created using import_all method
SQL> BEGIN
2 DBMS_JSON_DUALITY.import_all(JSON('{"tableNames" : [ "STUDENT_TAB", "TEACHER_TAB", "COURSE_TAB" ],
3 "viewNames" : [ "STUDENT", "TEACHER", "COURSE" ],
4 "errorLog" : [ "STUDENT_ERR_LOG", "TEACHER_ERR_LOG", "COURSE_ERR_LOG" ]}'));
5 END;
6 /
PL/SQL procedure successfully completed.
インポート・データとエラー・ログ・データを検証します。
-- Verify data after import
SELECT json_serialize(data PRETTY) FROM student;
SELECT json_serialize(data PRETTY) FROM teacher;
SELECT json_serialize(data PRETTY) FROM course;
-- check error logs
SELECT ora_err_number$, ora_err_mesg$, ora_err_tag$ FROM student_err_log;
SELECT ora_err_number$, ora_err_mesg$, ora_err_tag$ FROM teacher_err_log;
SELECT ora_err_number$, ora_err_mesg$, ora_err_tag$ FROM course_err_log;
ログ・ファイルを検証します:
SQL> -- Verify data after import
SQL> SELECT json_serialize(data PRETTY) FROM student;
JSON_SERIALIZE(DATAPRETTY)
--------------------------------------------------------------------------------
{
"_id" : 1,
"_metadata" :
{
"etag" : "F0BB009B61526E8EBD946892BFE652D5",
"asof" : "0000000000331A3D"
},
"studentId" : 1,
"name" : "Donald P.",
"age" : 20,
"advisorId" : 102,
"courses" :
[
{
"courseNumber" : "MATH101",
"name" : "Algebra",
"avgGrade" : 90,
"ora$id" : 1
},
{
"courseNumber" : "CS102",
"name" : "Data Structures",
"avgGrade" : "TBD",
"ora$id" : 2
},
{
"courseNumber" : "CS101",
"name" : "Algorithms",
"avgGrade" : 75,
"ora$id" : 3
}
],
"dormitory" :
{
"dormId" : 201,
"dormName" : "ABC",
"ora$id" : 1
}
}
{
"_id" : 2,
"_metadata" :
{
"etag" : "3446D64543E7D02471614A6DBCF256C9",
"asof" : "0000000000331A3D"
},
"studentId" : 2,
"name" : "Elena H.",
"age" : 21,
"advisorId" : 103,
"courses" :
[
{
"courseNumber" : "MATH102",
"name" : "Calculus",
"avgGrade" : 95,
"ora$id" : 4
},
{
"courseNumber" : "CS102",
"name" : "Data Structures",
"avgGrade" : "TBD",
"ora$id" : 5
},
{
"courseNumber" : "CS101",
"name" : "Algorithms",
"avgGrade" : 75,
"ora$id" : 6
}
],
"dormitory" :
{
"dormId" : 202,
"dormName" : "XYZ",
"ora$id" : 2
}
}
{
"_id" : 3,
"_metadata" :
{
"etag" : "0F91B5AAD26CF62EF879F8CDF590E359",
"asof" : "0000000000331A3D"
},
"studentId" : 3,
"name" : "Francis K.",
"age" : 20,
"advisorId" : 103,
"courses" :
[
{
"courseNumber" : "MATH103",
"name" : "Advanced Algebra",
"avgGrade" : 82,
"ora$id" : 7
}
],
"dormitory" :
{
"dormId" : 204,
"dormName" : "QWE",
"ora$id" : 3
}
}
{
"_id" : 4,
"_metadata" :
{
"etag" : "27AB02546179A05E538E28EDEFE680A9",
"asof" : "0000000000331A3D"
},
"studentId" : 4,
"name" : "Georgia D.",
"age" : 19,
"advisorId" : 101,
"courses" :
[
{
"courseNumber" : "MATH103",
"name" : "Advanced Algebra",
"avgGrade" : 82,
"ora$id" : 8
},
{
"courseNumber" : "MATH102",
"name" : "Calculus",
"avgGrade" : 95,
"ora$id" : 9
},
{
"courseNumber" : "CS101",
"name" : "Algorithms",
"avgGrade" : 75,
"ora$id" : 10
}
],
"dormitory" :
{
"dormId" : 203,
"dormName" : "LMN",
"ora$id" : 4
}
}
{
"_id" : 5,
"_metadata" :
{
"etag" : "E5EA57B504C820A896706E517E0CB1A7",
"asof" : "0000000000331A3D"
},
"studentId" : 5,
"name" : "Hye E.",
"age" : 21,
"advisorId" : 103,
"courses" :
[
{
"courseNumber" : "MATH101",
"name" : "Algebra",
"avgGrade" : 90,
"ora$id" : 11
},
{
"courseNumber" : "CS102",
"name" : "Data Structures",
"avgGrade" : "TBD",
"ora$id" : 12
}
],
"dormitory" :
{
"dormId" : 201,
"dormName" : "ABC",
"ora$id" : 5
}
}
{
"_id" : 6,
"_metadata" :
{
"etag" : "C39E4C365410FD0A6D8C5903C0D86E3B",
"asof" : "0000000000331A3D"
},
"studentId" : 6,
"name" : "Ileana D.",
"age" : 21,
"advisorId" : 102,
"courses" :
[
{
"courseNumber" : "MATH103",
"name" : "Advanced Algebra",
"avgGrade" : 82,
"ora$id" : 13
}
],
"dormitory" :
{
"dormId" : 205,
"dormName" : "GHI",
"ora$id" : 6
}
}
{
"_id" : 7,
"_metadata" :
{
"etag" : "E2751BF24D34E1973695FEDDBCC47BE9",
"asof" : "0000000000331A3D"
},
"studentId" : 7,
"name" : "Jatin S.",
"age" : 20,
"advisorId" : 101,
"courses" :
[
{
"courseNumber" : "CS102",
"name" : "Data Structures",
"avgGrade" : "TBD",
"ora$id" : 14
},
{
"courseNumber" : "CS101",
"name" : "Algorithms",
"avgGrade" : 75,
"ora$id" : 15
}
],
"dormitory" :
{
"dormId" : 204,
"dormName" : "QWE",
"ora$id" : 7
}
}
{
"_id" : 8,
"_metadata" :
{
"etag" : "1F113FE52C21DBBDD71B1CA47E48F517",
"asof" : "0000000000331A3D"
},
"studentId" : 8,
"name" : "Katie H.",
"age" : 21,
"advisorId" : 102,
"courses" :
[
{
"courseNumber" : "MATH103",
"name" : "Advanced Algebra",
"avgGrade" : 82,
"ora$id" : 16
},
{
"courseNumber" : "CS102",
"name" : "Data Structures",
"avgGrade" : "TBD",
"ora$id" : 17
}
],
"dormitory" :
{
"dormId" : 205,
"dormName" : "GHI",
"ora$id" : 8
}
}
{
"_id" : 9,
"_metadata" :
{
"etag" : "515C42A82BAA59A00C4006D6A59FAB33",
"asof" : "0000000000331A3D"
},
"studentId" : 9,
"name" : "Luis F.",
"age" : "Nineteen",
"advisorId" : 101,
"courses" :
[
{
"courseNumber" : "MATH103",
"name" : "Advanced Algebra",
"avgGrade" : 82,
"ora$id" : 18
},
{
"courseNumber" : "MATH102",
"name" : "Calculus",
"avgGrade" : 95,
"ora$id" : 19
},
{
"courseNumber" : "CS101",
"name" : "Algorithms",
"avgGrade" : 75,
"ora$id" : 20
}
],
"dormitory" :
{
"dormId" : 201,
"dormName" : "ABC",
"ora$id" : 9
}
}
{
"_id" : 10,
"_metadata" :
{
"etag" : "E13468279294236B728DA174F6A9720A",
"asof" : "0000000000331A3D"
},
"studentId" : 10,
"name" : "Ming L.",
"age" : 20,
"advisorId" : 101,
"courses" :
[
{
"courseNumber" : "MATH102",
"name" : "Calculus",
"avgGrade" : 95,
"ora$id" : 21
}
],
"dormitory" :
{
"dormId" : 202,
"dormName" : "XYZ",
"ora$id" : 10
}
}
10 rows selected.
SQL> SELECT json_serialize(data PRETTY) FROM teacher;
JSON_SERIALIZE(DATAPRETTY)
--------------------------------------------------------------------------------
{
"_id" : 101,
"_metadata" :
{
"etag" : "CE2DA3E57BD4172E78D0398350683F68",
"asof" : "0000000000331A43"
},
"name" : "Abdul J.",
"phoneNumber" :
[
"222-555-011",
"222-555-012"
],
"salary" : 200000,
"department" : "Mathematics",
"coursesTaught" :
[
{
"courseId" : "MATH102",
"name" : "Calculus",
"classType" : "In-person",
"ora$id" : 1
},
{
"courseId" : "MATH101",
"name" : "Algebra",
"classType" : "Online",
"ora$id" : 2
}
],
"studentsAdvised" :
[
{
"studentId" : 9,
"name" : "Luis F.",
"dormId" : 201,
"ora$id" : 1
},
{
"studentId" : 4,
"name" : "Georgia D.",
"dormId" : 203,
"ora$id" : 2
},
{
"studentId" : 10,
"name" : "Ming L.",
"dormId" : 202,
"ora$id" : 3
},
{
"studentId" : 7,
"name" : "Jatin S.",
"dormId" : 204,
"ora$id" : 4
}
]
}
{
"_id" : 102,
"_metadata" :
{
"etag" : "55C8549D080C1A7FC683E3E2379D09A8",
"asof" : "0000000000331A43"
},
"name" : "Betty Z.",
"phoneNumber" : "222-555-022",
"salary" : 300000,
"department" : "Computer Science",
"coursesTaught" :
[
{
"courseId" : "CS102",
"name" : "Data Structures",
"classType" : "In-person",
"ora$id" : 3
},
{
"courseId" : "CS101",
"name" : "Algorithms",
"classType" : "Online",
"ora$id" : 4
}
],
"studentsAdvised" :
[
{
"studentId" : 8,
"name" : "Katie H.",
"dormId" : 205,
"ora$id" : 5
},
{
"studentId" : 6,
"name" : "Ileana D.",
"dormId" : 205,
"ora$id" : 6
},
{
"studentId" : 1,
"name" : "Donald P.",
"dormId" : 201,
"ora$id" : 7
}
]
}
{
"_id" : 103,
"_metadata" :
{
"etag" : "90D13738146638E2FC1B016B191C0713",
"asof" : "0000000000331A43"
},
"name" : "Colin J.",
"phoneNumber" :
[
"222-555-023"
],
"salary" : 220000,
"department" : "Mathematics",
"coursesTaught" :
[
{
"courseId" : "MATH103",
"name" : "Advanced Algebra",
"classType" : "Online",
"ora$id" : 5
}
],
"studentsAdvised" :
[
{
"studentId" : 5,
"name" : "Hye E.",
"dormId" : 201,
"ora$id" : 8
},
{
"studentId" : 3,
"name" : "Francis K.",
"dormId" : 204,
"ora$id" : 9
},
{
"studentId" : 2,
"name" : "Elena H.",
"dormId" : 202,
"ora$id" : 10
}
]
}
{
"_id" : 104,
"_metadata" :
{
"etag" : "D4D644FB68590D5A00EC53778F0E7226",
"asof" : "0000000000331A43"
},
"name" : "Natalie C.",
"phoneNumber" : "222-555-044",
"salary" : 180000,
"department" : "Computer Science",
"coursesTaught" :
[
],
"studentsAdvised" :
[
]
}
SQL> SELECT json_serialize(data PRETTY) FROM course;
JSON_SERIALIZE(DATAPRETTY)
--------------------------------------------------------------------------------
{
"_id" : 1,
"_metadata" :
{
"etag" : "CCB9A27CFA882216E1FD17E01A5AB715",
"asof" : "0000000000331A49"
},
"courseId" : "MATH101",
"name" : "Algebra",
"creditHours" : 3,
"students" :
[
{
"studentId" : 5,
"name" : "Hye E.",
"ora$id" : 1
},
{
"studentId" : 1,
"name" : "Donald P.",
"ora$id" : 2
}
],
"teacher" :
{
"teacherId" : 101,
"name" : "Abdul J.",
"ora$id" : 1
},
"Notes" : "Prerequisite for Advanced Algebra"
}
{
"_id" : 2,
"_metadata" :
{
"etag" : "1453FD5A57994916112993C04CC504E7",
"asof" : "0000000000331A49"
},
"courseId" : "MATH102",
"name" : "Calculus",
"creditHours" : 4,
"students" :
[
{
"studentId" : 9,
"name" : "Luis F.",
"ora$id" : 3
},
{
"studentId" : 2,
"name" : "Elena H.",
"ora$id" : 4
},
{
"studentId" : 10,
"name" : "Ming L.",
"ora$id" : 5
},
{
"studentId" : 4,
"name" : "Georgia D.",
"ora$id" : 6
}
],
"teacher" :
{
"teacherId" : 101,
"name" : "Abdul J.",
"ora$id" : 2
},
"Notes" : null
}
{
"_id" : 3,
"_metadata" :
{
"etag" : "DB4D293C0FBE10479973F4E3C2EC9259",
"asof" : "0000000000331A49"
},
"courseId" : "CS101",
"name" : "Algorithms",
"creditHours" : 5,
"students" :
[
{
"studentId" : 2,
"name" : "Elena H.",
"ora$id" : 7
},
{
"studentId" : 1,
"name" : "Donald P.",
"ora$id" : 8
},
{
"studentId" : 7,
"name" : "Jatin S.",
"ora$id" : 9
},
{
"studentId" : 9,
"name" : "Luis F.",
"ora$id" : 10
},
{
"studentId" : 4,
"name" : "Georgia D.",
"ora$id" : 11
}
],
"teacher" :
{
"teacherId" : 102,
"name" : "Betty Z.",
"ora$id" : 3
},
"Notes" : null
}
{
"_id" : 4,
"_metadata" :
{
"etag" : "1EF009C45D1C6EE93824AB30B75FA47C",
"asof" : "0000000000331A49"
},
"courseId" : "CS102",
"name" : "Data Structures",
"creditHours" : 3,
"students" :
[
{
"studentId" : 2,
"name" : "Elena H.",
"ora$id" : 12
},
{
"studentId" : 1,
"name" : "Donald P.",
"ora$id" : 13
},
{
"studentId" : 7,
"name" : "Jatin S.",
"ora$id" : 14
},
{
"studentId" : 8,
"name" : "Katie H.",
"ora$id" : 15
},
{
"studentId" : 5,
"name" : "Hye E.",
"ora$id" : 16
}
],
"teacher" :
{
"teacherId" : 102,
"name" : "Betty Z.",
"ora$id" : 4
},
"Notes" : null
}
{
"_id" : 5,
"_metadata" :
{
"etag" : "0E6E4127968CB3744D7F3B20A2D2FC4C",
"asof" : "0000000000331A49"
},
"courseId" : "MATH103",
"name" : "Advanced Algebra",
"creditHours" : "3",
"students" :
[
{
"studentId" : 4,
"name" : "Georgia D.",
"ora$id" : 17
},
{
"studentId" : 3,
"name" : "Francis K.",
"ora$id" : 18
},
{
"studentId" : 8,
"name" : "Katie H.",
"ora$id" : 19
},
{
"studentId" : 9,
"name" : "Luis F.",
"ora$id" : 20
},
{
"studentId" : 6,
"name" : "Ileana D.",
"ora$id" : 21
}
],
"teacher" :
{
"teacherId" : 103,
"name" : "Colin J.",
"ora$id" : 5
},
"Notes" : null
}
SQL>
SQL> -- check error logs
SQL> SELECT ora_err_number$, ora_err_mesg$, ora_err_tag$ FROM student_err_log;
no rows selected
SQL> SELECT ora_err_number$, ora_err_mesg$, ora_err_tag$ FROM teacher_err_log;
no rows selected
SQL> SELECT ora_err_number$, ora_err_mesg$, ora_err_tag$ FROM course_err_log;
no rows selected親トピック: 定義済JSONスキーマでのコンバータの使用
21.11.4 入力データがあるJSONスキーマの使用によるリレーショナル・スキーマの推測の例
JSON列に対してJSONスキーマを定義し、同時に入力表にデータを挿入します。INFER_AND_GENERATE_SCHEMAファンクションでは、そのスキーマとデータの両方を使用して、より正確で完全なリレーショナル・データベース・スキーマが作成されます。
この例では、コンバータ・ファンクションをコールする前にJSONスキーマ定義を使用するとともに同時にJSONデータを挿入することでデータベース入力表を作成する方法を示します。コンバータでは、明示的なJSONスキーマ制約と実際のJSONデータ・パターンを両方使用して、最適化されたリレーショナル・データベース・スキーマが生成されます。JSONスキーマにおいて定義されているフィールドと、挿入されたデータに存在するフィールドとが異なる可能性があるため、両方のソースが、最終的な正規化されたスキーマに寄与します。
例21-27 拡張されたJSONスキーマの使用によるデータベース入力表の作成
次のSQLコード例では、元のデータに存在しないフィールドがさらに含まれているJSONスキーマを使用した入力表を示し、スキーマの柔軟性を示しています:
-- Create student_tab input table with JSON schema
-- "Note: The dormLocation field is added to the JSON schema but is not required when importing data from
-- the input table. The dorm_location column will be added to the student_dormitory table after
-- applying the converter function."
DROP TABLE IF EXISTS student_tab;
CREATE TABLE student_tab (
data JSON VALIDATE '{
"title": "student",
"type": "object",
"properties": {
"studentId": { "type": "number" },
"name": { "type": "string", "maxLength": 100 },
"advisorId": { "type": "number" },
"age": { "type": ["number", "string"] },
"courses": {
"type": "array",
"items": {
"type": "object",
"properties": {
"courseNumber": { "type": "string" },
"name": { "type": "string", "maxLength": 100 },
"avgGrade": { "type": ["number", "string"] }
},
"required": ["courseNumber", "name", "avgGrade"]
}
},
"dormitory": {
"type": "object",
"properties": {
"dormId": { "type": "number" },
"dormName": { "type": "string", "maxLength": 100 },
"dormLocation": { "type": "string", "maxLength": 100 }
},
"required": ["dormId", "dormName"]
}
},
"required": ["studentId", "name", "age", "advisorId", "courses", "dormitory"]
}'
);
-- Create course_tab input table with JSON schema
-- The 'location' field is added to schema and will create location column in course_root table
DROP TABLE IF EXISTS course_tab;
CREATE TABLE course_tab (
data JSON VALIDATE '{
"title": "course",
"type": "object",
"properties": {
"courseId": { "type": "string" },
"name": { "type": "string", "maxLength": 100 },
"creditHours": { "type": ["number", "string"] },
"location": { "type": "string" },
"students": {
"type": "array",
"items": {
"type": "object",
"properties": {
"studentId": { "type": "number" },
"name": { "type": "string", "maxLength": 100 }
},
"required": ["studentId", "name"]
}
},
"teacher": {
"type": "object",
"properties": {
"teacherId": { "type": "number" },
"name": { "type": "string", "maxLength": 100 }
},
"required": ["teacherId", "name"]
},
"Notes": { "type": "string" }
},
"required": ["courseId", "name", "creditHours", "students", "teacher"]
}'
);
-- Create teacher_tab input table with JSON schema
-- emailId field added to schema, phoneNumber omitted from schema but present in data
DROP TABLE IF EXISTS teacher_tab;
CREATE TABLE teacher_tab (
data JSON VALIDATE '{
"title": "teacher",
"type": "object",
"properties": {
"_id": { "type": "number" },
"name": { "type": "string", "maxLength": 100 },
"emailId": { "type": "string" },
"salary": { "type": "number" },
"department": { "type": "string", "maxLength": 100 },
"coursesTaught": {
"type": "array",
"items": {
"type": "object",
"properties": {
"courseId": { "type": "string" },
"name": { "type": "string", "maxLength": 100 },
"classType": { "type": "string", "enum": ["Online", "In-person"] }
},
"required": ["courseId", "name", "classType"]
}
},
"studentsAdvised": {
"type": "array",
"items": {
"type": "object",
"properties": {
"studentId": { "type": "number" },
"name": { "type": "string", "maxLength": 100 },
"dormId": { "type": "number" }
},
"required": ["studentId", "name", "dormId"]
}
}
},
"required": ["_id", "name", "salary", "department", "coursesTaught", "studentsAdvised"]
}'
);
例21-28 データベース・スキーマ生成前のJSONデータの挿入
JSONスキーマとデータの複合移行では、コンバータ・ファンクションをコールする前に、JSONデータがデータベース入力表に挿入されます:
-- Insert data into input tables before schema generation
INSERT INTO student_tab SELECT data FROM dataset_student;
INSERT INTO teacher_tab SELECT data FROM dataset_teacher;
INSERT INTO course_tab SELECT data FROM dataset_course;
COMMIT;
-- Validate inserted data
SELECT json_serialize(data PRETTY) FROM student_tab;
SELECT json_serialize(data PRETTY) FROM teacher_tab;
SELECT json_serialize(data PRETTY) FROM course_tab;
例21-29 データ検証の出力
SQL> SELECT json_serialize(data PRETTY) FROM student_tab;
JSON_SERIALIZE(DATAPRETTY)
--------------------------------------------------------------------------------
{
"studentId" : 1,
"name" : "Donald P.",
"age" : 20,
"advisorId" : 102,
"courses" :
[
{
"courseNumber" : "CS101",
"name" : "Algorithms",
"avgGrade" : 75
},
{
"courseNumber" : "CS102",
"name" : "Data Structures",
"avgGrade" : "TBD"
},
{
"courseNumber" : "MATH101",
"name" : "Algebra",
"avgGrade" : 90
}
],
"dormitory" :
{
"dormId" : 201,
"dormName" : "ABC"
}
}
{
"studentId" : 2,
"name" : "Elena H.",
"age" : 21,
"advisorId" : 103,
"courses" :
[
{
"courseNumber" : "CS101",
"name" : "Algorithms",
"avgGrade" : 75
},
{
"courseNumber" : "CS102",
"name" : "Data Structures",
"avgGrade" : "TBD"
},
{
"courseNumber" : "MATH102",
"name" : "Calculus",
"avgGrade" : 95
}
],
"dormitory" :
{
"dormId" : 202,
"dormName" : "XYZ"
}
}
{
"studentId" : 3,
"name" : "Francis K.",
"age" : 20,
"advisorId" : 103,
"courses" :
[
{
"courseNumber" : "MATH103",
"name" : "Advanced Algebra",
"avgGrade" : 82
}
],
"dormitory" :
{
"dormId" : 204,
"dormName" : "QWE"
}
}
{
"studentId" : 4,
"name" : "Georgia D.",
"age" : 19,
"advisorId" : 101,
"courses" :
[
{
"courseNumber" : "CS101",
"name" : "Algorithms",
"avgGrade" : 75
},
{
"courseNumber" : "MATH102",
"name" : "Calculus",
"avgGrade" : 95
},
{
"courseNumber" : "MATH103",
"name" : "Advanced Algebra",
"avgGrade" : 82
}
],
"dormitory" :
{
"dormId" : 203,
"dormName" : "LMN"
}
}
{
"studentId" : 5,
"name" : "Hye E.",
"age" : 21,
"advisorId" : 103,
"courses" :
[
{
"courseNumber" : "CS102",
"name" : "Data Structures",
"avgGrade" : "TBD"
},
{
"courseNumber" : "MATH101",
"name" : "Algebra",
"avgGrade" : 90
}
],
"dormitory" :
{
"dormId" : 201,
"dormName" : "ABC"
}
}
{
"studentId" : 6,
"name" : "Ileana D.",
"age" : 21,
"advisorId" : 102,
"courses" :
[
{
"courseNumber" : "MATH103",
"name" : "Advanced Algebra",
"avgGrade" : 82
}
],
"dormitory" :
{
"dormId" : 205,
"dormName" : "GHI"
}
}
{
"studentId" : 7,
"name" : "Jatin S.",
"age" : 20,
"advisorId" : 101,
"courses" :
[
{
"courseNumber" : "CS101",
"name" : "Algorithms",
"avgGrade" : 75
},
{
"courseNumber" : "CS102",
"name" : "Data Structures",
"avgGrade" : "TBD"
}
],
"dormitory" :
{
"dormId" : 204,
"dormName" : "QWE"
}
}
{
"studentId" : 8,
"name" : "Katie H.",
"age" : 21,
"advisorId" : 102,
"courses" :
[
{
"courseNumber" : "CS102",
"name" : "Data Structures",
"avgGrade" : "TBD"
},
{
"courseNumber" : "MATH103",
"name" : "Advanced Algebra",
"avgGrade" : 82
}
],
"dormitory" :
{
"dormId" : 205,
"dormName" : "GHI"
}
}
{
"studentId" : 9,
"name" : "Luis F.",
"age" : "Nineteen",
"advisorId" : 101,
"courses" :
[
{
"courseNumber" : "CS101",
"name" : "Algorithms",
"avgGrade" : 75
},
{
"courseNumber" : "MATH102",
"name" : "Calculus",
"avgGrade" : 95
},
{
"courseNumber" : "MATH103",
"name" : "Advanced Algebra",
"avgGrade" : 82
}
],
"dormitory" :
{
"dormId" : 201,
"dormName" : "ABC"
}
}
{
"studentId" : 10,
"name" : "Ming L.",
"age" : 20,
"advisorId" : 101,
"courses" :
[
{
"courseNumber" : "MATH102",
"name" : "Calculus",
"avgGrade" : 95
}
],
"dormitory" :
{
"dormId" : 202,
"dormName" : "XYZ"
}
}
10 rows selected.
SQL> SELECT json_serialize(data PRETTY) FROM teacher_tab;
JSON_SERIALIZE(DATAPRETTY)
--------------------------------------------------------------------------------
{
"_id" : 101,
"name" : "Abdul J.",
"phoneNumber" :
[
"222-555-011",
"222-555-012"
],
"salary" : 200000,
"department" : "Mathematics",
"coursesTaught" :
[
{
"courseId" : "MATH101",
"name" : "Algebra",
"classType" : "Online"
},
{
"courseId" : "MATH102",
"name" : "Calculus",
"classType" : "In-person"
}
],
"studentsAdvised" :
[
{
"studentId" : 4,
"name" : "Georgia D.",
"dormId" : 203
},
{
"studentId" : 7,
"name" : "Jatin S.",
"dormId" : 204
},
{
"studentId" : 9,
"name" : "Luis F.",
"dormId" : 201
},
{
"studentId" : 10,
"name" : "Ming L.",
"dormId" : 202
}
]
}
{
"_id" : 102,
"name" : "Betty Z.",
"phoneNumber" : "222-555-022",
"salary" : 300000,
"department" : "Computer Science",
"coursesTaught" :
[
{
"courseId" : "CS101",
"name" : "Algorithms",
"classType" : "Online"
},
{
"courseId" : "CS102",
"name" : "Data Structures",
"classType" : "In-person"
}
],
"studentsAdvised" :
[
{
"studentId" : 1,
"name" : "Donald P.",
"dormId" : 201
},
{
"studentId" : 6,
"name" : "Ileana D.",
"dormId" : 205
},
{
"studentId" : 8,
"name" : "Katie H.",
"dormId" : 205
}
]
}
{
"_id" : 103,
"name" : "Colin J.",
"phoneNumber" :
[
"222-555-023"
],
"salary" : 220000,
"department" : "Mathematics",
"coursesTaught" :
[
{
"courseId" : "MATH103",
"name" : "Advanced Algebra",
"classType" : "Online"
}
],
"studentsAdvised" :
[
{
"studentId" : 2,
"name" : "Elena H.",
"dormId" : 202
},
{
"studentId" : 3,
"name" : "Francis K.",
"dormId" : 204
},
{
"studentId" : 5,
"name" : "Hye E.",
"dormId" : 201
}
]
}
{
"_id" : 104,
"name" : "Natalie C.",
"phoneNumber" : "222-555-044",
"salary" : 180000,
"department" : "Computer Science",
"coursesTaught" :
[
],
"studentsAdvised" :
[
]
}
SQL> SELECT json_serialize(data PRETTY) FROM course_tab;
JSON_SERIALIZE(DATAPRETTY)
--------------------------------------------------------------------------------
{
"courseId" : "MATH101",
"name" : "Algebra",
"creditHours" : 3,
"students" :
[
{
"studentId" : 1,
"name" : "Donald P."
},
{
"studentId" : 5,
"name" : "Hye E."
}
],
"teacher" :
{
"teacherId" : 101,
"name" : "Abdul J."
},
"Notes" : "Prerequisite for Advanced Algebra"
}
{
"courseId" : "MATH102",
"name" : "Calculus",
"creditHours" : 4,
"students" :
[
{
"studentId" : 2,
"name" : "Elena H."
},
{
"studentId" : 4,
"name" : "Georgia D."
},
{
"studentId" : 9,
"name" : "Luis F."
},
{
"studentId" : 10,
"name" : "Ming L."
}
],
"teacher" :
{
"teacherId" : 101,
"name" : "Abdul J."
}
}
{
"courseId" : "CS101",
"name" : "Algorithms",
"creditHours" : 5,
"students" :
[
{
"studentId" : 1,
"name" : "Donald P."
},
{
"studentId" : 2,
"name" : "Elena H."
},
{
"studentId" : 4,
"name" : "Georgia D."
},
{
"studentId" : 7,
"name" : "Jatin S."
},
{
"studentId" : 9,
"name" : "Luis F."
}
],
"teacher" :
{
"teacherId" : 102,
"name" : "Betty Z."
}
}
{
"courseId" : "CS102",
"name" : "Data Structures",
"creditHours" : 3,
"students" :
[
{
"studentId" : 1,
"name" : "Donald P."
},
{
"studentId" : 2,
"name" : "Elena H."
},
{
"studentId" : 5,
"name" : "Hye E."
},
{
"studentId" : 7,
"name" : "Jatin S."
},
{
"studentId" : 8,
"name" : "Katie H."
}
],
"teacher" :
{
"teacherId" : 102,
"name" : "Betty Z."
}
}
{
"courseId" : "MATH103",
"name" : "Advanced Algebra",
"creditHours" : "3",
"students" :
[
{
"studentId" : 3,
"name" : "Francis K."
},
{
"studentId" : 4,
"name" : "Georgia D."
},
{
"studentId" : 6,
"name" : "Ileana D."
},
{
"studentId" : 8,
"name" : "Katie H."
},
{
"studentId" : 9,
"name" : "Luis F."
}
],
"teacher" :
{
"teacherId" : 103,
"name" : "Colin J."
}
}例21-30 INFER_AND_GENERATE_SCHEMAの使用によるJSONスキーマとデータの複合ソースからのDDL出力スキーマの生成
このコンバータ・ファンクションでは、JSONスキーマ定義と挿入されたJSONデータが両方処理されて、最適化されたデータベース・オブジェクトが生成されます:
DECLARE
ddl_sql CLOB;
BEGIN
ddl_sql :=
DBMS_JSON_DUALITY.infer_and_generate_schema(
JSON ('{"tableNames" : [ "STUDENT_TAB", "TEACHER_TAB", "COURSE_TAB"],
"viewNames" : [ "STUDENT", "TEACHER", "COURSE" ]}'));
EXECUTE IMMEDIATE ddl_sql;
END;
/
生成されたDDL出力は次のようになります:
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE course_teacher(
name varchar2(128) /* UNIQUE */,
teacher_id number GENERATED BY DEFAULT ON NULL AS IDENTITY,
ora$course_flex JSON(Object),
PRIMARY KEY(teacher_id)
)';
EXECUTE IMMEDIATE 'CREATE TABLE map_course_root_to_student_root(
map_course_id varchar2(64) DEFAULT ON NULL SYS_GUID(),
map_student_id number GENERATED BY DEFAULT ON NULL AS IDENTITY,
ora$student_flex JSON(Object),
ora$course_flex JSON(Object),
PRIMARY KEY(map_course_id,map_student_id)
)';
EXECUTE IMMEDIATE 'CREATE TABLE student_dormitory(
dorm_id number GENERATED BY DEFAULT ON NULL AS IDENTITY,
dorm_name varchar2(128) /* UNIQUE */,
dorm_location varchar2(128),
ora$student_flex JSON(Object),
PRIMARY KEY(dorm_id)
)';
EXECUTE IMMEDIATE 'CREATE TABLE student_root(
age json VALIDATE CAST USING ''{"oneOf" : [{ "type" :"number"}, { "type" :"string"}]}'',
name varchar2(128) /* UNIQUE */,
dorm_id number,
advisor_id number,
student_id number GENERATED BY DEFAULT ON NULL AS IDENTITY,
ora$student_flex JSON(Object),
ora$teacher_flex JSON(Object),
PRIMARY KEY(student_id)
)';
EXECUTE IMMEDIATE 'CREATE TABLE teacher_root(
"_id" number GENERATED BY DEFAULT ON NULL AS IDENTITY,
name varchar2(128) /* UNIQUE */,
salary number /* UNIQUE */,
email_id varchar2(64),
department varchar2(128),
phone_number json VALIDATE CAST USING ''{"oneOf" : [{ "type" :"string"}, { "type" :"array"}]}'',
ora$teacher_flex JSON(Object),
PRIMARY KEY("_id")
)';
EXECUTE IMMEDIATE 'CREATE TABLE course_root(
name varchar2(128) /* UNIQUE */,
notes varchar2(64),
location varchar2(64),
avg_grade json VALIDATE CAST USING ''{"oneOf" : [{ "type" :"number"}, { "type" :"string"}]}'',
course_id varchar2(64) DEFAULT ON NULL SYS_GUID(),
class_type varchar2(64),
credit_hours json VALIDATE CAST USING ''{"oneOf" : [{ "type" :"number"}, { "type" :"string"}]}'',
"_id_teacher_root" number,
teacher_id_course_teacher number,
ora$teacher_flex JSON(Object),
ora$course_flex JSON(Object),
PRIMARY KEY(course_id)
)';
EXECUTE IMMEDIATE 'ALTER TABLE map_course_root_to_student_root
ADD CONSTRAINT fk_map_course_root_to_student_root_to_course_root FOREIGN KEY (map_course_id) REFERENCES course_root(course_id) DEFERRABLE';
EXECUTE IMMEDIATE 'ALTER TABLE map_course_root_to_student_root
ADD CONSTRAINT fk_map_course_root_to_student_root_to_student_root FOREIGN KEY (map_student_id) REFERENCES student_root(student_id) DEFERRABLE';
EXECUTE IMMEDIATE 'ALTER TABLE student_root
ADD CONSTRAINT fk_student_root_to_teacher_root FOREIGN KEY (advisor_id) REFERENCES teacher_root("_id") DEFERRABLE';
EXECUTE IMMEDIATE 'ALTER TABLE student_root
ADD CONSTRAINT fk_student_root_to_student_dormitory FOREIGN KEY (dorm_id) REFERENCES student_dormitory(dorm_id) DEFERRABLE';
EXECUTE IMMEDIATE 'ALTER TABLE course_root
ADD CONSTRAINT fk_course_root_to_teacher_root FOREIGN KEY ("_id_teacher_root") REFERENCES teacher_root("_id") DEFERRABLE';
EXECUTE IMMEDIATE 'ALTER TABLE course_root
ADD CONSTRAINT fk_course_root_to_course_teacher FOREIGN KEY (teacher_id_course_teacher) REFERENCES course_teacher(teacher_id) DEFERRABLE';
EXECUTE IMMEDIATE 'CREATE INDEX IF NOT EXISTS fk_map_course_root_to_student_root_to_course_root_index ON map_course_root_to_student_root(map_course_id)';
EXECUTE IMMEDIATE 'CREATE INDEX IF NOT EXISTS fk_map_course_root_to_student_root_to_student_root_index ON map_course_root_to_student_root(map_student_id)';
EXECUTE IMMEDIATE 'CREATE INDEX IF NOT EXISTS fk_student_root_to_teacher_root_index ON student_root(advisor_id)';
EXECUTE IMMEDIATE 'CREATE INDEX IF NOT EXISTS fk_student_root_to_student_dormitory_index ON student_root(dorm_id)';
EXECUTE IMMEDIATE 'CREATE INDEX IF NOT EXISTS fk_course_root_to_teacher_root_index ON course_root("_id_teacher_root")';
EXECUTE IMMEDIATE 'CREATE INDEX IF NOT EXISTS fk_course_root_to_course_teacher_index ON course_root(teacher_id_course_teacher)';
EXECUTE IMMEDIATE 'CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW STUDENT AS
student_root @insert @update @delete
{
_id : student_id
age
name
courses: map_course_root_to_student_root @insert @update @delete @array
[{
ora$mapCourseId: map_course_id
ora$mapStudentId: map_student_id
ora$student_flex @flex
course_root @unnest @insert @update @object
{
name
avgGrade: avg_grade
courseNumber: course_id
}
}]
advisorId:advisor_id
dormitory: student_dormitory @insert @update @object
{
dormId: dorm_id
dormName: dorm_name
dormLocation: dorm_location
ora$student_flex @flex
}
studentId @generated (path: "$._id")
ora$student_flex @flex
}';
EXECUTE IMMEDIATE 'CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW TEACHER AS
teacher_root @insert @update @delete
{
"_id"
name
salary
emailId: email_id
department
phoneNumber: phone_number
coursesTaught: course_root @insert @update @delete @array
[{
name
courseId: course_id
classType: class_type
ora$teacher_flex @flex
}]
studentsAdvised: student_root @insert @update @delete @array
[{
name
dormId:dorm_id
studentId: student_id
ora$teacher_flex @flex
}]
ora$teacher_flex @flex
}';
EXECUTE IMMEDIATE 'CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW COURSE AS
course_root @insert @update @delete
{
_id : course_id
name
Notes: notes
teacher: course_teacher @insert @update @object
{
name
teacherId: teacher_id
ora$course_flex @flex
}
courseId @generated (path: "$._id")
location
students: map_course_root_to_student_root @insert @update @delete @array
[{
ora$mapCourseId: map_course_id
ora$mapStudentId: map_student_id
ora$course_flex @flex
student_root @unnest @insert @update @object
{
name
studentId: student_id
}
}]
creditHours: credit_hours
ora$course_flex @flex
}';
EXECUTE IMMEDIATE 'CREATE OR REPLACE TRIGGER INSERT_TRIGGER_STUDENT
BEFORE INSERT
ON STUDENT
FOR EACH ROW
DECLARE
inp_jsonobj json_object_t;
BEGIN
inp_jsonobj := json_object_t(:new.data);
IF NOT inp_jsonobj.has(''_id'')
THEN
inp_jsonobj.put(''_id'', inp_jsonobj.get(''studentId''));
:new.data := inp_jsonobj.to_json;
END IF;
END;';
EXECUTE IMMEDIATE 'CREATE OR REPLACE TRIGGER INSERT_TRIGGER_COURSE
BEFORE INSERT
ON COURSE
FOR EACH ROW
DECLARE
inp_jsonobj json_object_t;
BEGIN
inp_jsonobj := json_object_t(:new.data);
IF NOT inp_jsonobj.has(''_id'')
THEN
inp_jsonobj.put(''_id'', inp_jsonobj.get(''courseId''));
:new.data := inp_jsonobj.to_json;
END IF;
END;';
END;
例21-31 二面性ビューへのデータ・インポート
データベース・スキーマの生成後、SQLを使用してデータをインポートします:
-- CREATE ERROR LOG TABLES
BEGIN
DBMS_ERRLOG.create_error_log(dml_table_name => 'COURSE',
err_log_table_name => 'COURSE_ERR_LOG',
skip_unsupported => TRUE);
DBMS_ERRLOG.create_error_log(dml_table_name => 'TEACHER',
err_log_table_name => 'TEACHER_ERR_LOG',
skip_unsupported => TRUE);
DBMS_ERRLOG.create_error_log(dml_table_name => 'STUDENT',
err_log_table_name => 'STUDENT_ERR_LOG',
skip_unsupported => TRUE);
END;
/
-- Import the data in the input tables into the duality views created using import_all method
BEGIN
DBMS_JSON_DUALITY.import_all(JSON('{"tableNames" : [ "STUDENT_TAB", "TEACHER_TAB", "COURSE_TAB" ],
"viewNames" : [ "STUDENT", "TEACHER", "COURSE" ],
"errorLog" : [ "STUDENT_ERR_LOG", "TEACHER_ERR_LOG", "COURSE_ERR_LOG" ]}'));
END;
/
-- Verify data after import
SELECT json_serialize(data PRETTY) FROM student;
SELECT json_serialize(data PRETTY) FROM teacher;
SELECT json_serialize(data PRETTY) FROM course;
-- check error logs
SELECT ora_err_number$, ora_err_mesg$, ora_err_tag$ FROM student_err_log;
SELECT ora_err_number$, ora_err_mesg$, ora_err_tag$ FROM teacher_err_log;
SELECT ora_err_number$, ora_err_mesg$, ora_err_tag$ FROM course_err_log;
ログ・ファイルを確認します:
SQL> -- CREATE ERROR LOG TABLES
SQL> BEGIN
2 DBMS_ERRLOG.create_error_log(dml_table_name => 'COURSE',
3 err_log_table_name => 'COURSE_ERR_LOG',
4 skip_unsupported => TRUE);
5 DBMS_ERRLOG.create_error_log(dml_table_name => 'TEACHER',
6 err_log_table_name => 'TEACHER_ERR_LOG',
7 skip_unsupported => TRUE);
8 DBMS_ERRLOG.create_error_log(dml_table_name => 'STUDENT',
9 err_log_table_name => 'STUDENT_ERR_LOG',
10 skip_unsupported => TRUE);
11 END;
12 /
PL/SQL procedure successfully completed.
SQL>
SQL> -- Import the data in the input tables into the duality views created using import_all method
SQL> BEGIN
2 DBMS_JSON_DUALITY.import_all(JSON('{"tableNames" : [ "STUDENT_TAB", "TEACHER_TAB", "COURSE_TAB" ],
3 "viewNames" : [ "STUDENT", "TEACHER", "COURSE" ],
4 "errorLog" : [ "STUDENT_ERR_LOG", "TEACHER_ERR_LOG", "COURSE_ERR_LOG" ]}'));
5 END;
6 /
PL/SQL procedure successfully completed.
例21-32 インポート後のデータの検証
インポート後に、エラー・ログ表を確認してデータを検証します。
SQL> -- Verify data after import
SQL> SELECT json_serialize(data PRETTY) FROM student;
JSON_SERIALIZE(DATAPRETTY)
--------------------------------------------------------------------------------
{
"_id" : 1,
"_metadata" :
{
"etag" : "D59BBECDEF2CB781DDDF5843CD4686DA",
"asof" : "000000000033126E"
},
"age" : 20,
"name" : "Donald P.",
"courses" :
[
{
"ora$mapCourseId" : "CS101",
"ora$mapStudentId" : 1,
"name" : "Algorithms",
"avgGrade" : 75,
"courseNumber" : "CS101"
},
{
"ora$mapCourseId" : "CS102",
"ora$mapStudentId" : 1,
"name" : "Data Structures",
"avgGrade" : "TBD",
"courseNumber" : "CS102"
},
{
"ora$mapCourseId" : "MATH101",
"ora$mapStudentId" : 1,
"name" : "Algebra",
"avgGrade" : 90,
"courseNumber" : "MATH101"
}
],
"advisorId" : 102,
"dormitory" :
{
"dormId" : 201,
"dormName" : "ABC",
"dormLocation" : null
},
"studentId" : 1
}
{
"_id" : 2,
"_metadata" :
{
"etag" : "8CF3F43FAFFDFEDA524CB6DBCA0FB90F",
"asof" : "000000000033126E"
},
"age" : 21,
"name" : "Elena H.",
"courses" :
[
{
"ora$mapCourseId" : "CS101",
"ora$mapStudentId" : 2,
"name" : "Algorithms",
"avgGrade" : 75,
"courseNumber" : "CS101"
},
{
"ora$mapCourseId" : "CS102",
"ora$mapStudentId" : 2,
"name" : "Data Structures",
"avgGrade" : "TBD",
"courseNumber" : "CS102"
},
{
"ora$mapCourseId" : "MATH102",
"ora$mapStudentId" : 2,
"name" : "Calculus",
"avgGrade" : 95,
"courseNumber" : "MATH102"
}
],
"advisorId" : 103,
"dormitory" :
{
"dormId" : 202,
"dormName" : "XYZ",
"dormLocation" : null
},
"studentId" : 2
}
{
"_id" : 3,
"_metadata" :
{
"etag" : "533366851CC9508AFA08C394F3616972",
"asof" : "000000000033126E"
},
"age" : 20,
"name" : "Francis K.",
"courses" :
[
{
"ora$mapCourseId" : "MATH103",
"ora$mapStudentId" : 3,
"name" : "Advanced Algebra",
"avgGrade" : 82,
"courseNumber" : "MATH103"
}
],
"advisorId" : 103,
"dormitory" :
{
"dormId" : 204,
"dormName" : "QWE",
"dormLocation" : null
},
"studentId" : 3
}
{
"_id" : 4,
"_metadata" :
{
"etag" : "693C9DDBAEE3FD99A6967ADBF713BA1A",
"asof" : "000000000033126E"
},
"age" : 19,
"name" : "Georgia D.",
"courses" :
[
{
"ora$mapCourseId" : "CS101",
"ora$mapStudentId" : 4,
"name" : "Algorithms",
"avgGrade" : 75,
"courseNumber" : "CS101"
},
{
"ora$mapCourseId" : "MATH102",
"ora$mapStudentId" : 4,
"name" : "Calculus",
"avgGrade" : 95,
"courseNumber" : "MATH102"
},
{
"ora$mapCourseId" : "MATH103",
"ora$mapStudentId" : 4,
"name" : "Advanced Algebra",
"avgGrade" : 82,
"courseNumber" : "MATH103"
}
],
"advisorId" : 101,
"dormitory" :
{
"dormId" : 203,
"dormName" : "LMN",
"dormLocation" : null
},
"studentId" : 4
}
{
"_id" : 5,
"_metadata" :
{
"etag" : "E923DE9EFD2F19338A2F0657B061AF22",
"asof" : "000000000033126E"
},
"age" : 21,
"name" : "Hye E.",
"courses" :
[
{
"ora$mapCourseId" : "CS102",
"ora$mapStudentId" : 5,
"name" : "Data Structures",
"avgGrade" : "TBD",
"courseNumber" : "CS102"
},
{
"ora$mapCourseId" : "MATH101",
"ora$mapStudentId" : 5,
"name" : "Algebra",
"avgGrade" : 90,
"courseNumber" : "MATH101"
}
],
"advisorId" : 103,
"dormitory" :
{
"dormId" : 201,
"dormName" : "ABC",
"dormLocation" : null
},
"studentId" : 5
}
{
"_id" : 6,
"_metadata" :
{
"etag" : "992F54118924F34D2F58F7056C093280",
"asof" : "000000000033126E"
},
"age" : 21,
"name" : "Ileana D.",
"courses" :
[
{
"ora$mapCourseId" : "MATH103",
"ora$mapStudentId" : 6,
"name" : "Advanced Algebra",
"avgGrade" : 82,
"courseNumber" : "MATH103"
}
],
"advisorId" : 102,
"dormitory" :
{
"dormId" : 205,
"dormName" : "GHI",
"dormLocation" : null
},
"studentId" : 6
}
{
"_id" : 7,
"_metadata" :
{
"etag" : "A34FBC7FCAE6B060B987C0F37B184CE6",
"asof" : "000000000033126E"
},
"age" : 20,
"name" : "Jatin S.",
"courses" :
[
{
"ora$mapCourseId" : "CS101",
"ora$mapStudentId" : 7,
"name" : "Algorithms",
"avgGrade" : 75,
"courseNumber" : "CS101"
},
{
"ora$mapCourseId" : "CS102",
"ora$mapStudentId" : 7,
"name" : "Data Structures",
"avgGrade" : "TBD",
"courseNumber" : "CS102"
}
],
"advisorId" : 101,
"dormitory" :
{
"dormId" : 204,
"dormName" : "QWE",
"dormLocation" : null
},
"studentId" : 7
}
{
"_id" : 8,
"_metadata" :
{
"etag" : "4D120806D5BBA8A11B06BE3303E0C30E",
"asof" : "000000000033126E"
},
"age" : 21,
"name" : "Katie H.",
"courses" :
[
{
"ora$mapCourseId" : "CS102",
"ora$mapStudentId" : 8,
"name" : "Data Structures",
"avgGrade" : "TBD",
"courseNumber" : "CS102"
},
{
"ora$mapCourseId" : "MATH103",
"ora$mapStudentId" : 8,
"name" : "Advanced Algebra",
"avgGrade" : 82,
"courseNumber" : "MATH103"
}
],
"advisorId" : 102,
"dormitory" :
{
"dormId" : 205,
"dormName" : "GHI",
"dormLocation" : null
},
"studentId" : 8
}
{
"_id" : 9,
"_metadata" :
{
"etag" : "B43B57898F7E95F93A2A8CD878CD17B3",
"asof" : "000000000033126E"
},
"age" : "Nineteen",
"name" : "Luis F.",
"courses" :
[
{
"ora$mapCourseId" : "CS101",
"ora$mapStudentId" : 9,
"name" : "Algorithms",
"avgGrade" : 75,
"courseNumber" : "CS101"
},
{
"ora$mapCourseId" : "MATH102",
"ora$mapStudentId" : 9,
"name" : "Calculus",
"avgGrade" : 95,
"courseNumber" : "MATH102"
},
{
"ora$mapCourseId" : "MATH103",
"ora$mapStudentId" : 9,
"name" : "Advanced Algebra",
"avgGrade" : 82,
"courseNumber" : "MATH103"
}
],
"advisorId" : 101,
"dormitory" :
{
"dormId" : 201,
"dormName" : "ABC",
"dormLocation" : null
},
"studentId" : 9
}
{
"_id" : 10,
"_metadata" :
{
"etag" : "6E74518D9B67EFF7D3E0162256CEFF4C",
"asof" : "000000000033126E"
},
"age" : 20,
"name" : "Ming L.",
"courses" :
[
{
"ora$mapCourseId" : "MATH102",
"ora$mapStudentId" : 10,
"name" : "Calculus",
"avgGrade" : 95,
"courseNumber" : "MATH102"
}
],
"advisorId" : 101,
"dormitory" :
{
"dormId" : 202,
"dormName" : "XYZ",
"dormLocation" : null
},
"studentId" : 10
}
10 rows selected.
SQL> SELECT json_serialize(data PRETTY) FROM teacher;
JSON_SERIALIZE(DATAPRETTY)
--------------------------------------------------------------------------------
{
"_id" : 101,
"_metadata" :
{
"etag" : "4E1D8E180D9FE3E418FB3765D87A80F4",
"asof" : "0000000000331274"
},
"name" : "Abdul J.",
"salary" : 200000,
"emailId" : null,
"department" : "Mathematics",
"phoneNumber" :
[
"222-555-011",
"222-555-012"
],
"coursesTaught" :
[
{
"name" : "Algebra",
"courseId" : "MATH101",
"classType" : "Online"
},
{
"name" : "Calculus",
"courseId" : "MATH102",
"classType" : "In-person"
}
],
"studentsAdvised" :
[
{
"name" : "Georgia D.",
"dormId" : 203,
"studentId" : 4
},
{
"name" : "Jatin S.",
"dormId" : 204,
"studentId" : 7
},
{
"name" : "Luis F.",
"dormId" : 201,
"studentId" : 9
},
{
"name" : "Ming L.",
"dormId" : 202,
"studentId" : 10
}
]
}
{
"_id" : 102,
"_metadata" :
{
"etag" : "20BDE221E92B567A11E1652005B76B76",
"asof" : "0000000000331274"
},
"name" : "Betty Z.",
"salary" : 300000,
"emailId" : null,
"department" : "Computer Science",
"phoneNumber" : "222-555-022",
"coursesTaught" :
[
{
"name" : "Algorithms",
"courseId" : "CS101",
"classType" : "Online"
},
{
"name" : "Data Structures",
"courseId" : "CS102",
"classType" : "In-person"
}
],
"studentsAdvised" :
[
{
"name" : "Donald P.",
"dormId" : 201,
"studentId" : 1
},
{
"name" : "Ileana D.",
"dormId" : 205,
"studentId" : 6
},
{
"name" : "Katie H.",
"dormId" : 205,
"studentId" : 8
}
]
}
{
"_id" : 103,
"_metadata" :
{
"etag" : "B7DB096CFD8939D2C5339F0FF43C07B5",
"asof" : "0000000000331274"
},
"name" : "Colin J.",
"salary" : 220000,
"emailId" : null,
"department" : "Mathematics",
"phoneNumber" :
[
"222-555-023"
],
"coursesTaught" :
[
{
"name" : "Advanced Algebra",
"courseId" : "MATH103",
"classType" : "Online"
}
],
"studentsAdvised" :
[
{
"name" : "Elena H.",
"dormId" : 202,
"studentId" : 2
},
{
"name" : "Francis K.",
"dormId" : 204,
"studentId" : 3
},
{
"name" : "Hye E.",
"dormId" : 201,
"studentId" : 5
}
]
}
{
"_id" : 104,
"_metadata" :
{
"etag" : "F82846A780D35515745D14B0ACF372E9",
"asof" : "0000000000331274"
},
"name" : "Natalie C.",
"salary" : 180000,
"emailId" : null,
"department" : "Computer Science",
"phoneNumber" : "222-555-044",
"coursesTaught" :
[
],
"studentsAdvised" :
[
]
}
SQL> SELECT json_serialize(data PRETTY) FROM course;
JSON_SERIALIZE(DATAPRETTY)
--------------------------------------------------------------------------------
{
"_id" : "CS101",
"_metadata" :
{
"etag" : "36C477EBFE254EF8C668FB1CEE298AEC",
"asof" : "0000000000331274"
},
"name" : "Algorithms",
"Notes" : null,
"teacher" :
{
"name" : "Betty Z.",
"teacherId" : 102
},
"location" : null,
"students" :
[
{
"ora$mapCourseId" : "CS101",
"ora$mapStudentId" : 1,
"name" : "Donald P.",
"studentId" : 1
},
{
"ora$mapCourseId" : "CS101",
"ora$mapStudentId" : 2,
"name" : "Elena H.",
"studentId" : 2
},
{
"ora$mapCourseId" : "CS101",
"ora$mapStudentId" : 4,
"name" : "Georgia D.",
"studentId" : 4
},
{
"ora$mapCourseId" : "CS101",
"ora$mapStudentId" : 7,
"name" : "Jatin S.",
"studentId" : 7
},
{
"ora$mapCourseId" : "CS101",
"ora$mapStudentId" : 9,
"name" : "Luis F.",
"studentId" : 9
}
],
"creditHours" : 5,
"courseId" : "CS101"
}
{
"_id" : "CS102",
"_metadata" :
{
"etag" : "C521E7258A6786CFCC67A550D4FDA3BF",
"asof" : "0000000000331274"
},
"name" : "Data Structures",
"Notes" : null,
"teacher" :
{
"name" : "Betty Z.",
"teacherId" : 102
},
"location" : null,
"students" :
[
{
"ora$mapCourseId" : "CS102",
"ora$mapStudentId" : 1,
"name" : "Donald P.",
"studentId" : 1
},
{
"ora$mapCourseId" : "CS102",
"ora$mapStudentId" : 2,
"name" : "Elena H.",
"studentId" : 2
},
{
"ora$mapCourseId" : "CS102",
"ora$mapStudentId" : 5,
"name" : "Hye E.",
"studentId" : 5
},
{
"ora$mapCourseId" : "CS102",
"ora$mapStudentId" : 7,
"name" : "Jatin S.",
"studentId" : 7
},
{
"ora$mapCourseId" : "CS102",
"ora$mapStudentId" : 8,
"name" : "Katie H.",
"studentId" : 8
}
],
"creditHours" : 3,
"courseId" : "CS102"
}
{
"_id" : "MATH101",
"_metadata" :
{
"etag" : "EADFDF3C9F43773C23C14E391D384C44",
"asof" : "0000000000331274"
},
"name" : "Algebra",
"Notes" : "Prerequisite for Advanced Algebra",
"teacher" :
{
"name" : "Abdul J.",
"teacherId" : 101
},
"location" : null,
"students" :
[
{
"ora$mapCourseId" : "MATH101",
"ora$mapStudentId" : 1,
"name" : "Donald P.",
"studentId" : 1
},
{
"ora$mapCourseId" : "MATH101",
"ora$mapStudentId" : 5,
"name" : "Hye E.",
"studentId" : 5
}
],
"creditHours" : 3,
"courseId" : "MATH101"
}
{
"_id" : "MATH102",
"_metadata" :
{
"etag" : "0E6F52978EFBF49B251C1E442B5019B4",
"asof" : "0000000000331274"
},
"name" : "Calculus",
"Notes" : null,
"teacher" :
{
"name" : "Abdul J.",
"teacherId" : 101
},
"location" : null,
"students" :
[
{
"ora$mapCourseId" : "MATH102",
"ora$mapStudentId" : 2,
"name" : "Elena H.",
"studentId" : 2
},
{
"ora$mapCourseId" : "MATH102",
"ora$mapStudentId" : 4,
"name" : "Georgia D.",
"studentId" : 4
},
{
"ora$mapCourseId" : "MATH102",
"ora$mapStudentId" : 9,
"name" : "Luis F.",
"studentId" : 9
},
{
"ora$mapCourseId" : "MATH102",
"ora$mapStudentId" : 10,
"name" : "Ming L.",
"studentId" : 10
}
],
"creditHours" : 4,
"courseId" : "MATH102"
}
{
"_id" : "MATH103",
"_metadata" :
{
"etag" : "3E8CC5863A4A794F4547AD5BF22D6628",
"asof" : "0000000000331274"
},
"name" : "Advanced Algebra",
"Notes" : null,
"teacher" :
{
"name" : "Colin J.",
"teacherId" : 103
},
"location" : null,
"students" :
[
{
"ora$mapCourseId" : "MATH103",
"ora$mapStudentId" : 3,
"name" : "Francis K.",
"studentId" : 3
},
{
"ora$mapCourseId" : "MATH103",
"ora$mapStudentId" : 4,
"name" : "Georgia D.",
"studentId" : 4
},
{
"ora$mapCourseId" : "MATH103",
"ora$mapStudentId" : 6,
"name" : "Ileana D.",
"studentId" : 6
},
{
"ora$mapCourseId" : "MATH103",
"ora$mapStudentId" : 8,
"name" : "Katie H.",
"studentId" : 8
},
{
"ora$mapCourseId" : "MATH103",
"ora$mapStudentId" : 9,
"name" : "Luis F.",
"studentId" : 9
}
],
"creditHours" : "3",
"courseId" : "MATH103"
}
SQL>
SQL> -- check error logs
SQL> SELECT ora_err_number$, ora_err_mesg$, ora_err_tag$ FROM student_err_log;
no rows selected
SQL> SELECT ora_err_number$, ora_err_mesg$, ora_err_tag$ FROM teacher_err_log;
no rows selected
SQL> SELECT ora_err_number$, ora_err_mesg$, ora_err_tag$ FROM course_err_log;
no rows selected
親トピック: 定義済JSONスキーマでのコンバータの使用