MySQL HeatWave is the only fully managed MySQL database service that combines transactions, analytics, machine learning, and GenAI services, without ETL duplication. Also included is MySQL HeatWave Lakehouse, which allows users to query data in object storage, MySQL databases, or a combination of both. Users can deploy MySQL HeatWave–powered apps on a choice of public clouds: Oracle Cloud Infrastructure (OCI), Amazon Web Services (AWS), and Microsoft Azure.
Developers of modern applications have a need to implement document-based workflows using JSON to communicate or exchange data between the front and back-end components. They prefer to use JSON collection abstractions for these interactions. The JSON Duality View introduces an innovative way to work with document-centric applications without abandoning the strengths of relational databases. It opens up new possibilities for hybrid workloads (SQL and document processing), smoother interoperability, and simplified development processes, all while still benefiting from the robust features of MySQL, such as referential integrity and strong transactional consistency.
This blog post outlines the support for JSON Relational Duality Views introduced in version 9.4 and made available in the MySQL HeatWave managed cloud service and elsewhere.
What Are JSON Duality Views?
Put simply, the new JSON Duality View feature lets you unify both structured (relational) and semi-structured (JSON) data. With Duality Views, your application can read and write using either data model. Imagine a single source of truth: accessible through both traditional SQL and rich, REST-friendly JSON, with MySQL ensuring your data remains consistent, normalized, and easy to manage. No more trade-offs or out-of-sync referenced data.
Why Duality Views Change the Game
Let’s recap the strengths (and headaches) of both traditional models:
| JSON Data Model | Relational Model | ||
|---|---|---|---|
| Pros: |
|
|
|
| Cons: |
|
|
With MySQL JSON Duality Views, you don’t have to compromise anymore. Now, you can harness the strengths of both, with none of their trade-offs!
Simplify Application Stack
-
Unified access, say goodbye to ETL and sync hassles: In most database systems, you’re forced to choose between a flexible JSON document model (beloved by API and app developers) or the structure and reliability of relational tables (essential for reporting and business logic). Developers and DBAs often end up building complicated synchronization layers or running heavy ETL jobs just to keep these models in sync. With Duality Views, you get a single, real-time representation of your data, consistently accessible as both JSON and relational tables. Any change made through one interface is instantly reflected in the other, so your apps, reports, and APIs are always on the same page.
-
No more ORM or data mapping glue: Modern web and API projects, especially those built with JavaScript love working directly with JSON. Traditional relational data, though, usually demands an Object-Relational Mapping (ORM) layer. ORMs can add complexity, introduce bugs, and sometimes feel like a barrier between your app and your data. JSON Duality Views takes over the responsibility of Object-relational mapping from within the database. Therefore, no more application specific data mappings, just seamless, intuitive data access.
-
Seamless data evolution and migration: Developers can move fast with schema-less JSON stores, however as products mature, scaling become a bottleneck. Migrating away from JSON to relational data model can be a massive undertaking. This is where JSON Duality Views allow you to transition at your own pace. You can begin with JSON, enjoying the agility it brings, and transition towards a normalized, relational design as your requirements grow, all while continuing to serve both models to your application, APIs, and analytics. There’s no need for big-bang migrations or downtime, your data evolves with your business.
Key Highlights of MySQL JSON Duality View Feature
- New DDL syntax for creating JSON Duality Views using InnoDB or Lakehouse tables. (detailed article)
- Query JSON Duality Views using SELECT statements, the result not only produces JSON documents but also computes ETAG for change tracking.
- Perform DML operations (INSERT, UPDATE, DELETE) directly on JSON Duality Views using JSON objects. (detailed article)
- Introduces new lockless optimistic concurrent control inside the database to prevent conflicting read-write operations performed via state-less (REST) operations. (detailed article)
- New Information_Schema Views to access metadata for the JSON Duality VIEWs.
Getting Started: Example Walkthrough
Lets start by creating 2 sample tables using a PK-FK relationship
CREATE TABLE customers (
customer_id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100)
);
CREATE TABLE orders (
order_id INT PRIMARY KEY AUTO_INCREMENT,
customer_id INT,
product VARCHAR(100),
amount DECIMAL(10,2),
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
Now lets define JSON Duality View using customers as root JSON object with nested orders
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW customers_orders_dv AS
SELECT JSON_DUALITY_OBJECT( WITH(INSERT,UPDATE,DELETE)
'_id': customer_id,
'customer_name': name,
'orders': (
SELECT JSON_ARRAYAGG(
JSON_DUALITY_OBJECT( WITH(INSERT,UPDATE,DELETE)
'order_id': order_id,
'product': product,
'amount': amount
)
)
FROM orders
WHERE orders.customer_id = customers.customer_id
)
)
FROM customers;
Lets insert some data into the empty view using JSON objects directly
mysql> INSERT INTO customers_orders_dv VALUES (
'{ "customer_name": "Alice",
"_id": 1,
"orders": [
{"order_id": 1, "product": "Laptop", "amount": 1299.99},
{"order_id": 2, "product": "Mouse", "amount": 19.99}
]
}');
Lets check the normalized relational tables that got populated with the JSON data
mysql> SELECT * FROM customers;
+-------------+-------+
| customer_id | name |
+-------------+-------+
| 1 | Alice |
+-------------+-------+
mysql> SELECT * FROM orders;
+----------+-------------+---------+---------+
| order_id | customer_id | product | amount |
+----------+-------------+---------+---------+
| 1 | 1 | Laptop | 1299.99 |
| 2 | 1 | Mouse | 19.99 |
+----------+-------------+---------+---------+
Finally, lets query the JSON Duality View, to receive the JSON document along with ETAG computed
mysql> SELECT * FROM customers_orders_dv;
+--------------------------------------------------+
| data |
+--------------------------------------------------+
| { |
| "_id": 1, |
| "orders": [ |
| { |
| "amount": 1299.99, |
| "product": "Laptop", |
| "order_id": 1 |
| }, |
| { |
| "amount": 19.99, |
| "product": "Mouse", |
| "order_id": 2 |
| } |
| ], |
| "_metadata": { |
| "etag": "e6d40eabf2e070ffd2719c6755d50f1a" |
| }, |
| "customer_name": "Alice" |
| } |
+--------------------------------------------------+
Summary
MySQL’s JSON Duality View is another step toward a truly unified data experience, blending flexibility and data consistency.
As your business evolves, so can your data model without rewrites, duplicated data, or ETL headaches.
Learn more: MySQL JSON Duality View documentation
Try it out in MySQL 9.4, and discover how simple, consistent data management can supercharge your applications:
- On MySQL Heatwave cloud service (Try free tier here)
- In Enterprise Edition (Download for free)
- Or in the Community Edition with restriction (read-only views)