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)