The
      CREATE
      JSON RELATIONAL DUALITY VIEW statement is an extension
      of the CREATE VIEW statement. There
      are some additional clauses with
      CREATE
      JSON RELATIONAL DUALITY VIEW, and some restrictions on
      the SELECT statement. The
      RELATIONAL keyword is optional, and is omitted
      in examples.
    
      In this example, we create two tables,
      customers and orders, and
      insert some data into each table:
    
mysql> CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  name VARCHAR(100)
);
mysql> 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)
);
mysql> INSERT INTO customers VALUES (1, "Alice"), (2, "Bob");
Query OK, 2 rows affected (0.009 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> INSERT INTO orders VALUES (1, 1, "Milk", 10), (2, 1, "Curd", 5), (3, 2, "Flour", 20), (4, 2, "Biscuits", 5);
Query OK, 4 rows affected (0.007 sec)
Records: 4  Duplicates: 0  Warnings: 0
      The JSON duality views customer_orders_dv and
      order_dv give us a hierarchical view of the
      data in the table. We can create them using the following SQL
      statements:
    
mysql> 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;
mysql> 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;
      JSON duality views support SELECT
      statements in the same way as regular views, allowing you to
      retrieve the relevant data. The result set is structured with a
      single column named data, and a unique row
      identifier named _id. You can also use
      WHERE clauses to filter results. See the
      following example:
    
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)