📋Steps in this guide1/9

JSON-Relational Duality Views in Oracle Database 23ai/26ai

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.

oracle 23configurationintermediate

by OracleDba

140 views

Setup

The examples in this article require the following tables. 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.

Code/Command (click line numbers to comment):

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'),
  (20,'RESEARCH','DALLAS'),
  (30,'SALES','CHICAGO'),
  (40,'OPERATIONS','BOSTON');

insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values
  (7369,'SMITH','CLERK',7902,to_date('17-DEC-80','DD-MON-RR'),800,null,20),
  (7499,'ALLEN','SALESMAN',7698,to_date('20-FEB-81','DD-MON-RR'),1600,300,30),
  (7521,'WARD','SALESMAN',7698,to_date('22-FEB-81','DD-MON-RR'),1250,500,30),
  (7566,'JONES','MANAGER',7839,to_date('02-APR-81','DD-MON-RR'),2975,null,20),
  (7654,'MARTIN','SALESMAN',7698,to_date('28-SEP-81','DD-MON-RR'),1250,1400,30),
  (7698,'BLAKE','MANAGER',7839,to_date('01-MAY-81','DD-MON-RR'),2850,null,30),
  (7782,'CLARK','MANAGER',7839,to_date('09-JUN-81','DD-MON-RR'),2450,null,10),
  (7788,'SCOTT','ANALYST',7566,to_date('19-APR-87','DD-MON-RR'),3000,null,20),
  (7839,'KING','PRESIDENT',null,to_date('17-NOV-81','DD-MON-RR'),5000,null,10),
  (7844,'TURNER','SALESMAN',7698,to_date('08-SEP-81','DD-MON-RR'),1500,0,30),
  (7876,'ADAMS','CLERK',7788,to_date('23-MAY-87','DD-MON-RR'),1100,null,20),
  (7900,'JAMES','CLERK',7698,to_date('03-DEC-81','DD-MON-RR'),950,null,30),
  (7902,'FORD','ANALYST',7566,to_date('03-DEC-81','DD-MON-RR'),3000,null,20),
  (7934,'MILLER','CLERK',7782,to_date('23-JAN-82','DD-MON-RR'),1300,null,10);
commit;

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 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 . 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 . 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 , and directives to control the allowed actions. We could tell the database the foreign key column used to make the join between the tables using the directive. The resulting view is the same, regardless of the syntax, so use the one you prefer. The resulting view has a single column called with a data type of JSON. 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. We use the function to pretty print the JSON data, so it is more readable. 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 ( ) to omit the table from the "etag" generation. This way changes to the employees for the department don't affect optimistic locking of the department itself. We can query data from SQL using dot notation. Notice that department 40 has no employees. 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 keyword (@unnest) allows us to produce flat documents by unnesting the result of a scalar subquery. In these examples we create the view, which contains employee information, along with the associated department information for each employee in a flat document. Notice the keyword in both examples. The view gives us the flat documents we expect. We can calculate additional values using 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. We'll use the view in the subsequent examples.

Code/Command (click line numbers to comment):

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;

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
  }]
};

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
  }]
};

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

SQL>

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>

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>

-- 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
  }]
};

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>

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>

-- 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
  }
};

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>

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>

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. We see the new department in its JSON form. As we would expect, the data is also visible directly from the relational tables. We rollback the changes, so we have the test data in its original form for the next test.

Code/Command (click line numbers to comment):

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

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>

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>

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 clause. We can see the employee is now visible in the JSON and relational forms. We rollback the changes. We can also update the table using , so we are treating the table as if it were a JSON table. We rollback the change to return the test data to its original form.

Code/Command (click line numbers to comment):

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;

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>

rollback;

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>

rollback;

DELETE

We can delete rows from the base tables by deleting them from the view. We check the current data for department 40. We delete department 40 from the view using dot notation to reference the department number. We can see the department is now gone. We rollback the change to return the test data to its original form.

Code/Command (click line numbers to comment):

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>

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

1 row deleted.

SQL>

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

no rows selected

SQL>

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. Let's assume we want to make a change to this document. We query the JSON document. Notice the "etag" value. We add another employee to department "50" using a conventional insert. This simulates the data changing between the last time we checked the document. Now we attempt to update the department, passing the original "etag" value in the "_metadata" tag. 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.

Code/Command (click line numbers to comment):

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

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>

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

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>

Views

The following views are available to support JSON-relational duality views. There are also the , and versions of the views. - The view includes a column that represents the structure of the JSON data in the view.

SODA and JSON-Relational Duality Views

Initial releases of version 23 required an extra step to visualise duality views as SODA collections. In later releases, duality views are automatically visible as SODA collections, so this additional step is no longer necessary. There is more information about SODA here.

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: Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!