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.
Modern applications increasingly rely on flexible, JSON data models to simplify development and accelerate innovation. However, traditional JSON/document stores, while powerful for rapid prototyping, often lack the robust mechanisms of relational databases, such as referential integrity and data normalization. This means:
- Risk of inconsistent referenced data: Deleting or updating objects can unintentionally leave behind inconsistent, meaningless data.
- Risk of Redundant Data: Same data is stored in the multiple objects.
- No enforced foreign key constraints: Applications are responsible for ensuring references between objects remain valid.
As applications grow, this can lead to data bloating, data corruption, business rule violations, and costly cleanup operations.
The JSON Relational Duality View is introduced in version 9.4 and made available in the MySQL HeatWave managed cloud service and elsewhere. JSON Duality Views combines the agility of JSON-centric development with the rock-solid integrity and compliance of a relational database. In this blog we will focus on DML operations on JSON Duality Views. DML enable seamless INSERT, UPDATE, and DELETE operations directly against developer–friendly JSON documents while ensuring data consistency through the underlying relational schema.
How JSON Duality Views Simplify DML Operations for the Developer
JSON Duality Views handle much of the data management complexity that applications would otherwise have to manage themselves when interacting with relational databases. By automating key transformation and validation steps, JSON Duality Views reduce the amount of custom code developers need to write and maintain. This results in simpler, cleaner, and more maintainable application logic, allowing development teams to focus on business logic rather than data plumbing. DML operation on a JSON Duality View involves several orchestrated steps work together.
- Document Validation: The View automatically checks each input JSON document for correct syntax and ensures it matches the expected schema, eliminating the need for manual checks in your application.
- Type Conversion: JSON data types are seamlessly mapped to database data types, taking care of conversion edge-cases and compatibility without extra effort from developers.
- Sub-statement Generation: The Duality View decomposes and generates the necessary DML operations targeting the normalized base tables.
- Sub-statement Execution: Finally, the sequenced DML sub-statements execute as a single atomic operation
- Optimistic concurrency control: As an additional preventive measure, JSON Duality View prevents conflicting read-write operations in state-less concurrent REST calls. See more details in Lockless Optimistic Concurrency Control using ETAGs.
DML Operations
Lets dive right into the DML operations, we will go over INSERT, UPDATE and DELETE operations on JSON Duality Views. For more information on creating JSON Duality Views please refer to our earlier post.
JSON Duality Views introduces modification tags, annotations that specify the intended operation (INSERT, UPDATE, DELETE) on each JSON object/sub-object. If modification tags are not specified, the object or sub-object is treated as read-only, and DML operations are not permitted. This intent-driven system is vital for:
- Preventing accidental data changes
- Enforcing business rules at every level (root, singleton, nested)
- Making operations explicit and auditable
Lets use the following relational tables “customer” and “orders” for creating JSON Duality Views.
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
name VARCHAR(100)
);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
product VARCHAR(100),
amount DECIMAL(10,2),
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
First, let’s create the JSON duality view “customer_orders_dv”, which presents customer order information as a JSON document. This duality view is built on the underlying relational tables, “customers” and “orders”. “customer_orders_dv” JSON Duality View contains nested descendent “orders”. View allows INSERT, UPDATE and DELETE operations on Root and sub-object “orders”.
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW customer_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;
Next, we’ll create another JSON Duality View named “order_dv”, designed to present individual order information as a JSON document. Like before, this view is also built on the “customers” and “orders” tables. The “order_dv” JSON Duality View contains a singleton descendents, “customer”. This view allows INSERT, UPDATE, and DELETE operations on the root object. Since the sub-object is a singleton descendent, only INSERT and UPDATE operations are permitted on it.
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW order_dv AS
SELECT JSON_DUALITY_OBJECT( WITH(INSERT,UPDATE,DELETE)
'_id' : order_id,
'product' : product,
'amount' : amount,
'customer': (
SELECT JSON_DUALITY_OBJECT( WITH(INSERT,UPDATE)
'customer_id': customer_id,
'customer_name': name
)
FROM customers
WHERE customers.customer_id = orders.customer_id
)
)
FROM orders;
Inserting a JSON Document
In this section, we explore how to create JSON documents by performing an INSERT operation on the JSON Duality View. During an insert, you can either create a complete document, including all sub-objects or create only the root object while referencing existing sub-objects
Inserting a complete document: You can create an entire JSON document including the root object and all sub-objects with a single INSERT operation.
mysql> INSERT INTO customer_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}
]
}');
Query OK, 3 rows affected (0.018 sec)
Rows affected: 3 Warnings: 0.
mysql> SELECT * FROM customer_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" |
|} |
+--------------------------------------------------+
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 |
+----------+-------------+---------+---------+
mysql> SELECT * FROM orders_dv;
+--------------------------------------------------+
| data |
+--------------------------------------------------+
|{ |
| "_id": 1, |
| "amount": 1299.99, |
| "product": "Laptop", |
| "customer": { |
| "customer_id": 1, |
| "customer_name": "Alice" |
| }, |
| "_metadata": { |
| "etag": "52f3a7039e0bc75dd31fc7239227d6bb" |
| } |
|} |
+--------------------------------------------------+
|{ |
| "_id": 2, |
| "amount": 19.99, |
| "product": "Mouse", |
| "customer": { |
| "customer_id": 1, |
| "customer_name": "Alice" |
| }, |
| "_metadata": { |
| "etag": "305bd687b1c71ef35561e1b2a2481083" |
| } |
|} |
+--------------------------------------------------+
Key take aways:
- A single JSON document insert operation can insert both
"customer"and"orders"data appropriately in one atomic transaction. - JSON document INSERT operation, inserts rows in relational tables “customer” and “orders’ via JSON Duality View.
- The “
customer_orders_dv"also impacts the “orders_dv"since all information is made consistent via relational tables, the new orders added are visible here as well. - Note:
- For Insert operation Users must specify values for all keys in the JSON document.
- Exception: If a projected column for a key has a default value, it can be omitted. In such cases, the default value is stored in the respective relational table.
- The “
orders.customer_id"column is not projected in the “customer_orders_dv"JSON duality view, but its value is still populated during the INSERT operation. The system deduces values for unprojected columns, or even missing keys in the document based on the sub-object’s JOIN condition. - Currently, inserting multiple JSON documents with a single INSERT statement is not supported. Each JSON document must be inserted individually.
- For Insert operation Users must specify values for all keys in the JSON document.
Inserting a partial document: Users have the flexibility to create partial JSON documents. Lets start again with empty customers and orders table
mysql> INSERT INTO customer_orders_dv VALUES ( '{
"customer_name": "Alice",
"_id": 1 }');
Query OK, 1 row affected (0.023 sec)
Rows affected: 1 Warnings: 0.
mysql> SELECT * FROM customer_orders_dv;
+--------------------------------------------------+
| data |
+--------------------------------------------------+
|{ |
| "_id": 1, |
| "orders": null, |
| "_metadata": { |
| "etag": "847e705fbe181f5b9360da3a911204df" |
| }, |
| "customer_name": "Alice" |
|} |
+--------------------------------------------------+
mysql> SELECT * FROM customers;
+-------------+-------+
| customer_id | name |
+-------------+-------+
| 1 | Alice |
+-------------+-------+
mysql> SELECT * FROM orders;
Empty set (0.002 sec)
Users can even reference existing sub-objects when creating a JSON document and can also modify those existing sub-objects as needed. Lets continue with the above example with empty orders table and with customer table only containing Alice.
mysql> INSERT INTO order_dv VALUES('{
"_id" : 1,
"product" : "Laptop",
"amount" : 1299.99,
"customer" : {
"customer_id" : 1,
"customer_name" : "Alice_junior"
}
}');
Query OK, 3 rows affected (0.018 sec)
Rows affected: 3 Warnings: 0.
mysql> SELECT * FROM order_dv;
+--------------------------------------------------+
| data |
+--------------------------------------------------+
|{ |
| "_id": 1, |
| "amount": 1299.99, |
| "product": "Laptop", |
| "customer": { |
| "customer_id": 1, |
| "customer_name": "Alice_junior" |
| }, |
| "_metadata": { |
| "etag": "77d9965d5eaa089583d213442b19a5a6" |
| } |
|} |
+--------------------------------------------------+
mysql> SELECT * FROM customers;
+-------------+--------------+
| customer_id | name |
+-------------+--------------+
| 1 | Alice_junior |
+-------------+--------------+
mysql> SELECT * FROM orders;
+----------+-------------+---------+---------+
| order_id | customer_id | product | amount |
+----------+-------------+---------+---------+
| 1 | 1 | Laptop | 1299.99 |
+----------+-------------+---------+---------+
Key take aways:
- Users can create partial documents.
- Users may skip passing values for sub-objects or refer to existing sub-objects.
- Users can modify a sub-object while inserting the JSON document.
- Note:
- Users are allowed to delete elements from a nested sub-object array.
-
Only non-primary key columns can be modified.
Updating a JSON Document
In this section, we explore how to update a JSON document using the update operation on a JSON Duality View with the document’s “_id”. Users can perform a variety of update actions on a JSON document, including:
- Updating the root object
- Updating a sub-object
- Updating an element within a nested sub-object
- Inserting a new element into a nested sub-object
- Deleting an element from a nested sub-object
Updating a complete Document: With a single UPDATE command, users can modify both the root object and any sub-objects within the JSON document.
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 |
+----------+-------------+---------+---------+
mysql> UPDATE customer_orders_dv SET data = '{
"_id" : 1,
"customer_name" : "Alice_junior",
"orders" : [
{
"order_id" : 1,
"product" : "Laptop",
"amount" : 699.99
},
{
"order_id" : 2,
"product" : "Mouse",
"amount" : 9.99
}
]
}' WHERE JSON_EXTRACT(data, '$._id') = 1;
Query OK, 3 rows affected, 1 warning (0.012 sec)
Rows affected: 3 Warnings: 1.
mysql> SELECT * FROM customer_orders_dv;
+--------------------------------------------------+
| data |
+--------------------------------------------------+
|{ |
| "_id": 1, |
| "orders": [ |
| { |
| "amount": 699.99, |
| "product": "Laptop", |
| "order_id": 1 |
| }, |
| { |
| "amount": 9.99, |
| "product": "Mouse", |
| "order_id": 2 |
| } |
| ], |
| "_metadata": { |
| "etag": "a567b190aba288b5efef62343ebae901" |
| }, |
| "customer_name": "Alice_junior" |
|} |
+--------------------------------------------------+
mysql> SELECT * FROM customers;
+-------------+---------------+
| customer_id | name |
+-------------+---------------+
| 1 | Alice_junior |
+-------------+---------------+
mysql> SELECT * FROM orders;
+----------+-------------+---------+--------+
| order_id | customer_id | product | amount |
+----------+-------------+---------+--------+
| 1 | 1 | Laptop | 699.99 |
| 2 | 1 | Mouse | 9.99 |
+----------+-------------+---------+--------+
mysql> SELECT * FROM order_dv;
+--------------------------------------------------+
| data |
+--------------------------------------------------+
|{ |
| "_id": 1, |
| "amount": 699.99, |
| "product": "Laptop", |
| "customer": { |
| "customer_id": 1, |
| "customer_name": "Alice_junior" |
| }, |
| "_metadata": { |
| "etag": "989a494c383f0a8bd9395868dd89575d" |
| } |
|} |
+--------------------------------------------------+
|{ |
| "_id": 2, |
| "amount": 9.99, |
| "product": "Mouse", |
| "customer": { |
| "customer_id": 1, |
| "customer_name": "Alice_junior" |
| }, |
| "_metadata": { |
| "etag": "b21e3dd50ef83c0f9fb81ac4d1283ec0" |
| } |
|} |
+--------------------------------------------------+
Key take aways:
- Perform a single JSON document update operation that updates both the customers and orders tables in one atomic action.
- JSON document UPDATE operation, updates rows in relational tables “customer” and “orders’ via JSON Duality View.
- The “
customer_orders_dv"also impacts the “orders_dv"since all information is made consistent via relational tables, the new orders added are visible here as well. - Note:
- The user must specify key fields projecting primary columns in the JSON document, namely the root object’s “
_id"and the sub-object’s “order_id". - Currently, updating multiple JSON documents with a single UPDATE statement is not supported. Each JSON document must be updating individually.
- The user must specify key fields projecting primary columns in the JSON document, namely the root object’s “
Updating only the descendants: Users have the flexibility to update partial JSON documents, allowing for efficient and targeted modifications without needing to replace the entire document.
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 |
+----------+-------------+---------+---------+
mysql> UPDATE customer_orders_dv SET data = '
{
"_id" : 1,
"customer_name" : "Alice",
"orders" : [
{
"order_id" : 1,
"product" : "Laptop",
"amount" : 1299.99
},
{
"order_id" : 3,
"product" : "Keyboard",
"amount" : 29.99
}
]
}';
Query OK, 2 rows affected, 1 warning (0.011 sec)
Rows affected: 2 Warnings: 1.
mysql> SELECT * FROM customer_orders_dv;
+--------------------------------------------------+
| data |
+--------------------------------------------------+
|{ |
| "_id": 1, |
| "orders": [ |
| { |
| "amount": 1299.99, |
| "product": "Laptop", |
| "order_id": 1 |
| }, |
| { |
| "amount": 29.99, |
| "product": "Keyboard", |
| "order_id": 3 |
| } |
| ], |
| "_metadata": { |
| "etag": "0bbea4e26d455cd1458a3ebf6e05cdd7" |
| }, |
| "customer_name": "Alice" |
|} |
+--------------------------------------------------+
mysql> SELECT * FROM customers;
+-------------+-------+
| customer_id | name |
+-------------+-------+
| 1 | Alice |
+-------------+-------+
mysql> SELECT * FROM orders;
+----------+-------------+----------+---------+
| order_id | customer_id | product | amount |
+----------+-------------+----------+---------+
| 1 | 1 | Laptop | 1299.99 |
| 3 | 1 | Keyboard | 29.99 |
+----------+-------------+----------+---------+
Key take aways:
-
A single JSON document update operation can insert, modify, and delete rows from the orders table, all within a single atomic transaction.
-
Note:
-
When performing these operations, the user must specify the keys that project the primary columns in the JSON document, such as the root object’s “
_id"and the sub-object’s “order_id".
-
Deleting a JSON document
In this section, we explore how to delete a JSON document. You can choose to delete the entire JSON document or only a specific part of it. When performing a partial delete, any sub-objects that do not have the DELETE modification tag will remain unchanged.
Deleting a complete document: Here we explore the deletion of a complete JSON document.
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 |
+----------+-------------+---------+---------+
mysql> DELETE FROM customer_orders_dv WHERE JSON_VALUE(data, "$._id") = 1;
Query OK, 3 rows affected (0.015 sec)
mysql> SELECT * FROM customers;
Empty set (0.002 sec)
mysql> SELECT * FROM orders;
Empty set (0.002 sec)
Key take aways:
- Single JSON document delete operation, delete from “customers” and from “orders” in a single atomic operation.
- JSON document DELETE operation, deletes rows from relational tables “customer” and “orders’ via JSON Duality View.
- Note: Currently, deleting multiple JSON documents with a single DELETE statement is not supported. Each JSON document must be deleted individually.
Deleting a partial document: For a JSON Duality View with a singleton descendant, the DELETE modification tag is not allowed. If a sub-object does not include a DELETE modification tag, the corresponding rows in the sub-objects will not be deleted.
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 |
+----------+-------------+---------+---------+
mysql> DELETE FROM order_dv WHERE JSON_VALUE(data, "$._id") = 1;
Query OK, 1 row affected (0.009 sec)
mysql> SELECT * FROM customers;
+-------------+-------+
| customer_id | name |
+-------------+-------+
| 1 | Alice |
+-------------+-------+
mysql> SELECT * FROM orders;
+----------+-------------+---------+---------+
| order_id | customer_id | product | amount |
+----------+-------------+---------+---------+
| 1 | 1 | Laptop | 1299.99 |
+----------+-------------+---------+---------+
Key take aways:
- DELETE operation on a JSON document does not delete the singleton descendant sub-object.
- DELETE operation on a JSON document does not delete the sub-object if the DELETE modifier is not specified on it.
Summary
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. MySQL’s JSON Duality View DML capabilities solves common issues in pure JSON store due to lack of the robust mechanisms of relational databases, such as referential integrity and data normalization. Furthermore, explicit modification tags ensure every operation (insert, update, delete) is intentional and safe.
In this blog post, we explored how to perform DML operations on JSON documents generated from a JSON Duality View. The JSON Duality feature is now available in MySQL 9.4. We encourage you to try out the JSON Duality feature and experiment with DML operations on your own JSON data!
Learn more:
- MySQL JSON Duality View documentation
- Feature available on Heatwave cloud service (Try free tier here)
- Feature available in Enterprise Edition (Download for free)
- Feature available in Community Edition with restriction (read-only views)