MySQL 9.4 Release Notes
In MySQL Enterprise Edition, DML operations are supported on JSON duality views. DML
enables seamless INSERT,
UPDATE, and
DELETE operations directly in
developer-friendly JSON documents while ensuring data consistency
through the underlying relational schema.
DML operations on JSON duality views involves several orchestrated steps working together:
Document validation: The JSON duality view automatically validates the input JSON document for correct syntax and ensures it matches the expected schema.
Type conversion: JSON data types are seamlessly mapped to database data types.
Sub-statement generation: The JSON duality view decomposes and generates the necessary DML operations targeting the normalized base tables.
Sub-statement execution: The final step is executing the sequenced DML sub-statements as a single atomic operation.
Optimistic concurrency control: As an additional preventative measure the JSON duality view prevents conflicting read-write operations in stateless REST calls.
JSON duality views introduces modification tags, which are
annotations that specify the intended operation
(INSERT,
UPDATE, or
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.
Inserting objects for JSON duality views on self referencing tables and circularly referencing tables is supported.
Generated statements for DML operations on JSON duality view are executed as sub-statements of DML operations on view.
Sub-statement execution does not use any new metadata locks or row locks.
If any sub-statement fails, all sub-statements are rolled back.
For a projected column with AUTO_INCREMENT, a
value for the column must be specified explicitly. If not, then it
must be possible to deduce the column value from the join
condition; otherwise, the operation is rejected with an error.
For execution of all generated sub-statements, triggers defined on the base tables of a JSON duality view are executed.
For base tables of JSON duality views linked by referential constraints, sub-statement execution includes execution of any foreign key referential actions which may be defined; failure of a foreign key cascading operation causes the DML operation to be rejected with an error.
DML operations on JSON duality view and their sub-statements are replicated consistently. Should execution of any sub-statement fail, any other sub-statements which are part of this operation are not replicated.
You should be aware that one
INSERT,
UPDATE, or
DELETE statement on a duality view
may lead to multiple insert, update, or delete operations on the
view's base tables.
A JSON document which is used as input for data modification operations is validated to make sure that its schema matches that of a JSON document generated by the JSON duality view.