27.7.2 DML Operations on JSON Duality Views (MySQL Enterprise Edition)

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.