Oracle Live SQL - Script: JSON Generation from relational data (AskTom Office hours, Sept 11th 2018)

Statement 1

Create a table to store a department (work location). (This demo is inspired by the old scott/tiger schema.)

create table dept(  
  deptno     number(2,0),  
  dname      varchar2(30),  
  loc        varchar2(30),  
  constraint pk_dept primary key (deptno)  
)

Statement 2

Similarly, create a table to store information about employees. Employees are identified by an employee number (empno). They have a name, title (called job), etc. The manager of an employee is stored in the 'mgr' columns. This is a 'backpointer' to the same table. (The big boss has a null value in the 'mgr' column indicating that this employee is unmanaged ;-))

create table emp(  
  empno    number(4,0),  
  ename    varchar2(10),  
  job      varchar2(20),  
  mgr      number(4,0),  
  hiredate date,  
  sal      number(7,2),  
  comm     number(7,2),  
  deptno   number(2,0),  
  constraint pk_emp primary key (empno),  
  constraint fk_deptno foreign key (deptno) references dept (deptno)  
)

Statement 3

Populate the employee and department tables with data - good old relational data. No JSON yet, stay tuned.

insert into DEPT (DEPTNO, DNAME, LOC)
values(10, 'Software Engineering', 'Redwood Shores')

Statement 4

insert into dept  
values(20, 'Research', 'Redwood Shores')

Statement 5

insert into dept  
values(30, 'Sales', 'San Jose')

Statement 6

insert into dept  
values(40, 'Testing', 'Gilroy')

Statement 7

insert into emp  
values(  
 7839, 'King', 'CEO', null,  
 to_date('17-11-1981','dd-mm-yyyy'),  
 5000, null, 10  
)

Statement 8

insert into emp  
values(  
 7698, 'Blake', 'Manager', 7839,  
 to_date('1-5-1981','dd-mm-yyyy'),  
 2850, null, 30  
)

Statement 9

insert into emp  
values(  
 7782, 'Clark', 'Manager', 7839,  
 to_date('9-6-1981','dd-mm-yyyy'),  
 2450, null, 10  
)

Statement 10

insert into emp  
values(  
 7566, 'Jones', 'Manager', 7839,  
 to_date('2-4-1981','dd-mm-yyyy'),  
 2975, null, 20  
)

Statement 11

insert into emp  
values(  
 7788, 'Scott', 'Programmer', 7566,  
 to_date('13-JUL-87','dd-mm-rr') - 85,  
 3000, null, 20  
)

Statement 12

insert into emp  
values(  
 7902, 'Ford', 'Programmer', 7566,  
 to_date('3-12-1981','dd-mm-yyyy'),  
 3000, null, 20  
)

Statement 13

insert into emp  
values(  
 7369, 'Smith', 'Software Architect', 7902,  
 to_date('17-12-1980','dd-mm-yyyy'),  
 800, null, 20  
)

Statement 14

insert into emp  
values(  
 7499, 'Allen', 'Sales Support', 7698,  
 to_date('20-2-1981','dd-mm-yyyy'),  
 1600, 300, 30  
)

Statement 15

insert into emp  
values(  
 7521, 'Ward', 'Sales', 7698,  
 to_date('22-2-1981','dd-mm-yyyy'),  
 1250, 500, 30  
)

Statement 16

insert into emp  
values(  
 7654, 'Martin', 'Sales', 7698,  
 to_date('28-9-1981','dd-mm-yyyy'),  
 1250, 1400, 30  
)

Statement 17

insert into emp  
values(  
 7844, 'Turner', 'Support', 7698,  
 to_date('8-9-1981','dd-mm-yyyy'),  
 1500, 0, 30  
)

Statement 18

insert into emp  
values(  
 7876, 'Adams', 'Tester', 7788,  
 to_date('13-JUL-87', 'dd-mm-rr') - 51,  
 1100, null, 20  
)

Statement 19

insert into emp  
values(  
 7900, 'James', 'Security Tester', 7698,  
 to_date('3-12-1981','dd-mm-yyyy'),  
 950, null, 30  
)

Statement 20

insert into emp  
values(  
 7934, 'Miller', 'Release Manager', 7782,  
 to_date('23-1-1982','dd-mm-yyyy'),  
 1300, null, 10  
)

Statement 21

Puh, done inserting, lets take a look at the data we just threw at the database.

select * from emp

Statement 22

select * from dept

Statement 23

The easiest way to generate JSON from a table: turn each row into one JSON object. You list the field names and the expression that yields the corresponding value - in this case a column in the 'emp' table. The keyword 'VALUE' separates the key form the value. You could also put the keyword 'KEY' in fron of the key but this is optional and who likes to write more than needed?

select JSON_OBJECT ('id' VALUE empno, 
                    'name' VALUE ename,
                    'job' VALUE job,
                    'hired' VALUE hiredate,
                    'managerId' value MGR) 
from emp

Statement 24

Using JSON_ARRAYAGG we combine all the previous rows into one JSON array. One large value. An aggregation functions turns multiple rows into one row.

select JSON_ARRAYAGG(
 JSON_OBJECT ('id' VALUE empno, 
                    'name' VALUE ename,
                    'job' VALUE job,
                    'hired' VALUE hiredate,
                    'managerId' value MGR)
                    ) from emp

Statement 25

Retrieve a single JSON record for one employee by just adding a filter condition in the WHERE clause. SQL still rulez! But: just having the manager's employee number is boring, how can we get the manager's name?

select JSON_OBJECT ('id' VALUE empno, 
                    'name' VALUE ename,
                    'job' VALUE job,
                    'managerId' value MGR) 
from emp
where empno = 7782

Statement 26

We get the managers name by joining the emp table with itself: one rows gives us the employee data the other row the manager's data (who is also an employee).

select JSON_OBJECT ('id' VALUE e.empno, 
                    'name' VALUE e.ename,
                    'job' VALUE e.job,
                    'managerId' value e.MGR,
                    'managerName' value m.ename) 
from emp e, emp m
where e.mgr = m.empno

Statement 27

Let's also do the inverse: list the reports (aka subordinates) of an employee. First we count them by issuing a subquery. Did I already say that SQL still rulez?

select JSON_OBJECT ('id' VALUE e.empno, 
                    'name' VALUE e.ename,
                    'job' VALUE e.job,
                    'managerId' value e.MGR,
                    'managerName' value m.ename,
                    'numReports' value (select count(*) from emp e2 where e.empno = e2.mgr )) 
from emp e, emp m
where e.mgr = m.empno

Statement 28

Just the count is boring, we want the names. Should i rename the 'reports' field to 'minions'?

select JSON_OBJECT ('id' VALUE e.empno, 
                    'name' VALUE e.ename,
                    'job' VALUE e.job,
                    'managerId' value e.MGR,
                    'managerName' value m.ename,
                    'reports' value (select JSON_ARRAYAGG(e2.ename) from emp e2 where e.empno = e2.mgr )) 
from emp e, emp m
where e.mgr = m.empno

Statement 29

In case we want more informations about the reports then just their name: just use JSON_OBJECT tp generate a separate object for each one of them.

select JSON_OBJECT ('id' VALUE e.empno, 
                    'name' VALUE e.ename,
                    'job' VALUE e.job,
                    'managerId' value e.MGR,
                    'managerName' value m.ename,
                    'reports' value (select JSON_ARRAYAGG(
                                            JSON_OBJECT(
                                              'id' VALUE e2.empno, 
                                              'name' VALUE e2.ename, 
                                              'job' VALUE e2.job)) from emp e2 where e.empno = e2.mgr 
                                            )
                                    ) 
from emp e, emp m
where e.mgr = m.empno

Statement 30

Just to show it's possible. A connect-by query listing all reports and sub-reports and sub-sub-reports of the big guy.

SELECT JSON_ARRAYAGG(ename)
FROM emp
CONNECT BY PRIOR empno = mgr
START WITH empno = 7839

Statement 31

Pull the department name into the employees record. Easy in SQL: just join with 'dept' table.

select JSON_OBJECT ('id' VALUE empno, 
                    'name' VALUE ename,
                    'job' VALUE job,
                    'managerId' value MGR,
                    'department' value dname
                    ) 
from emp e, dept d
where e.deptno = d.deptno

Statement 32

JSON_OBJECTAGG build as JSON object where not only the values but also the keys come from an expression - in this case column values. The result is one object build from multiple rows. Keys need to be strings - therefor the to_char.

select json_objectagg(to_char(deptno) VALUE dname) from dept

Statement 33

If keys are not unique then previous values get overwritten. Not good.

select json_objectagg('bla' VALUE dname) from dept

Statement 34

add 'with unique keys' to check for duplicate key names when generating the JSON object. You see an error if they got detected. Also, this option will be a bit slower since we have to check that the names are not overwritten.

select json_objectagg('bla' VALUE dname with unique keys) from dept

Statement 35

Now, we want to extend our application. We want to allow user to maintain a profile. List their skills, add keywords, photo url etc. We chose JSON because this is schema flexible. If employees need to state their shoe size we can ad dit later into the JSON as we go - no need for an ALTER TABLE ADD COLUMN. Here, we create a SODA collection using pl/sql. SODA stands for Simple Oracle Document Access. Just think of the collection as a fancy table for now. We call the collection 'EMP_PROFILES' and we tell the collection that we will provide the keys associated with each document.

DECLARE
    collection SODA_COLLECTION_T;
    metadata VARCHAR2(4000) :=
      '{"keyColumn" : {"name" : "KEY",  
                       "assignmentMethod": "CLIENT" },
        "contentColumn" : { "name" : "PROFILE", 
                            "sqlType": "VARCHAR2" } }';
BEGIN
    collection := DBMS_SODA.create_collection('EMP_PROFILES', metadata);
END;

Statement 36

Using the SODA Api we insert 3 documents into the collection.

DECLARE
    collection  SODA_COLLECTION_T;
    document    SODA_DOCUMENT_T;
    status      NUMBER;
BEGIN
    -- Open the collection
    collection := DBMS_SODA.open_collection('EMP_PROFILES');
    document := SODA_DOCUMENT_T(key =>7369, v_content =>'{"skills":["java", "c", "pl/sql"]}');
    -- Insert a document
    status := collection.insert_one(document);


    document := SODA_DOCUMENT_T(key =>7788, v_content =>'{"skills":["javascript, python, perl"]}');
    status := collection.insert_one(document);
    document := SODA_DOCUMENT_T(key =>7900, v_content =>'{"skills":["fuzzy testing"]}');
    status := collection.insert_one(document);
END;

Statement 37

We can query the collection like a table (because it is just a fancy table).

select * from emp_profiles

Statement 38

Now we can pull the 'profiles' JSON data into out employee record. Please note that the 'info' value is not double quoted although the values is a string. This is because the collection has an IS JSON check constraint. The JSON generation knows that the value is JSON data. Therefore it does not quote the value like it would do normally for a string value.

select JSON_OBJECT ('id' VALUE empno, 
                    'name' VALUE ename,
                    'job' VALUE job,
                    'managerId' value MGR,
                    'info' value profile
                    ) 
from emp e, emp_profiles p
where e.empno = p.key

Statement 39

A stupid function that just returns the inputted value. If I were a mathematician I would call the function 'identity' ;-)

create or replace function foo (v in varchar) return varchar2 is
begin
  return v;
end;

Statement 40

Now look at this: the function foo() does not alter the value but our JSON_OBJECT function now does not know anymore that the value originated from the collection. It is just a regular string value - therefore it gets double quoted.

select JSON_OBJECT ('id' VALUE empno, 
                    'name' VALUE ename,
                    'job' VALUE job,
                    'managerId' value MGR,
                    'info' value foo(profile)
                    ) 
from emp e, emp_profiles p
where e.empno = p.key

Statement 41

If you do not want a string to be double quoted just add FORMAT JSON. This way you tell the generation function that a value is to be treated like JSON. We trust you!

select JSON_OBJECT ('id' VALUE empno, 
                    'name' VALUE ename,
                    'job' VALUE job,
                    'managerId' value MGR,
                    'info' value foo(profile) FORMAT JSON
                    ) 
from emp e, emp_profiles p
where e.empno = p.key

Statement 42

Now, if you give us garbage and tell us it is JSON we trust you and generate some out put that is not valid JSON!

select JSON_OBJECT ('id' VALUE empno, 
                    'name' VALUE ename,
                    'job' VALUE job,
                    'managerId' value MGR,
                    'info' value 'I am not Json' FORMAT JSON
                    ) 
from emp e, emp_profiles p
where e.empno = p.key

Statement 43

If you think we should check the generated output for syntax validity then just add the keyword STRICT at then end of the JSON generation function. Now were checking that everything is fine. If you cheated with the FORMAT JSON we'll find out! Why don't we do this by default? Because the extra checks cost time.

select JSON_OBJECT ('id' VALUE empno, 
                    'name' VALUE ename,
                    'job' VALUE job,
                    'managerId' value MGR,
                    'info' value 'I am not Json' FORMAT JSON
                    STRICT) 
from emp e, emp_profiles p
where e.empno = p.key

Statement 44

This function takes a JSON payload (like {"empno":7934, "newName":"Patel"}) and updates the underlying relational table. We extract the empno and new name and run the update.

create or replace procedure renameEmp (rec VARCHAR2) is
 id      number;
 newName varchar2(40);
begin
  id := json_value(rec, '$.empno');
  newName := json_value(rec, '$.newName');
  update emp set ename = newName where empno = id;
end;

Statement 45

rename employee

exec renameEmp('{"empno":7934, "newName":"Patel"}')

Statement 46

Similarly, this is how we can update a JSON record. We parse the old record, extract the skills array, add a new skill to the end of the array and serialize the in-memory representation back to a string that we then use to update the old JSON column value. This is just one way of doing such update.

create or replace procedure addSkill (rec VARCHAR2) is
 id          number;
 skill       varchar2(4000);
 profile_v   varchar2(4000);
 profile_obj JSON_OBJECT_T;
 skills_arr  JSON_ARRAY_T;
begin
  id    := json_value(rec, '$.empno');
  skill := json_value(rec, '$.newSkill');

  select profile into profile_v from emp_profiles where key = id;
  
  profile_obj := JSON_OBJECT_T(profile_v);
  
  skills_arr := profile_obj.get_array('skills');
  skills_arr.append(skill);
  
  profile_v := profile_obj.stringify;
  update emp_profiles set profile = profile_v where key = id;
end;

Statement 47

Someone got a new skill.....

exec addSkill('{"empno":7900, "newSkill":"Youtube videos"}')

Statement 48

pull the employees record and see the new skill

select JSON_OBJECT ('id' VALUE empno, 
                    'name' VALUE ename,
                    'job' VALUE job,
                    'managerId' value MGR,
                    'info' value profile
                    ) 
from emp e, emp_profiles p
where e.empno = p.key
  and e.empno = 7900

JSON Generation from relational data (AskTom Office hours, Sept 11th 2018)

Additional Information