8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 23 » Here

JSON-Relational Duality Views in Oracle Database 23ai

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.

Related 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

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 DEPARTMENT_DV, 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 department_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.

create or replace json relational duality view department_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
  }
};

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 department_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 department_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 department_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 also query data from SQL using dot notation.

column departmentname format A20
column location format A20

select d.data.departmentName,
       d.data.location
from   department_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   department_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.

The UNNEST keyword 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'll use the DEPARTMENT_DV view in the subsequent examples.

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 department_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   department_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 department_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   department_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 department_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   department_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 department_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   department_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

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. 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 department_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   department_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 department_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 department_dv d
*
ERROR at line 1:
ORA-42699: Cannot update JSON Relational Duality View 'DEPARTMENT_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.

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

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       => 'DEPARTMENT_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.

Additional Information

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

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...

Back to the Top.