Example 21-4 Create an Oracle Document Set (Course) From a JSON Dump File.

This example creates an Oracle Database external table, dataset_course, from a JSON dump file of a set of course documents, course.json. It then creates temporary transfer table course_tab with JSON-type column data. Finally, it imports the course documents into temporary transfer table course_tab, which can be used as input to the JSON-relational converter.

The documents in course_tab.data are those shown in the example "Course Document Set (Migrator Input)" in School Administration Example, Migrator Input Documents.

(Similarly student and teacher document sets are loaded into transfer tables student_tab and teacher_tab from external tables dataset_student and dataset_teacher created from dump files student.json and teacher.json, respectively.)

CREATE TABLE dataset_course (data JSON)
  ORGANIZATION EXTERNAL
    (TYPE ORACLE_BIGDATA
     ACCESS PARAMETERS (com.oracle.bigdata.fileformat = jsondoc)
     LOCATION (data_dir:'course.json'))
  PARALLEL
  REJECT LIMIT UNLIMITED;

CREATE TABLE course_tab AS SELECT * FROM dataset_course;
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."
  }
}