Modern applications often pass JSON back and forth with the database server. With MySQL, we have had great JSON support, but working with relational data as JSON usually meant generating documents manually with built-in JSON functions. When an application sent JSON back to the server, we often had to break that document apart and write separate statements for each relational table.
Well… no more.
With MySQL 9.7 Community, JSON Duality Views now have full DML support, which means we can INSERT, UPDATE, and DELETE JSON documents directly through a special view. The database still stores the data relationally, but applications can work with it naturally as JSON. This feature was originally available only in MySQL Enterprise Edition, but with MySQL 9.7, developers can now use the same document-shaped interface for reads and writes while MySQL Copmmunity keeps the underlying relational tables in sync.
Instead of choosing between a relational model and a document model, MySQL Community now lets you use both together.
The Setup
For this post, we are using a simple sales order system. The demo creates two related tables in the 97_demos schema:
CREATE TABLE `dv_sales_order` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`order_number` VARCHAR(30) NOT NULL,
`customer_name` VARCHAR(100) NOT NULL,
`customer_email` VARCHAR(150) NOT NULL,
`order_status` VARCHAR(20) NOT NULL DEFAULT 'draft',
`ordered_at` DATETIME NOT NULL,
`ship_to_city` VARCHAR(75) NOT NULL,
`ship_to_state` CHAR(2) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `dv_sales_order_order_number_unique` (`order_number`),
KEY `dv_sales_order_customer_email_index` (`customer_email`),
KEY `dv_sales_order_status_index` (`order_status`)
)
CREATE TABLE `dv_sales_order_item` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`order_id` INT UNSIGNED NOT NULL,
`sku` VARCHAR(40) NOT NULL,
`item_name` VARCHAR(125) NOT NULL,
`quantity` INT UNSIGNED NOT NULL,
`unit_price` DECIMAL(10,2) NOT NULL,
`sort_order` INT NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
KEY `dv_sales_order_item_order_id_index` (`order_id`),
KEY `dv_sales_order_item_sku_index` (`sku`),
KEY `dv_sales_order_item_sort_order_index` (`sort_order`),
CONSTRAINT `dv_sales_order_item_order_id_fk`
FOREIGN KEY (`order_id`) REFERENCES `dv_sales_order` (`id`)
ON DELETE CASCADE
ON UPDATE NO ACTION
)
The dv_sales_order table stores the order details: customer information, order status, order date, and shipping information.
The dv_sales_order_item table stores the individual line items for each order.
This is a classic normalized relational design. One order can have many order items. Relational databases are great at modeling this kind of structure with primary keys, foreign keys, constraints, and indexes.
But applications often do not want to work with order details and order items as separate result sets. They usually want the full order as a single JSON document with an embedded array of items.
That is where JSON Duality Views shine.
In this demo, we will expose each order as a JSON document with a nested items array, which is a great shape for application code because the app can send and receive the full order document while MySQL keeps the order details and line items normalized
Creating the JSON Duality View
The demo creates a JSON Relational Duality View called sales_order_dv.
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW sales_order_dv AS
SELECT JSON_DUALITY_OBJECT( WITH (INSERT, UPDATE, DELETE)
'_id': id,
'order_number': order_number,
'customer_name': customer_name,
'customer_email': customer_email,
'order_status': order_status,
'ordered_at': ordered_at,
'ship_to_city': ship_to_city,
'ship_to_state': ship_to_state,
'items': (
SELECT JSON_ARRAYAGG(
JSON_DUALITY_OBJECT( WITH (INSERT, UPDATE, DELETE)
'id': id,
'order_id': order_id,
'sku': sku,
'item_name': item_name,
'quantity': quantity,
'unit_price': unit_price,
'sort_order': sort_order
)
)
FROM dv_sales_order_item
WHERE dv_sales_order_item.order_id = dv_sales_order.id
)
)
FROM dv_sales_order;
Note that the value of the id column in the root obejct is defined as _id. JSON duality views will expect this in the JSON definition.
Another thing I would like to highlight is that we specify INSERT, UPDATE, DELETE so that the data in these tables can be inserted updated and deleted directly through the view.
That means the application can work with the full sales order document, including its line items, while MySQL maps those document operations back to the underlying relational tables.
The database still stores everything relationally.
The application gets to work with JSON
That is a very nice combination.
Reading Orders Through the View
Once the view exists, querying complete sales order documents is simple:
SELECT JSON_PRETTY(`data`) AS `sales_order`
FROM `sales_order_dv`
Instead of manually joining order details to order items and then building the JSON document in application code, the view returns each order as a complete JSON document. NOTE: in a real application, we would not use JSON_PRETTY(), that is just to see the JSON in a more readable format for this demo.
Conceptually, an order looks like this:
{
"_id": 1,
"order_number": "SO-2026-1001",
"customer_name": "Jordan Lee"
"customer_email": "jordan.lee@example.com",
"order_status": "paid",
"ordered_at": "2026-06-08 09:15:00",
"ship_to_city": "Charlotte",
"ship_to_state": "NC",
"items": [
{
"id": 10,
"order_id": 1,
"sku": "HW-KBD-75",
"item_name": "Compact mechanical keyboard",
"quantity": 1,
"unit_price": 129.00,
"sort_order": 1
}
]
}
This is exactly the kind of structure many applications want to use. The order details and order items are delivered together as one document, but the actual data remains normalized inside MySQL
Inserting a New Order Document
With full DML support in MySQL 9.7 Community, we can insert an entire order document directly through the duality view.
INSERT INTO `sales_order_dv`
VALUES (
'{
"order_number": "SO-2026-1003",
"customer_name": "Morgan Rivera",
"customer_email": "morgan.rivera@example.com",
"order_status": "paid",
"ordered_at": "2026-06-08 11:45:00",
"ship_to_city": "Phoenix",
"ship_to_state": "AZ",
"items": [
{
"id": 60,
"sku": "CAM-USB-4K",
"item_name": "4K USB conference camera",
"quantity": 1,
"unit_price": 189.00,
"sort_order": 1
},
{
"id": 70,
"sku": "MIC-ARRAY-USBC",
"item_name": "USB-C microphone array",
"quantity": 1,
"unit_price": 119.00,
"sort_order": 2
}
]
}'
);
This may seem like a lot of typing, but, remember, the order information we are inserting will most likely be passed form the application as JSON, so we won’t need to parse it and provide INSERT statements for each item being inserted.
One especially nice detail is that the child order_id values are omitted from the insert document. Since the items are nested inside the order, MySQL can infer the relationship from the duality view definition.
From the application’s perspective, this feels natural. You are inserting an order, and the order contains items. From the database’s perspective, the data is still stored properly in normalized tables.
Updating an Order Document
The update example changes the order status from paid to packed, updates existing item details, and adds another item to the order.
UPDATE `sales_order_dv`
SET `data` = '{
"_id": 3,
"order_number": "SO-2026-1003",
"customer_name": "Morgan Rivera",
"customer_email": "morgan.rivera@example.com",
"order_status": "packed",
"ordered_at": "2026-06-08 11:45:00",
"ship_to_city": "Phoenix",
"ship_to_state": "AZ",
"items": [
{
"id": 60,
"order_id": 3,
"sku": "CAM-USB-4K",
"item_name": "4K USB conference camera",
"quantity": 2,
"unit_price": 179.00,
"sort_order": 1
},
{
"id": 70,
"order_id": 3,
"sku": "MIC-ARRAY-USBC",
"item_name": "USB-C microphone array",
"quantity": 1,
"unit_price": 119.00,
"sort_order": 2
},
{
"id": 80,
"order_id": 3,
"sku": "LIGHT-RING-18",
"item_name": "18 inch desktop ring light",
"quantity": 1,
"unit_price": 69.00,
"sort_order": 3
}
]
}'
WHERE data ->> '$._id' = 3;
This is where JSON Duality Views become especially powerful. The application can send an updated version of the order document, and MySQL maps that change back to the underlying relational tables.
The order details are updated.
The existing line items are updated.
The new line item is inserted.
All of that happens through the JSON Duality View.
Instead of writing separate statements for the order details and each order item, the application can work with the document as the unit of change.
Deleting Through the View
Deleting an order through the duality view is simple:
DELETE FROM `sales_order_dv`
WHERE data ->> '$._id' = 3;
The application deletes the order document from the view.
Behind the scenes, MySQL deletes the corresponding row from the order table. Since the underlying relationship between dv_sales_order and dv_sales_order_item uses cascading behavior, the related order items are removed as well.
The application works with a document.
The database enforces the relational rules.
That is the point of JSON Duality Views.
Why This Matters
JSON Duality Views let MySQL support two development styles at the same time.
Relational database design still matters. Tables, keys, constraints, indexes, and normalization are incredibly valuable. They help protect data quality, avoid duplication, and make data easier to query and maintain over time.
But modern applications often communicate using JSON. APIs, services, and front-end applications usually think in terms of complete objects: an order with its items, a customer with addresses, a product with attributes, or an invoice with lines. JSON Duality Views bridge that gap.
With JSON Duality Views, MySQL can shape relational data into JSON directly in the database. The application gets the document it needs, while MySQL can still use the relational model underneath, including indexes, keys, and constraints. For writes, full DML support means the application can send a document-level change through the view instead of coordinating separate statements for each table.
This does not mean every workload automatically becomes faster, but it does mean there is less application plumbing, fewer opportunities for inefficient data handling, and a cleaner path for MySQL to manage the relational work close to where the data lives.
With MySQL 9.7 Community, JSON Duality Views are even more useful because they are fully writable. The same document-shaped interface can now be used for reads and writes.
That means we can:
- SELECT JSON documents
- INSERT JSON documents
- UPDATE JSON documents
- DELETE JSON documents
All while keeping the relational model intact.
Best of Both Worlds
We start with normalized relational tables:
- Orders
- Order items
Then we expose that relational structure as a JSON document:
- One order with an array of items
The application gets a clean JSON document model.
The database keeps relational integrity.
With MySQL 9.7 Community, the JSON document model is not just read-only. It now supports full DML, which makes JSON Duality Views much more practical for real application development
Wrap Up
JSON Duality Views in MySQL 9.7 Community are an intriguing step forward for developers who want the flexibility of JSON without giving up the strength of relational design.
This is the best of both worlds: relational integrity with document-style developer ergonomics.
MySQL 9.7 Community makes JSON Duality Views feel complete, practical, and ready for modern application development.
And honestly, I think that is pretty exciting.