Unlocking Oracle 23 AI’s JSON Relational Duality

Oracle 23ai’s JSON Relational Duality bridges the hole between JSON and relational information, which permits seamless querying and updating of knowledge in each codecs. This characteristic permits builders to effectively handle hybrid information fashions, simplify utility workflows, and scale back information transformation overhead.

It additionally permits them to question and replace information in JSON and relational codecs seamlessly, simplifying information dealing with for contemporary purposes.

Desk Setup for Examples

To discover JSON Relational Duality, we’ll create and populate two relational tables: departments and staff. These would be the basis for our JSON views.

--- DROP present TABLES (IF ANY)
DROP TABLE IF EXISTS staff purge;
DROP TABLE IF exists departments purge;

--- CREATE departments TABLE
CREATE TABLE departments
             (
                          dept_id   NUMBER(2) PRIMARY KEY,
                          dept_name VARCHAR2(50),
                          location  VARCHAR2(50)
             );
             
--- CREATE staff TABLE
CREATE TABLE staff
             (
                          emp_id   NUMBER(4) PRIMARY KEY,
                          emp_name VARCHAR2(50),
                          ROLE     VARCHAR2(50),
                          wage   NUMBER(10,2),
                          dept_id  NUMBER(2) REFERENCES departments(dept_id)
             );
             
--- INSERT pattern information
INSERT INTO departments VALUES
            (
                        1,
                        'Engineering',
                        'San Francisco'
            );
INSERT INTO departments VALUES
            (
                        2,
                        'Advertising',
                        'New York'
            );
INSERT INTO departments VALUES
            (
                        3,
                        'HR',
                        'Chicago'
            );
INSERT INTO staff VALUES
            (
                        1001,
                        'Alice',
                        'Engineer',
                        95000,
                        1
            );
INSERT INTO staff VALUES
            (
                        1002,
                        'Bob',
                        'Supervisor',
                        120000,
                        1
            );
INSERT INTO staff VALUES
            (
                        2001,
                        'Carol',
                        'Marketer',
                        70000,
                        2
            );
INSERT INTO staff VALUES
            (
                        3001,
                        'Eve',
                        'HR Specialist',
                        80000,
                        3
            );
COMMIT;

Making a JSON-Relational Duality View

A JSON Relational Duality View combines relational and JSON information right into a unified, JSON-based interface. Let’s create a view department_details that consolidates division data and its staff into JSON paperwork.

SQL Syntax

CREATE
OR
exchange json relational duality VIEW department_details AS
SELECT json { '_id': d.dept_id,
       'departmentName': d.dept_name,
       'location': d.location,
       'staff':         [
SELECT json { 'employeeId': e.emp_id,
       'employeeName': e.emp_name,
       'role': e.ROLE,
       'salary': e.salary }
FROM   employees e WITH
INSERT
UPDATE
DELETE
WHERE  e.dept_id = d.dept_id ] }

We create a JSON-relational duality view by defining the doc construction and specifying the information sources. Within the following instance, we create a view known as department_details utilizing SQL or JSON.

This view lists division data, together with an array of staff inside every division. For every desk, we outline the doable operations that may be carried out on the underlying desk. On this instance, we are able to enable the INSERT, UPDATE, and DELETE operations for each tables.

Querying the JSON Knowledge

SQL> desc department_details
 Title                                                  Null?    Sort
 ----------------------------------------------------- -------- ------------------------------------
 DATA  


SQL> SELECT JSON_SERIALIZE(d.information PRETTY) FROM department_details d;

Pattern output:

{
  "_id": 1,
  "departmentName": "Engineering",
  "location": "San Francisco",
  "staff": [
    {
      "employeeId": 1001,
      "employeeName": "Alice",
      "role": "Engineer",
      "salary": 95000
    },
    {
      "employeeId": 1002,
      "employeeName": "Bob",
      "role": "Manager",
      "salary": 120000
    }
  ]
}

Updating Knowledge With JSON Views

With JSON Relational Duality, we are able to replace information utilizing JSON buildings immediately.

UPDATE department_details d
SET    d.information = json { '_id': 1,
       'departmentName': 'Engineering & Growth',
       'location': 'San Francisco',
       'staff': [ { 'employeeId': 1001,
       'employeeName': 'Alice',
       'role': 'Senior Engineer',
       'salary': 105000 } ] }
WHERE  d.information."_id" = 1;

This replace modifies the relational tables behind the view whereas preserving the JSON abstraction.

We are able to additionally replace the desk utilizing JSON_TRANSFORM, so we’re treating the desk as if it have been a JSON desk.

replace departments_dv d
set    d.information = json_transform(d.information, set '$.location' = 'CHICAGO2')
the place d.information."_id" = 30;


choose * from dept the place deptno = 30;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        30 SALES     		CHICAGO2

Including New Information

We are able to insert a brand new division and its staff utilizing a single JSON doc.

INSERT INTO department_details d
            (
                        information
            )
            VALUES
            (
                        json { '_id': 4,
                        'departmentName': 'IT Assist',
                        'location': 'Austin',
                        'staff': [ { 'employeeId': 4001,
                        'employeeName': 'Dave',
                        'role': 'Technician',
                        'salary': 65000 } ] }
            );

The brand new division and its staff will seem in each JSON and relational tables.

Deleting Knowledge

Deleting a division removes its related staff as effectively.

DELETE FROM department_details d WHERE d.information."_id" = 3;
1 row deleted.

SQL>

Optimistic Locking With _etag

Every JSON doc features a _etag worth for optimistic locking, making certain protected concurrent updates.

In all of the earlier operations, we have ignored the state, assuming the information is just not altering. In actuality, the information might have modified between our service calls. JSON-relational duality views give us a technique to handle the state, offering us with an “etag” successfully a model we are able to use for optimistic locking. That is often known as value-based concurrency. The next instance reveals this.

Instance

Question the information to get the _etag worth:

SELECT JSON_SERIALIZE(d.information PRETTY) FROM department_details d WHERE d.information."_id" = 2;

Flattening Knowledge With @unnest

The UNNEST key phrase (@unnest) permits us to supply flat paperwork by unnesting the results of a scalar subquery. In these examples, we create the EMPLOYEES_DETAILS view containing worker data and the related division data for every worker in a flat doc. Discover the UNNEST key phrase in each examples.

We are able to create flat JSON paperwork combining staff and division data.

CREATE
OR
exchange json relational duality VIEW employee_details AS
SELECT json { '_id':   e.emp_id,
       'employeeName': e.emp_name,
       'position':         e.ROLE,
       'wage':       e.wage,
       unnest
       (
              SELECT json { 'departmentId': d.dept_id,
                     'departmentName':      d.dept_name,
                     'location': d.location }
              FROM   departments d WITH
              UPDATE
              WHERE  d.dept_id = e.dept_id ) }
FROM   staff e WITH
INSERT
UPDATE
DELETE;

Question the flattened view:

SELECT JSON_SERIALIZE(d.information PRETTY) FROM employee_details d;

set lengthy 1000000 pagesize 1000 linesize 100

choose json_serialize(d.information fairly) from employee_details d;

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

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

-- X rows chosen.

Conclusion

Oracle 23ai’s JSON Relational Duality improves information dealing with by merging the very best of relational and JSON worlds. It empowers builders to simply construct trendy, scalable purposes, whether or not for querying, updating, or managing concurrency.