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. 

MySQL introduced the JSON Relational Duality View in version 9.4. JSON Duality Views blends the flexibility of JSON-centric development with the robust integrity and compliance of relational databases. Developers can now build document-based workflows and exchange data using flexible JSON collections, without giving up the normalization and referential integrity of MySQL.

JSON Duality Views let you interact with your data using either traditional SQL or REST-friendly JSON. MySQL manages consistency and normalization behind the scenes. This solves common issues in pure JSON stores, such as data duplication and out-of-sync references, while retaining the agility that modern development teams expect.
 
This article offers a high-level overview of the new DDL statements for creating, altering, displaying, usage and dropping JSON Duality Views. It also provides practical examples and outlines the key syntax and validation rules associated with each operation. Support for JSON Relational Duality Views is available in MySQL 9.4, the MySQL HeatWave managed cloud service, and elsewhere.

How to Define the Relational Tables to JSON Object Mapping

The first step in using JSON Duality Views is defining how your relational tables map onto a hierarchical, multi-level JSON document. This is achieved using a CREATE (DDL) statement for JSON Duality Views, which establishes the relationships between tables and their corresponding JSON structure via nested SELECT statements with sub-queries. Table-to-JSON relationships are specified using the new built-in function JSON_DUALITY_OBJECT. This function allows direct mapping of table columns to JSON keys, supporting both root objects and nested sub-objects within a document structure. Below we describe how to formulate the CREATE statement to model the root and the sub-objects of the JSON document:

JSON Duality View CREATE DDL

Root Object: To model the root object of the hierarchical JSON document, the CREATE statement uses a single SELECT statement, which must include exactly one call to JSON_DUALITY_OBJECT function [Refer A in the diagram]. The function maps key-column pairs from a single (root) table declared in the FROM clause [Refer B in the diagram]. A special key “_id” is used to map the primary-key column of the root table, ensuring each generated JSON document has a unique identifier. For root tables, the WHERE clause should be omitted.

Sub-objects: To model the nested-levels, SELECT sub-queries are used, each using a single (child) table in their respective FROM clause. Similar to root tables the primary-key columns need to be projected in the sub-queries for child tables, however no special key is needed to map the primary-keys. For child tables, a WHERE clause is mandatory that defines the relationship with the parent table. The relationship can be either one-to-one or one-to-many as described below:

  • A one-to-one relationship exists between a parent and a child table, when the WHERE condition expresses a any-column to primary-key equality. The resulting JSON document sub-object is projected as a singleton descendant JSON object. A singleton descendant JSON object [Refer C in the diagram], is defined by a SELECT statement containing JSON_DUALITY_OBJECT in its select list and a FROM clause. 
  • For one-to-many relationship, the nested descendant JSON objects [Refer D in the diagram] are obtained by selecting an expression using JSON_ARRAYAGG() containing JSON_DUALITY_OBJECT. The WHERE condition expresses a parent object’s primary key to sub-object’s any column equality. WHERE condition results in multiple child object for each parent object.

In addition to relational to JSON mapping the CREATE JSON Duality View DDL allows the user to annotate the root object and sub-objects to configure read/write capabilities.  Possible annotations or modification tags are INSERT, UPDATE, and DELETE that can be described within the JSON_DUALITY_OBJECT clause. All three annotations are required to enable full write support. Omitting all annotations creates a read-only view. Specifying only some annotations or using duplicate annotations is not allowed. The only exception is for singleton descendant objects [Refer C in the diagram], where providing a DELETE tag results in an error. Detailed article here.

Creating JSON Duality Views

The following tables, “customers” and “orders,” will be used to create 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)
    );
     
    INSERT INTO customers VALUES (1, "Alice"), (2, "Bob");
    INSERT INTO orders VALUES (1, 1, "Milk", 10), (2, 1, "Curd", 5), (3, 2, "Flour", 20), (4, 2, "Biscuits", 5);

Multiple JSON Duality Views can be created from a set of base tables. To define a JSON Duality View, the CREATE JSON [RELATIONAL] DUALITY VIEW statement is used:

CREATE JSON RELATIONAL DUALITY VIEW customer_orders_dv AS
    SELECT JSON_DUALITY_OBJECT(
        '_id': customer_id,
        'customer_name': name,
        'orders': (
            SELECT JSON_ARRAYAGG(
                JSON_DUALITY_OBJECT(
                    'order_id': order_id,
                    'product': product,
                    'amount': amount
                )
            )
            FROM orders
            WHERE orders.customer_id = customers.customer_id
        )
    )
    FROM customers;
     
    CREATE 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;

Using JSON Duality Views

JSON Duality Views support SELECT statements in the same manner as regular views, allowing you to retrieve the relevant data. The result set is structured similarly to a MongoDB collection, with a single column named “data” and a unique row identifier called “_id”. Additionally, WHERE clauses are supported to filter results, as demonstrated below:

mysql> SELECT JSON_PRETTY(data) FROM customer_orders_dv WHERE data->'$._id' = 1 \G
    *************************** 1. row ***************************
    JSON_PRETTY(data): {
      "_id": 1,
      "orders": [
        {
          "amount": 10.00,
          "product": "Milk",
          "order_id": 1
        },
        {
          "amount": 5.00,
          "product": "Curd",
          "order_id": 2
        }
      ],
      "_metadata": {
        "etag": "a6f0e76602398bc2df6fdd09494ae07b"
      },
      "customer_name": "Alice"
    }
    1 row in set (0.006 sec)
     
    mysql> SELECT JSON_PRETTY(data) FROM order_dv WHERE data->'$._id' = 2 \G
    *************************** 1. row ***************************
    JSON_PRETTY(data): {
      "_id": 2,
      "amount": 5.00,
      "product": "Curd",
      "customer": {
        "customer_id": 1,
        "customer_name": "Alice"
      },
      "_metadata": {
        "etag": "c73b5526988116524f005b3ae73bbea8"
      }
    }
    1 row in set (0.004 sec)

Accessing the JSON Duality View Metadata 

The original CREATE VIEW statement used to define a view can be retrieved with SHOW CREATE VIEW statement:

mysql> SHOW CREATE VIEW order_dv\G
    *************************** 1. row ***************************
                    View: order_dv
             Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER JSON RELATIONAL DUALITY VIEW `order_dv` AS select json_duality_object( WITH (INSERT,UPDATE,DELETE) '_id':`orders`.`order_id`,'product':`orders`.`product`,'amount':`orders`.`amount`,'customer':(select json_duality_object( WITH (INSERT,UPDATE) 'customer_id':`customers`.`customer_id`,'customer_name':`customers`.`name`) from `customers` where (`customers`.`customer_id` = `orders`.`customer_id`))) AS `Name_exp_1` from `orders`
    character_set_client: utf8mb4
    collation_connection: utf8mb4_0900_ai_ci
    1 row in set (0.002 sec)

Metadata for JSON Duality Views is provided through specific INFORMATION_SCHEMA tables such as JSON_DUALITY_VIEWS, JSON_DUALITY_VIEW_TABLES, JSON_DUALITY_VIEW_COLUMNS, and JSON_DUALITY_VIEW_LINKS. Each table exposes metadata at a distinct level:

  • JSON_DUALITY_VIEWS contains view-level information, including validity, read-only status, and whether the view supports INSERT, UPDATE, and DELETE operations.
  • JSON_DUALITY_VIEW_TABLES provides metadata about each participating table. This includes the table’s read-only status, supported DML operations (INSERT, UPDATE, DELETE), referenced table details for sub-objects, and the relationships to referenced tables.
  • JSON_DUALITY_VIEW_COLUMNS lists information about columns projected from participating tables, such as read-only status, supported DML operations, and the associated JSON key.
  • JSON_DUALITY_VIEW_LINKS describes columns used in join conditions, including the names of the joined columns from both tables and the type of relationship (Nested or Outer).
mysql> SELECT * FROM INFORMATION_SCHEMA.JSON_DUALITY_VIEWS WHERE TABLE_NAME = 'customer_orders_dv';
    +---------------+--------------+--------------------+----------------+---------------+------------------+--------------------+-------------------+-----------------+--------------+--------------+--------------+-----------+--------+
    | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME         | DEFINER        | SECURITY_TYPE | JSON_COLUMN_NAME | ROOT_TABLE_CATALOG | ROOT_TABLE_SCHEMA | ROOT_TABLE_NAME | ALLOW_INSERT | ALLOW_UPDATE | ALLOW_DELETE | READ_ONLY | STATUS |
    +---------------+--------------+--------------------+----------------+---------------+------------------+--------------------+-------------------+-----------------+--------------+--------------+--------------+-----------+--------+
    | def           | customers    | customer_orders_dv | root@localhost | DEFINER       | data             | def                | customers         | customers       | 0            | 0            | 0            | 1         | valid  |
    +---------------+--------------+--------------------+----------------+---------------+------------------+--------------------+-------------------+-----------------+--------------+--------------+--------------+-----------+--------+
    1 row in set (0.003 sec)
     
    mysql> SELECT * FROM INFORMATION_SCHEMA.JSON_DUALITY_VIEWS WHERE TABLE_NAME = 'order_dv';
    +---------------+--------------+------------+----------------+---------------+------------------+--------------------+-------------------+-----------------+--------------+--------------+--------------+-----------+--------+
    | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | DEFINER        | SECURITY_TYPE | JSON_COLUMN_NAME | ROOT_TABLE_CATALOG | ROOT_TABLE_SCHEMA | ROOT_TABLE_NAME | ALLOW_INSERT | ALLOW_UPDATE | ALLOW_DELETE | READ_ONLY | STATUS |
    +---------------+--------------+------------+----------------+---------------+------------------+--------------------+-------------------+-----------------+--------------+--------------+--------------+-----------+--------+
    | def           | customers    | order_dv   | root@localhost | DEFINER       | data             | def                | customers         | orders          | 1            | 1            | 1            | 0         | valid  |
    +---------------+--------------+------------+----------------+---------------+------------------+--------------------+-------------------+-----------------+--------------+--------------+--------------+-----------+--------+
    1 row in set (0.008 sec)

Altering the JSON Duality View definition

If required, the definition of an existing JSON Duality View can be modified using the ALTER JSON DUALITY VIEW statement. The updated definition must comply with all syntax and semantic rules described above. Let’s make customer_orders_dv from read-only to fully-writable:  

mysql> ALTER JSON 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;
    Query OK, 0 rows affected (0.016 sec)
     
    mysql> SHOW CREATE VIEW customer_orders_dv \G
    *************************** 1. row ***************************
                    View: customer_orders_dv
             Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER JSON RELATIONAL DUALITY VIEW `customer_orders_dv` AS select json_duality_object( WITH (INSERT,UPDATE,DELETE) '_id':`customers`.`customer_id`,'customer_name':`customers`.`name`,'orders':(select json_arrayagg(json_duality_object( WITH (INSERT,UPDATE,DELETE) 'order_id':`orders`.`order_id`,'product':`orders`.`product`,'amount':`orders`.`amount`)) from `orders` where (`orders`.`customer_id` = `customers`.`customer_id`))) AS `Name_exp_1` from `customers`
    character_set_client: utf8mb4
    collation_connection: utf8mb4_0900_ai_ci
    1 row in set (0.001 sec)

When no longer required, a JSON Duality View can be removed using the DROP VIEW statement, just like a regular view: 

mysql> DROP VIEW customer_orders_dv;
    Query OK, 0 rows affected (0.007 sec)
     
    mysql> DROP VIEW order_dv;
    Query OK, 0 rows affected (0.012 sec)

Summary

The new JSON Duality View feature enables unification of structured (relational) and semi-structured (JSON) data. With Duality Views, applications can read and write using either data model. The DML capabilities of JSON Duality Views address common limitations of pure JSON stores. For instance, robust relational mechanisms such as referential integrity and data normalization prevent issues like data duplication and inconsistent references during updates. This blog demonstrated DDL operations on JSON documents generated from a JSON Duality View. We recommend exploring the JSON Duality View feature, along with its DML operations, which is available in MySQL 9.4, the MySQL HeatWave managed cloud service, and elsewhere. 

Learn more: