DatabaseVerse: Journey into the World of Databases

1. Introduction
2. Use cases
   1. Application Modernization
   2. Data Integration
   3. API Development
   4. Data Analytics
   5. Data Migration
3. Demo
   1. Query as relational database
   2. Query using JSON Duality
4. Conclusion

Introduction

Oracle JSON Duality is a feature introduced in Oracle Database 23ai that provides seamless bi-directional mapping between relational tables and JSON documents. This allows you to query and manipulate data in both JSON and relational formats interchangeably. It ensures that any changes made in the JSON representation are automatically reflected in the relational tables and vice versa, providing flexibility and ease of use for applications that need to work with both data models. This feature simplifies the integration and management of structured and semi-structured data within Oracle databases.

A JSON-relational duality view presents data from relational database tables as JSON documents, which are created on demand and not stored as such. This provides both a conceptual and operational duality to the data, organizing it both relationally and hierarchically. Different duality views can be based on the same tables, offering varied JSON hierarchies over the shared data.

This means that applications can access (create, query, modify) the same data as a set of JSON documents or as a set of related tables and columns, and both approaches can be employed at the same time.

Use cases

Application Modernization

• A legacy application stores data in relational tables.
• A new microservices-based application requires the same data in JSON format for flexible data interchange.

Solution:

• Use JSON Duality views to expose relational data as JSON without changing the existing schema.
• New applications can interact with JSON documents while legacy systems continue using relational tables.

Data Integration

• Data from multiple sources need to be integrated.
• Some sources provide data in JSON format, while others use relational tables.

Solution:

• Use JSON Duality to seamlessly map JSON data to relational tables.
• Integrated views allow for unified querying and manipulation of both JSON and relational data.

API Development

• Developing RESTful APIs that require JSON data interchange.
• Backend systems use relational databases.

Solution:

• Use JSON Duality views to automatically convert relational data to JSON format.
• Simplifies API development by providing JSON endpoints directly from relational data.

Data Analytics

• Analyzing and reporting on semi-structured data stored in JSON format.
• Need to join this data with structured relational data.

Solution:

• Use JSON Duality views to expose JSON data as relational tables.
• Perform complex queries and analytics using SQL, combining both JSON and relational data.

Data Migration

• Migrating data from a relational database to a document-based NoSQL database.
• Need a temporary solution to expose relational data as documents.

Solution:

• Use JSON Duality views to convert relational data to JSON documents.
• Simplifies the migration process by providing a JSON representation of the data.

Demo

In this demo, we’ll explore Oracle Database 23ai JSON Duality feature through a practical example involving a movie database. This demo will showcase the creation of tables for movies and reviews, the establishment of JSON Duality views, and the querying of data in both relational and JSON formats. By the end, you’ll understand how Oracle JSON Duality can simplify data management and integration, bridging the gap between structured and semi-structured data.

Let’s create the tables

-- Drop Tables and Views if They Exist
DROP VIEW IF EXISTS review_dv;
DROP VIEW IF EXISTS movie_dv;
DROP TABLE IF EXISTS reviews;
DROP TABLE IF EXISTS movies;

-- Create Tables
CREATE TABLE movies (
    movie_id INTEGER GENERATED BY DEFAULT ON NULL AS IDENTITY,
    title VARCHAR2(255) NOT NULL,
    genre VARCHAR2(100),
    release_year INTEGER,
    director VARCHAR2(100),
    PRIMARY KEY (movie_id)
);

CREATE TABLE reviews (
    review_id INTEGER GENERATED BY DEFAULT ON NULL AS IDENTITY,
    movie_id INTEGER NOT NULL,
    reviewer VARCHAR2(100),
    rating INTEGER CHECK (rating BETWEEN 1 AND 10),
    review_comment VARCHAR2(1000),
    PRIMARY KEY (review_id),
    FOREIGN KEY (movie_id) REFERENCES movies(movie_id)
);

Now let’s create the JSON duality views, you will see some explanation in the next part.

-- Create JSON Relational Duality Views

-- Movie Duality View
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW movie_dv AS
SELECT JSON {
    '_id' : m.movie_id, -- This uniquely identifies each JSON document
    'title' : m.title,
    'genre' : m.genre,
    'releaseYear' : m.release_year,
    'director' : m.director,
    'reviews' : [ 
        SELECT JSON {
            'reviewId' : r.review_id,
            'reviewer' : r.reviewer,
            'rating' : r.rating,
            'reviewComment' : r.review_comment
        } FROM reviews r WITH INSERT UPDATE DELETE
        WHERE r.movie_id = m.movie_id
    ]
} FROM movies m WITH INSERT UPDATE DELETE;

-- Review Duality View
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW review_dv AS
SELECT JSON {
    '_id' : r.review_id, -- This uniquely identifies each JSON document
    'reviewer' : r.reviewer,
    'rating' : r.rating,
    'reviewComment' : r.review_comment,
    'movie' : 
        (SELECT JSON {
            'movieId' : m.movie_id,
            'title' : m.title,
			'genre' : m.genre,
			'release_year': m.release_year,
			'director': m.director
        } FROM movies m WITH NOINSERT UPDATE NODELETE
        WHERE m.movie_id = r.movie_id)
} FROM reviews r WITH INSERT UPDATE DELETE;

Let’s insert some data but… take a look! We are using the duality views instead of the underlying tables. This will automatically populate the movie and review tables (yep!)

-- Insert Movies
INSERT INTO movie_dv VALUES ('{
    "_id": 1,
    "title": "Inception",
    "genre": "Sci-Fi",
    "releaseYear": 2010,
    "director": "Christopher Nolan",
    "reviews": [
        {"reviewId": 101, "reviewer": "Alice", "rating": 9, "reviewComment": "Mind-bending and thrilling."},
        {"reviewId": 102, "reviewer": "Bob", "rating": 8, "reviewComment": "Great plot and visuals."}
    ]
}');

INSERT INTO movie_dv VALUES ('{
    "_id": 2,
    "title": "Parasite",
    "genre": "Thriller",
    "releaseYear": 2019,
    "director": "Bong Joon-ho",
    "reviews": [
        {"reviewId": 103, "reviewer": "Charlie", "rating": 10, "reviewComment": "A groundbreaking film."},
        {"reviewId": 104, "reviewer": "Dave", "rating": 9, "reviewComment": "Exceptional and thought-provoking."}
    ]
}');

INSERT INTO movie_dv VALUES ('{
    "_id": 3,
    "title": "The Godfather",
    "genre": "Crime",
    "releaseYear": 1972,
    "director": "Francis Ford Coppola",
    "reviews": [
        {"reviewId": 105, "reviewer": "Eve", "rating": 10, "reviewComment": "A masterpiece."},
        {"reviewId": 106, "reviewer": "Frank", "rating": 10, "reviewComment": "Unmatched in every aspect."}
    ]
}');

-- Insert Additional Reviews
INSERT INTO review_dv VALUES ('{
    "_id": 107,
    "reviewer": "Grace",
    "rating": 8,
    "reviewComment": "Very entertaining.",
    "movie": {"movieId": 1, "title": "Inception"}
}');

INSERT INTO review_dv VALUES ('{
    "_id": 108,
    "reviewer": "Hank",
    "rating": 9,
    "reviewComment": "Amazing film.",
    "movie": {"movieId": 2, "title": "Parasite"}
}');

COMMIT;

Let’s see what we have! I will query the tables first:

SET LINESIZE 250
col title for a20
col genre for a20
col director for a20
SELECT * FROM movies;


col reviewer for a20
col review_comment for a50
SELECT * FROM reviews;

Now, let’s review our JSON duality views:

SELECT * FROM moview_dv;

SELECT * FROM review_dv;

So, we need to format it a little bit to see the information properly in SQL*Plus, let’s try again:

SET LINESIZE 200
SET PAGESIZE 1000
SET LONG 5000
SET LONGCHUNKSIZE 5000

SELECT JSON_SERIALIZE(data PRETTY) FROM movie_dv;

SELECT JSON_SERIALIZE(data PRETTY) FROM review_dv;

Nice! Now let’s execute the same query as we would in a relational database, and then query the duality views.

Query as relational database

I will query to get all review information for a particular movie

SELECT m.movie_id, m.title, m.genre, m.release_year, m.director, r.review_id, r.reviewer, r.rating, r.review_comment
FROM movies m
 JOIN reviews r ON m.movie_id = r.movie_id
where r.movie_id=1;

Query using JSON Duality

Following the example above will do the same using JSON duality views

SELECT JSON_SERIALIZE(data PRETTY) 
FROM review_dv
WHERE JSON_VALUE(data, '$.movie.movieId') = 1;

You can also do something like this:

col movie_id for a10
col title for a20
col genre for a20
col release_year for a10
col director for a20
col review_id for a10
col reviewer for a20
col rating for a10
col review_comment for a50
SELECT 
    JSON_VALUE(data, '$.movie.movieId') AS movie_id,
    JSON_VALUE(data, '$.movie.title') AS title,	
    JSON_VALUE(data, '$.movie.genre') AS genre,		
    JSON_VALUE(data, '$.movie.release_year') AS release_year,		
    JSON_VALUE(data, '$.movie.director') AS director,		
    JSON_VALUE(data, '$._id') AS review_id,
    JSON_VALUE(data, '$.reviewer') AS reviewer,
    JSON_VALUE(data, '$.rating') AS rating,
    JSON_VALUE(data, '$.reviewComment') AS review_comment
FROM review_dv
WHERE JSON_VALUE(data, '$.movie.movieId') = 1;

So, as you can see, the JSON Duality view is a powerful feature. I’m really happy that Oracle has done this. It was something developers were requesting for a long time, and they did it in a way that is even better compared with other vendors and NoSQL databases. Also, you have one database for all purposes.

Conclusion

The Oracle JSON Duality feature introduced in Oracle Database 23ai represents a significant leap in data management and integration capabilities. By providing seamless bi-directional mapping between relational tables and JSON documents, it enables organizations to work with structured and semi-structured data interchangeably. This feature not only simplifies the integration and management of data within Oracle databases but also addresses various use cases such as application modernization, data integration, API development, data analytics, and data migration.

Through a practical demonstration involving a movie database, we’ve witnessed the creation of tables, establishment of JSON Duality views, data insertion, and querying in both relational and JSON formats. The demonstration served as a compelling showcase of how Oracle JSON Duality can bridge the gap between structured and semi-structured data, offering flexibility, ease of use, and unprecedented power to developers and organizations.

In conclusion, the Oracle JSON Duality feature stands as a testament to Oracle’s commitment to innovation and addressing real-world challenges in data management. Its ability to seamlessly navigate between the relational and JSON worlds adds a new dimension to database capabilities, making it a valuable asset for modern applications and systems requiring flexible data interchange and integration.