Unlocking Oracle 23 AI's JSON Relational Duality
Oracle 23ai combines AI with JSON and relational duality, allowing easy querying and updates across both formats. Learn how this integration streamlines data management.
Join the DZone community and get the full member experience.
Join For FreeOracle 23ai's JSON Relational Duality bridges the gap between JSON and relational data, which allows seamless querying and updating of data in both formats. This feature allows developers to efficiently manage hybrid data models, simplify application workflows, and reduce data transformation overhead.
It also allows them to query and update data in JSON and relational formats seamlessly, simplifying data handling for modern applications.
Table Setup for Examples
To explore JSON Relational Duality, we’ll create and populate two relational tables: departments and employees. These will be the foundation for our JSON views.
--- DROP existing TABLES (IF ANY)
DROP TABLE IF EXISTS employees 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 employees TABLE
CREATE TABLE employees
(
emp_id NUMBER(4) PRIMARY KEY,
emp_name VARCHAR2(50),
ROLE VARCHAR2(50),
salary NUMBER(10,2),
dept_id NUMBER(2) REFERENCES departments(dept_id)
);
--- INSERT sample data
INSERT INTO departments VALUES
(
1,
'Engineering',
'San Francisco'
);
INSERT INTO departments VALUES
(
2,
'Marketing',
'New York'
);
INSERT INTO departments VALUES
(
3,
'HR',
'Chicago'
);
INSERT INTO employees VALUES
(
1001,
'Alice',
'Engineer',
95000,
1
);
INSERT INTO employees VALUES
(
1002,
'Bob',
'Manager',
120000,
1
);
INSERT INTO employees VALUES
(
2001,
'Carol',
'Marketer',
70000,
2
);
INSERT INTO employees VALUES
(
3001,
'Eve',
'HR Specialist',
80000,
3
);
COMMIT;Creating a JSON-Relational Duality View
A JSON Relational Duality View combines relational and JSON data into a unified, JSON-based interface. Let’s create a view department_details that consolidates department information and its employees into JSON documents.
SQL Syntax
CREATE
OR
replace json relational duality VIEW department_details AS
SELECT json { '_id': d.dept_id,
'departmentName': d.dept_name,
'location': d.location,
'employees': [
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 document structure and specifying the data sources. In the following example, we create a view called department_details using SQL or JSON.
This view lists department information, including an array of employees within each department. For each table, we define the possible operations that can be performed on the underlying table. In this example, we can allow the INSERT, UPDATE, and DELETE operations for both tables.
Querying the JSON Data
SQL> desc department_details
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
DATA
SQL> SELECT JSON_SERIALIZE(d.data PRETTY) FROM department_details d;
Sample output:
{
"_id": 1,
"departmentName": "Engineering",
"location": "San Francisco",
"employees": [
{
"employeeId": 1001,
"employeeName": "Alice",
"role": "Engineer",
"salary": 95000
},
{
"employeeId": 1002,
"employeeName": "Bob",
"role": "Manager",
"salary": 120000
}
]
}
Updating Data With JSON Views
With JSON Relational Duality, we can update data using JSON structures directly.
UPDATE department_details d
SET d.data = json { '_id': 1,
'departmentName': 'Engineering & Development',
'location': 'San Francisco',
'employees': [ { 'employeeId': 1001,
'employeeName': 'Alice',
'role': 'Senior Engineer',
'salary': 105000 } ] }
WHERE d.data."_id" = 1;This update modifies the relational tables behind the view while preserving the JSON abstraction.
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' = 'CHICAGO2')
where d.data."_id" = 30;
select * from dept where deptno = 30;
DEPTNO DNAME LOC
---------- -------------- -------------
30 SALES CHICAGO2Adding New Records
We can insert a new department and its employees using a single JSON document.
INSERT INTO department_details d
(
data
)
VALUES
(
json { '_id': 4,
'departmentName': 'IT Support',
'location': 'Austin',
'employees': [ { 'employeeId': 4001,
'employeeName': 'Dave',
'role': 'Technician',
'salary': 65000 } ] }
);The new department and its employees will appear in both JSON and relational tables.
Deleting Data
Deleting a department removes its associated employees as well.
DELETE FROM department_details d WHERE d.data."_id" = 3;
1 row deleted.
SQL>Optimistic Locking With _etag
Each JSON document includes a _etag value for optimistic locking, ensuring safe concurrent updates.
In all the previous operations, we've ignored the state, assuming the data is not changing. In reality, the data may have changed between our service calls. JSON-relational duality views give us a way to manage the state, providing us with an "etag" effectively a version we can use for optimistic locking. This is also known as value-based concurrency. The following example shows this.
Example
Query the data to get the _etag value:
SELECT JSON_SERIALIZE(d.data PRETTY) FROM department_details d WHERE d.data."_id" = 2;Flattening Data With @unnest
The UNNEST keyword (@unnest) allows us to produce flat documents by unnesting the result of a scalar subquery. In these examples, we create the EMPLOYEES_DETAILS view containing employee information and the associated department information for each employee in a flat document. Notice the UNNEST keyword in both examples.
We can create flat JSON documents combining employees and department information.
CREATE
OR
replace json relational duality VIEW employee_details AS
SELECT json { '_id': e.emp_id,
'employeeName': e.emp_name,
'role': e.ROLE,
'salary': e.salary,
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 employees e WITH
INSERT
UPDATE
DELETE;Query the flattened view:
SELECT JSON_SERIALIZE(d.data PRETTY) FROM employee_details d;
set long 1000000 pagesize 1000 linesize 100
select json_serialize(d.data pretty) from employee_details 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"
}
.
.
.
-- X rows selected.
Conclusion
Oracle 23ai's JSON Relational Duality improves data handling by merging the best of relational and JSON worlds. It empowers developers to easily build modern, scalable applications, whether for querying, updating, or managing concurrency.
Opinions expressed by DZone contributors are their own.
Comments