Working with JSON Duality Views in node-oracledb

In a previous blog, we built a simple Coffee Brew Tracker application. Now, it’s time to extend that application and explore one of the 300+ new features in Oracle Database 23ai: JSON Relational Duality Views (or just Duality Views from here on out).

This post will briefly explain what Duality Views are and walk you through how to easily add them in your applications.

Before you begin:

This blog assumes you’ve already built the Coffee Brew Tracker application from the previous post. If not, grab a coffee (pun intended) and catch up.

Step 1: Extending the data model

1. Wait, what are Duality Views?

   
   

   Duality Views let your application work with data in either JSON or relational formats. While Oracle Database has supported the JSON datatype natively since 21c and supported JSON using the IS JSON constraint since 12c, Oracle Database 23ai adds a new approach, JSON Duality Views. Duality Views create a JSON Document structure on top of one or multiple relational tables and offer greater flexibility and simplicity when building applications.

   
   

   Think of Duality Views as the bridge between structured, relational data and the flexible, document-based JSON format. With them, you gain the power to say how data is accessed (via @insert, @update, and @delete operations), without being tied to storage specifics. The result? More control and flexibility for developers.

   
   

2. Creating the Duality View

   
   

   First, let's create a Duality View. Duality Views allow us to create these 'views' that exist on top of our underlying relational table(s). In this case, we'll create a view on top of our coffee_brews table

   
   

   Using SQL Developer VSCode extension, execute the following SQL to define the Duality View:

CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW coffee_brew_dv AS
coffee_brews @insert @update @delete
{
    _id: id,
    brew_method: brew_method,
    bean_origin: bean_origin,
    grind_size: grind_size,
    temperature: temperature,
    brew_time: brew_time,
    notes: notes,
    rating: rating,
    timestamp: timestamp
};

See the previous blog to setup the SQL Developer Extension on VSCode

Step 2: Updating the JavaScript

1. GET /brews endpoint - Index.js

   
   

   Now that the Duality View exists, let's update our backend code. Replace the SQL table query in your index.js file with this:

const result = await conn.execute(
    `
    SELECT b.data 
    FROM coffee_brew_dv b 
    ORDER BY b.data.timestamp DESC
    `,
    [],
    { outFormat: oracledb.OUT_FORMAT_OBJECT }
);

console.log("Fetched brews from database:", result.rows);

Here, we're selecting directly from the Duality View.

2. POST /brews endpoint - Index.js

   
   

   To insert data, use the DB_TYPE_JSON binding to interact directly with the Duality View:

app.post('/brews', async (req, res) => {
    const brewData = {
        brew_method: req.body.brew_method,
        bean_origin: req.body.bean_origin,
        grind_size: req.body.grind_size,
        temperature: req.body.temperature,
        brew_time: req.body.brew_time,
        notes: req.body.notes,
        rating: req.body.rating,
        timestamp: new Date().toISOString()
    };

    const conn = await oracledb.getConnection();
    try {
        // Here we insert into the JSON Duality View
        await conn.execute(
            `INSERT INTO coffee_brew_dv VALUES (:bv)`,
            { 
                bv: { 
                    val: brewData, 
                    type: oracledb.DB_TYPE_JSON 
                } 
            },
            { autoCommit: true }
        );

Step 3: Frontend changes

1. Modify brews.js

   
   

   In brews.js, simplify how the JSON data is displayed on the front end:

listItem.innerHTML = `
    <div class="brew-card-details">
        <div class="main-info">
            <div class="brew-title">${brew.brew_method}</div>
            <div class="brew-subtitle">${brew.bean_origin}</div>
        </div>
        <div class="secondary-info">
            <div class="info-row">
           <span><strong>Grind Size:</strong> ${brew.grind_size}</span>
                <span><strong>Temperature:</strong> ${brew.temperature}°C</span>
            </div>
            <div class="info-row">
                <span><strong>Brew Time:</strong> ${brew.brew_time}s</span>
                <span><strong>Rating:</strong> ${brew.rating}/10</span>
            </div>
            ${notes}
            <div class="logged-date">
                <span><strong>Logged on:</strong> ${loggedDate}</span>
            </div>
        </div>
    </div>
`;

More info on Duality View

This post offers a high-level overview of Duality Views showing their use in a basic Coffee Brew Tracker application.

For a more in-depth exploration, check out resources like Domains, Annotations, and Duality Views together and the JSON Relational Duality Views Doc covers advanced use cases, like nested JSON object mapping, managing updates and deletes, optimizing Duality Views for performance and tons more

For other new features in Oracle Database 23ai and access to free OCI resources for testing, check out Oracle LiveLabs.

-Killian