JSON Relational Duality in Oracle 23ai

-- Create a JSON table for customer data

CREATE TABLE customers_json (

    id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,

    customer_doc JSON

);



-- Create a relational view of the JSON data

CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW customers_relational

FOR customers_json

HIERARCHICAL(customer_doc)

WITH (

    id NUMBER PATH '$.id',

    first_name VARCHAR2(100) PATH '$.firstName',

    last_name VARCHAR2(100) PATH '$.lastName',

    email VARCHAR2(255) PATH '$.email',

    addresses NESTED PATH '$.addresses[*]' COLUMNS (

        street VARCHAR2(200) PATH '$.street',

        city VARCHAR2(100) PATH '$.city',

        state VARCHAR2(50) PATH '$.state',

        postal_code VARCHAR2(20) PATH '$.postalCode',

        address_type VARCHAR2(20) PATH '$.type'

    ),

    phone_numbers NESTED PATH '$.phoneNumbers[*]' COLUMNS (

        number VARCHAR2(20) PATH '$.number',

        type VARCHAR2(20) PATH '$.type'

    )

);

-- Create traditional relational tables

CREATE TABLE customers (

    customer_id NUMBER PRIMARY KEY,

    first_name VARCHAR2(100),

    last_name VARCHAR2(100),

    email VARCHAR2(255)

);



CREATE TABLE addresses (

    address_id NUMBER PRIMARY KEY,

    customer_id NUMBER REFERENCES customers(customer_id),

    street VARCHAR2(200),

    city VARCHAR2(100),

    state VARCHAR2(50),

    postal_code VARCHAR2(20),

    address_type VARCHAR2(20)

);



CREATE TABLE phone_numbers (

    phone_id NUMBER PRIMARY KEY,

    customer_id NUMBER REFERENCES customers(customer_id),

    number VARCHAR2(20),

    type VARCHAR2(20)

);



-- Create a JSON view of the relational data

CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW customer_documents

FOR customers

WITH (

    customer_id,

    first_name AS "firstName",

    last_name AS "lastName",

    email,

    addresses NESTED (

        SELECT a.street, a.city, a.state, a.postal_code AS "postalCode", a.address_type AS "type"

        FROM addresses a

        WHERE a.customer_id = customers.customer_id

    ),

    phone_numbers NESTED (

        SELECT p.number, p.type

        FROM phone_numbers p

        WHERE p.customer_id = customers.customer_id

    )

);

-- Query using relational paradigm

SELECT c.first_name, c.last_name, a.city, a.state

FROM customers_relational c

JOIN customers_relational.addresses a ON 1=1

WHERE a.address_type = 'HOME'

ORDER BY c.last_name;



-- Query using document paradigm

SELECT c.customer_doc.firstName, 

       c.customer_doc.lastName,

       j.city, 

       j.state

FROM customers_json c,

     JSON_TABLE(c.customer_doc, '$.addresses[*]' COLUMNS (

         city VARCHAR2(100) PATH '$.city',

         state VARCHAR2(50) PATH '$.state',

         type VARCHAR2(20) PATH '$.type'

     )) j

WHERE j.type = 'HOME'

ORDER BY c.customer_doc.lastName;



-- Update using relational paradigm

UPDATE customers_relational

SET email = 'john.smith@example.com'

WHERE first_name = 'John' AND last_name = 'Smith';



-- Update using document paradigm

UPDATE customers_json c

SET c.customer_doc = JSON_TRANSFORM(c.customer_doc, 

                                   SET '$.email' = 'john.smith@example.com')

WHERE c.customer_doc.firstName = 'John' 

  AND c.customer_doc.lastName = 'Smith';