DZone
Data Engineering
Databases
Unlocking Oracle 23 AI's JSON Relational Duality

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.

👁 arvind toorpu user avatar

arvind toorpu

👁 DZone Core
CORE ·

Mar. 19, 25 · Analysis

Likes (3)

Comment

Save

3.3K Views

Join the DZone community and get the full member experience.

Join For Free

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

SQL

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

SQL

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

SQL> desc department_details
 Name Null? Type
 ----------------------------------------------------- -------- ------------------------------------
 DATA 


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

Sample output:

JSON

{
 "_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.

SQL

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.

MariaDB SQL

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     		CHICAGO2

Adding New Records

We can insert a new department and its employees using a single JSON document.

SQL

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.

SQL

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:

SQL

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.

SQL

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:

SQL

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.

AI JSON relational

Opinions expressed by DZone contributors are their own.

Give Your AI Assistant Long-Term Memory With perag
MCP Elicitation: Human-in-the-Loop for MCP Servers
Logging MCP Protocol When Using stdio, Part II
Fresh Data for AI With Spring AI Function Calls

URL: https://dzone.com/articles/unlocking-oracle-23c-ais-json-relational-duality