JSON-Relational Duality Views in Oracle Database 23c
JSON-Relational Duality Views in Oracle Database 23ai






      



 Home » Articles » 23 » Here



JSON-relational duality views expose our relational data as JSON documents, allowing both query and DML operations to be performed using conventional SQL or directly using JSON.

There is a whole manual devoted to JSON-Relational Duality, so this article will just be a brief introduction to the subject.


  Setup
  Create a JSON-Relational Duality View (SQL and GraphQL)
  INSERT
  UPDATE
  DELETE
  Managing State (Value-Based Concurrency)
  Views
  SODA and JSON-Relational Duality Views
  Additional Information


Related articles.


  Oracle REST Data Services (ORDS) : AutoREST of JSON-Relational Duality Views
  JSON Support Enhancements in Oracle Database 23ai
  JSON Quick Links : 
      Introduction,
      12cR1,
      12cR2,
      18c,
      19c,
      21c,
      23ai,
      All Articles
  




Setup

The examples in this article require the following tables.

drop table if exists emp purge;
drop table if exists dept purge;

create table dept (
  deptno number(2) constraint pk_dept primary key,
  dname varchar2(14),
  loc varchar2(13)
) ;

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

create index emp_dept_fk_i on emp(deptno);

insert into dept values (10,'ACCOUNTING','NEW YORK');
insert into dept values (20,'RESEARCH','DALLAS');
insert into dept values (30,'SALES','CHICAGO');
insert into dept values (40,'OPERATIONS','BOSTON');

insert into emp values (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,null,20);
insert into emp values (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
insert into emp values (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
insert into emp values (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,null,20);
insert into emp values (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
insert into emp values (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,null,30);
insert into emp values (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,null,10);
insert into emp values (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,null,20);
insert into emp values (7839,'KING','PRESIDENT',null,to_date('17-11-1981','dd-mm-yyyy'),5000,null,10);
insert into emp values (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
insert into emp values (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,null,20);
insert into emp values (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,null,30);
insert into emp values (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,null,20);
insert into emp values (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,null,10);
commit;

Make sure you are using a new version of the client to connect to the database. Older clients may struggle with some of the syntax variations.



Create a JSON-Relational Duality View (SQL and GraphQL)

We create a JSON-relational duality view by defining the document structure, stating where the data is sourced from. In the following example we create a view called DEPARTMENTS_DV using SQL/JSON, which lists department information, including an array of employees within the department. For each table we define the operations that are possible against the underlying table. In this example we set both to WITH INSERT UPDATE DELETE.

create or replace json relational duality view departments_dv as
select json {'_id' : d.deptno,
             'departmentName'   : d.dname,
             'location'         : d.loc,
             'employees' :
               [ select json {'employeeNumber' : e.empno,
                              'employeeName'   : e.ename,
                              'job'            : e.job,
                              'salary'         : e.sal}
                 from   emp e with insert update delete
                 where  d.deptno = e.deptno ]}
from dept d with insert update delete;

The "_id" tag represents the document identifier, which we have mapped to the primary key. For a composite primary key we would use a JSON object as the value of the "_id" tag, for example {col1, col2, col3}.

Alternatively we can use a GraphQL syntax to create the view. In this example we reference the tables and columns, and rely on the database to work out the relationship between the tables using the foreign keys. We use the @insert, @update and @delete directives to control the allowed actions.

create or replace json relational duality view departments_dv as
dept @insert @update @delete
{
  _id: deptno
  departmentName : dname
  location : loc
  employees : emp @insert @update @delete
  [{
    employeeNumber : empno
    employeeName : ename
    job : job
    salary : sal
  }]
};

We could tell the database the foreign key column used to make the join between the tables using the @link directive.

create or replace json relational duality view departments_dv as
dept @insert @update @delete
{
  _id: deptno
  departmentName : dname
  location : loc
  employees : emp @insert @update @delete @link (to : ["deptno"])
  [{
    employeeNumber : empno
    employeeName : ename
    job : job
    salary : sal
  }]
};

The resulting view is the same, regardless of the syntax, so use the one you prefer.

The resulting view has a single column called DATA with a data type of JSON.

SQL> desc departments_dv
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 DATA                                                           JSON

SQL>

If we query the view we can see a number of system generated identifiers, and the JSON data, based on the contents of the referenced tables.

set long 1000000 pagesize 1000 linesize 100

select * from departments_dv;

DATA
--------------------------------------------------------------------------------
{"_id":10,"_metadata":{"etag":"E546E2220E8F9620E36C2A7F8858D6F7","asof":"0000000
0002F03CA"},"departmentName":"ACCOUNTING","location":"NEW YORK","employees":[{"e
mployeeNumber":7782,"employeeName":"CLARK","job":"MANAGER","salary":2450},{"empl
oyeeNumber":7839,"employeeName":"KING","job":"PRESIDENT","salary":5000},{"employ
eeNumber":7934,"employeeName":"MILLER","job":"CLERK","salary":1300}]}

{"_id":20,"_metadata":{"etag":"8DAFACC22EC949A2C54B9F7BBE79B171","asof":"0000000
0002F03CA"},"departmentName":"RESEARCH","location":"DALLAS","employees":[{"emplo
yeeNumber":7369,"employeeName":"SMITH","job":"CLERK","salary":800},{"employeeNum
ber":7566,"employeeName":"JONES","job":"MANAGER","salary":2975},{"employeeNumber
":7788,"employeeName":"SCOTT","job":"ANALYST","salary":3000},{"employeeNumber":7
876,"employeeName":"ADAMS","job":"CLERK","salary":1100},{"employeeNumber":7902,"
employeeName":"FORD","job":"ANALYST","salary":3000}]}

{"_id":30,"_metadata":{"etag":"72D95F921FBC3FFC59C269B80EFBA5CF","asof":"0000000
0002F03CA"},"departmentName":"SALES","location":"CHICAGO","employees":[{"employe
eNumber":7499,"employeeName":"ALLEN","job":"SALESMAN","salary":1600},{"employeeN
umber":7521,"employeeName":"WARD","job":"SALESMAN","salary":1250},{"employeeNumb
er":7654,"employeeName":"MARTIN","job":"SALESMAN","salary":1250},{"employeeNumbe
r":7698,"employeeName":"BLAKE","job":"MANAGER","salary":2850},{"employeeNumber":
7844,"employeeName":"TURNER","job":"SALESMAN","salary":1500},{"employeeNumber":7
900,"employeeName":"JAMES","job":"CLERK","salary":950}]}

{"_id":40,"_metadata":{"etag":"6FAB9798FF405D87F0EB44456398A5D5","asof":"0000000
0002F03CA"},"departmentName":"OPERATIONS","location":"BOSTON","employees":[]}


SQL>

We use the JSON_SERIALIZE function to pretty print the JSON data, so it is more readable.

select json_serialize(d.data pretty) from departments_dv d;

JSON_SERIALIZE(D.DATAPRETTY)
----------------------------------------------------------------------------------------------------
{
  "_id" : 10,
  "_metadata" :
  {
    "etag" : "E546E2220E8F9620E36C2A7F8858D6F7",
    "asof" : "00000000002F03D5"
  },
  "departmentName" : "ACCOUNTING",
  "location" : "NEW YORK",
  "employees" :
  [
    {
      "employeeNumber" : 7782,
      "employeeName" : "CLARK",
      "job" : "MANAGER",
      "salary" : 2450
    },
    {
      "employeeNumber" : 7839,
      "employeeName" : "KING",
      "job" : "PRESIDENT",
      "salary" : 5000
    },
    {
      "employeeNumber" : 7934,
      "employeeName" : "MILLER",
      "job" : "CLERK",
      "salary" : 1300
    }
  ]
}

{
  "_id" : 20,
  "_metadata" :
  {
    "etag" : "8DAFACC22EC949A2C54B9F7BBE79B171",
    "asof" : "00000000002F03D5"
  },
  "departmentName" : "RESEARCH",
  "location" : "DALLAS",
  "employees" :
  [
    {
      "employeeNumber" : 7369,
      "employeeName" : "SMITH",
      "job" : "CLERK",
      "salary" : 800
    },
    {
      "employeeNumber" : 7566,
      "employeeName" : "JONES",
      "job" : "MANAGER",
      "salary" : 2975
    },
    {
      "employeeNumber" : 7788,
      "employeeName" : "SCOTT",
      "job" : "ANALYST",
      "salary" : 3000
    },
    {
      "employeeNumber" : 7876,
      "employeeName" : "ADAMS",
      "job" : "CLERK",
      "salary" : 1100
    },
    {
      "employeeNumber" : 7902,
      "employeeName" : "FORD",
      "job" : "ANALYST",
      "salary" : 3000
    }
  ]
}

{
  "_id" : 30,
  "_metadata" :
  {
    "etag" : "72D95F921FBC3FFC59C269B80EFBA5CF",
    "asof" : "00000000002F03D5"
  },
  "departmentName" : "SALES",
  "location" : "CHICAGO",
  "employees" :
  [
    {
      "employeeNumber" : 7499,
      "employeeName" : "ALLEN",
      "job" : "SALESMAN",
      "salary" : 1600
    },
    {
      "employeeNumber" : 7521,
      "employeeName" : "WARD",
      "job" : "SALESMAN",
      "salary" : 1250
    },
    {
      "employeeNumber" : 7654,
      "employeeName" : "MARTIN",
      "job" : "SALESMAN",
      "salary" : 1250
    },
    {
      "employeeNumber" : 7698,
      "employeeName" : "BLAKE",
      "job" : "MANAGER",
      "salary" : 2850
    },
    {
      "employeeNumber" : 7844,
      "employeeName" : "TURNER",
      "job" : "SALESMAN",
      "salary" : 1500
    },
    {
      "employeeNumber" : 7900,
      "employeeName" : "JAMES",
      "job" : "CLERK",
      "salary" : 950
    }
  ]
}

{
  "_id" : 40,
  "_metadata" :
  {
    "etag" : "6FAB9798FF405D87F0EB44456398A5D5",
    "asof" : "00000000002F03D5"
  },
  "departmentName" : "OPERATIONS",
  "location" : "BOSTON",
  "employees" :
  [
  ]
}


SQL>

As expected, each row contains a single document defining the department, and all the employees in that department. The "etag" attribute is a hash generated from the contents of the record, which can be used to support optimistic locking, checking that the contents of the record have not changed since the JSON document was created. We can think of this as a version of the document.

We can control which elements are used when generating the "etag". In the following example use WITH NOCHECK (@nocheck) to omit the EMP table from the "etag" generation. This way changes to the employees for the department don't affect optimistic locking of the department itself.

-- SQL syntax.
create or replace json relational duality view departments_dv as
select json {'_id' : d.deptno,
             'departmentName'   : d.dname,
             'location'         : d.loc,
             'employees' :
               [ select json {'employeeNumber' : e.empno,
                              'employeeName'   : e.ename,
                              'job'            : e.job,
                              'salary'         : e.sal}
                 from   emp e with nocheck
                 where  d.deptno = e.deptno ]}
from dept d with insert update delete;


-- GraphQL syntax.
create or replace json relational duality view departments_dv as
dept @insert @update @delete
{
  _id: deptno
  departmentName : dname
  location : loc
  employees : emp @nocheck
  [{
    employeeNumber : empno
    employeeName : ename
    job : job
    salary : sal
  }]
};

We can query data from SQL using dot notation.

column departmentname format A20
column location format A20

select d.data.departmentName,
       d.data.location
from   departments_dv d
where  d.data."_id"= 40;

DEPARTMENTNAME       LOCATION
-------------------- --------------------
"OPERATIONS"         "BOSTON"

SQL>

Notice that department 40 has no employees.

select json_serialize(d.data pretty)
from   departments_dv d
where  d.data."_id" = 40;

JSON_SERIALIZE(D.DATAPRETTY)
----------------------------------------------------------------------------------------------------
{
  "_id" : 40,
  "_metadata" :
  {
    "etag" : "6FAB9798FF405D87F0EB44456398A5D5",
    "asof" : "00000000002F2799"
  },
  "departmentName" : "OPERATIONS",
  "location" : "BOSTON",
  "employees" :
  [
  ]
}


SQL>

We can still use conventional DML against the base tables to modify the data, but now we can also work directly against JSON documents, as demonstrated in the following sections.

The UNNEST keyword (@unnest) allows us to produce flat documents by unnesting the result of a scalar subquery. In these examples we create the EMPLOYEE_DV view, which contains employee information, along with the associated department information for each employee in a flat document. Notice the UNNEST keyword in both examples.

-- SQL syntax.
create or replace json relational duality view employee_dv as
select json {'_id' : e.empno,
             'employeeName'   : e.ename,
             'job'             : e.job,
             'salary'          : e.sal,
             unnest (select json {'departmentNumber' : d.deptno,
                                  'departmentName'   : d.dname,
                                  'location'         : d.loc}
                     from   dept d with update
                     where  d.deptno = e.deptno)}
from emp e with insert update delete;


-- GraphQL syntax.
create or replace json relational duality view employee_dv as
emp @insert @update @delete
{
  _id : empno
  employeeName : ename
  job : job
  salary : sal
  dept @unnest @update
  {
    departmentNumber : deptno
    departmentName : dname
    location : loc
  }
};

The EMPLOYEE_DV view gives us the flat documents we expect.

set long 1000000 pagesize 1000 linesize 100

select json_serialize(d.data pretty) from employee_dv d;

JSON_SERIALIZE(D.DATAPRETTY)
----------------------------------------------------------------------------------------------------
{
  "_id" : 7369,
  "_metadata" :
  {
    "etag" : "A63777A126E5F53961E8C4A16C266EBB",
    "asof" : "00000000002F2825"
  },
  "employeeName" : "SMITH",
  "job" : "CLERK",
  "salary" : 800,
  "departmentNumber" : 20,
  "departmentName" : "RESEARCH",
  "location" : "DALLAS"
}

{
  "_id" : 7499,
  "_metadata" :
  {
    "etag" : "9D9E402CAF3D10EF54D4247D73823D3F",
    "asof" : "00000000002F2825"
  },
  "employeeName" : "ALLEN",
  "job" : "SALESMAN",
  "salary" : 1600,
  "departmentNumber" : 30,
  "departmentName" : "SALES",
  "location" : "CHICAGO"
}

{
  "_id" : 7521,
  "_metadata" :
  {
    "etag" : "74F4CD7F3B259FEA3FC0DDCCFB1401C8",
    "asof" : "00000000002F2825"
  },
  "employeeName" : "WARD",
  "job" : "SALESMAN",
  "salary" : 1250,
  "departmentNumber" : 30,
  "departmentName" : "SALES",
  "location" : "CHICAGO"
}

{
  "_id" : 7566,
  "_metadata" :
  {
    "etag" : "08D7586DDDDA8815C79F7699B27855D0",
    "asof" : "00000000002F2825"
  },
  "employeeName" : "JONES",
  "job" : "MANAGER",
  "salary" : 2975,
  "departmentNumber" : 20,
  "departmentName" : "RESEARCH",
  "location" : "DALLAS"
}

{
  "_id" : 7654,
  "_metadata" :
  {
    "etag" : "F5CD155921D861857FD235AE2BA33B32",
    "asof" : "00000000002F2825"
  },
  "employeeName" : "MARTIN",
  "job" : "SALESMAN",
  "salary" : 1250,
  "departmentNumber" : 30,
  "departmentName" : "SALES",
  "location" : "CHICAGO"
}

{
  "_id" : 7698,
  "_metadata" :
  {
    "etag" : "D3BBEE8D354F8D8196A341DD4D6BD5A0",
    "asof" : "00000000002F2825"
  },
  "employeeName" : "BLAKE",
  "job" : "MANAGER",
  "salary" : 2850,
  "departmentNumber" : 30,
  "departmentName" : "SALES",
  "location" : "CHICAGO"
}

{
  "_id" : 7782,
  "_metadata" :
  {
    "etag" : "54B65297EE2FDD71C2446AF340EF5FEB",
    "asof" : "00000000002F2825"
  },
  "employeeName" : "CLARK",
  "job" : "MANAGER",
  "salary" : 2450,
  "departmentNumber" : 10,
  "departmentName" : "ACCOUNTING",
  "location" : "NEW YORK"
}

{
  "_id" : 7788,
  "_metadata" :
  {
    "etag" : "0F8E38BC4010500A69BB7DEB60A866B2",
    "asof" : "00000000002F2825"
  },
  "employeeName" : "SCOTT",
  "job" : "ANALYST",
  "salary" : 3000,
  "departmentNumber" : 20,
  "departmentName" : "RESEARCH",
  "location" : "DALLAS"
}

{
  "_id" : 7839,
  "_metadata" :
  {
    "etag" : "0843EAB8EC26FA3750DFA257EE4CD226",
    "asof" : "00000000002F2825"
  },
  "employeeName" : "KING",
  "job" : "PRESIDENT",
  "salary" : 5000,
  "departmentNumber" : 10,
  "departmentName" : "ACCOUNTING",
  "location" : "NEW YORK"
}

{
  "_id" : 7844,
  "_metadata" :
  {
    "etag" : "9013BF2FAB19018195178852392505D0",
    "asof" : "00000000002F2825"
  },
  "employeeName" : "TURNER",
  "job" : "SALESMAN",
  "salary" : 1500,
  "departmentNumber" : 30,
  "departmentName" : "SALES",
  "location" : "CHICAGO"
}

{
  "_id" : 7876,
  "_metadata" :
  {
    "etag" : "6644297DF23A67BB8B64E58684BB3AE6",
    "asof" : "00000000002F2825"
  },
  "employeeName" : "ADAMS",
  "job" : "CLERK",
  "salary" : 1100,
  "departmentNumber" : 20,
  "departmentName" : "RESEARCH",
  "location" : "DALLAS"
}

{
  "_id" : 7900,
  "_metadata" :
  {
    "etag" : "608D2FE0707077C3CBCBC0433E5EC4A6",
    "asof" : "00000000002F2825"
  },
  "employeeName" : "JAMES",
  "job" : "CLERK",
  "salary" : 950,
  "departmentNumber" : 30,
  "departmentName" : "SALES",
  "location" : "CHICAGO"
}

{
  "_id" : 7902,
  "_metadata" :
  {
    "etag" : "DE4B194E2B4D20E581D5EBADAA05EA2A",
    "asof" : "00000000002F2825"
  },
  "employeeName" : "FORD",
  "job" : "ANALYST",
  "salary" : 3000,
  "departmentNumber" : 20,
  "departmentName" : "RESEARCH",
  "location" : "DALLAS"
}

{
  "_id" : 7934,
  "_metadata" :
  {
    "etag" : "7B4F094D40AB63A8C87DF841D3C9870B",
    "asof" : "00000000002F2825"
  },
  "employeeName" : "MILLER",
  "job" : "CLERK",
  "salary" : 1300,
  "departmentNumber" : 10,
  "departmentName" : "ACCOUNTING",
  "location" : "NEW YORK"
}


14 rows selected.

SQL>

We can calculate additional values using @generated directive in the GraphQL syntax. In the following example we calculate the sum of the salaries for all the employees in the department and use it to create the "employeeSalaries" element.

create or replace json relational duality view departments_dv as
dept
{
  _id: deptno,
  departmentName : dname
  location : loc
  employeeSalaries @generated(path: "$.employees.salary.sum()")
  employees : emp
  [{
    employeeNumber : empno
    employeeName : ename
    job : job
    salary : sal
  }]
};


column "_id" format a10
column "departmentName" format a20
column "employeeSalaries" format a20

select d.data."_id",
       d.data."departmentName",
       d.data."employeeSalaries"
from   departments_dv d
order by 1;

_id        departmentName       employeeSalaries
---------- -------------------- --------------------
10         "ACCOUNTING"         8750
20         "RESEARCH"           10875
30         "SALES"              9400
40         "OPERATIONS"

SQL>

We'll use the DEPARTMENTS_DV view in the subsequent examples.

create or replace json relational duality view departments_dv as
dept @insert @update @delete
{
  _id: deptno
  departmentName : dname
  location : loc
  employees : emp @insert @update @delete
  [{
    employeeNumber : empno
    employeeName : ename
    job : job
    salary : sal
  }]
};



INSERT

We create a new department via the view, with the JSON document to define the department and employees in a single statement.

insert into departments_dv d (data)
values ('
{
  "_id" : 50,
  "departmentName" : "DBA",
  "location" : "BIRMINGHAM",
  "employees" : [
    {
      "employeeNumber" : 9999,
      "employeeName" : "HALL",
      "job" : "CLERK",
      "salary" : 500
    }
  ]
}');

We see the new department in its JSON form.

select json_serialize(d.data pretty)
from   departments_dv d
where  d.data."_id" = 50;

JSON_SERIALIZE(D.DATAPRETTY)
----------------------------------------------------------------------------------------------------
{
  "_id" : 50,
  "_metadata" :
  {
    "etag" : "77052B06E84B60749E410D5C2BA797DF",
    "asof" : "00000000002F284B"
  },
  "departmentName" : "DBA",
  "location" : "BIRMINGHAM",
  "employees" :
  [
    {
      "employeeNumber" : 9999,
      "employeeName" : "HALL",
      "job" : "CLERK",
      "salary" : 500
    }
  ]
}


SQL>

As we would expect, the data is also visible directly from the relational tables.

select * from dept where deptno = 50;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        50 DBA            BIRMINGHAM

SQL>


select * from emp where deptno = 50;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      9999 HALL       CLERK                                 500                    50

SQL>

We rollback the changes, so we have the test data in its original form for the next test.

rollback;



UPDATE

In the following example we update department 40, adding a new employee into the department. Notice we have limited the update to department 40 using dot-notation in the WHERE clause.

update departments_dv d
set d.data = ('
{
  "_id" : 40,
  "departmentName" : "OPERATIONS",
  "location" : "BOSTON",
  "employees" : [
    {
      "employeeNumber" : 9999,
      "employeeName" : "HALL",
      "job" : "CLERK",
      "salary" : 500
    }
  ]
}')
where d.data."_id" = 40;

We can see the employee is now visible in the JSON and relational forms.

select json_serialize(d.data pretty)
from   departments_dv d
where  d.data."_id" = 40;

JSON_SERIALIZE(D.DATAPRETTY)
----------------------------------------------------------------------------------------------------
{
  "_id" : 40,
  "_metadata" :
  {
    "etag" : "B3AFCB3BFEC978F7D4FA139C516CBB4D",
    "asof" : "00000000002F28F8"
  },
  "departmentName" : "OPERATIONS",
  "location" : "BOSTON",
  "employees" :
  [
    {
      "employeeNumber" : 9999,
      "employeeName" : "HALL",
      "job" : "CLERK",
      "salary" : 500
    }
  ]
}


SQL>


select * from emp where empno = 9999;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      9999 HALL       CLERK                                 500                    40

SQL>

We rollback the changes.

rollback;

We can also update the table using JSON_TRANSFORM, so we are treating the table as if it were a JSON table.

update departments_dv d
set    d.data = json_transform(d.data, set '$.location' = 'BOSTON2')
where d.data."_id" = 40;


select * from dept where deptno = 40;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        40 OPERATIONS     BOSTON2

SQL>

We rollback the change to return the test data to its original form.

rollback;



DELETE

We can delete rows from the base tables by deleting them from the view. We check the current data for department 40.

select json_serialize(d.data pretty)
from   departments_dv d
where  d.data."_id" = 40;

JSON_SERIALIZE(D.DATAPRETTY)
----------------------------------------------------------------------------------------------------
{
  "_metadata" :
  {
    "etag" : "6FAB9798FF405D87F0EB44456398A5D5",
    "asof" : "00000000001FAA64"
  },
  "departmentNumber" : 40,
  "departmentName" : "OPERATIONS",
  "location" : "BOSTON",
  "employees" :
  [
  ]
}


SQL>

We delete department 40 from the view using dot notation to reference the department number.

delete from departments_dv d
where  d.data."_id" = 40;

1 row deleted.

SQL>

We can see the department is now gone.

select json_serialize(d.data pretty)
from   departments_dv d
where  d.data."_id" = 40;

no rows selected

SQL>

We rollback the change to return the test data to its original form.

rollback;



Managing State (Value-Based Concurrency)

In all the previous operations we've ignored state, assuming the data is not changing. In reality it's possible the data has changed between our service calls. JSON-relational duality views give us a way to manage the state, providing us with an "etag" which is effectively a version we can use for optimistic locking. This is also known as value-based concurrency. The following example shows this.

We create a new department via the view, with the JSON document to define the department and employees in a single statement.

insert into departments_dv d (data)
values ('
{
  "_id" : 50,
  "departmentName" : "DBA",
  "location" : "BIRMINGHAM",
  "employees" : [
    {
      "employeeNumber" : 9999,
      "employeeName" : "HALL",
      "job" : "CLERK",
      "salary" : 500
    }
  ]
}');

Let's assume we want to make a change to this document. We query the JSON document. Notice the "etag" value.

select json_serialize(d.data pretty)
from   departments_dv d
where  d.data."_id" = 50;

JSON_SERIALIZE(D.DATAPRETTY)
----------------------------------------------------------------------------------------------------
{
  "_id" : 50,
  "_metadata" :
  {
    "etag" : "77052B06E84B60749E410D5C2BA797DF",
    "asof" : "00000000002F293E"
  },
  "departmentName" : "DBA",
  "location" : "BIRMINGHAM",
  "employees" :
  [
    {
      "employeeNumber" : 9999,
      "employeeName" : "HALL",
      "job" : "CLERK",
      "salary" : 500
    }
  ]
}


SQL>

We add another employee to department "50" using a conventional insert. This simulates the data changing between the last time we checked the document.

insert into emp values (9997,'WOOD','CLERK',null,null,1300,null,50);
commit;

Now we attempt to update the department, passing the original "etag" value in the "_metadata" tag.

update departments_dv d
set d.data = ('
{
  "_metadata" : {"etag" : "77052B06E84B60749E410D5C2BA797DF"},
  "_id" : 50,
  "departmentName" : "DBA",
  "location" : "BIRMINGHAM",
  "employees" : [
    {
      "employeeNumber" : 9999,
      "employeeName" : "HALL",
      "job" : "SALESMAN",
      "salary" : 1000
    }
  ]
}')
where d.data."_id" = 40;

update departments_dv d
*
ERROR at line 1:
ORA-42699: Cannot update JSON Relational Duality View 'DEPARTMENTS_DV': The ETAG of document with ID 'FB03C12900' in the database did not match the ETAG passed in.


SQL>

The data change has caused the "etag" value to change, so the update caused an error. In order to proceed, we would have to re-query the data to get the new "etag" value, then try again.



Views

The following views are available to support JSON-relational duality views. There are also the ALL, DBA and CDB versions of the views.


  USER_JSON_DUALITY_VIEWS
  USER_JSON_DUALITY_VIEW_TABS
  USER_JSON_DUALITY_VIEW_TAB_COLS
  USER_JSON_DUALITY_VIEW_LINKS


The USER_JSON_DUALITY_VIEWS view includes a JSON_SCHEMA column that represents the structure of the JSON data in the view.



SODA and JSON-Relational Duality Views

Initial releases of version 23 required this step to visualise duality views as SODA collections. In later releases, duality views are automatically visible as SODA collections, so this step is no longer necessary.

We can create a SODA collection based on a JSON-relational duality view using the CREATE_DUALV_COLLECTION function in the DBMS_SODA package.

set serveroutput on
declare
  l_collection  soda_collection_t;
begin
  l_collection := dbms_soda.create_dualv_collection(
                    collection_name => 'departments',
                    view_name       => 'DEPARTMENTS_DV');

  if l_collection is not null then
    dbms_output.put_line('Collection ID : ' || l_collection.get_name());
  else
    dbms_output.put_line('Collection does not exist.');  
  end if;
end;
/
Collection ID : departments

PL/SQL procedure successfully completed.

SQL>

We drop the collection using the following code.

set serveroutout on
declare
  l_status  number := 0;
begin
  l_status := dbms_soda.drop_collection('departments');

  dbms_output.put_line('status    : ' || l_status);
end;
/
status          : 1

PL/SQL procedure successfully completed.

SQL>

There is more information about SODA here.


  Oracle REST Data Services (ORDS) : Simple Oracle Document Access (SODA) for REST
  Simple Oracle Document Access (SODA) for PL/SQL in Oracle Database 18c




Additional Information

There are two important notes in the documentation towards the bottom of the "Car-Racing Example, Tables" section here.


  "Primary-key, unique-key, and foreign-key integrity constraints must be defined for the tables that underlie duality views (or else an error is raised), but they need not be enforced."
  "The SQL data types allowed for a column in a table underlying a duality view are JSON, BLOB, CLOB, NCLOB, VARCHAR2, NVARCHAR2, CHAR, NCHAR, RAW, BOOLEAN, DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, INTERVAL YEAR TO MONTH. INTERVAL DAY TO SECOND, NUMBER, BINARY_DOUBLE, and BINARY_FLOAT. An error is raised if you specify any other column data type."


Always refer back to the documentation for information about restrictions, as these may change over time.



For more information see:


  JSON-Relational Duality Developer's Guide
  Oracle REST Data Services (ORDS) : AutoREST of JSON-Relational Duality Views
  JSON Support Enhancements in Oracle Database 23ai
  Oracle REST Data Services (ORDS) : Simple Oracle Document Access (SODA) for REST
  Simple Oracle Document Access (SODA) for PL/SQL in Oracle Database 18c
  JSON Quick Links : 
      Introduction,
      12cR1,
      12cR2,
      18c,
      19c,
      21c,
      23ai,
      All Articles
  


Hope this helps. Regards Tim...

Back to the Top.






