MySQL HeatWaveは、トランザクション、分析、機械学習、生成AIサービスを組み合わせた唯一のフルマネージドのMySQLデータベース・サービスです。また、MySQL HeatWave Lakehouseも含まれており、ユーザーはオブジェクト・ストレージ、MySQLデータベース、またはその両方の組合せに格納されているデータを分析できます。ユーザーは、MySQL HeatWaveを利用したアプリケーションを、Oracle Cloud Infrastructure(OCI)、Amazon Web Services(AWS)およびMicrosoft Azureのいずれかのパブリック・クラウドにデプロイできます。
最新アプリケーションは、開発を簡素化し、イノベーションを加速するために、柔軟なJSONデータ・モデルにますます依存しています。ただし、従来のJSONドキュメント・ストアは、プロトタイプ開発には向いていますが、多くの場合、参照整合性やデータ正規化などのリレーショナル・データベースの堅牢なメカニズムがありません。これは、次のことを意味します。:
- 参照データの一貫性のないリスク: オブジェクトを削除または更新すると、意図せず一貫性のない無意味なデータが残される可能性があります
- 冗長データのリスク: 同じデータが複数のオブジェクトに格納されることが発生し得ます
- 外部キー制約による強制なし: アプリケーション側でオブジェクト間の参照が有効であることを確認する必要があります
アプリケーションが成長するにつれて、データの膨張、データ破損、ビジネス・ルール違反、コストのかかるデータの洗い替えにつながる可能性があります。
JSON Duality Viewは、バージョン9.4で導入され、MySQL HeatWaveマネージド・クラウド・サービスやその他の場所で利用可能になりました。JSON Duality Viewは、JSON中心の開発の俊敏性と、リレーショナル・データベースの堅実な整合性とコンプライアンスを組み合わせたものです。このブログでは、JSON Duality Viewに対するDML操作に焦点を当てます。DMLを使用すると、開発者が使いやすいJSONドキュメントに対して直接シームレスなINSERT、UPDATEおよびDELETE操作が可能になるとともに、基礎となるリレーショナル・スキーマを介したデータの一貫性を確保できます。
JSON Duality ViewがどのようにDML操作をシンプルにするのか
JSON Duality Viewは、リレーショナル・データベースとの対話時にアプリケーション自身が管理する必要のある、データ管理の複雑さの多くを代行します。主な変換および検証ステップを自動化することで、JSON Duality Viewにより、開発者が記述および保守する必要があるコードの量が削減されます。これにより、アプリケーションのロジックのシンプル化、クリーン化、保守性が向上し、開発チームはデータのメンテナンスではなくビジネス・ロジックに集中できます。JSON Duality Viewに対するDML操作には、いくつかの調整済ステップが連携して動作します。
- ドキュメント検証: JSON Duality Viewでは、各入力JSONドキュメントが正しい構文になっているかが自動的にチェックされ、必要なスキーマと一致していることが確認されるため、アプリケーションの手動チェックが不要になります
- 型変換: JSONのデータ型は、データベースのデータ型にシームレスにマップされ、開発者の余計な労力を必要とせずに、変換のエッジ・ケースおよび互換性を考慮します
- サブ・ステートメントの生成: JSON Duality Viewは、正規化された実表をターゲットとする必要なDML操作を分解して生成します
- サブ・ステートメントの実行: 最後に、順序付けされたDML文は単一のアトミック操作として実行されます
- オプティミスティック同時実行性制御: 追加の予防策として、JSON Duality Viewは、ステートレスなRESTコールの同時実行時の読取り/書込み操作の競合を防ぎます。詳細は、ETAGを使用したロックレス・オプティミスティック同時実行性制御を参照してください
DML操作
DML操作について詳しく説明します。ここでは、JSON Duality Viewに対するINSERT、UPDATEおよびDELETE操作について説明します。JSON Duality Viewの作成の詳細は、「MySQLでのJSON Duality Viewのハンズオン: 作成から日常の使用まで」の記事投稿を参照してください。
JSON Duality Viewには、変更タグという各JSONオブジェクト/サブオブジェクトに対する目的の操作(INSERT、UPDATE、DELETE)を指定する注釈が導入されています。変更タグが指定されていない場合、オブジェクトまたはサブオブジェクトは読取り専用として扱われ、DML操作は許可されません。このインテント駆動システム(意図的に操作するよう設計された仕組み)は、次の場合に重要です。
- 偶発的なデータ変更の防止
- すべてのレベルでのビジネス・ルールの施行 (ルート、シングルトン、ネスト)
- 操作を明示的かつ監査可能にする
リレーショナルなテーブルの”customer” と “orders”を使ってJSON Duality Viewを作成します。
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)
);
最初にJSON Duality Viewである”customer_orders_dv”を作成します。顧客の注文情報をJSONドキュメントで表現します。このビューは、リレーショナルなテーブルの”customer” と “orders”に基づいています。 JSON Duality Viewの”customer_orders_dv”は”orders”テーブルが持つ制約を継承してます。このビューは、元となる”orders”テーブルおよび派生するデータに対するINSERT, UPDATE および DELETEの各操作が可能です。
CREATE OR REPLACE JSON RELATIONAL 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;
次に、「order_dv」という名前の別のJSON Duality Viewを作成します。これは、個々の注文情報をJSONドキュメントとして示すように設計されています。これまでと同様に、このビューはcustomers表およびorders表に基づいて作成されます。order_dv JSON Duality Viewには、単一の子データとしてcustomerが含まれています。このビューでは、ルート・オブジェクトに対するINSERT、UPDATEおよびDELETE操作が可能です。サブオブジェクトは単一の子データであるため、INSERTおよびUPDATE操作のみが許可されます。
CREATE OR REPLACE 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ドキュメントの追加
この項では、JSON Duality ViewでINSERT操作を実行してJSONドキュメントを追加する方法について説明します。挿入時に、すべてのサブオブジェクトを含む完全なドキュメントを作成することも、既存のサブオブジェクトを参照しているときにルートオブジェクトのみを作成することもできます。
完全なドキュメントの挿入: ルート・オブジェクトを含むJSONドキュメント全体と、単一のINSERT操作ですべてのサブオブジェクトを作成できます。
mysql> INSERT INTO customer_orders_dv VALUES ( '{ "customer_name": "Alice",
"_id": 1,
"orders": [
{"order_id": 1, "product": "Laptop", "amount": 1299.99},
{"order_id": 2, "product": "Mouse", "amount": 19.99}
]
}');
Query OK, 3 rows affected (0.018 sec)
Rows affected: 3 Warnings: 0.
mysql> SELECT * FROM customer_orders_dv;
+--------------------------------------------------+
| data |
+--------------------------------------------------+
|{ |
| "_id": 1, |
| "orders": [ |
| { |
| "amount": 1299.99, |
| "product": "Laptop", |
| "order_id": 1 |
| }, |
| { |
| "amount": 19.99, |
| "product": "Mouse", |
| "order_id": 2 |
| } |
| ], |
| "_metadata": { |
| "etag": "e6d40eabf2e070ffd2719c6755d50f1a" |
| }, |
| "customer_name": "Alice" |
|} |
+--------------------------------------------------+
mysql> SELECT * FROM customers;
+-------------+-------+
| customer_id | name |
+-------------+-------+
| 1 | Alice |
+-------------+-------+
mysql> SELECT * FROM orders;
+----------+-------------+---------+---------+
| order_id | customer_id | product | amount |
+----------+-------------+---------+---------+
| 1 | 1 | Laptop | 1299.99 |
| 2 | 1 | Mouse | 19.99 |
+----------+-------------+---------+---------+
mysql> SELECT * FROM orders_dv;
+--------------------------------------------------+
| data |
+--------------------------------------------------+
|{ |
| "_id": 1, |
| "amount": 1299.99, |
| "product": "Laptop", |
| "customer": { |
| "customer_id": 1, |
| "customer_name": "Alice" |
| }, |
| "_metadata": { |
| "etag": "52f3a7039e0bc75dd31fc7239227d6bb" |
| } |
|} |
+--------------------------------------------------+
|{ |
| "_id": 2, |
| "amount": 19.99, |
| "product": "Mouse", |
| "customer": { |
| "customer_id": 1, |
| "customer_name": "Alice" |
| }, |
| "_metadata": { |
| "etag": "305bd687b1c71ef35561e1b2a2481083" |
| } |
|} |
+--------------------------------------------------+
要点:
-
1つのJSONドキュメント挿入操作で、1つのアトミックなトランザクションに
"customer"と"orders"の両方のデータを適切に挿入できます。 -
JSONドキュメントのINSERT操作では、JSON Duality Viewを介して、リレーショナル表
"customer"と"orders"に行を挿入します。 -
customer_orders_dvは、すべての情報がリレーショナル表を介して一貫性を持たせられ、追加された新しい注文もここに表示されるため、orders_dvにも影響します。
-
注意:
-
挿入操作の場合、ユーザーはJSONドキュメント内のすべてのキーの値を指定する必要があります。
-
例外: キーの投影された列にデフォルト値がある場合は、省略できます。このような場合、デフォルト値はそれぞれのリレーショナル表に格納されます。
-
“
orders.customer_id"列は、 “customer_orders_dv"JSON Duality Viewには投影されませんが、INSERT操作中は値が移入されます。投影されていないカラムの値、またはサブオブジェクトのJOIN条件に基づいてドキュメント内の欠落キーの値が差し引かれます。 -
現在、単一のINSERT文を使用した複数のJSONドキュメントの挿入はサポートされていません。各JSONドキュメントを個別に挿入する必要があります。
-
-
一部のドキュメントの挿入: JSONドキュメントの一部を柔軟に作成できます。空の顧客および注文の表から再開します
mysql> INSERT INTO customer_orders_dv VALUES ( '{
"customer_name": "Alice",
"_id": 1 }');
Query OK, 1 row affected (0.023 sec)
Rows affected: 1 Warnings: 0.
mysql> SELECT * FROM customer_orders_dv;
+--------------------------------------------------+
| data |
+--------------------------------------------------+
|{ |
| "_id": 1, |
| "orders": null, |
| "_metadata": { |
| "etag": "847e705fbe181f5b9360da3a911204df" |
| }, |
| "customer_name": "Alice" |
|} |
+--------------------------------------------------+
mysql> SELECT * FROM customers;
+-------------+-------+
| customer_id | name |
+-------------+-------+
| 1 | Alice |
+-------------+-------+
mysql> SELECT * FROM orders;
Empty set (0.002 sec)
JSONドキュメントの作成時に既存のサブオブジェクトを参照することもでき、必要に応じて既存のサブオブジェクトを変更することもできます。空のorder表を使用し、customer表にAliceのみを含めます。
mysql> INSERT INTO order_dv VALUES('{
"_id" : 1,
"product" : "Laptop",
"amount" : 1299.99,
"customer" : {
"customer_id" : 1,
"customer_name" : "Alice_junior"
}
}');
Query OK, 3 rows affected (0.018 sec)
Rows affected: 3 Warnings: 0.
mysql> SELECT * FROM order_dv;
+--------------------------------------------------+
| data |
+--------------------------------------------------+
|{ |
| "_id": 1, |
| "amount": 1299.99, |
| "product": "Laptop", |
| "customer": { |
| "customer_id": 1, |
| "customer_name": "Alice_junior" |
| }, |
| "_metadata": { |
| "etag": "77d9965d5eaa089583d213442b19a5a6" |
| } |
|} |
+--------------------------------------------------+
mysql> SELECT * FROM customers;
+-------------+--------------+
| customer_id | name |
+-------------+--------------+
| 1 | Alice_junior |
+-------------+--------------+
mysql> SELECT * FROM orders;
+----------+-------------+---------+---------+
| order_id | customer_id | product | amount |
+----------+-------------+---------+---------+
| 1 | 1 | Laptop | 1299.99 |
+----------+-------------+---------+---------+
要点:
-
ユーザーは、 部分的なドキュメントを作成できます。
-
ユーザーは、サブオブジェクトの値の受渡しをスキップしたり、既存のサブオブジェクトを参照したりできます。
-
ユーザーは、JSONドキュメントの挿入中にサブオブジェクトを変更できます。
-
注意:
-
ユーザーは、ネストされたサブオブジェクト配列から要素を削除できます。
-
主キー以外の列のみ変更できます。
-
-
JSONドキュメントの更新
このセクションでは、ドキュメントの”_id”を使用してJSON Duality View の更新操作を使用してJSONドキュメントを更新する方法を説明します。ユーザーは、次のような様々な更新アクションをJSONドキュメントに対して実行できます:
- ルート・オブジェクトの更新
- サブオブジェクトの更新
- ネストされたサブオブジェクト内の要素の更新
- ネストされたサブオブジェクトへの新しい要素の挿入
- ネストされたサブオブジェクトからの要素の削除
完全なドキュメントの更新: 単一のUPDATEコマンドを使用すると、ユーザーはJSONドキュメント内のルート・オブジェクトと任意のサブオブジェクトの両方を変更できます。
mysql> SELECT * FROM customers;
+-------------+-------+
| customer_id | name |
+-------------+-------+
| 1 | Alice |
+-------------+-------+
mysql> SELECT * FROM orders;
+----------+-------------+---------+---------+
| order_id | customer_id | product | amount |
+----------+-------------+---------+---------+
| 1 | 1 | Laptop | 1299.99 |
| 2 | 1 | Mouse | 19.99 |
+----------+-------------+---------+---------+
mysql> UPDATE customer_orders_dv SET data = '{
"_id" : 1,
"customer_name" : "Alice_junior",
"orders" : [
{
"order_id" : 1,
"product" : "Laptop",
"amount" : 699.99
},
{
"order_id" : 2,
"product" : "Mouse",
"amount" : 9.99
}
]
}' WHERE JSON_EXTRACT(data, '$._id') = 1;
Query OK, 3 rows affected, 1 warning (0.012 sec)
Rows affected: 3 Warnings: 1.
mysql> SELECT * FROM customer_orders_dv;
+--------------------------------------------------+
| data |
+--------------------------------------------------+
|{ |
| "_id": 1, |
| "orders": [ |
| { |
| "amount": 699.99, |
| "product": "Laptop", |
| "order_id": 1 |
| }, |
| { |
| "amount": 9.99, |
| "product": "Mouse", |
| "order_id": 2 |
| } |
| ], |
| "_metadata": { |
| "etag": "a567b190aba288b5efef62343ebae901" |
| }, |
| "customer_name": "Alice_junior" |
|} |
+--------------------------------------------------+
mysql> SELECT * FROM customers;
+-------------+---------------+
| customer_id | name |
+-------------+---------------+
| 1 | Alice_junior |
+-------------+---------------+
mysql> SELECT * FROM orders;
+----------+-------------+---------+--------+
| order_id | customer_id | product | amount |
+----------+-------------+---------+--------+
| 1 | 1 | Laptop | 699.99 |
| 2 | 1 | Mouse | 9.99 |
+----------+-------------+---------+--------+
mysql> SELECT * FROM order_dv;
+--------------------------------------------------+
| data |
+--------------------------------------------------+
|{ |
| "_id": 1, |
| "amount": 699.99, |
| "product": "Laptop", |
| "customer": { |
| "customer_id": 1, |
| "customer_name": "Alice_junior" |
| }, |
| "_metadata": { |
| "etag": "989a494c383f0a8bd9395868dd89575d" |
| } |
|} |
+--------------------------------------------------+
|{ |
| "_id": 2, |
| "amount": 9.99, |
| "product": "Mouse", |
| "customer": { |
| "customer_id": 1, |
| "customer_name": "Alice_junior" |
| }, |
| "_metadata": { |
| "etag": "b21e3dd50ef83c0f9fb81ac4d1283ec0" |
| } |
|} |
+--------------------------------------------------+
要点:
- 1つのアトミック・アクションでcustomers表とorder表の両方を更新する単一のJSONドキュメント更新操作を実行します。
- JSONドキュメントの更新操作では、JSON Duality Viewを介してリレーショナル表”customer” と “orders”の行を更新します。
- “
customer_orders_dv"は、すべての情報がリレーショナル表を介して一貫性を持たせられ、追加された新しい注文もここに表示されるため、”orders_dv"にも影響します。 - 注意:
- ユーザーは、JSONドキュメントでプライマリ列を投影するキー・フィールド、つまりルート・オブジェクトの”
_id"とサブオブジェクトの”order_id"を指定する必要があります。 - 現在、単一のUPDATE文を使用した複数のJSONドキュメントの更新はサポートされていません。各JSONドキュメントは個別に更新する必要があります。
- ユーザーは、JSONドキュメントでプライマリ列を投影するキー・フィールド、つまりルート・オブジェクトの”
子データのみの更新: ユーザーは、部分的なJSONドキュメントを柔軟に更新できるため、ドキュメント全体を置き換える必要なく、効率的でターゲットを絞った変更を行うことができます。
mysql> SELECT * FROM customers;
+-------------+-------+
| customer_id | name |
+-------------+-------+
| 1 | Alice |
+-------------+-------+
mysql> SELECT * FROM orders;
+----------+-------------+---------+---------+
| order_id | customer_id | product | amount |
+----------+-------------+---------+---------+
| 1 | 1 | Laptop | 1299.99 |
| 2 | 1 | Mouse | 19.99 |
+----------+-------------+---------+---------+
mysql> UPDATE customer_orders_dv SET data = '
{
"_id" : 1,
"customer_name" : "Alice",
"orders" : [
{
"order_id" : 1,
"product" : "Laptop",
"amount" : 1299.99
},
{
"order_id" : 3,
"product" : "Keyboard",
"amount" : 29.99
}
]
}';
Query OK, 2 rows affected, 1 warning (0.011 sec)
Rows affected: 2 Warnings: 1.
mysql> SELECT * FROM customer_orders_dv;
+--------------------------------------------------+
| data |
+--------------------------------------------------+
|{ |
| "_id": 1, |
| "orders": [ |
| { |
| "amount": 1299.99, |
| "product": "Laptop", |
| "order_id": 1 |
| }, |
| { |
| "amount": 29.99, |
| "product": "Keyboard", |
| "order_id": 3 |
| } |
| ], |
| "_metadata": { |
| "etag": "0bbea4e26d455cd1458a3ebf6e05cdd7" |
| }, |
| "customer_name": "Alice" |
|} |
+--------------------------------------------------+
mysql> SELECT * FROM customers;
+-------------+-------+
| customer_id | name |
+-------------+-------+
| 1 | Alice |
+-------------+-------+
mysql> SELECT * FROM orders;
+----------+-------------+----------+---------+
| order_id | customer_id | product | amount |
+----------+-------------+----------+---------+
| 1 | 1 | Laptop | 1299.99 |
| 3 | 1 | Keyboard | 29.99 |
+----------+-------------+----------+---------+
要点:
-
単一のJSONドキュメント更新操作では、すべて単一のアトミック・トランザクション内で、order表から行を挿入、変更および削除できます。
-
注意:
-
これらの操作を実行する場合、ユーザーは、ルート・オブジェクトの”
_id"やサブオブジェクトの””order_id"など、JSONドキュメントのプライマリ列を投影するキーを指定する必要があります。
-
JSONドキュメントの削除
このセクション項では、JSONドキュメントを削除する方法について説明します。JSONドキュメント全体を削除するか、その特定の部分のみを削除するかを選択できます。部分削除を実行する場合、DELETE変更タグを持たないサブオブジェクトは変更されません。
完全なドキュメントの削除: ここでは、完全なJSONドキュメントの削除を確認します。
mysql> SELECT * FROM customers;
+-------------+-------+
| customer_id | name |
+-------------+-------+
| 1 | Alice |
+-------------+-------+
mysql> SELECT * FROM orders;
+----------+-------------+---------+---------+
| order_id | customer_id | product | amount |
+----------+-------------+---------+---------+
| 1 | 1 | Laptop | 1299.99 |
| 2 | 1 | Mouse | 19.99 |
+----------+-------------+---------+---------+
mysql> DELETE FROM customer_orders_dv WHERE JSON_VALUE(data, "$._id") = 1;
Query OK, 3 rows affected (0.015 sec)
mysql> SELECT * FROM customers;
Empty set (0.002 sec)
mysql> SELECT * FROM orders;
Empty set (0.002 sec)
要点:
- 単一のJSONドキュメント削除操作で、単一のアトミック操作で”customers”および”orders”から削除します。
- JSONドキュメントDELETE操作では、JSON Duality Viewを介してリレーショナル表”customer”および”orders”から行を削除します。
- 注意: 現在、単一のDELETE文を使用した複数のJSONドキュメントの削除はサポートされていません。各JSONドキュメントを個別に削除する必要があります。
一部のドキュメントの削除: 単一の子データを持つJSON Duality Viewの場合、DELETE変更タグは許可されません。サブオブジェクトにDELETE変更タグが含まれていない場合、サブオブジェクト内の対応する行は削除されません。
mysql> SELECT * FROM customers;
+-------------+-------+
| customer_id | name |
+-------------+-------+
| 1 | Alice |
+-------------+-------+
mysql> SELECT * FROM orders;
+----------+-------------+---------+---------+
| order_id | customer_id | product | amount |
+----------+-------------+---------+---------+
| 1 | 1 | Laptop | 1299.99 |
| 2 | 1 | Mouse | 19.99 |
+----------+-------------+---------+---------+
mysql> DELETE FROM order_dv WHERE JSON_VALUE(data, "$._id") = 1;
Query OK, 1 row affected (0.009 sec)
mysql> SELECT * FROM customers;
+-------------+-------+
| customer_id | name |
+-------------+-------+
| 1 | Alice |
+-------------+-------+
mysql> SELECT * FROM orders;
+----------+-------------+---------+---------+
| order_id | customer_id | product | amount |
+----------+-------------+---------+---------+
| 1 | 1 | Laptop | 1299.99 |
+----------+-------------+---------+---------+
要点:
- サブオブジェクトの単一の子データはDELETE操作では削除されません
- DELETE変更タグが設定されていないサブオブジェクトはDELETE操作では削除されません
まとめ
新しいJSON Duality View機能を使用すると、構造化(リレーショナル)データと半構造化(JSON)データの両方を統合できます。 JSON Duality Viewを使用すると、どちらのデータ・モデルを使用しても、アプリケーションで読取りおよび書込みを実行できます。MySQLのJSON Duality ViewのDML機能は、参照整合性やデータ正規化などのリレーショナル・データベースの堅牢なメカニズムが隠蔽された形で、純粋なJSONストアで一般的な問題を解決します。さらに、明示的な変更タグにより、すべての操作(挿入、更新、削除)が意図的かつ安全であることが保証されます。
このブログ投稿では、JSON Duality Viewから生成されたJSONドキュメントに対してDML操作を実行する方法について説明しました。JSON Duality View機能は、MySQL 9.4で使用できるようになりました。JSON Duality View機能を試して、独自のJSONデータに対するDML操作を試すことをお薦めします。
追加情報:
- MySQL JSON Duality Viewリファレンス・マニュアル
- MySQL Heatwaveで利用可能 (無料で利用する)
- MySQL Enterprise Editionで利用可能 (無料でダウンロード / 開発ライセンスで永続利用可能)
- MySQL Community Editionで利用可能 (参照専用ビュー)