Oracle Live SQL - Script: AskTom Office Hours March 2020: JSON data modeling (Moon Landing)

Statement 1

Create table for student data

create table student(student_id varchar2(10), first varchar2(50), last varchar2(50), grade number)

Statement 2

Create table for teacher data

create table teacher(teacher_id varchar2(10), first varchar2(50), last varchar2(50), salary number, join_date date)

Statement 3

Create table for course data. A course is associated with at most one teacher (no teacher if 'teacher_id' is NULL). If we want to associate multiple teachers with one course this schema would need to change.

create table course (course_id varchar2(10), course_name varchar2(50), teacher_id varchar2(10), start_date DATE, end_date DATE)

Statement 4

this N-M table holds the mapping of students to courses

create table attends (student_id varchar2(10), course_id varchar2(10))

Statement 5

Primary constraint guarantee uniqueness of ID values. They're the target of foreign keys (see below).

ALTER TABLE student ADD CONSTRAINT student_pk PRIMARY KEY (student_id)

Statement 6

ALTER TABLE teacher ADD CONSTRAINT teacher_pk PRIMARY KEY (teacher_id)

Statement 7

ALTER TABLE course ADD CONSTRAINT course_pk PRIMARY KEY (course_id)

Statement 8

ALTER TABLE attends ADD CONSTRAINT attends_pk PRIMARY KEY (student_id, course_id)

Statement 9

Now we add constraints to enforce 'referential integrity'. This implies for example that we cannot set a non-existing teacher to a course or delete a teacher that is still associated with a cause. Please note that while constraints are very useful many application do not rely on them because of the cost to validate them.

ALTER TABLE course
ADD CONSTRAINT course_fk
   FOREIGN KEY (teacher_id)
   REFERENCES teacher (teacher_id)

Statement 10

ALTER TABLE attends
ADD CONSTRAINT student_fk
   FOREIGN KEY (student_id)
   REFERENCES student (student_id)

Statement 11

ALTER TABLE attends
ADD CONSTRAINT course_fk2
   FOREIGN KEY (course_id)
   REFERENCES course (course_id)

Statement 12

Now we insert some date...

insert into teacher values('t1', 'Beda', 'Hammerschmidt', 0, sysdate)

Statement 13

insert into teacher values('t2', 'Blaine', 'Carter', 5000, sysdate)

Statement 14

insert into course values('c1', 'Databases', 't1', to_date('1-FEB-20'), to_date('20-JUN-20'))

Statement 15

insert into course values('c2', 'JSON', 't1', to_date('1-FEB-20'), to_date('20-JUN-20'))

Statement 16

insert into course values('c3', 'Pragramming', 't2', to_date('1-FEB-20'), to_date('20-JUN-20'))

Statement 17

insert into course values('c4', 'Math', null, null, null)

Statement 18

insert into student values('s1', 'Mickey', 'Mouse', 1)

Statement 19

insert into student values('s2', 'Donald', 'Duck', 1)

Statement 20

insert into attends values('s1', 'c1')

Statement 21

insert into attends values('s1', 'c3')

Statement 22

insert into attends values('s1', 'c4')

Statement 23

This insert fails because no course c99 exists. The integrity constraints reject invalid data.

insert into attends values('s1', 'c99')

Statement 24

find lazy students

select first, last
from student s
where student_id not in (select student_id from attends)

Statement 25

find all teachers of student 's1'

select DISTINCT t.first, t.last
from student s, teacher t, attends a, course c
where s.student_id = a.student_id
and   a.course_id = c.course_id
and   c.teacher_id = t.teacher_id
and   s.student_id = 's1'

Statement 26

rename a course

update course set course_name = 'Algebra' 
where course_id = 'c4'

Statement 27

This is a 'benign' schema change: we just add a new column to the table. Exiting records will get a NULL value for 'credits'. Existing queries should continue to work - unless you used 'SELECT *'.

alter table course add (credits number)

Statement 28

This is an example of a more complex schema changes: For each teacher we want to store all spoken languages. Approach 1 uses multiple new fields. The disadvantage of this approach that a query for languages has to include all fields. Also a teacher is limited to 3 languages in this example.

alter table teacher add (lang1 varchar2(20), lang2 varchar2(20), lang3 varchar2(20))

Statement 29

This is a second approach: add a separate table for languages and associate the entries with teacher. Now, if you want to pull out the information about a teacher you need to select from 2 tables. Same goes for inserts.

create table teacher_languages (teacher_id varchar2(10), language varchar2(20))

Statement 30

SELECT '-------- END OF RELATIONAL MODEL ------- START OF PURE JSON MODEL ------' FROM DUAL;

Statement 31

Now we use one JSON collection (table) for our school application:

create table university (data CLOB, check (data IS JSON))

Statement 32

We insert 2 students and 2 teacher records. Please note that the course information is implicitly part of the 'teacher' and 'student' record.

insert into university values('
{
	"id": 12345,
	"role": "student",
	"user": {
		"id": "s1",
		"first": "Mickey",
		"last": "Mouse"
	},
	"course": [{
		"id": "c1",
		"name": "Databases",
		"startDate": "2020-02-01",
		"endDate": "2020-06-20"
	},{
		"id": "c3",
		"name": "Programming",
		"startDate": "2020-02-01",
		"endDate": "2020-06-20"
	},{
		"id": "c4",
		"name": "Math"
	}]
}
')

Statement 33

insert into university values('
{
	"id": 12346,
	"role": "student",
	"user": {
		"id": "s2",
		"first": "Donald",
		"last": "Duck"
	},
	"course": []
}
')

Statement 34

Note that the properties of a course are repeated for every occurence!

insert into university values('
{
	"id": 12347,
	"role": "teacher",
	"user": {
		"id": "t1",
		"first": "Beda",
		"last": "Hammerschmidt"
	},
	"course": [{
		"id": "c1",
		"name": "Databases",
		"startDate": "2020-02-01",
		"endDate": "2020-06-20"
	},{
		"id": "c2",
		"name": "JSON",
		"startDate": "2020-02-01",
		"endDate": "2020-06-20"
	}]
}
')

Statement 35

insert into university values('
{
	"id": 12348,
	"role": "teacher",
	"user": {
		"id": "t2",
		"first": "Blaine",
		"last": "Carter"
	},
	"course": [{
		"id": "c3",
		"name": "Programming",
		"startDate": "2020-02-01",
		"endDate": "2020-06-20"
	}]
}
')

Statement 36

Find lazy students. Although this is a SQL statement many NoSQL database can express such query (without SQL. The equivalent SODA query would be {"course":{"$.size":0}}

select data from university u
where u.data.course.size() = 0

Statement 37

Changing a property of a course requires us to make changes to every occurrence! Ideally, such change is done transactionally - otherwise data inconsistencies are possible! So given the nature of the database in use (noSQL?) this requires custom user code! There are multiple ways to do this using a SQL database. One way would be to decompose the JSON data (using JSON_TABLE) and regenerate it with the modified data. Here we use another solution: a stored procedure using PL/SQL code. The code works a follows: we parse a student/teacher document into an instance of JSON_OBJECT_T. We then extract the array with the courses, iterate over it and check for the name. If the name matches we change it. At then end we turn the JSON_OBJECT_T back into a JSON and return.

create or replace function updateCourseName(
      data varchar2,
      oldName varchar2,
      newName varchar2) return varchar2 as
  data_obj    JSON_OBJECT_T;
  courses_arr JSON_ARRAY_T;
  course_obj  JSON_OBJECT_T;
begin
  data_obj    := JSON_OBJECT_T(data);
  courses_arr := data_obj.get_array('course');
  for i in 0 .. (courses_arr.get_size -1) loop
    course_obj := TREAT (courses_arr.get(i) as JSON_OBJECT_T);
    if (course_obj.get_string('name') = oldName) then
        course_obj.put('name', newName);
    end if;
  end loop;
  return data_obj.to_string;
end;
/

Statement 38

We call the function and show the modified data. Since the function call is in the SELECT we do no modify the data on disk. Use the function in an UPDATE statement instead to persistently modify the data.

select updateCourseName(u.data, 'Math', 'Algebra') 
from university u
where JSON_EXISTS(u.data, '$.course?(@.name == "Math")');

Statement 39

JSON_TRANSFORM will be available in 20. JSON_Mergepatch (available in 19) cannot be used because it lacks the ability to make a change inside a JSON_Array.

select JSON_TRANSFORM(u.data, SET '$.course?(@.id == "c4").name' = 'Algebra')
from university u;

Statement 40

Finding all teacher of a given student is non-trivial on this JSON data model - unless you turn the JSON into the relational model (using JSON_TABLE) and express the query using good-old SQL. Imagine doing this without SQL! But even if this is possible using SQL the execution might still be expensive - a consequence of embedding the relevant information in every document.

select jt.* 
from university u, JSON_TABLE(u.data, '$' 
columns(
  user_role  varchar2(10) path '$.role',
  user_id    varchar2(10) path '$.user.id',
  user_first varchar2(20) path '$.user.first',
  user_last  varchar2(20) path '$.user.last',
  nested path '$.course[*]' columns (
     course_id varchar2(10) path '$.id'
  )
)) jt;

Statement 41

We use above query in the WITH clause to perform a SQL (self) join to find all teachers of a student.

with flatJson as (
select jt.* 
from university u, JSON_TABLE(u.data, '$' 
columns(
  user_role  varchar2(10) path '$.role',
  user_id    varchar2(10) path '$.user.id',
  user_first varchar2(20) path '$.user.first',
  user_last  varchar2(20) path '$.user.last',
  nested path '$.course[*]' columns (
     course_id varchar2(10) path '$.id'
  )
)) jt)
select DISTINCT teacher.user_first, teacher.user_last
from flatJson student, flatJson teacher
where student.user_first = 'Mickey'
  and student.course_id  = teacher.course_id
  and teacher.user_role  = 'teacher'
  and student.user_role  = 'student';

Statement 42

Schema changes on the other hand are quite easy: here, in a new record, we add a list of languages. This was pretty problematic in the relational model but in JSON it is very easy to evolve the schema.

insert into university values('
{
	"id": 23456,
	"role": "teacher",
	"user": {
		"id": "t3",
		"first": "Steven",
		"last": "F"
	}, 
       languages:["English", "Spanish", "PL/SQL"]
   }')

Statement 43

select u.data from university u where json_exists(u.data,'$?(@.languages == "Spanish")');

Statement 44

43 SELECT '----- END OF PURE JSON MODEL -------START OF JSON IN RDBMS MODEL---------' FROM DUAL

Statement 45

create table student_j(id varchar2(20), data CLOB, check (data IS JSON))

Statement 46

create table teacher_j(id varchar2(20), data CLOB, check (data IS JSON))

Statement 47

create table course_j (id varchar2(20), data CLOB, check (data IS JSON))

Statement 48

We add a primary key constraint to the teacher's id column.

ALTER TABLE teacher_j ADD CONSTRAINT teacher_j_pk PRIMARY KEY (id);

Statement 49

insert into teacher_j values('t1', '
{ 
		"first": "Beda",
		"last": "Hammerschmidt",
		"salary" :0
	}')

Statement 50

insert into teacher_j values('t2', '
{ 
		"first": "Blaine",
		"last": "Carter",
		"salary":5000
	}')

Statement 51

insert into course_j values('c1','
{
  "name": "Databases",
  "teacher" :"t1",
  "startDate": "2020-02-01",
  "endDate": "2020-06-20"
}')

Statement 52

insert into course_j values('c2','
{
  "name": "JSON",
  "teacher" :"t1",
  "startDate": "2020-02-01",
  "endDate": "2020-06-20"
}')

Statement 53

insert into course_j values('c3','
{
  "name": "Programming",
  "teacher" :"t2",
  "startDate": "2020-02-01",
  "endDate": "2020-06-20"
}')

Statement 54

insert into course_j values('c4','
{
  "name": "Math",
  "startDate" : "2020-03-15", 
  "endDate ": "2020-04-15"
}');

Statement 55

insert into student_j values('s1', '
{ 
		"grade": 1,
		"first": "Mickey",
		"last": "Mouse",
		"courses":["c1","c3", "c4"]
	}')

Statement 56

insert into student_j values('s2', '
{ 
		"grade": 1,
		"first": "Donald",
		"last": "Duck",
		"courses":[]
	}')

Statement 57

Since a course is only stored in one record it is simple to update it. Here we use JSON_Mergepatch to perform the update. Note that in this update we set the missing information for a course and also do a schema change by adding a 'capacity' to the course.

update course_j set data = JSON_MergePatch(data, '
{"teacher" :"t2",
  "startDate" : "2020-03-15",
  "endDate ": "2020-04-15",
  "capacity" : 30
}')
where id = 'c4'

Statement 58

The record (course) shows the updated attributes.

select data from course_j where id = 'c4'

Statement 59

Finding lazy students is easy: we use the size() trailing functions which returns the number of elements in the course array.

select data from student_j s
where s.data.courses.size() = 0

Statement 60

This query (with JSON_TABLE in the FROM clause) returns the name of a student together with a list of the courses the student has signed up for. JSON_TABLE projects the data relationally and generated a separate column for each course.

select s.data.first, s.data.last, jt.course_id
     from   student_j s,  
            JSON_TABLE(s.data, '$.courses[*]' 
                columns (course_id varchar2(20) PATH '$')) jt

Statement 61

Given above query (in the WITH clause) we perform a join with the courses and teacher table to find a list of all teacher for a student.

with student_courses as
  (select s.data.first, s.data.last, jt.course_id
     from   student_j s,  
            JSON_TABLE(s.data, '$.courses[*]' 
                columns (course_id varchar2(20) PATH '$')) jt
  )
select DISTINCT student_courses.first, 
       student_courses.last, 
       teacher.data.first, 
       teacher.data.last
from student_courses student_courses, course_j course, teacher_j teacher
where  student_courses.course_id = course.id
  and course.data.teacher = teacher.id
  and student_courses.last = 'Mouse';

Statement 62

We can use SQL not only to query data but also to generate new data - here by using JSON generation operators.

with student_courses as
  (select s.id, s.data.first, s.data.last, jt.course_id
     from   student_j s,  
            JSON_TABLE(s.data, '$.courses[*]' 
                columns (course_id varchar2(20) PATH '$')) jt
  )
select JSON_Object('name'    value (student.data.first || ' ' || student.data.last),
                   'teacher' value (
       select JSON_ArrayAgg(teacher.data.last)
       from teacher_j teacher, course_j course, student_courses student_courses
       where student.id = student_courses.id
        and  student_courses.course_id = course.id
        and  course.data.teacher = teacher.id
)
) from student_j student;

Statement 63

In order to enforce referential integrity we extract the teacher id of each course with a virtual column. A virtual column's value is not stored on disk but instead computed using a function, in this case JSON_VALUE. This way, the virtual column is in sync with the JSON data which serves as input to the computation (JSON_VALUE input).

alter table course_j  add (teacher_id  generated always as (JSON_VALUE(data, '$.teacher')));

Statement 64

We use the virtual column to define a foreign key constraint to the teacher's table.

ALTER TABLE course_j
ADD CONSTRAINT course_j_fk
   FOREIGN KEY (teacher_id)
   REFERENCES teacher_j (id);

Statement 65

This deletion fails: I cannot remove a teacher who still has course.

delete from teacher_j teacher where teacher.data.last= 'Carter';

Statement 66

Similarly, I cannot insert a new course with invalid (non-existing) teacher.

insert into course_j (id, data )values('c5',
'{
  "name": "PL/SQL",
  "teacher" :"t17"
}');

Statement 67

A course with a missing teacher (NULL values in the virtual column) on the other hand is fine: we just have not found a teacher for this course yet.

insert into course_j (id, data )values('c5',
'{
  "name": "PL/SQL"
}');

Statement 68

Using a virtual column was easy - but it is limited to one value per json (one teacher per course). This limitation is because of the relational model, the so called 'first normal form' where column values are scalar, this means not multiple values. How do we validate that a student has only valid entries in the student array, then? One solution is to use a trigger that is executed before an update or insert. Here we iterate thru the course array and make sure every course id exists.

CREATE OR REPLACE TRIGGER validateCourse
   BEFORE INSERT OR UPDATE ON student_j FOR EACH ROW
DECLARE
   o json_object_t;
   course_arr json_array_t;
   course_id varchar2(20);
   n number;
BEGIN
   o := new JSON_OBJECT_T(:NEW.data);
   course_arr := o.get_array('courses');
   for i in 0 .. (course_arr.get_size -1) loop
       course_id := course_arr.get_string(i);
       select count(1) into n from course_j where id = course_id;
       if (n = 0) then
         RAISE_APPLICATION_ERROR( -20001, 
                             'course ' || course_id || ' not valid' );
       end if;
   end loop;
END;
/

Statement 69

c99 does not exists. Insert rejected.

insert into student_j values('s3', '
{ 
		"first": "Tintin",
		"courses":["c1","c99"]
	}')

Statement 70

This insert succeeds because both courses do exist.

insert into student_j values('s3', '
{ 
		"first": "Tintin",
		"courses":["c1","c2"]
	}')

AskTom Office Hours March 2020: JSON data modeling (Moon Landing)

Additional Information