MySQL HeatWaveは、トランザクション、分析、機械学習、生成AIサービスを組み合わせた唯一のフルマネージドのMySQLデータベース・サービスです。また、MySQL HeatWave Lakehouseも含まれており、ユーザーはオブジェクト・ストレージ、MySQLデータベース、またはその両方の組合せに格納されているデータを分析できます。ユーザーは、MySQL HeatWaveを利用したアプリケーションを、Oracle Cloud Infrastructure (OCI)、Amazon Web Services (AWS)およびMicrosoft Azureのいずれかのパブリック・クラウドにデプロイできます。

MySQLでは、バージョン9.4のJSON Duality Viewが導入されました。JSON Duality Viewは、JSON中心の開発の柔軟性と、リレーショナル・データベースの堅牢な整合性とコンプライアンスを融合させます。開発者は、MySQLの正規化と参照整合性を放棄することなく、ドキュメントベースのワークフローを構築し、柔軟なJSONコレクションを使用してデータを交換できるようになりました。

JSON Duality Viewでは、従来のSQLまたはREST対応のJSONを使用してデータを操作できます。MySQLは、バックグラウンドでの一貫性と正規化を管理します。これにより、データ重複や非同期参照など、純粋なJSONストアの一般的な問題が解決され、最新の開発チームが期待する俊敏性が維持されます。

この記事では、JSON Duality Viewを作成、変更、表示、使用および削除するための新しいDDL文の概要を示します。また、実践的な例を示し、各操作に関連する主要な構文および検証ルールの概要を示します。JSON Duality Viewは、MySQL 9.4, MySQL HeatWaveマネージド・クラウド・サービスなどで利用できます。

 

リレーショナルなテーブルとJSONオブジェクトのマッピング

JSON Duality Viewを使用する最初のステップは、リレーショナル表が階層型の複数レベルのJSONドキュメントにどのようにマップされるかを定義することです。これは、JSON Duality ViewのCREATE文(DDL)を使用して実現されます。この文は、副問合せを含むネストされたSELECT文を介して、表とそれに対応するJSON構造間の関係を確立します。表とJSONの関係は、新しい組込み関数JSON_DUALITY_OBJECTを使用して指定します。この関数では、表の列をJSONキーに直接マッピングできるため、ドキュメント構造内のルート・オブジェクトとネストされたサブオブジェクトの両方がサポートされます。JSONドキュメントのルートおよびサブオブジェクトをモデル化するCREATE文を策定する方法について説明します:

JSON Duality View CREATE DDL

ルート・オブジェクト: 階層JSONドキュメントのルート・オブジェクトをモデル化するために、CREATE文では単一のSELECT文を使用します。この文には、JSON_DUALITY_OBJECT関数[図のAを参照]へのコールが1つのみ含まれている必要があります。この関数は、FROM句[図のBを参照]で宣言された単一(ルート)表からキー列のペアをマップします。特殊なキー”_id”を使用して、ルート表の主キー列をマップし、生成された各JSONドキュメントに一意の識別子があることを確認します。ルート表の場合、WHERE句は省略する必要があります。

サブオブジェクト: ネストされたレベルをモデル化するために、SELECT副問合せが使用され、それぞれがそれぞれのFROM句に単一の(子)表を使用します。ルート表と同様に、主キー列は子表の副問合せに投影する必要がありますが、主キーをマップするために特別なキーは必要ありません。子表の場合、親表との関係を定義するWHERE句は必須です。関係は、次に説明するように、1対1または1対多のいずれかです:

  • WHERE条件が任意の列から主キーへの等価を表す場合、親表と子表の間に1対1の関係が存在します。結果のJSONドキュメント・サブオブジェクトは、単一の子JSONオブジェクトとして投影されます。単一の子JSONオブジェクト[図のCを参照]は、選択リストおよびFROM句にJSON_DUALITY_OBJECTを含むSELECT文によって定義されます。

  • 1対多の関係の場合、ネストされた子JSONオブジェクト[図のDを参照]は、JSON_DUALITY_OBJECTを含むJSON_ARRAYAGG()を使用して式を選択することで取得されます。WHERE条件は、サブオブジェクトの任意の列の等価に対する親オブジェクトの主キーを表します。WHERE条件では、親オブジェクトごとに複数の子オブジェクトが作成されます。


JSONへのリレーショナル・マッピングに加えて、CREATE JSON RELATIONAL DUALITY VIEW文を使用すると、ユーザーはルート・オブジェクトおよびサブオブジェクトに注釈を付けて読取り/書込み機能を構成できます。使用可能な注釈または変更タグは、JSON_DUALITY_OBJECT句で説明できるINSERT、UPDATEおよびDELETEです。完全な書込みサポートを有効にするには、3つの注釈がすべて必要です。すべての注釈を省略すると、読取り専用ビューが作成されます。一部の注釈のみを指定したり、重複する注釈を使用することはできません。唯一の例外は、単一の子オブジェクト[図のCを参照]の場合で、DELETEタグを指定するとエラーが発生します。詳細は「DMLでのJSON Duality Viewのデータの変更」の記事を参照してください。

JSON Duality Viewの作成

“customers” と “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)
);
 
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);

ベースとなるテーブルに対して複数のJSON Duality Viewが作成可能です。 JSON Duality Viewを作成するには CREATE JSON [RELATIONAL] DUALITY VIEW 文を使います:

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;

JSON Duality Viewを使う

JSON Duality Viewは通常のビューと同じようにSELECT文でデータを参照できます。結果はMongoDBのコレクションと似た構造で返り、”data”という名称の列と行の識別子としての”_id”列が含まれます。またWHERE区を使って下記のように結果をフィルタリングできます。:

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)

JSON Duality Viewのメタデータへのアクセス 

どのようなCREATE VIEW文を使ってビューが作成されたかは、SHOW CREATE VIEW文で確認できます:

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)

JSON Duality Viewのメタデータは INFORMATION_SCHEMA のテーブルにも格納されています。 JSON_DUALITY_VIEWS, JSON_DUALITY_VIEW_TABLES, JSON_DUALITY_VIEW_COLUMNS, および JSON_DUALITY_VIEW_LINKS です。これらのテーブルからは、それぞれの異なるレベルの情報を得ることができます:

  • JSON_DUALITY_VIEWS: 有効性、読取り専用ステータス、ビューでINSERT、UPDATEおよびDELETE操作がサポートされるかどうかなど、ビュー・レベルの情報が含まれます
  • JSON_DUALITY_VIEW_TABLES: ビューで利用されているする各テーブルに関するメタデータを提供します。これには、テーブルの読取り専用ステータス、サポートされているDML操作(INSERT、UPDATE、DELETE)、サブオブジェクトの参照テーブルの詳細、および参照テーブルとの関係が含まれます
  • JSON_DUALITY_VIEW_COLUMNS: 読取り専用ステータス、サポートされているDML操作、関連するJSONキーなど、ビューに利用されているテーブルから投影された列に関する情報を示します
  • JSON_DUALITY_VIEW_LINKS: 結合条件で使用される列(両方のテーブルの結合列の名前および関係のタイプ(ネストまたは外部)など)を示します
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)

JSON Duality Viewの定義を変更

既存のJSON Duality Viewを変更するには、 ALTER JSON DUALITY VIEW文を使用します。変更後の定義はこれまで説明してきた構文とルールに則っている必要があります。ここの例は参照専用だった customer_orders_dv を更新可能に変更しています:  

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)

不要となったJSON Duality Viewは DROP 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)

まとめ

新しいJSON Duality View機能を使用すると、構造化(リレーショナル)データと半構造化(JSON)データの両方を統合できます。 JSON Duality Viewを使用すると、どちらのデータ・モデルを使用しても、アプリケーションで読取りおよび書込みを実行できます。MySQLのJSON Duality ViewのDML機能は、参照整合性やデータ正規化などのリレーショナル・データベースの堅牢なメカニズムが隠蔽された形で、純粋なJSONストアで一般的な問題を解決します。さらに、明示的な変更タグにより、すべての操作(挿入、更新、削除)が意図的かつ安全であることが保証されます。

追加情報: