Introduction
In the last episode, we looked at some new features in Oracle Database 23.4. The IslandSQL grammar now covers all statements that can contain static DML statements and code in PL/SQL and PL/pgSQL.
While implementing the ANTLR grammar for the create JSON relational duality view statement I stumbled over GraphQL in this syntax diagram:
You can use GraphQL as an alternative to a subquery to describe the source of your JSON relational duality view. This feature was already part of 23.3. Usually, I don’t like it when there are several ways to do the same thing. However, in this case, GraphQL helped me to understand the variant of the select statement in the duality view better. GraphQL might even be better suited to describe the content of a duality view.
The funny thing is that JSON is about schema flexibility and GraphQL needs a schema to work. That sounds contradictory. However, if we reduce the scope of schema flexibility to a JSON object within existing entities, this can work quite well.
In this blog post, I explore some features related to the schema flexibility of JSON relational duality views.
- Setup
- Read-only View à la 19c
- Read-only Duality View
- Updateable Duality View Using SELECT
- Updateable Duality View Using GraphQL
- GraphQL vs. SELECT
- Insert Into Duality View
- Update Duality View
- Delete From Duality View
1. Setup
The examples in this blog post require an Oracle Database 23.4 (yes, 23.3 is not enough). In a schema of your choice you can run the following setup script:
set linesize 200
set pagesize 1000
set long 32767
column ext format a72
column data format a130
alter session set nls_date_format = 'YYYY-MM-DD';
drop table if exists emp;
drop table if exists dept;
create table dept (
deptno number(2, 0) not null constraint dept_pk primary key,
dname varchar2(14 char) not null,
loc varchar2(13 char) not null,
ext json(object)
);
create table emp (
empno number(4, 0) not null constraint emp_pk primary key,
ename varchar2(10 char) not null,
job varchar2(9 char) not null,
mgr number(4, 0) constraint emp_mgr_fk references emp,
hiredate date not null,
sal number(7, 2) not null,
comm number(7, 2),
deptno number(2, 0) not null constraint emp_deptno_fk references dept,
ext json(object)
);
insert into dept (deptno, dname, loc)
values (10, 'ACCOUNTING', 'NEW YORK'),
(20, 'RESEARCH', 'DALLAS'),
(30, 'SALES', 'CHICAGO'),
(40, 'OPERATIONS', 'BOSTON');
commit;
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7566, 'JONES', 'MANAGER', 7839, date '1981-04-02', 2975, null, 20),
(7698, 'BLAKE', 'MANAGER', 7839, date '1981-05-01', 2850, null, 30),
(7782, 'CLARK', 'MANAGER', 7839, date '1981-06-09', 2450, null, 10),
(7788, 'SCOTT', 'ANALYST', 7566, date '1987-04-19', 3000, null, 20),
(7902, 'FORD', 'ANALYST', 7566, date '1981-12-03', 3000, null, 20),
(7499, 'ALLEN', 'SALESMAN', 7698, date '1981-02-20', 1600, 300, 30),
(7521, 'WARD', 'SALESMAN', 7698, date '1981-02-22', 1250, 500, 30),
(7654, 'MARTIN', 'SALESMAN', 7698, date '1981-09-28', 1250, 1400, 30),
(7844, 'TURNER', 'SALESMAN', 7698, date '1981-09-08', 1500, 0, 30),
(7900, 'JAMES', 'CLERK', 7698, date '1981-12-03', 950, null, 30),
(7934, 'MILLER', 'CLERK', 7782, date '1982-01-23', 1300, null, 10),
(7369, 'SMITH', 'CLERK', 7902, date '1980-12-17', 800, null, 20),
(7839, 'KING', 'PRESIDENT', null, date '1981-11-17', 5000, null, 10),
(7876, 'ADAMS', 'CLERK', 7788, date '1987-05-23', 1100, null, 20);
commit;
Session altered.
Table EMP dropped.
Table DEPT dropped.
Table DEPT created.
Table EMP created.
4 rows inserted.
Commit complete.
14 rows inserted.
Commit complete.There are a few changes to the well-known dept and emp tables I’d like to highlight:
- Firstly, primary keys and foreign keys. They are required for the duality views, however, they do not need to be enabled.
- Secondly, both tables got an additional
extcolumn. The data type isjson(object). Before 23.4 there was just a genericjsondata type. With 23.4 it’s possible to add modifiers. The modifierobjectis required for flex columns in duality views.
2. Read-Only View à la 19c
Let’s step back and create a view that returns a single JSON column, as in Oracle Database 19c (to make it work in 19c you have to use for example ext clob check (ext is json) instead of ext json(object) in the tables dept and emp).
create or replace view dept_v as
select json_object(
deptno,
dname,
loc,
ext,
'sal': (select sum(sal) from emp where emp.deptno = dept.deptno)
absent on null
) as data
from dept;
select * from dept_v;View DEPT_V created.
DATA
--------------------------------------------------------------
{"deptno":10,"dname":"ACCOUNTING","loc":"NEW YORK","sal":8750}
{"deptno":20,"dname":"RESEARCH","loc":"DALLAS","sal":10875}
{"deptno":30,"dname":"SALES","loc":"CHICAGO","sal":9400}
{"deptno":40,"dname":"OPERATIONS","loc":"BOSTON"}3. Read-Only Duality View
And now let’s try to use the previous subquery in a duality view.
create or replace json duality view dept_dv as
select json_object(
deptno,
dname,
loc,
ext,
'sal': (select sum(sal) from emp where emp.deptno = dept.deptno)
absent on null
) as data
from dept;ORA-40616: Cannot create JSON Relational Duality View 'DEPT_DV': using ABSENT ON NULL in JSON_OBJECT() is not permitted.This does not work. The absent on null clause on line 8 is not supported in a duality view. Let’s remove this line and try again.
create or replace json duality view dept_dv as
select json_object(
deptno,
dname,
loc,
ext,
'sal': (select sum(sal) from emp where emp.deptno = dept.deptno)
) as data
from dept;ORA-40895: invalid SQL expression in JSON relational duality view (operators except JSON_OBJECT or JSON_ARRAYAGG not allowed)This still does not work. It’s not allowed to include an aggregate as in line 7. Let’s also remove this line and try again.
create or replace json duality view dept_dv as
select json_object(
deptno,
dname,
loc,
ext
) as data
from dept;ORA-40941: cannot specify a column name or subquery alias for JSON relational duality viewArgh. We cannot use the column alias data on line 7. Let’s remove the alias and try again.
create or replace json duality view dept_dv as
select json_object(
deptno,
dname,
loc,
ext
)
from dept;ORA-42647: Missing '_id' field at the root level for JSON-relational duality view 'DEPT_DV'.Okay, an _id field is required to identify a document. Composite keys can be passed as a JSON array. In this case, we do not need that. We can use deptno. Let’s amend the query and try again.
create or replace json duality view dept_dv as
select json_object(
'_id': deptno,
dname,
loc,
ext
)
from dept;
select * from dept_dv;Json DUALITY created.
DATA
----------------------------------------------------------------------------------------------------------------------------------
{"_id":10,"dname":"ACCOUNTING","loc":"NEW YORK","_metadata":{"etag":"38CBB37294BEE09C6D9867B5B1871FE2","asof":"000025652FBC556F"}}
{"_id":20,"dname":"RESEARCH","loc":"DALLAS","_metadata":{"etag":"1D1973E9B068183129F7DA59F6A9C283","asof":"000025652FBC556F"}}
{"_id":30,"dname":"SALES","loc":"CHICAGO","_metadata":{"etag":"11CC2AE352D52FFDAE0B7A3DFC99F836","asof":"000025652FBC556F"}}
{"_id":40,"dname":"OPERATIONS","loc":"BOSTON","_metadata":{"etag":"B33BBA9A74C046813C59BA0763AD81C9","asof":"000025652FBC556F"}}Finally, we have a working read-only duality view.
Please note that each document contains a _metadata object. The etag field is a hash value based on all document fields by default. It can be used for optimistic locking. The asof field represents the SCN (system change number). It’s useful for read consistency, this means querying related data in subsequent queries using the flashback_query_clause.
4. Updateable Duality View Using SELECT
Let’s create an updateable duality view that represents all data in our model and uses all relationships.
The highlighted lines contain clauses that work only in a duality view. In other words, the “select” part does not work as a standalone statement as in common relational views.
create or replace json duality view dept_dv as
select json {
'_id': deptno,
dname,
loc,
ext as flex,
'emps':
(
select json_arrayagg(
JSON {
emp.empno,
emp.ename,
emp.job,
unnest
(
select json {
'mgr' : mgr.empno with nocheck,
'mgrname': mgr.ename with nocheck
}
from emp mgr
where mgr.empno = emp.mgr
),
emp.hiredate,
emp.sal,
emp.comm,
ext as flex
}
)
from emp with insert update delete
where emp.deptno = dept.deptno
)
}
from dept with insert update delete;
select json_serialize(data returning clob pretty) as data
from dept_dv dv
where dv.data."_id".numberOnly() in (20, 40);Json DUALITY created.
DATA
------------------------------------------------
{
"_id" : 20,
"_metadata" :
{
"etag" : "88A4A9648C2CA1752E477545DCA85FD3",
"asof" : "00002565300350E5"
},
"dname" : "RESEARCH",
"loc" : "DALLAS",
"emps" :
[
{
"empno" : 7369,
"ename" : "SMITH",
"job" : "CLERK",
"mgr" : 7902,
"mgrname" : "FORD",
"hiredate" : "1980-12-17T00:00:00",
"sal" : 800,
"comm" : null
},
{
"empno" : 7566,
"ename" : "JONES",
"job" : "MANAGER",
"mgr" : 7839,
"mgrname" : "KING",
"hiredate" : "1981-04-02T00:00:00",
"sal" : 2975,
"comm" : null
},
{
"empno" : 7788,
"ename" : "SCOTT",
"job" : "ANALYST",
"mgr" : 7566,
"mgrname" : "JONES",
"hiredate" : "1987-04-19T00:00:00",
"sal" : 3000,
"comm" : null
},
{
"empno" : 7876,
"ename" : "ADAMS",
"job" : "CLERK",
"mgr" : 7788,
"mgrname" : "SCOTT",
"hiredate" : "1987-05-23T00:00:00",
"sal" : 1100,
"comm" : null
},
{
"empno" : 7902,
"ename" : "FORD",
"job" : "ANALYST",
"mgr" : 7566,
"mgrname" : "JONES",
"hiredate" : "1981-12-03T00:00:00",
"sal" : 3000,
"comm" : null
}
]
}
{
"_id" : 40,
"_metadata" :
{
"etag" : "28E9C49240CD26A29FDED7B253A38ED7",
"asof" : "00002565300350E5"
},
"dname" : "OPERATIONS",
"loc" : "BOSTON",
"emps" :
[
]
}Since the ext column in the dept and emp table is empty for all rows, we do not see any additional fields in the two JSON documents.
Due to the unest clause in line 14, the fields mgr and mgrname appear on the same level as all other fields of the table emp.
5. Updateable Duality View Using GraphQL
The next duality view is equivalent to the one in the previous chapter.
The highlighted lines with annotations match the highlighted clauses in the previous statement.
GraphQL requires a model for a query. The Oracle implementation uses tables, primary, and foreign keys to build the underlying model.
Some explanations
- In line 2, we use the
depttable as root. For select, insert, update and delete. This means we get a JSON document per department. - In line 8, we use the
emptable for the fieldemps. We expect an array of objects. However, we do not have to tell that explicitly. The Oracle Database will figure that out. There is just one relationship betweendeptandemp. Therefore it is clear how to join the tables and access the data. For select, insert, update and delete. - In line 13, we use the
emptable for the fieldsmgrandmgrname. The access is possible via the foreign keysemp_mgr_fkandemp_deptno_fk. We know that it isemp_mgr_fkbut the Oracle Database does not. We have to tell it. We do that with the@link(from: [mgr])annotation, to use themgrfield for the recursive join. An update ofmgrnameis not allowed (it is read-only by default). An update ofmgris allowed (it will update the foreign key column inemp).
create or replace json duality view dept_dv as
dept @insert @update @delete
{
_id: deptno
dname
loc
ext @flex
emps: emp @insert @update @delete
{
empno
ename
job
emp @unnest @link(from: [mgr])
{
mgr : empno @nocheck
mgrname: ename @nocheck
}
hiredate
sal
comm
ext @flex
}
};
select json_serialize(data returning clob pretty) as data
from dept_dv dv
where dv.data."_id".numberOnly() in (20, 40);Json DUALITY created.
DATA
------------------------------------------------
{
"_id" : 20,
"_metadata" :
{
"etag" : "88A4A9648C2CA1752E477545DCA85FD3",
"asof" : "00002565301D6D5C"
},
"dname" : "RESEARCH",
"loc" : "DALLAS",
"emps" :
[
{
"empno" : 7369,
"ename" : "SMITH",
"job" : "CLERK",
"mgr" : 7902,
"mgrname" : "FORD",
"hiredate" : "1980-12-17T00:00:00",
"sal" : 800,
"comm" : null
},
{
"empno" : 7566,
"ename" : "JONES",
"job" : "MANAGER",
"mgr" : 7839,
"mgrname" : "KING",
"hiredate" : "1981-04-02T00:00:00",
"sal" : 2975,
"comm" : null
},
{
"empno" : 7788,
"ename" : "SCOTT",
"job" : "ANALYST",
"mgr" : 7566,
"mgrname" : "JONES",
"hiredate" : "1987-04-19T00:00:00",
"sal" : 3000,
"comm" : null
},
{
"empno" : 7876,
"ename" : "ADAMS",
"job" : "CLERK",
"mgr" : 7788,
"mgrname" : "SCOTT",
"hiredate" : "1987-05-23T00:00:00",
"sal" : 1100,
"comm" : null
},
{
"empno" : 7902,
"ename" : "FORD",
"job" : "ANALYST",
"mgr" : 7566,
"mgrname" : "JONES",
"hiredate" : "1981-12-03T00:00:00",
"sal" : 3000,
"comm" : null
}
]
}
{
"_id" : 40,
"_metadata" :
{
"etag" : "28E9C49240CD26A29FDED7B253A38ED7",
"asof" : "00002565301D6D5C"
},
"dname" : "OPERATIONS",
"loc" : "BOSTON",
"emps" :
[
]
}The result is the same as for the variant based on select. The only difference is the asof fields, which is expected.
6. GraphQL vs. SELECT
What I like about the GraphQL variant is that the syntax is simple. It looks similar to the result document and is easy to read. No compromises due to feature parity. I run less risk of trying SQL expressions that are not applicable in a duality view. The downside is that the definition might become ambiguous when extending the model with additional foreign key relationships. You might need to add @link annotations to your existing duality views to successfully recreate them. The variant using select cannot become ambiguous. There is no default join logic in SQL yet.
However, writing complex duality views might be easier with the variant using select. I can temporarily comment out all duality-view-specific clauses to make the select part work as a standalone statement until I’m happy with the result.
From a performance point of view, it should theoretically not matter which syntax variant you use. Any duality view can be built on GraphQL or select. The optimizer has all the information it needs to produce an optimal execution plan for both variants. I see no reason why the internal representation should differ.
Maybe a future version of the Oracle Database will offer options to generate the preferred syntax variant independently of the originally deployed variant. By extending dbms_metadata.get_ddl, for example.
7. Insert Into Duality View
Here’s an example of inserting a JSON document with one department and two employees into the previously created duality view.
Some explanations
- In line 6, we populate a department field named
secretwith the boolean valuetrue. This field does not exist in the model. Therefore it will be stored in theextcolumn of thedepttable. - In line 13, we set the
mgrfield to1. That’s the foreign key column in theemptable. - In line 16 we populate an employee field named
toolswith an array. The field does not exist in the model. Therefore it will be stored in theextcolumn of theemptable.
insert into dept_dv values ('
{
"_id" : 50,
"dname" : "MI6",
"loc" : "LONDON",
"secret" : true,
"emps" :
[
{
"empno" : 7,
"ename" : "BOND",
"job" : "AGENT",
"mgr" : 1,
"hiredate" : "1950-01-01T00:00:00",
"sal" : 500,
"tools" : ["Knife", "Garrote Watch", "Walther PPK"]
},
{
"empno" : 1,
"ename" : "M",
"job" : "MANAGER",
"hiredate" : "1940-01-01T00:00:00",
"sal" : 1000,
"comm" : 8000
}
]
}
');
commit;
select * from dept where deptno = 50;
select * from emp where deptno = 50;
select json_serialize(data returning clob pretty) as data
from dept_dv dv
where dv.data.secret.booleanOnly();1 row inserted.
Commit complete.
DEPTNO DNAME LOC EXT
---------- -------------- ------------- --------------------
50 MI6 LONDON {"secret":true}
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO EXT
---------- ---------- --------- ---------- ---------- ---------- ---------- ---------- ---------------------------------------------------
1 M MANAGER 1940-01-01 1000 8000 50
7 BOND AGENT 1 1950-01-01 500 50 {"tools":["Knife","Garrote Watch","Walther PPK"]}
DATA
------------------------------------------------
{
"_id" : 50,
"_metadata" :
{
"etag" : "486256AA33D638F4D339FFE534EC910F",
"asof" : "0000256530950ADB"
},
"dname" : "MI6",
"loc" : "LONDON",
"emps" :
[
{
"empno" : 1,
"ename" : "M",
"job" : "MANAGER",
"hiredate" : "1940-01-01T00:00:00",
"sal" : 1000,
"comm" : 8000
},
{
"empno" : 7,
"ename" : "BOND",
"job" : "AGENT",
"mgr" : 1,
"mgrname" : "M",
"hiredate" : "1950-01-01T00:00:00",
"sal" : 500,
"comm" : null,
"tools" :
[
"Knife",
"Garrote Watch",
"Walther PPK"
]
}
],
"secret" : true
}One insert statement leads to three new rows in two tables. Before 23ai, a view and an instead-of trigger would have been required for this.
The fields secret and tools are automatically stored in the flex columns ext. This shows how easy it is to extend the data model on the fly with an insert statement. Without DDL statements. Without PL/SQL code.
8. Update Duality View
Let’s update the previously created document.
Some explanations
- In line 4, we add a new field named
streetfor the department50. - In line 6, we change the salary for all employees in the department
50by the factor of42. - In line 9, we increase the salary of
BONDby1. Please note that this is the second change of the salary for this employee in thisupdatestatement. - In line 10, we append the
Aston Martin DB5to the list ofBOND‘s tools.
update dept_dv v
set v.data = json_transform(
v.data,
set '$.street' = '85 Albert Embankment',
nested '$.emps[*]' (
set '@.sal' = path '@.sal * 42'
),
nested '$.emps[*]?(@.ename == "BOND")' (
set '@.sal' = path '@.sal + 1',
append '@.tools' = 'Aston Martin DB5'
)
)
where v.data."_id".numberOnly() = 50;
commit;
select * from dept where deptno = 50;
select * from emp where deptno = 50;
select json_serialize(data returning clob pretty) as data
from dept_dv v
where v.data."_id".numberOnly() = 50;1 row updated.
Commit complete.
DEPTNO DNAME LOC EXT
---------- -------------- ------------- -----------------------------------------------
50 MI6 LONDON {"secret":true,"street":"85 Albert Embankment"}
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO EXT
---------- ---------- --------- ---------- ---------- ---------- ---------- ---------- ----------------------------------------------------------------------
1 M MANAGER 1940-01-01 42000 8000 50
7 BOND AGENT 1 1950-01-01 21001 50 {"tools":["Knife","Garrote Watch","Walther PPK","Aston Martin DB5"]}
DATA
------------------------------------------------
{
"_id" : 50,
"_metadata" :
{
"etag" : "1591A6C3A20C4BC85C0498F7B1F4031F",
"asof" : "000025653374F0C7"
},
"dname" : "MI6",
"loc" : "LONDON",
"emps" :
[
{
"empno" : 1,
"ename" : "M",
"job" : "MANAGER",
"hiredate" : "1940-01-01T00:00:00",
"sal" : 42000,
"comm" : 8000
},
{
"empno" : 7,
"ename" : "BOND",
"job" : "AGENT",
"mgr" : 1,
"mgrname" : "M",
"hiredate" : "1950-01-01T00:00:00",
"sal" : 21001,
"comm" : null,
"tools" :
[
"Knife",
"Garrote Watch",
"Walther PPK",
"Aston Martin DB5"
]
}
],
"secret" : true,
"street" : "85 Albert Embankment"
}9. Delete From Duality View
And now, let’s delete department 50 with all its employees to restore the original content of the dept and emp tables.
delete dept_dv v
where v.data."_id".numberOnly() = 50;
commit;
select * from dept order by deptno;
select * from emp order by deptno, empno;1 row deleted.
Commit complete.
DEPTNO DNAME LOC EXT
---------- -------------- ------------- --------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO EXT
---------- ---------- --------- ---------- ---------- ---------- ---------- ---------- --------------------
7782 CLARK MANAGER 7839 1981-06-09 2450 10
7839 KING PRESIDENT 1981-11-17 5000 10
7934 MILLER CLERK 7782 1982-01-23 1300 10
7369 SMITH CLERK 7902 1980-12-17 800 20
7566 JONES MANAGER 7839 1981-04-02 2975 20
7788 SCOTT ANALYST 7566 1987-04-19 3000 20
7876 ADAMS CLERK 7788 1987-05-23 1100 20
7902 FORD ANALYST 7566 1981-12-03 3000 20
7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 1250 500 30
7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 2850 30
7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30
7900 JAMES CLERK 7698 1981-12-03 950 30
14 rows selected.Outlook
One thing is missing in version 0.9 of IslandSQL grammar. The support of PL/pgSQL in PostgreSQL statements create function, create procedure, create trigger and do. These statements can already be parsed but the PL/pgSQL code passed as string is not further analyzed. This will change in the next and final episode of this season.