Working with Oracle Database 23ai JSON Relational Duality Views

Executive Summary

JSON Relational Duality Views in Oracle Database 23ai bridge the gap between JSON document databases and relational systems, enabling JSON developers to perform full CRUD operations on relational data using familiar JSON syntax while maintaining ACID compliance and referential integrity.

Database Enhancement: Sakila DVD Rental Schema

Background and History

The Sakila database is a widely-used sample database originally developed by MySQL AB to demonstrate the capabilities of MySQL database server. Sakila has become the de facto standard sample database for learning and demonstrating database concepts across multiple database platforms.

Evolution Across Database Platforms:

• Original MySQL Version: Created by MySQL as a replacement for the older "world" sample database
• PostgreSQL Port: Community-driven effort to provide PostgreSQL developers with the same learning resources
• Oracle Implementation: Modern adaptation leveraging Oracle Database 23ai's advanced JSON and relational capabilities

The migration to Oracle represents an evolution from simple relational modeling to a hybrid approach that supports both traditional SQL operations and modern JSON document paradigms. This Oracle implementation maintains full compatibility with the original Sakila schema while adding JSON Relational Duality Views that enable NoSQL-style development patterns.

Why Sakila for JSON Relational Duality Demonstration:

• Complex Relationships: Demonstrates many-to-many associations (films-actors, films-categories)
• Hierarchical Data: Geographic relationships (country-city-address) ideal for nested JSON
• Business Logic: Real-world rental transactions with temporal data and financial calculations
• Varied Data Types: Mix of strings, numbers, dates, and text fields representative of modern applications

Schema Overview

The Sakila database represents a DVD rental business with the following core entities:

Geographic Hierarchy:

• 109 countries containing 600 cities with 603 addresses
• Supports customer, staff, and store locations

Film Catalog:

• 1,001 films in 6 languages across 16 categories
• 200 actors with 5,462 film-actor relationships
• Complex many-to-many relationships between films, actors, and categories

Business Operations:

• 599 customers across 2 stores with 2 staff members
• 4,581 inventory items tracking film copies
• 16,044 rentals generating 16,049 payment transactions

Relationship Patterns

Master-Detail Relationships:

COUNTRY -> CITY -> ADDRESS -> CUSTOMER/STAFF/STORE
LANGUAGE -> FILM -> INVENTORY -> RENTAL -> PAYMENT

Many-to-Many Associations:

FILM <-> FILM_ACTOR <-> ACTOR
FILM <-> FILM_CATEGORY <-> CATEGORY

Business Process Flow:

CUSTOMER rents FILM through INVENTORY managed by STAFF at STORE
RENTAL generates PAYMENT tracked by STAFF

---

JSON Relational Duality Views Implementation

Implementation Strategy

Based on the relationship analysis, I created nine complementary JSON Relational Duality Views that provide different perspectives on the data:

1. Entity-focused views for core business objects
2. Relationship-focused views for managing associations
3. Transaction-focused views for business processes
4. Simple and complex variants for different use cases

Key Design Principles

Updatability Rules:

• Root tables support full CRUD operations (@insert @update @delete)
• Dependent tables limited to updates only (@update)
• Junction table arrays support full CRUD for relationship management
• All primary key columns must be included for unique identification

JSON Structure Design:

• Use _id as the primary identifier for JSON developers
• Nest related data logically (address within customer)
• Represent one-to-many relationships as arrays
• Include foreign keys for relationship management

CRUD Operations Guide

CREATE Operations

Customer Creation: JSON developers can create new customers or staff with a single JSON document with either SQL or MQL:

INSERT INTO customers_dv (data) VALUES (
  json_object(
    '_id' VALUE 1001,
    'firstName' VALUE 'Sarah',
    'lastName' VALUE 'Developer',
    'email' VALUE 'sarah@jsonteam.com',
    'storeId' VALUE 1,
    'active' VALUE '1',
    'createDate' VALUE SYSDATE,
    'addressId' VALUE 5
  )
);

db.staff_dv.insertOne({
  _id: 1002,
  firstName: "Matt",
  lastName: "DeMarco",
  email: "matthew.demarco@oracle.com",
  username: "oramatt",
  password: "Secret123!",         
  active: "1",
  storeId: 1,
  addressId: 5,
  lastUpdate: new Date()         
});

db.staff_dv.aggregate([{$match:{email:"matthew.demarco@oracle.com" }}]).explain();

Film with Cast Creation:

INSERT INTO films_dv (data) VALUES (
  json_object(
    '_id' VALUE 1004,
    'title' VALUE 'JSON Chronicles',
    'description' VALUE 'Epic tale of data transformation',
    'languageId' VALUE 1,
    'rentalRate' VALUE 4.99,
    'actors' VALUE json_array(
      json_object(
        'actorId' VALUE 1,
        'actor' VALUE json_object('actorId' VALUE 1)
      )
    )
  )
);

READ Operations

Simple JSON Queries:

-- Find customers by city
SELECT json_value(data, '$.firstName') as name,
       json_value(data, '$.email') as email
FROM customers_dv 
WHERE json_value(data, '$.addressId') IN (
  SELECT json_value(data, '$._id') 
  FROM addresses_dv 
  WHERE json_value(data, '$.cityId') = 300
);

Complex Array Queries:

-- Find films with specific actors
SELECT json_value(data, '$.title') as film_title
FROM films_dv 
WHERE json_exists(data, '$.actors[*] ? (@.actor.firstName == "TOM")');

Aggregation with JSON:

-- Analyze rental patterns
SELECT 
  json_value(data, '$.inventory.film.rating') as rating,
  COUNT(*) as rental_count,
  AVG(json_value(data, '$.payments[0].amount')) as avg_payment
FROM rentals_dv
GROUP BY json_value(data, '$.inventory.film.rating');

UPDATE Operations

Property Updates:

-- Update customer email and status
UPDATE customers_dv 
SET data = json_transform(data, 
  SET '$.email' = 'newemail@domain.com',
  SET '$.active' = '1'
)
WHERE json_value(data, '$._id') = 1000;

Conditional Updates:

-- Increase rental rates for popular films
UPDATE films_simple_dv 
SET data = json_transform(data, 
  SET '$.rentalRate' = json_value(data, '$.rentalRate') + 0.50
)
WHERE json_value(data, '$.rating') IN ('PG', 'PG-13');

Array Management:

-- Add actor to film (through films_dv)
UPDATE films_dv 
SET data = json_transform(data, 
  APPEND '$.actors' = json_object(
    'actorId' VALUE 150,
    'actor' VALUE json_object('actorId' VALUE 150)
  )
)
WHERE json_value(data, '$._id') = 1;

DELETE Operations

Simple Deletion:

-- Remove customer
DELETE FROM customers_dv 
WHERE json_value(data, '$._id') = 1001;

Conditional Deletion:

-- Remove inactive customers
DELETE FROM customers_dv 
WHERE json_value(data, '$.active') = '0';

Benefits for JSON Developers

Development Experience

Familiar JSON Patterns:

• Use dot notation for property access ($.customer.firstName)
• Work with arrays using standard JSON syntax
• Apply JSON functions for filtering and transformation
• No need to learn complex SQL JOIN syntax

Simplified Data Access:

• Single JSON document represents complete business entities
• No need to manage multiple table relationships
• Automatic foreign key resolution and validation
• Intuitive nested object navigation

Enterprise Database Benefits

ACID Compliance:

• Full transactional integrity across all operations
• Automatic rollback on constraint violations
• Consistent reads across related tables
• Deadlock detection and resolution

Performance Optimization:

• JSON path expressions can leverage database indexes
• Query optimizer handles JOIN operations automatically
• Efficient storage with no data duplication
• Parallel query execution for complex aggregations

Data Integrity:

• Foreign key constraints automatically enforced
• Check constraints applied to JSON operations
• Triggers fire on JSON document changes
• Audit trails maintained automatically

Migration Strategy for MongoDB Applications

Phase 1: Create Duality Views

1. Analyze existing MongoDB collections and document structures
2. Map to relational tables with appropriate foreign key relationships
3. Create JSON Relational Duality Views that mirror MongoDB collection interfaces
4. Test CRUD operations for compatibility

Phase 2: Application Integration

1. Update connection strings to point to Oracle
2. Replace MongoDB queries with JSON path expressions
3. Modify insert/update operations to use json_transform
4. Add transaction management (commit/rollback)

Phase 3: Enhanced Capabilities

1. Leverage SQL for complex analytics not possible in MongoDB
2. Add relational reporting alongside JSON operations
3. Implement cross-collection JOINs using SQL
4. Utilize Oracle enterprise features (partitioning, compression, etc.)

Use Case Examples

E-commerce Application

• Products Duality View: Manage product catalog with categories and specifications
• Orders Duality View: Handle complete order lifecycle with line items
• Customers Duality View: Profile management with purchase history

Content Management System

• Articles Duality View: Content with tags, authors, and comments arrays
• Users Duality View: User profiles with roles and permissions
• Media Duality View: Asset management with metadata and usage tracking

Registration System (Your App)

• Registrations Duality View: User registrations with geographic data
• Events Duality View: Event management with attendee arrays
• Analytics Duality View: Aggregated data for reporting dashboards

Performance Considerations

Optimization Strategies

Indexing:

• Create functional indexes on frequently queried JSON paths
• Use composite indexes for complex filtering scenarios
• Consider partial indexes for conditional queries

Query Design:

• Use json_value for simple property access
• Use json_query for array and object extractions
• Leverage json_exists for efficient filtering
• Combine JSON and relational predicates for optimal performance

Memory Management:

• JSON documents cached in buffer pool like relational data
• Large JSON documents benefit from compression
• Consider partitioning strategies for high-volume collections

Conclusion

JSON Relational Duality Views provide a powerful bridge between JSON document paradigms and enterprise relational databases. They enable:

• Developer Productivity: JSON developers can work with familiar syntax
• Enterprise Reliability: Full ACID compliance and data integrity
• Migration Flexibility: Gradual transition from MongoDB to Oracle
• Analytical Power: SQL capabilities alongside JSON operations
• Performance Scale: Enterprise-grade optimization and indexing

The implementation demonstrates that JSON and relational paradigms can coexist seamlessly, providing the best of both worlds for modern application development.

---

Appendix: Getting Started with JSON Relational Duality Views

What You Need

JSON Relational Duality Views allow your existing relational tables to be accessed as JSON documents - no data migration required. Here are the only two requirements:

1. Database Version

Oracle Database 23ai or later

That's it. JSON Relational Duality Views are a feature introduced in Oracle Database 23ai. If you have 23ai, you're ready to go.

To check your version:

SELECT version FROM v$instance;

2. Table Requirements

Tables need primary keys

Any table you want to access as JSON must have a primary key. This is the same best practice you're likely already following.

-- Your existing table probably already looks like this:
CREATE TABLE customers (
    customer_id NUMBER PRIMARY KEY,  -- This primary key is all you need
    first_name VARCHAR2(50),
    last_name VARCHAR2(50),
    email VARCHAR2(100)
);

Relationships, use foreign keys

To create nested JSON documents that show related data, your tables should have foreign keys. Again, this is standard relational database design you're probably already using.

-- Parent-child relationship you likely already have:
CREATE TABLE orders (
    order_id NUMBER PRIMARY KEY,
    customer_id NUMBER,
    order_date DATE,
    total_amount NUMBER(10,2),
    CONSTRAINT fk_order_customer 
        FOREIGN KEY (customer_id) 
        REFERENCES customers(customer_id)
);

That's All You Need

With Oracle Database 23ai and tables that have primary keys, you can immediately start creating JSON views of your data:

-- Turn your existing customer table into a JSON-accessible view
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW customers_dv AS
    customers @insert @update @delete
    {
        _id: customer_id,
        firstName: first_name,
        lastName: last_name,
        email: email
    };

Now your applications can work with this data as JSON:

-- Insert data using JSON
INSERT INTO customers_dv (data) VALUES (
    json_object(
        '_id' VALUE 1,
        'firstName' VALUE 'John',
        'lastName' VALUE 'Doe',
        'email' VALUE 'john@example.com'
    )
);

-- Query returns JSON documents
SELECT data FROM customers_dv;

Why This Matters

• No Migration: Your existing tables stay exactly as they are
• No Duplication: The JSON view is just another way to access the same data
• Full ACID Compliance: All your database constraints and transactions still work
• Best of Both Worlds: SQL applications keep working while new applications use JSON

Quick Checklist

Before creating a JSON Relational Duality View:

• Oracle Database 23ai? Check your version with SELECT version FROM v$instance
• Primary Keys? Your tables need them (you probably already have them)
• That's it! You're ready to create JSON views of your relational data

Simple Example: From Relational to JSON in 30 Seconds

-- Step 1: You already have this table
CREATE TABLE products (
    product_id NUMBER PRIMARY KEY,
    name VARCHAR2(100),
    price NUMBER(10,2),
    category VARCHAR2(50)
);

-- Step 2: Create a JSON view of it (takes 5 seconds)
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW products_dv AS
    products @insert @update @delete
    {
        _id: product_id,
        name: name,
        price: price,
        category: category
    };

-- Step 3: Use it with JSON (your MongoDB developers will love this)
INSERT INTO products_dv (data) VALUES (
    json_object(
        '_id' VALUE 1,
        'name' VALUE 'Laptop',
        'price' VALUE 999.99,
        'category' VALUE 'Electronics'
    )
);

-- The data is stored in your regular relational table
-- But accessed as JSON documents
SELECT data FROM products_dv;

The Bottom Line

If you have:

1. Oracle Database 23ai
2. Tables with primary keys (standard practice)

You can start using JSON Relational Duality Views today. No migration, no data duplication, no complexity. Your relational data can now speak JSON fluently while maintaining all the enterprise features Oracle provides.

Common Questions

Q: Do I need to change my existing tables? A: No. Your tables stay exactly as they are.

Q: Will my existing SQL applications still work? A: Yes. The JSON view is just another way to access the same data.

Q: Is the JSON data stored separately? A: No. There's only one copy of the data - in your relational tables.

Q: What if I don't have primary keys? A: You should add them - it's a database best practice anyway.

Q: Can I use this with my existing foreign keys? A: Yes. Foreign keys let you create nested JSON documents showing related data.

Next Steps

1. Verify you have Oracle Database 23ai: SELECT version FROM v$instance
2. Pick a table with a primary key
3. Create a JSON Relational Duality View
4. Start using JSON with your relational data

It's that simple. No migration projects, no data synchronization, no additional storage costs. Just instant JSON access to your existing relational data.

---

Appendix: JSON Relational Duality View DDL

1 Customer Management Views

-- Customer with Address Hierarchy
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW customers_dv AS
  customer @insert @update @delete
  {
    _id: customer_id,
    firstName: first_name,
    lastName: last_name,
    email: email,
    storeId: store_id,
    active: active,
    createDate: create_date,
    addressId: address_id
  };

-- Standalone Address Management
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW addresses_dv AS
  address @insert @update @delete
  {
    _id: address_id,
    street: address,
    address2: address2,
    district: district,
    postalCode: postal_code,
    phone: phone,
    cityId: city_id,
    lastUpdate: last_update
  };

2 Film Catalog Views

-- Complete Film Catalog with Relationships
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW films_dv AS
  film @insert @update @delete
  {
    _id: film_id,
    title: title,
    description: description,
    releaseYear: release_year,
    languageId: language_id,
    rentalDuration: rental_duration,
    rentalRate: rental_rate,
    length: length,
    replacementCost: replacement_cost,
    rating: rating,
    specialFeatures: special_features,
    actors: film_actor @insert @update @delete
    [
      {
        actorId: actor_id,
        actor: actor @update
        {
          actorId: actor_id,
          firstName: first_name,
          lastName: last_name
        }
      }
    ],
    categories: film_category @insert @update @delete
    [
      {
        categoryId: category_id,
        category: category @update
        {
          categoryId: category_id,
          categoryName: name
        }
      }
    ]
  };

-- Simplified Film Management
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW films_simple_dv AS
  film @insert @update @delete
  {
    _id: film_id,
    title: title,
    description: description,
    releaseYear: release_year,
    languageId: language_id,
    rentalDuration: rental_duration,
    rentalRate: rental_rate,
    length: length,
    replacementCost: replacement_cost,
    rating: rating,
    specialFeatures: special_features
  };

3 Actor and Content Views

-- Actor Filmography Management
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW actors_dv AS
  actor @insert @update @delete
  {
    _id: actor_id,
    firstName: first_name,
    lastName: last_name,
    lastUpdate: last_update,
    filmography: film_actor @insert @update @delete
    [
      {
        filmId: film_id,
        film: film @update
        {
          filmId: film_id,
          title: title,
          releaseYear: release_year,
          rating: rating
        }
      }
    ]
  };

4 Business Operations Views

-- Rental Transaction Management
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW rentals_dv AS
  rental @insert @update @delete
  {
    _id: rental_id,
    rentalDate: rental_date,
    returnDate: return_date,
    customer: customer @update
    {
      customerId: customer_id,
      firstName: first_name,
      lastName: last_name,
      email: email
    },
    staff: staff @update
    {
      staffId: staff_id,
      firstName: first_name,
      lastName: last_name
    },
    inventory: inventory @update
    {
      inventoryId: inventory_id,
      film: film @update
      {
        filmId: film_id,
        title: title,
        rentalRate: rental_rate,
        rating: rating
      }
    },
    payments: payment @insert @update @delete
    [
      {
        paymentId: payment_id,
        amount: amount,
        paymentDate: payment_date
      }
    ]
  };

-- Store Operations Management
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW stores_dv AS
  store @insert @update @delete
  {
    _id: store_id,
    managerStaffId: manager_staff_id,
    lastUpdate: last_update,
    address: address @update
    {
      addressId: address_id,
      street: address,
      district: district,
      city: city @update
      {
        cityId: city_id,
        cityName: city,
        country: country @update
        {
          countryId: country_id,
          countryName: country
        }
      }
    },
    staff: staff @insert @update @delete
    [
      {
        staffId: staff_id,
        firstName: first_name,
        lastName: last_name,
        email: email,
        active: active
      }
    ]
  };

-- Simple Staff Management View
-- This view provides direct CRUD operations on staff members
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW staff_dv AS
  staff @insert @update @delete
  {
    _id: staff_id,
    firstName: first_name,
    lastName: last_name,
    email: email,
    username: username,
    password: password,
    active: active,
    storeId: store_id,
    addressId: address_id,
    lastUpdate: last_update
  };

-- Staff with Location Details View
-- This view provides staff information organized by address location
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW staff_details_dv AS
  address
  {
    _id: address_id,
    street: address,
    address2: address2,
    district: district,
    postalCode: postal_code,
    phone: phone,
    city: city @update
    {
      cityId: city_id,
      cityName: city,
      country: country @update
      {
        countryId: country_id,
        countryName: country
      }
    },
    staffMembers: staff @insert @update @delete
    [
      {
        staffId: staff_id,
        firstName: first_name,
        lastName: last_name,
        email: email,
        username: username,
        password: password,
        active: active,
        storeId: store_id,
        lastUpdate: last_update
      }
    ]
  };

5 Inventory and Payment Views

-- Inventory Management
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW inventory_dv AS
  inventory @insert @update @delete
  {
    _id: inventory_id,
    filmId: film_id,
    storeId: store_id,
    lastUpdate: last_update
  };

-- Payment Processing
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW payments_dv AS
  payment @insert @update @delete
  {
    _id: payment_id,
    customerId: customer_id,
    staffId: staff_id,
    rentalId: rental_id,
    amount: amount,
    paymentDate: payment_date,
    lastUpdate: last_update
  };

6 Sample CRUD Operations

-- CREATE: Add new customer
INSERT INTO customers_dv (data) VALUES (
  json_object(
    '_id' VALUE 1000,
    'firstName' VALUE 'Alice',
    'lastName' VALUE 'JSONDev',
    'email' VALUE 'alice@jsondev.com',
    'storeId' VALUE 1,
    'active' VALUE '1',
    'createDate' VALUE SYSDATE,
    'addressId' VALUE 1
  )
);

-- CREATE: Add new film
INSERT INTO films_simple_dv (data) VALUES (
  json_object(
    '_id' VALUE 1002,
    'title' VALUE 'JSON Warriors',
    'description' VALUE 'Developers battle legacy systems',
    'releaseYear' VALUE '2025',
    'languageId' VALUE 1,
    'rentalDuration' VALUE 7,
    'rentalRate' VALUE 4.99,
    'length' VALUE 145,
    'replacementCost' VALUE 24.99,
    'rating' VALUE 'PG-13',
    'specialFeatures' VALUE 'Deleted Scenes'
  )
);

-- READ: Query customer information
SELECT 
  json_value(data, '$.firstName') as first_name,
  json_value(data, '$.email') as email,
  json_value(data, '$.active') as status
FROM customers_dv 
WHERE json_value(data, '$._id') = 1000;

-- READ: Complex film analysis
SELECT 
  json_value(data, '$.title') as title,
  json_value(data, '$.rating') as rating,
  json_value(data, '$.actors.size()') as actor_count,
  json_value(data, '$.categories.size()') as category_count
FROM films_dv 
WHERE json_value(data, '$.rating') = 'PG';

-- UPDATE: Modify customer information
UPDATE customers_dv 
SET data = json_transform(data, 
  SET '$.email' = 'alice.updated@jsondev.com',
  SET '$.active' = '1'
)
WHERE json_value(data, '$._id') = 1000;

-- UPDATE: Update film details
UPDATE films_simple_dv 
SET data = json_transform(data, 
  SET '$.rentalRate' = 3.99,
  SET '$.rating' = 'PG-13',
  SET '$.description' = 'Updated epic tale of JSON mastery'
)
WHERE json_value(data, '$._id') = 1002;

-- DELETE: Remove customer
DELETE FROM customers_dv 
WHERE json_value(data, '$._id') = 1000;

-- Advanced: Find films by actor
SELECT json_value(data, '$.title') as film_title
FROM films_dv 
WHERE json_exists(data, '$.actors[*] ? (@.actor.firstName == "PENELOPE")');

-- Advanced: Revenue analysis
SELECT 
  json_value(f.data, '$.title') as film_title,
  json_value(f.data, '$.rentalRate') as rental_rate,
  COUNT(p.data) as payment_count,
  SUM(json_value(p.data, '$.amount')) as total_revenue
FROM films_simple_dv f
JOIN payments_dv p ON json_value(f.data, '$._id') = (
  SELECT json_value(r.data, '$.inventory.film.filmId')
  FROM rentals_dv r 
  WHERE json_value(r.data, '$._id') = json_value(p.data, '$.rentalId')
)
GROUP BY json_value(f.data, '$.title'), json_value(f.data, '$.rentalRate')
ORDER BY total_revenue DESC;

---

Questions/problems/errors/need to vent or help

• Please feel free to contact Matt DeMarco

---

References

• Sakila ports

• Outliers, high-entropy and flex columns in JSON-to-Duality by Julian Dontcheff

• JSON Relational Duality: The Revolutionary Unification of Document, Object, and Relational Models

• JSON Relational Duality

• JSON-Relational Duality Developer's Guide